SheetJS xlsx read and write workbooks in memory: working versions (tested 2026-10-15)

{
  "name": "sheetjs-xlsx-workbook",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "xlsx": "0.18.5"
  },
  "devDependencies": {
    "@types/node": "26.6.5",
    "tsx": "4.23.15"
  }
}

import * as XLSX from "xlsx";

export type Row = { sku: string; qty: number; price: number };

export function rowsToBuffer(rows: Row[]): Buffer {
  const wb = XLSX.utils.book_new();
  const ws = XLSX.utils.json_to_sheet(rows);
  XLSX.utils.book_append_sheet(wb, ws, "Inventory");
  return XLSX.write(wb, { type: "buffer", bookType: "xlsx" }) as Buffer;
}

export function bufferToRows(buf: Buffer): Row[] {
  const wb = XLSX.read(buf, { type: "buffer" });
  const ws = wb.Sheets[wb.SheetNames[0]];
  return XLSX.utils.sheet_to_json<Row>(ws);
}

export function totalsSheet(rows: Row[]): Array<Array<string | number>> {
  const total = rows.reduce((s, r) => s + r.qty * r.price, 0);
  return [
    ["sku", "line_total"],
    ...rows.map((r) => [r.sku, r.qty * r.price]),
    ["TOTAL", total],
  ];
}

export function writeAoa(aoa: Array<Array<string | number>>): Buffer {
  const wb = XLSX.utils.book_new();
  const ws = XLSX.utils.aoa_to_sheet(aoa);
  XLSX.utils.book_append_sheet(wb, ws, "Totals");
  return XLSX.write(wb, { type: "buffer", bookType: "xlsx" }) as Buffer;
}

import { test } from "node:test";
import assert from "node:assert/strict";
import * as XLSX from "xlsx";
import { bufferToRows, rowsToBuffer, totalsSheet, writeAoa } from "../src/workbook.ts";

const rows = [
  { sku: "A-1", qty: 2, price: 10 },
  { sku: "B-2", qty: 1, price: 25 },
];

test("json sheet roundtrip", () => {
  const out = bufferToRows(rowsToBuffer(rows));
  assert.deepEqual(out, rows);
});

test("aoa totals workbook", () => {
  const buf = writeAoa(totalsSheet(rows));
  const wb = XLSX.read(buf, { type: "buffer" });
  const data = XLSX.utils.sheet_to_json<(string | number)[]>(wb.Sheets.Totals, { header: 1 });
  assert.deepEqual(data[0], ["sku", "line_total"]);
  assert.deepEqual(data.at(-1), ["TOTAL", 45]);
});