SPB Git

Repository Public Research preview

xlsx-writer.mjs 7.3 KB 74 lines javascript

1// Minimal, deterministic .xlsx writer used only to build test fixtures.2// Unlike openpyxl, it can store a cached value next to each formula, which the audit tools need.3import { deflateRawSync } from "node:zlib";45const CRC = (() => { const t = new Uint32Array(256); for (let n = 0; n < 256; n++) { let c = n; for (let k = 0; k < 8; k++) c = c & 1 ? 0xedb88320 ^ (c >>> 1) : c >>> 1; t[n] = c >>> 0; } return t; })();6export function crc32(buf) { let c = 0xffffffff; for (const b of buf) c = CRC[(c ^ b) & 0xff] ^ (c >>> 8); return (c ^ 0xffffffff) >>> 0; }78/**9 * entries: [{ name, data: Buffer|string, store?: boolean, declaredSize?: number, flags?: number }]10 * `declaredSize` lets a test write a header that lies about the uncompressed size.11 */12export function zip(entries) {13  const locals = [], central = [];14  let offset = 0;15  for (const e of entries) {16    const data = Buffer.isBuffer(e.data) ? e.data : Buffer.from(e.data, "utf8"), name = Buffer.from(e.name, "utf8");17    const body = e.store ? data : deflateRawSync(data, { level: 9 }), method = e.store ? 0 : 8, usize = e.declaredSize ?? data.length, crc = crc32(data);18    const head = Buffer.alloc(30);19    head.writeUInt32LE(0x04034b50, 0); head.writeUInt16LE(20, 4); head.writeUInt16LE(e.flags ?? 0, 6); head.writeUInt16LE(method, 8);20    head.writeUInt16LE(0, 10); head.writeUInt16LE(0x21, 12); // fixed timestamp: 1980-01-01, so output is reproducible21    head.writeUInt32LE(crc, 14); head.writeUInt32LE(body.length, 18); head.writeUInt32LE(usize >>> 0, 22); head.writeUInt16LE(name.length, 26);22    const cd = Buffer.alloc(46);23    cd.writeUInt32LE(0x02014b50, 0); cd.writeUInt16LE(20, 4); cd.writeUInt16LE(20, 6); cd.writeUInt16LE(e.flags ?? 0, 8); cd.writeUInt16LE(method, 10);24    cd.writeUInt16LE(0, 12); cd.writeUInt16LE(0x21, 14); cd.writeUInt32LE(crc, 16); cd.writeUInt32LE(body.length, 20); cd.writeUInt32LE(usize >>> 0, 24);25    cd.writeUInt16LE(name.length, 28); cd.writeUInt32LE(offset, 42);26    locals.push(head, name, body); central.push(cd, name);27    offset += 30 + name.length + body.length;28  }29  const cdBuf = Buffer.concat(central), end = Buffer.alloc(22);30  end.writeUInt32LE(0x06054b50, 0); end.writeUInt16LE(entries.length, 8); end.writeUInt16LE(entries.length, 10); end.writeUInt32LE(cdBuf.length, 12); end.writeUInt32LE(offset, 16);31  return Buffer.concat([...locals, cdBuf, end]);32}3334const esc = (s) => String(s).replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;").replace(/"/g, "&quot;");35const colNum = (s) => [...s].reduce((n, ch) => n * 26 + ch.charCodeAt(0) - 64, 0);36const HEAD = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n';37const NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main", REL = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";3839function cellXml(ref, c) {40  if (c !== null && typeof c === "object" && "xml" in c) return c.xml;41  if (typeof c === "number") return `<c r="${ref}"><v>${c}</v></c>`;42  if (typeof c === "boolean") return `<c r="${ref}" t="b"><v>${c ? 1 : 0}</v></c>`;43  if (typeof c === "string") return `<c r="${ref}" t="inlineStr"><is><t xml:space="preserve">${esc(c)}</t></is></c>`;44  const f = `<f>${esc(c.f)}</f>`, v = c.v;45  if (v === undefined) return `<c r="${ref}">${f}</c>`; // no cached value, as a library that cannot calculate would write46  if (typeof v === "number") return `<c r="${ref}">${f}<v>${v}</v></c>`;47  if (typeof v === "boolean") return `<c r="${ref}" t="b">${f}<v>${v ? 1 : 0}</v></c>`;48  if (typeof v === "string" && v.startsWith("#")) return `<c r="${ref}" t="e">${f}<v>${esc(v)}</v></c>`;49  return `<c r="${ref}" t="str">${f}<v>${esc(v)}</v></c>`;50}5152export function sheetXml(cells) {53  const rows = new Map();54  for (const [ref, c] of Object.entries(cells)) { const m = /^([A-Z]+)(\d+)$/.exec(ref); const r = Number(m[2]); if (!rows.has(r)) rows.set(r, []); rows.get(r).push([colNum(m[1]), ref, c]); }55  const body = [...rows].sort((a, b) => a[0] - b[0]).map(([r, list]) => `<row r="${r}">${list.sort((a, b) => a[0] - b[0]).map(([, ref, c]) => cellXml(ref, c)).join("")}</row>`).join("");56  return `${HEAD}<worksheet xmlns="${NS}"><sheetData>${body}</sheetData></worksheet>`;57}5859const STYLES = `${HEAD}<styleSheet xmlns="${NS}"><fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></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="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs></styleSheet>`;6061/** { sheets: [{ name, state?, cells: { A1: 1 | "text" | true | { f, v? } | { xml } }, xml? }], names?: [{ name, ref, localSheetId? }], extra?: [{ name, data }], styles?, sharedStrings? } */62export function workbook({ sheets, names = [], extra = [], styles = STYLES, sharedStrings = null }) {63  const ct = `${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"/>${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"/>${sharedStrings ? '<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>' : ""}</Types>`;64  const rels = `${HEAD}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="${REL}/officeDocument" Target="xl/workbook.xml"/></Relationships>`;65  const wb = `${HEAD}<workbook xmlns="${NS}" xmlns:r="${REL}"><sheets>${sheets.map((s, i) => `<sheet name="${esc(s.name)}" sheetId="${i + 1}"${s.state ? ` state="${s.state}"` : ""} r:id="rId${i + 1}"/>`).join("")}</sheets>${names.length ? `<definedNames>${names.map((n) => `<definedName name="${esc(n.name)}"${n.localSheetId !== undefined ? ` localSheetId="${n.localSheetId}"` : ""}>${esc(n.ref)}</definedName>`).join("")}</definedNames>` : ""}</workbook>`;66  const wbRels = `${HEAD}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">${sheets.map((_, i) => `<Relationship Id="rId${i + 1}" Type="${REL}/worksheet" Target="worksheets/sheet${i + 1}.xml"/>`).join("")}<Relationship Id="rId${sheets.length + 1}" Type="${REL}/styles" Target="styles.xml"/>${sharedStrings ? `<Relationship Id="rId${sheets.length + 2}" Type="${REL}/sharedStrings" Target="sharedStrings.xml"/>` : ""}</Relationships>`;67  return zip([68    { name: "[Content_Types].xml", data: ct }, { name: "_rels/.rels", data: rels }, { name: "xl/workbook.xml", data: wb }, { name: "xl/_rels/workbook.xml.rels", data: wbRels },69    ...sheets.map((s, i) => ({ name: `xl/worksheets/sheet${i + 1}.xml`, data: s.xml ?? sheetXml(s.cells) })), { name: "xl/styles.xml", data: styles },70    ...(sharedStrings ? [{ name: "xl/sharedStrings.xml", data: sharedStrings }] : []), ...extra,71  ]);72}73export { HEAD, NS };74
Files main
Menu

Theme