Skip to content

excel-file-calculation-engine-node.md

Latest commit

 

History

History
170 lines (130 loc) · 6.44 KB

excel-file-calculation-engine-node.md

File metadata and controls

170 lines (130 loc) · 6.44 KB
title Use an Excel file as a calculation engine in Node.js
published true
description Decide whether a Node.js app should use Excel, xlsx-calc, HyperFormula, or @bilig/headless when an uploaded XLSX workbook is meant to calculate backend outputs.
tags typescript, node, excel, xlsx, spreadsheet, formulas
canonical_url https://proompteng.github.io/bilig/excel-file-calculation-engine-node.html
cover_image https://raw.githubusercontent.com/proompteng/bilig/main/docs/assets/github-social-preview.png
image /assets/github-social-preview.png

Use an Excel file as a calculation engine in Node.js

This is the backend workflow people usually mean:

  1. A user or operations team maintains an Excel workbook.
  2. A Node.js route receives request inputs.
  3. The service writes those inputs into known cells.
  4. Formulas produce output cells.
  5. The service stores or returns those outputs.

That can work, but only if the service is honest about what owns calculation. Writing cells into an .xlsx file is not the same thing as recalculating the workbook.

The common public question is not "can Node write an Excel file?" It can. The harder question is: after the service writes inputs into an existing calculator workbook, can it trust the output cells before Excel, LibreOffice, or Graph opens the file? That is the boundary to decide first.

Short answer

Use Excel or LibreOffice automation if exact desktop Excel behavior is the requirement.

Use xlsx-calc if your workbook is already a SheetJS-style object and its formula coverage is enough.

Use HyperFormula if the main requirement is a mature headless formula engine with broad spreadsheet-function coverage.

Use @bilig/headless when the service can treat the workbook as WorkPaper state: write inputs, recalculate, read values back, persist JSON, and import or export XLSX at the boundary.

The decision that matters

The key question is whether the backend must trust the calculated answer before a person opens the file.

If the file is only a report, you can write formulas and ask Excel to recalculate on open.

If the backend accepts, rejects, prices, pays, queues, or stores something based on the calculated value, the backend needs a runtime that recalculates before returning a response.

There are three honest shapes:

Shape What owns calculation When it fits
File generation Excel later You only need a report file, and stale cached values are acceptable until open.
Hosted spreadsheet automation Excel, LibreOffice, or Graph Exact Excel behavior is worth the latency, auth, and operational dependency.
Local WorkPaper runtime Your Node process The API must write inputs, read outputs, persist state, and respond now.

Avoid the fourth shape: treating a file writer's cached formula value as if it were a fresh calculation result.

Minimal Bilig shape

import { readFile, writeFile } from 'node:fs/promises'
import { WorkPaper } from '@bilig/headless'
import { exportXlsx, importXlsx } from '@bilig/headless/xlsx'

const imported = importXlsx(await readFile('vehicle-calculator.xlsx'), 'vehicle-calculator.xlsx')
const workbook = WorkPaper.buildFromSnapshot(imported.snapshot)

const inputs = workbook.getSheetId('Inputs')
const quote = workbook.getSheetId('Quote')
if (inputs === undefined || quote === undefined) {
  throw new Error('Expected Inputs and Quote sheets')
}

workbook.setCellContents({ sheet: inputs, row: 1, col: 1 }, 'Toyota')
workbook.setCellContents({ sheet: inputs, row: 2, col: 1 }, 2024)
workbook.setCellContents({ sheet: inputs, row: 3, col: 1 }, 18_500)

const output = workbook.getCellValue({ sheet: quote, row: 10, col: 1 })
const edited = exportXlsx(workbook.exportSnapshot())
await writeFile('vehicle-calculator-edited.xlsx', edited)

console.log({ output })

In a production service, wrap this in a small adapter. Hard-code the input and output cell contract, then test it with fixtures. Do not let arbitrary workbook layout become invisible application logic.

The adapter should be boring enough to review in a code review:

const quoteContract = {
  inputs: {
    make: 'Inputs!B2',
    year: 'Inputs!B3',
    price: 'Inputs!B4',
  },
  outputs: {
    decision: 'Quote!B11',
  },
} as const

That is the maintainable version of "use Excel as a calculation engine": the spreadsheet owns formulas, but the service owns the cell contract, fixture tests, and rollback path.

Run the maintained XLSX proof

git clone https://github.com/proompteng/bilig.git
cd bilig/examples/xlsx-recalculation-node
npm install
npm start

The example imports an XLSX pricing workbook, writes inputs, reads recalculated outputs, exports XLSX, reimports the exported file, and checks that formulas survived the round trip.

Expected checks:

{
  "decisionChanged": true,
  "exportedReimportMatchesAfter": true,
  "formulasSurvivedXlsxRoundTrip": true,
  "verified": true
}

Production checks

Before using any spreadsheet runtime as a backend decision path, test:

  • the exact cells the API writes;
  • the exact cells the API reads;
  • unsupported formulas;
  • stale cached formula values in source XLSX files;
  • shared formulas and copied formulas;
  • exported XLSX reimport;
  • golden fixtures opened in Excel or LibreOffice.

If those checks are too heavy, the workbook is probably still a human artifact, not a backend calculation engine.

Related

If this helps you avoid a fragile Excel worker in a backend service, star or bookmark the repository: https://github.com/proompteng/bilig/stargazers.