Excel .xlsx export and import with ExcelJS: working versions (tested 2026-10-15)

{
  "name": "exceljs-read-write",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "exceljs": "4.4.0"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "24.19.2"
  }
}

import ExcelJS from "exceljs";

export type Sale = { region: string; units: number; price: number; date: Date };

export async function buildReport(sales: Sale[]) {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("Sales", { views: [{ state: "frozen", ySplit: 1 }] });
  ws.columns = [
    { header: "Region", key: "region", width: 14 },
    { header: "Units", key: "units", width: 8 },
    { header: "Price", key: "price", width: 10, style: { numFmt: "$#,##0.00" } },
    { header: "Date", key: "date", width: 12, style: { numFmt: "yyyy-mm-dd" } },
    { header: "Total", key: "total", width: 12, style: { numFmt: "$#,##0.00" } },
  ];
  ws.getRow(1).font = { bold: true };
  sales.forEach((s, i) => {
    const r = i + 2;
    ws.addRow({ ...s, total: { formula: `B${r}*C${r}`, result: s.units * s.price } });
  });
  const last = sales.length + 1;
  ws.addRow({ region: "TOTAL", total: { formula: `SUM(E2:E${last})`, result: sales.reduce((n, s) => n + s.units * s.price, 0) } });
  return Buffer.from(await wb.xlsx.writeBuffer());
}

export async function readSales(buf: Buffer): Promise<Sale[]> {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.load(buf as any);
  const ws = wb.getWorksheet("Sales") ?? wb.worksheets[0];
  const out: Sale[] = [];
  ws.eachRow((row, n) => {
    if (n === 1 || row.getCell(1).value === "TOTAL") return;
    out.push({ region: String(row.getCell(1).value), units: Number(row.getCell(2).value), price: Number(row.getCell(3).value), date: row.getCell(4).value as Date });
  });
  return out;
}

import { test } from "node:test";
import assert from "node:assert/strict";
import ExcelJS from "exceljs";
import { buildReport, readSales } from "../src/xlsx.ts";

const sales = [
  { region: "North", units: 3, price: 9.5, date: new Date(Date.UTC(2026, 9, 1)) },
  { region: "South", units: 2, price: 20, date: new Date(Date.UTC(2026, 9, 2)) },
];

test("round-trips rows", async () => {
  const back = await readSales(await buildReport(sales));
  assert.deepEqual(back.map((s) => [s.region, s.units, s.price, s.date.toISOString().slice(0, 10)]), [["North", 3, 9.5, "2026-10-01"], ["South", 2, 20, "2026-10-02"]]);
});

test("formulas, formats and frozen header are written", async () => {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.load((await buildReport(sales)) as any);
  const ws = wb.getWorksheet("Sales")!;
  assert.deepEqual(ws.getCell("E2").value, { formula: "B2*C2", result: 28.5 });
  assert.equal((ws.getCell("E4").value as any).formula, "SUM(E2:E3)");
  assert.equal(ws.getCell("C2").numFmt, "$#,##0.00");
  assert.equal(ws.getRow(1).font?.bold, true);
  assert.equal((ws.views[0] as any).state, "frozen");
});