Skip to main content

Let Data Soar with AstroJS

AstroJS1 is a site generator. AstroJS projects support ViteJS2 plugins for making custom data sources.

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo uses AstroJS and SheetJS to pull data from a spreadsheet and display the content in an HTML table. We'll explore how to load SheetJS libraries during the AstroJS build process and create static HTML pages.

The "Complete Example" section includes a complete website powered by Apple Numbers spreadsheets.

The following diagram depicts the workbook waltz:

Telemetry

AstroJS enables telemetry by default. The tool has an option to disable telemetry:

npx astro telemetry disable
Tested Deployments

This demo was tested in the following environments:

AstroJSTemplateDate
3.6.5: Starlight 0.14.02024-04-14
4.6.1: Starlight 0.21.52024-04-14

AstroJS has introduced a number of breaking changes in minor releases.

The demos worked as expected with the listed versions on the listed dates.

Integration​

This demo uses "Base64 Loader" from the ViteJS demo.

The ViteJS demo used the query ?b64 to identify files. To play nice with AstroJS, this demo matches the file extensions directly.

Since AstroJS performs per-page processing at build time, it is recommended to use the Base64 string loader to get file data and parse with the SheetJS library in the relevant pages. If the SheetJS operations are performed in frontmatter, only the results will be added to the generated pages!

Loader​

The loader should be added to astro.config.mjs under the vite key.

astro.config.mjs
import { readFileSync } from 'fs';
import { defineConfig } from 'astro/config';
export default defineConfig({
  vite: {
    // this tells astro which extensions to handle
    assetsInclude: ['**/*.numbers', '**/*.xlsx', '**/*.xls', '**/*.xlsb'],

    plugins: [
      { // this plugin presents the data as a Base64 string
        name: "sheet-base64",
        transform(code, id) {
          if(!id.match(/\.(numbers|xlsx)$/)) return;
          var data = readFileSync(id, "base64");
          return `export default '${data}'`;
        }
      }
    ]
  }
});

Types​

For VSCode and VSCodium integration, types can be specified in src/env.d.ts.

This data loader returns Base64 strings:

src/env.d.ts
/// <reference types="astro/client" />
declare module '*.numbers' { const data: string; export default data; }
declare module '*.xlsx'    { const data: string; export default data; }
declare module '*.xls'     { const data: string; export default data; }
declare module '*.xlsb'    { const data: string; export default data; }
// ... (more spreadsheet formats) ...

AstroJS Frontmatter​

Typically projects store files in src/pages. Assuming pres.numbers is stored in the src/data directory in the project, the relative import

import b64 from "../data/pres.numbers"

will return a Base64 string.

The Base64 string can be parsed in the frontmatter using the SheetJS read3 method with the base64 type. The method returns a SheetJS workbook object4.

import { read } from "xlsx";

const wb = read(b64, { type: "base64" });

The workbook object can be post-processed using utility functions. The following example uses the sheet_to_json5 method to generate row objects from the first worksheet in the workbook:

src/pages/index.astro
---
/* -- the code in the frontmatter is only run at build time -- */
import { read, utils } from "xlsx";

/* parse workbook */
import b64 from "../data/pres.numbers";
const wb = read(b64, { type: "base64" });

/* generate row objects */
interface IPresident {
  Name: string;
  Index: number;
}
const data = utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]);
---

Page Body​

The frontmatter used sheet_to_json to create an "array of objects"6. Using standard JSX techniques, the data can be rendered as table rows:

src/pages/index.astro
<html>
  <body>
    <h3>Presidents</h3>
    <table>
      <thead><tr><th>Name</th><th>Index</th></tr></thead>
      {/* Display each row object as a TR within the TBODY element */}
      <tbody>{data.map(row => (
        <tr><td>{row.Name}</td><td>{row.Index}</td></tr>
      ))}</tbody>
    </table>
  </body>
</html>

When built using npx astro build, AstroJS will perform the conversion and emit a simple HTML table without any reference to the existing spreadsheet file!

Complete Example​

AstroJS has introduced a number of breaking changes. If the demo fails, please leave a note

  1. Disable AstroJS telemetry:
npx astro telemetry disable
  1. Create a new site using the starlight template:
npm create astro@latest -- --template starlight --yes ./sheetjs-astro
cd sheetjs-astro

To test an older version of AstroJS, install the specific version of astro and a supported starter template after creating the project.

For major version 3, Starlight must be version 0.14.0:

npm install --force [email protected] @astrojs/[email protected]

The version can be verified by running:

npx astro --version
  1. Fetch the example file pres.numbers:
mkdir -p src/data
curl -Lo src/data/pres.numbers https://docs.sheetjs.com/pres.numbers
  1. Install the SheetJS library:
npm i --save https://cdn.sheetjs.com/xlsx-0.20.2/xlsx-0.20.2.tgz
  1. Append the following lines to src/env.d.ts:
src/env.d.ts (add to end)
/* add to the end of the file */
declare module '*.numbers' { const data: string; export default data; }
declare module '*.xlsx'    { const data: string; export default data; }
  1. Add the highlighted lines to astro.config.mjs:
  • At the top of the script, import readFileSync:
astro.config.mjs (add higlighted lines)
/* import `readFileSync` at the top of the script*/
import { readFileSync } from 'fs';
import { defineConfig } from 'astro/config';
  • In the object argument to defineConfig, add a vite section:
astro.config.mjs (add highlighted lines)
export default defineConfig({
  /* this vite section should be added as a property of the object */
  vite: {
    // this tells astro which extensions to handle
    assetsInclude: ['**/*.numbers', '**/*.xlsx'],

    plugins: [
      { // this plugin presents the data as a Base64 string
        name: "sheet-base64",
        transform(code, id) {
          if(!id.match(/\.(numbers|xlsx)$/)) return;
          var data = readFileSync(id, "base64");
          return `export default '${data}'`;
        }
      }
    ]
  },
  1. Download index.astro and save to src/pages/index.astro (creating a new folder if it does not exist):
mkdir -p src/pages
curl -o src/pages/index.astro https://docs.sheetjs.com/astrojs/index.astro
  1. Remove any index files in the src/content directory:
rm src/content/index.*

This command may show an error:

zsh: no matches found: src/content/index.*

This error can be ignored.

  1. Build the static site:
npx astro build

AstroJS will place the generated site in the dist subfolder.

  1. Start a web server to host the static site:
npx http-server dist

Open a web browser and access the displayed URL ( http://localhost:8080 ). View the page source and confirm that no JS was added to the page. It only contains the content from the file in an HTML table.

If the site shows a Welcome page, inspect the project!

When this demo was first written, the docs template used src/pages/index.astro

In a later test, AstroJS removed the docs template and introduced the starlight template. This template included src/content/index.mdx, which takes priority over src/pages/index.astro.

To resolve this issue, as noted in step 7, remove any index.* files in the src/content folder.

Footnotes​

  1. The official website uses the name "Astro" but most of the tooling uses the name @astrojs. ↩

  2. See "ViteJS" demo for more details and advanced use cases. ↩

  3. See read in "Reading Files" ↩

  4. See "SheetJS Data Model" ↩

  5. See sheet_to_json in "Utilities" ↩

  6. See "Arrays of Objects" in "Utilities" ↩