xlsx-template formula recalculation in Node.js

xlsx-template is good at one job: take an XLSX template, substitute values, and write the workbook back out.

It does not turn the generated workbook into a freshly calculated model. If a template writes Sheet1!A1 and another sheet has =Sheet1!A1, the generated file can still require Excel, LibreOffice, or another formula engine before an API can trust the formula result.

That is not a templating bug. It is a calculation-boundary problem.

The production split

Keep the workflow explicit:

Use xlsx-template to fill the XLSX template.
Run a formula runtime only for the cells that the backend must read before a human opens the file.
Add a reduced fixture for the exact input cells, output cells, and formulas.
Export XLSX only after the backend decision has already been made from a verified readback path.

For tiny formula sets, xlsx-calc may be enough. For exact Excel behavior, use Excel, LibreOffice, or Microsoft Graph. Use @bilig/headless when the service can own the workbook state locally and needs write, recalculate, readback, JSON persistence, and restore checks.

Bilig-shaped check

After xlsx-template writes result.xlsx, import the workbook into WorkPaper and read the output cells that matter to the API:

import { readFileSync } from 'node:fs'
import { WorkPaper } from '@bilig/headless'
import { importXlsx } from '@bilig/headless/xlsx'

const imported = importXlsx(new Uint8Array(readFileSync('result.xlsx')), 'result.xlsx')
const workbook = WorkPaper.buildFromSnapshot(imported.snapshot, {
  evaluationTimeoutMs: 30_000,
  useColumnIndex: true,
})

try {
  const sheetId = workbook.getSheetId('Sheet2')
  if (sheetId === undefined) {
    throw new Error('Missing Sheet2')
  }

  const value = workbook.getCellDisplayValue({ sheet: sheetId, row: 1, col: 1 })
  console.log({ outputCell: 'Sheet2!B2', value })
} finally {
  workbook.dispose()
}

The important part is the boundary: the API does not read stale XLSX cached formula values and hope a later Excel open fixes them.

Fixture-first debugging

Before changing a production template pipeline, reduce the workbook to:

the template input cells;
the formula output cells;
the expected output from Excel, LibreOffice, or a manual check;
any formula families that must be supported.

Then generate a local report without uploading the workbook:

curl -fsSLo formula-clinic-report.ts \
  https://proompteng.github.io/bilig/formula-clinic-report.ts
npx tsx formula-clinic-report.ts ./reduced-template-output.xlsx \
  --cells "Sheet2!B2"

Paste the report into the formula bug clinic if the reduced case fails.

When not to use Bilig

Do not use Bilig as a drop-in replacement for xlsx-template.

If your only deliverable is a styled XLSX file and a person will open it before the value matters, keep the current template pipeline and set recalculation on open. If exact Excel compatibility, macros, pivots, charts, or full formatting fidelity are the product, keep Excel/LibreOffice/Microsoft Graph in the loop.

Use Bilig when the backend owns the calculation decision and stale cached XLSX values are the thing you are trying to remove.

Source issue: https://github.com/optilude/xlsx-template/issues/192

xlsx-template formula recalculation in Node.js

The production split

Bilig-shaped check

Fixture-first debugging

When not to use Bilig

Related