Repository Public Research preview
1// Builds the committed .xlsx fixtures. Run: node test/fixtures/make-fixtures.mjs2//3// Cached values are computed here with ordinary JavaScript arithmetic, independently of the audit4// engine, so the recalculation tests compare two separate computations. clean-libreoffice.xlsx is a5// third, external oracle: nocache.xlsx opened and saved by LibreOffice (see README.md in this folder).6import { writeFileSync } from "node:fs";7import { HEAD, NS, sheetXml, workbook } from "./xlsx-writer.mjs";89const COLS = ["B", "C", "D", "E", "F"];10const round2 = (x) => Math.sign(x) * Math.round(Math.abs(x) * 100 + 1e-9) / 100;11const sum = (a) => a.reduce((x, y) => x + y, 0);1213/**14 * A five-year margin model: Inputs → Calc → Outputs, with a Controls sheet and a Doc sheet.15 * Options plant defects or build a student submission; cached values always follow the formulas16 * actually written (as Excel would have stored them), except where `stale` says otherwise.17 */18export function model(o = {}) {19 const inp = { price: 100, growth: 0.05, units1: 1000, costRatio: 0.6, tax: 0.25, tol: 0.005 };20 const cached = o.cached !== false;21 const F = (f, v) => (cached ? { f, v } : { f });22 const Inputs = { A1: "Assumption", B1: "Value", C1: "Unit", A2: "Unit price", B2: inp.price, C2: "currency", A3: "Growth", B3: inp.growth, C3: "rate per year", A4: "Units year 1", B4: inp.units1, C4: "units", A5: "Cost ratio", B5: inp.costRatio, C5: "share of revenue", A6: "Tax rate", B6: inp.tax, C6: "share of margin", A7: "Tolerance", B7: inp.tol, C7: "currency" };23 if (o.defects) { Inputs.A8 = "Units year 0"; Inputs.B8 = 0; Inputs.C8 = "units"; }24 const Calc = { A1: "Line", G1: "Total", A2: "Units", A3: "Revenue", A4: "Costs", A5: "Margin", A6: "Tax", A7: "Net" };25 COLS.forEach((c, i) => { Calc[`${c}1`] = `Year ${i + 1}`; });26 const units = [], rev = [], cost = [], margin = [], tax = [], net = [];27 COLS.forEach((c, i) => {28 const p = COLS[i - 1];29 units[i] = i === 0 ? inp.units1 : units[i - 1] * (1 + inp.growth);30 Calc[`${c}2`] = i === 0 ? F("Inputs!$B$4", units[i]) : o.typedUnits ? units[i] : F(`${p}2*(1+Inputs!$B$3)`, units[i]);31 rev[i] = units[i] * inp.price;32 if (o.defects && c === "D") { rev[i] = 110000; Calc.D3 = 110000; } // pasted constant (the formula gives 110250)33 else Calc[`${c}3`] = F(o.submission && c === "C" ? "Inputs!$B$2*C2" : `${c}2*Inputs!$B$2`, rev[i]);34 const ratio = o.submission ? inp.tax : o.defects && c === "E" ? 0.65 : inp.costRatio; // submission: wrong input cell; defects: constant in E435 cost[i] = rev[i] * ratio;36 Calc[`${c}4`] = F(o.submission ? `${c}3*Inputs!$B$6` : o.defects && c === "E" ? "E3*0.65" : `${c}3*Inputs!$B$5`, cost[i]);37 margin[i] = rev[i] - cost[i];38 Calc[`${c}5`] = F(`${c}3-${c}4`, margin[i]);39 tax[i] = round2(margin[i] * inp.tax);40 Calc[`${c}6`] = F(`ROUND(${c}5*TaxRate,2)`, tax[i]);41 net[i] = margin[i] - tax[i];42 Calc[`${c}7`] = F(`${c}5-${c}6`, net[i]);43 });44 const tot = { 3: sum(rev), 4: sum(cost), 5: sum(margin), 6: sum(tax), 7: sum(net) };45 for (const r of [3, 4, 5, 6, 7]) Calc[`G${r}`] = F(`SUM(B${r}:F${r})`, tot[r]);46 if (o.defects) { Calc.A9 = "Interest"; Calc.B9 = F("B10*0.1", 0); Calc.A10 = "Debt"; Calc.B10 = F("B9+1000", 0); } // circular reference47 const Outputs = { A1: "Total revenue", B1: F("Calc!G3", tot[3]), A2: "Total net", B2: F("Calc!G7", tot[7]), A3: "Average margin", A4: "Best year net", A5: "Net margin" };48 Outputs.B3 = F(o.submission ? "SUM(Calc!B5:F5)/5" : "AVERAGE(Calc!B5:F5)", tot[5] / 5);49 if (o.defects) Outputs.B4 = F("MAX(Calc!#REF!)", "#REF!"); else if (!o.submission) Outputs.B4 = F("MAX(Calc!B7:F7)", Math.max(...net));50 Outputs.B5 = o.submission ? F("B2/B1*100", (tot[7] / tot[3]) * 100) : F("IF(B1=0,0,B2/B1)", o.stale ? 0.5 : tot[7] / tot[3]);51 if (o.defects) { Outputs.A6 = "Net per unit of year 0"; Outputs.B6 = F("B2/Inputs!B8", "#DIV/0!"); Outputs.A7 = "Twice that"; Outputs.B7 = F("B6*2", "#DIV/0!"); }52 const ctl = [53 ["Margin = revenue - costs", "Calc!G5", "Calc!G3-Calc!G4", tot[5], tot[3] - tot[4]],54 ["Net = margin - tax", "Calc!G7", "Calc!G5-Calc!G6", tot[7], tot[5] - tot[6]],55 ["Outputs total revenue ties to Calc", "Outputs!B1", "SUM(Calc!B3:F3)", tot[3], sum(rev)],56 ["Revenue = units x price", "Calc!G3", "SUM(Calc!B2:F2)*Inputs!$B$2", tot[3], sum(units) * inp.price],57 ];58 const Controls = { A1: "Control", B1: "Left", C1: "Right", D1: "Difference", E1: "OK", A7: "All checks pass" };59 const oks = ctl.map(([label, l, r, lv, rv], i) => {60 const row = i + 2, ok = Math.abs(lv - rv) < inp.tol;61 Object.assign(Controls, { [`A${row}`]: label, [`B${row}`]: F(l, lv), [`C${row}`]: F(r, rv), [`D${row}`]: F(`B${row}-C${row}`, lv - rv), [`E${row}`]: F(`ABS(D${row})<Inputs!$B$7`, ok) });62 return ok;63 });64 Controls.E7 = F("AND(E2:E5)", oks.every(Boolean));65 const Doc = { A1: "Model", B1: "Five-year margin model", A2: "Version", B2: "1.0.0", A3: "Units", B3: "Currency units; rates as decimals; costs and tax positive", A4: "Structure", B4: "Inputs -> Calc -> Outputs; Controls reconciles; no constant inside a formula" };66 return workbook({67 sheets: [{ name: "Inputs", cells: Inputs }, { name: "Calc", cells: Calc }, { name: "Outputs", cells: Outputs }, { name: "Controls", cells: Controls }, { name: "Doc", cells: Doc }],68 names: [{ name: "Price", ref: "Inputs!$B$2" }, { name: "Growth", ref: "Inputs!$B$3" }, { name: "TaxRate", ref: "Inputs!$B$6" }],69 });70}7172/** Hand-written XML exercising the reader: shared strings (rich text, phonetic run), shared formulas, number formats, hidden sheet, quoted sheet name, names with local scope, whole-column reference. */73export function features() {74 const shared = `${HEAD}<sst xmlns="${NS}" count="4" uniqueCount="4"><si><t>Item</t></si><si><r><rPr><b/></rPr><t>Rich </t></r><r><t xml:space="preserve">text & more</t></r></si><si><t>Qty</t><rPh sb="0" eb="1"><t>IGNORED</t></rPh></si><si><t>Total</t></si></sst>`;75 const styles = `${HEAD}<styleSheet xmlns="${NS}"><numFmts count="1"><numFmt numFmtId="164" formatCode="yyyy\\-mm\\-dd"/></numFmts><fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts><fills count="1"><fill><patternFill patternType="none"/></fill></fills><borders count="1"><border/></borders><cellStyleXfs count="1"><xf numFmtId="0"/></cellStyleXfs><cellXfs count="4"><xf numFmtId="0"/><xf numFmtId="164" applyNumberFormat="1"/><xf numFmtId="10" applyNumberFormat="1"/><xf numFmtId="4" applyNumberFormat="1"/></cellXfs></styleSheet>`;76 const data = `${HEAD}<worksheet xmlns="${NS}"><sheetData>` +77 `<row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>2</v></c><c r="C1" t="s"><v>3</v></c><c r="D1" t="s"><v>1</v></c></row>` +78 `<row r="2"><c r="A2" s="3"><v>10.5</v></c><c r="B2"><v>2</v></c><c r="C2"><f t="shared" ref="C2:C4" si="0">A2*B2+$E$1</f><v>21</v></c><c r="D2" s="1"><v>46000</v></c></row>` +79 `<row r="3"><c r="A3" s="3"><v>20</v></c><c r="B3"><v>3</v></c><c r="C3"><f t="shared" si="0"/><v>60</v></c><c r="D3" s="2"><v>0.125</v></c></row>` +80 `<row r="4"><c><v>30</v></c><c><v>4</v></c><c r="C4"><f t="shared" si="0"/><v>120</v></c><c r="D4" t="b"><v>1</v></c></row>` +81 `<row r="5"><c r="A5" s="1"/><c r="C5"><f>SUM(C2:C4)</f><v>201</v></c><c r="D5" t="str"><f>"a"&"b"</f><v>ab</v></c></row>` +82 `</sheetData><mergeCells count="1"><mergeCell ref="A7:B7"/></mergeCells></worksheet>`;83 const cells2 = { A1: { f: "SUM('My Data'!A:A)", v: 60.5 }, A2: { f: "LocalRate*'My Data'!C5", v: 20.1 }, A3: { f: "VLOOKUP(20,'My Data'!A2:C4,3,FALSE)", v: 60 }, A4: { f: "SUM('My Data'!2:2)", v: 46033.5 }, B1: 0.1 };84 return workbook({85 sheets: [{ name: "My Data", xml: data }, { name: "Report", cells: cells2 }, { name: "Hidden", state: "hidden", cells: { A1: 1 } }],86 names: [{ name: "LocalRate", ref: "Report!$B$1", localSheetId: 1 }, { name: "Block", ref: "'My Data'!$A$2:$B$4" }, { name: "_xlnm.Print_Area", ref: "Report!$A$1:$B$4", localSheetId: 1 }, { name: "Const", ref: "0.5" }],87 styles, sharedStrings: shared,88 });89}9091export const FIXTURES = {92 "clean.xlsx": () => model(),93 "defects.xlsx": () => model({ defects: true }),94 "stale.xlsx": () => model({ stale: true }),95 "nocache.xlsx": () => model({ cached: false }),96 "reference.xlsx": () => model(),97 "submission.xlsx": () => model({ submission: true, typedUnits: true }),98 "features.xlsx": () => features(),99};100101if (import.meta.url === `file://${process.argv[1]}`) {102 for (const [name, make] of Object.entries(FIXTURES)) { const buf = make(); writeFileSync(new URL(name, import.meta.url), buf); console.log(name, buf.length); }103}104export { sheetXml };105