Creating a spreadsheet from scratch

In this guide we’ll use Apiary to create a simple spreadsheet with values, formatting, and formulas, and then export it to an Excel .xlsx file. We’ll use Node.js and npm on a Unix-like system. Node 18+ is required, 22+ recommended.

1. Let’s start by creating a project:

   mkdir spreadsheet-from-scratch
   cd spreadsheet-from-scratch
   npm init -y

2. Configure npm to access private packages in the @grid-is scope. Create a .npmrc file:

   cat << 'EOF' > .npmrc
   @grid-is:registry=https://registry.npmjs.org
   //registry.npmjs.org:_authToken=${NPM_TOKEN}
   EOF

   Note

   This assumes you already have an environment variable, NPM_TOKEN, that gives you access to our packages on npmjs.com. If you don’t have such a token, please ask us for access.

3. Now we can add the project’s dependencies:

   • @grid-is/apiary (the spreadsheet engine)
   • @grid-is/csf2xlsx (to export the file to .xlsx when we’re finished)
   • jszip (we need zip support to create .xlsx files, which are a zipped directory of XML files)

   npm add @grid-is/apiary @grid-is/csf2xlsx jszip

4. Now let’s write the code to create a spreadsheet, add data and formatting, and export to .xlsx. Create an index.js file with:

   import { Model } from "@grid-is/apiary";
   import { toXlsx } from "@grid-is/csf2xlsx";
   import JSZip from "jszip";
   import { writeFile } from "node:fs/promises";
   
   // Init the engine (required for the WASM-based formula parser).
   await Model.preconditions;
   
   // Create a model containing a workbook with a single empty sheet.
   const model = new Model();
   const workbook = model.addWorkbook({
     name: "essentials.xlsx",
     sheets: [{ name: "Sheet1", cells: {} }],
   });
   
   // Add shopping item costs.
   workbook.write("A1", "Cost");
   workbook.write("A2", 5.49);
   workbook.write("A3", 1.6);
   workbook.write("A4", 1.15);
   
   // Oops, we forgot to add the first column with the names of our shopping items.
   workbook.insertColumns("Sheet1", 0, 1, false); // Insert a column left of column A.
   workbook.write("A1", "Item");
   workbook.write("A2", "Teabags");
   workbook.write("A3", "Malt Loaf");
   workbook.write("A4", "Crumpets");
   
   // Add some basic formatting to the header cells.
   for (const id of ["A1", "B1"]) {
     workbook.writeCellData(id, {
       s: {
         bold: true,
         "border-bottom-color": "#000",
         "border-bottom-style": "medium",
       },
     });
   }
   
   // Write a formula to sum up costs. Add number formatting while we're at it.
   workbook.writeCellData("E1", { f: "=SUM(B:B)", z: "£#,##0.00" });
   // Label that formula in the preceding cell, adding value and formatting in one go.
   workbook.writeCellData("D1", { v: "Total", s: { bold: true } });
   
   // We forgot butter! We can't have crumpets without butter!
   workbook.insertRows("Sheet1", 3, 1, false); // Insert a row above the last item.
   model.writeMultiple([
     // Write both cells in one op.
     ["A4", "Butter"],
     ["B4", 2.85],
   ]);
   
   // Add number formatting to cells in the "Cost" column.
   for (const id of ["B2", "B3", "B4", "B5"]) {
     workbook.writeCellData(id, { z: "£#,##0.00" });
   }
   
   // Export our handiwork to an .xlsx file.
   const container = new JSZip();
   toXlsx(workbook.toCSF(), container);
   const buffer = await container.generateAsync({
     type: "nodebuffer",
     compression: "DEFLATE",
     compressionOptions: { level: 9 },
   });
   await writeFile("essentials.xlsx", buffer);

5. Run the script using Node:

   node --experimental-wasm-modules index.js

   Once that’s complete you’ll see essentials.xlsx in your working directory.