Addison NuteHudson, NH

Own it · example handover · app/

src/xlsx/xlsx.ts

← All files · the demo this builds 

/**
 * Workbook description → real .xlsx, in the browser, on click.
 *
 * The same declarative description that renders the on-screen grid is
 * serialized here into Office Open XML — so the download always matches
 * the demo byte-for-byte in content: the hidden tab is actually hidden
 * (workbook.xml sheet state), the #REF! formulas are actually broken
 * (cached error values with the formula preserved for the formula bar),
 * merged cells actually merge, trailing spaces survive via
 * xml:space="preserve", and the mixed date column really is a mix of
 * date-serial cells and typed text. Nothing is painted on.
 *
 * Scope: exactly the SpreadsheetML this workbook needs — inline strings,
 * shared styles built from the same style table as the grid renderer,
 * built-in number formats (14 = m/d/yyyy, 4 = #,##0.00). No charts, no
 * shared-string table, no theme. Excel and LibreOffice open it clean.
 */

import { parseAddr, type WbCell, type WbSheet, type WbStyle, type Workbook } from "@portfolio/fabricator";
import { makeZip } from "./zip.js";

const XML_HEAD = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\r\n';

function esc(s: string): string {
  return s.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;").replace(/"/g, "&quot;");
}

// ——— styles.xml ————————————————————————————————————————————————

const FILL_ARGB: Record<string, string> = {
  yellow: "FFFFF066",
  header: "FFD9D9D9",
  title: "FFEDEDE3",
};

function stylesXml(styles: WbStyle[]): string {
  // fonts and fills are deduped per style table entry; cellXfs maps 1:1 to
  // the style indices the grid uses, so s= in the sheets needs no mapping
  const fonts: string[] = [];
  const fontId = new Map<string, number>();
  const fills: string[] = ['<fill><patternFill patternType="none"/></fill>', '<fill><patternFill patternType="gray125"/></fill>'];
  const fillId = new Map<string, number>();

  const fontFor = (st: WbStyle) => {
    const key = `${st.bold ? "b" : ""}${st.italic ? "i" : ""}${st.strike ? "s" : ""}${st.color ?? ""}${st.size ?? 11}`;
    if (!fontId.has(key)) {
      const color = st.color === "red" ? "FFC00000" : st.color === "gray" ? "FF808080" : "FF1A1A1A";
      fonts.push(
        `<font>${st.bold ? "<b/>" : ""}${st.italic ? "<i/>" : ""}${st.strike ? "<strike/>" : ""}<sz val="${st.size ?? 11}"/><color rgb="${color}"/><name val="Calibri"/></font>`,
      );
      fontId.set(key, fonts.length - 1);
    }
    return fontId.get(key)!;
  };
  const fillFor = (st: WbStyle) => {
    if (!st.fill) return 0;
    if (!fillId.has(st.fill)) {
      fills.push(`<fill><patternFill patternType="solid"><fgColor rgb="${FILL_ARGB[st.fill]}"/><bgColor indexed="64"/></patternFill></fill>`);
      fillId.set(st.fill, fills.length - 1);
    }
    return fillId.get(st.fill)!;
  };

  const xfs = styles.map((st) => {
    const f = fontFor(st);
    const fl = fillFor(st);
    const numFmt = st.fmt === "date" ? 14 : st.fmt === "money" ? 4 : 0;
    const align = st.align ? `<alignment horizontal="${st.align}"${st.align === "center" ? ' vertical="center"' : ""}/>` : "";
    return `<xf numFmtId="${numFmt}" fontId="${f}" fillId="${fl}" borderId="0" xfId="0"${numFmt ? ' applyNumberFormat="1"' : ""}${f ? ' applyFont="1"' : ""}${fl ? ' applyFill="1"' : ""}${align ? ' applyAlignment="1"' : ""}>${align}</xf>`;
  });

  return (
    XML_HEAD +
    '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
    `<fonts count="${fonts.length}">${fonts.join("")}</fonts>` +
    `<fills count="${fills.length}">${fills.join("")}</fills>` +
    '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>' +
    '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>' +
    `<cellXfs count="${xfs.length}">${xfs.join("")}</cellXfs>` +
    '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>' +
    "</styleSheet>"
  );
}

// ——— worksheets ————————————————————————————————————————————————

function cellXml(addr: string, c: WbCell): string {
  const s = c.s ? ` s="${c.s}"` : "";
  if (c.t === "e") {
    // a genuinely broken formula: cached error + the formula itself
    return `<c r="${addr}"${s} t="e">${c.f ? `<f>${esc(c.f)}</f>` : ""}<v>${esc(String(c.v ?? "#REF!"))}</v></c>`;
  }
  if (c.t === "n" || c.t === "d") {
    return `<c r="${addr}"${s}>${c.f ? `<f>${esc(c.f)}</f>` : ""}<v>${c.v}</v></c>`;
  }
  const text = String(c.v ?? "");
  const preserve = text !== text.trim() ? ' xml:space="preserve"' : "";
  return `<c r="${addr}"${s} t="inlineStr"><is><t${preserve}>${esc(text)}</t></is></c>`;
}

