Own it · example handover · app/

test/xlsx.test.ts

← All files · the demo this builds

/**
 * The downloadable workbook must be a real .xlsx with the rot intact —
 * hidden tab actually hidden, #REF! actually broken, merges actually
 * merged, trailing spaces preserved. These tests unzip the writer's
 * output (fflate, dev-only) and read the XML like a parser would.
 *
 * A copy is also written to the OS temp dir so the repo's PowerShell
 * smoke test can open it in real Excel via COM and prove there is no
 * repair dialog. (See BUILD-NOTES.md, P22 verification.)
 */

import { tmpdir } from "node:os";
import { join } from "node:path";
import { writeFileSync } from "node:fs";
import { describe, expect, it } from "vitest";
import { unzipSync } from "fflate";
import { DEMO_SEED, makeGsmDemo } from "@portfolio/fabricator";
import { workbookToXlsx } from "../src/xlsx/xlsx.js";

const NOW = new Date(2026, 5, 11, 12);
const demo = makeGsmDemo(DEMO_SEED, NOW);
const bytes = await workbookToXlsx(demo.workbook, NOW);
const parts = unzipSync(bytes); // throws on bad CRC / bad structure
const text = (name: string) => new TextDecoder().decode(parts[name]);

writeFileSync(join(tmpdir(), "p22-gsm-smoke.xlsx"), bytes);

describe("the package", () => {
  it("is a valid zip holding exactly the OOXML parts a reader expects", () => {
    const names = Object.keys(parts);
    expect(names).toContain("[Content_Types].xml");
    expect(names).toContain("_rels/.rels");
    expect(names).toContain("xl/workbook.xml");
    expect(names).toContain("xl/_rels/workbook.xml.rels");
    expect(names).toContain("xl/styles.xml");
    for (let i = 1; i <= 9; i++) expect(names).toContain(`xl/worksheets/sheet${i}.xml`);
    // every worksheet is declared in [Content_Types].xml
    const ct = text("[Content_Types].xml");
    for (let i = 1; i <= 9; i++) expect(ct).toContain(`/xl/worksheets/sheet${i}.xml`);
  });

  it("stays a sane download size", () => {
    expect(bytes.length).toBeGreaterThan(5_000);
    expect(bytes.length).toBeLessThan(400_000);
  });
});

describe("the rot survives serialization", () => {
  it("Mike's copy is genuinely hidden; all nine tabs are present", () => {
    const wb = text("xl/workbook.xml");
    const sheets = wb.match(/<sheet /g) ?? [];
    expect(sheets).toHaveLength(9);
    expect(wb).toMatch(/<sheet name="Mike(&apos;|')s copy"[^/]*state="hidden"/);
    expect(wb).toContain(`name="OLD - DO NOT USE"`);
    // opens on the jobs tab
    expect(wb).toContain(`activeTab="${demo.landmarks.jobsSheet}"`);
  });

  it("#REF! formulas are broken formulas, not painted text", () => {
    const jobs = text(`xl/worksheets/sheet${demo.landmarks.jobsSheet + 1}.xml`);
    const errs = jobs.match(/<c r="L\d+" t="e"><f>[^<]+#REF!<\/f><v>#REF!<\/v><\/c>/g) ?? [];
    expect(errs.length).toBeGreaterThanOrEqual(4);
    // and the healthy rows still carry live formulas with cached values
    expect(jobs).toMatch(/<f>H\d+-I\d+<\/f><v>-?[\d.]+<\/v>/);
  });

  it("merged cells, mixed dates, and trailing spaces all make it through", () => {
    const jobs = text(`xl/worksheets/sheet${demo.landmarks.jobsSheet + 1}.xml`);
    expect(jobs).toContain("<mergeCell ref=\"A1:L1\"/>");
    const sched = text("xl/worksheets/sheet1.xml");
    expect((sched.match(/<mergeCell /g) ?? []).length).toBeGreaterThanOrEqual(4);
    // date soup: serial date cells and typed-text dates in the same column
    const serialDates = jobs.match(/<c r="F\d+" s="4"><v>\d{5}<\/v><\/c>/g) ?? [];
    const textDates = jobs.match(/<c r="F\d+"[^>]*t="inlineStr"/g) ?? [];
    expect(serialDates.length).toBeGreaterThanOrEqual(5);
    expect(textDates.length).toBeGreaterThanOrEqual(5);
    const lookup = text("xl/worksheets/sheet7.xml");
    expect(lookup).toContain('xml:space="preserve"');
    expect(lookup).toContain("Done </t>");
  });

  it("the lying Total is a bare typed number — no formula behind it", () => {
    const jobs = text(`xl/worksheets/sheet${demo.landmarks.jobsSheet + 1}.xml`);
    const total = demo.workbook.sheets[demo.landmarks.jobsSheet]!.cells[demo.landmarks.totalCell]!;
    expect(jobs).toContain(`<c r="${demo.landmarks.totalCell}" s="9"><v>${total.v}</v></c>`);
  });

  it("real-world punctuation survives: apostrophes intact, ampersands escaped once", () => {
    const jobs = text(`xl/worksheets/sheet${demo.landmarks.jobsSheet + 1}.xml`);
    const withAmp = demo.data.jobs.find((j) => j.customer.includes("&"));
    if (withAmp) expect(jobs).toContain(withAmp.customer.replace("&", "&amp;"));
    expect(jobs).not.toContain("&amp;amp;");
    expect(text("xl/workbook.xml")).toContain("Mike's copy");
    const contacts = text("xl/worksheets/sheet5.xml");
    expect(contacts).toContain("Dottie Perron");
  });

  it("output is deterministic for a pinned seed and day", async () => {
    const again = await workbookToXlsx(makeGsmDemo(DEMO_SEED, NOW).workbook, NOW);
    expect(again.length).toBe(bytes.length);
    expect(Buffer.from(again).equals(Buffer.from(bytes))).toBe(true);
  });
});