function sheetXml(sheet: WbSheet): string {
  // group cells by row, ascending — the order Excel expects
  const rows = new Map<number, Array<[number, string, WbCell]>>();
  for (const [a, cell] of Object.entries(sheet.cells)) {
    const { c, r } = parseAddr(a);
    const list = rows.get(r) ?? [];
    list.push([c, a, cell]);
    rows.set(r, list);
  }
  const rowXml = [...rows.entries()]
    .sort((x, y) => x[0] - y[0])
    .map(([r, cells]) => {
      const cs = cells.sort((x, y) => x[0] - y[0]).map(([, a, cell]) => cellXml(a, cell));
      return `<row r="${r + 1}">${cs.join("")}</row>`;
    })
    .join("");

  const cols = sheet.cols
    ? `<cols>${sheet.cols.map((w, i) => `<col min="${i + 1}" max="${i + 1}" width="${w}" customWidth="1"/>`).join("")}</cols>`
    : "";
  const merges =
    sheet.merges && sheet.merges.length > 0
      ? `<mergeCells count="${sheet.merges.length}">${sheet.merges.map((m) => `<mergeCell ref="${m}"/>`).join("")}</mergeCells>`
      : "";

  return (
    XML_HEAD +
    '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
    '<sheetViews><sheetView workbookViewId="0"/></sheetViews>' +
    cols +
    `<sheetData>${rowXml}</sheetData>` +
    merges +
    "</worksheet>"
  );
}

// ——— package parts ————————————————————————————————————————————

export async function workbookToXlsx(wb: Workbook, stamp: Date): Promise<Uint8Array> {
  const enc = new TextEncoder();
  const n = wb.sheets.length;

  const contentTypes =
    XML_HEAD +
    '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' +
    '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' +
    '<Default Extension="xml" ContentType="application/xml"/>' +
    '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' +
    wb.sheets.map((_, i) => `<Override PartName="/xl/worksheets/sheet${i + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`).join("") +
    '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>' +
    '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>' +
    '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>' +
    "</Types>";

  const rootRels =
    XML_HEAD +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>' +
    '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>' +
    '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>' +
    "</Relationships>";

  const iso = stamp.toISOString().replace(/\.\d{3}Z$/, "Z");
  const core =
    XML_HEAD +
    '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' +
    "<dc:creator>Granite State Mechanical</dc:creator>" +
    "<cp:lastModifiedBy>Dottie Perron</cp:lastModifiedBy>" +
    `<dcterms:created xsi:type="dcterms:W3CDTF">${iso}</dcterms:created>` +
    `<dcterms:modified xsi:type="dcterms:W3CDTF">${iso}</dcterms:modified>` +
    "</cp:coreProperties>";

  const app =
    XML_HEAD +
    '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">' +
    "<Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity>" +
    "</Properties>";

  const workbookXml =
    XML_HEAD +
    '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">' +
    `<bookViews><workbookView activeTab="${wb.activeSheet}"/></bookViews>` +
    "<sheets>" +
    wb.sheets
      .map((s, i) => `<sheet name="${esc(s.name)}" sheetId="${i + 1}"${s.hidden ? ' state="hidden"' : ""} r:id="rId${i + 1}"/>`)
      .join("") +
    "</sheets></workbook>";

  const workbookRels =
    XML_HEAD +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    wb.sheets
      .map((_, i) => `<Relationship Id="rId${i + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${i + 1}.xml"/>`)
      .join("") +
    `<Relationship Id="rId${n + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>` +
    "</Relationships>";

  const entries = [
    { name: "[Content_Types].xml", data: enc.encode(contentTypes) },
    { name: "_rels/.rels", data: enc.encode(rootRels) },
    { name: "docProps/core.xml", data: enc.encode(core) },
    { name: "docProps/app.xml", data: enc.encode(app) },
    { name: "xl/workbook.xml", data: enc.encode(workbookXml) },
    { name: "xl/_rels/workbook.xml.rels", data: enc.encode(workbookRels) },
    { name: "xl/styles.xml", data: enc.encode(stylesXml(wb.styles)) },
    ...wb.sheets.map((s, i) => ({ name: `xl/worksheets/sheet${i + 1}.xml`, data: enc.encode(sheetXml(s)) })),
  ];

  return makeZip(entries, stamp);
}