@bilig/excel-import

@bilig/excel-import

CSV/XLSX-to-WorkbookSnapshot import helpers and supported-subset XLSX export helpers for bilig.

Package Status

This package is part of the bilig runtime npm package set. Install it with @bilig/headless when a Node project needs XLSX import, WorkPaper calculation, and XLSX export from the same public package path.

pnpm add @bilig/headless @bilig/excel-import

For a clean first-time smoke test in an empty project:

mkdir bilig-xlsx-smoke && cd bilig-xlsx-smoke
npm init -y
pnpm add @bilig/headless @bilig/excel-import
pnpm dlx tsx smoke.ts

Use this smoke.ts:

import { WorkPaper } from '@bilig/headless'
import { exportXlsx, importXlsx } from '@bilig/excel-import'

const source = WorkPaper.buildFromSheets({
  Inputs: [
    ['Metric', 'Value'],
    ['Customers', 20],
    ['Average revenue', 1200],
  ],
  Summary: [
    ['Metric', 'Value'],
    ['Revenue', '=Inputs!B2*Inputs!B3'],
  ],
})

const xlsxBytes = exportXlsx(source.exportSnapshot())
source.dispose()

const imported = importXlsx(xlsxBytes, 'revenue-model.xlsx')
const workbook = WorkPaper.buildFromSnapshot(imported.snapshot, {
  evaluationTimeoutMs: 30_000,
  useColumnIndex: true,
})

const inputs = requireSheet(workbook, 'Inputs')
const summary = requireSheet(workbook, 'Summary')
const before = numberValue(workbook.getCellValue({ sheet: summary, row: 1, col: 1 }))
workbook.setCellContents({ sheet: inputs, row: 1, col: 1 }, 32)
const after = numberValue(workbook.getCellValue({ sheet: summary, row: 1, col: 1 }))
const roundTrip = importXlsx(exportXlsx(workbook.exportSnapshot()), 'revenue-model-edited.xlsx')
workbook.dispose()

if (before !== 24000 || after !== 38400 || roundTrip.snapshot.sheets.length !== 2) {
  throw new Error(`Unexpected XLSX roundtrip: ${JSON.stringify({ before, after, sheets: roundTrip.snapshot.sheets.length })}`)
}

console.log({ before, after, sheets: roundTrip.snapshot.sheets.map((sheet) => sheet.name) })

function requireSheet(workpaper: ReturnType<typeof WorkPaper.buildFromSnapshot>, sheetName: string): number {
  const sheet = workpaper.getSheetId(sheetName)
  if (sheet === undefined) {
    throw new Error(`Expected sheet "${sheetName}" to exist`)
  }
  return sheet
}

function numberValue(cell: unknown): number {
  const value = typeof cell === 'object' && cell !== null && 'value' in cell ? cell.value : undefined
  if (typeof value === 'number') {
    return value
  }
  throw new Error(`Expected numeric cell value, got ${JSON.stringify(cell)}`)
}

Repository development:

pnpm install
pnpm --filter @bilig/excel-import build
pnpm exec vitest run packages/excel-import/src/__tests__/excel-import.test.ts

XLSX To WorkPaper

import { readFileSync, writeFileSync } from 'node:fs'
import { WorkPaper } from '@bilig/headless'
import { exportXlsx, importXlsx } from '@bilig/excel-import'

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

const firstSheetName = imported.snapshot.sheets[0]?.name
const firstSheet = firstSheetName === undefined ? undefined : workbook.getSheetId(firstSheetName)
if (firstSheet === undefined) throw new Error('Workbook has no sheets')

workbook.setCellContents({ sheet: firstSheet, row: 1, col: 1 }, 150_000)
const recalculated = workbook.getCellDisplayValue({ sheet: firstSheet, row: 1, col: 1 })

writeFileSync('model-edited.xlsx', exportXlsx(workbook.exportSnapshot()))
workbook.dispose()

console.log({ recalculated })

Use WorkPaper.buildFromSnapshot() for imported XLSX files. It preserves the workbook metadata that Excel formulas need, including defined names, table metadata, and structured-reference translations. WorkPaper.buildFromSheets() is intentionally metadata-free. Use workbook.exportSnapshot() with exportXlsx() when exporting a WorkPaper after edits.

Literal Excel error cells such as #N/A, #DIV/0!, #REF!, and #VALUE! are imported as their display text instead of SheetJS numeric error codes.

Hidden XLSX rows and columns are preserved in workbook metadata and exported back into worksheet XML, including hidden column states attached to width metadata.

Workbook calculation properties such as iterative calculation, iteration count and delta, forced recalculation, concurrent calculation, and manual calculation mode are preserved from XLSX <calcPr> metadata on roundtrip.

Worksheet protection elements preserve non-default XML attributes from source workbooks, so protected sheets are not normalized into a different <sheetProtection sheet="1"/> state during no-op roundtrips.

Worksheet printer settings preserve binary xl/printerSettings/*.bin parts, worksheet relationships, and pageSetup relationship links during no-op XLSX roundtrips.

Worksheet <sheetPr> properties preserve non-tabColor code names, outlinePr, and pageSetUpPr metadata during no-op XLSX roundtrips.

Workbook sheet visibility preserves hidden and very hidden worksheet state during no-op XLSX roundtrips.

Cell hyperlinks preserve external URL targets, internal workbook targets, tooltips, and display text during no-op XLSX roundtrips.

CSV Import

import { importCsv } from '@bilig/excel-import'

const imported = importCsv('Account;Amount\n4000;125,50', 'ledger.csv', {
  delimiter: ';',
  decimalSeparator: ',',
})

CSV import auto-detects comma, semicolon, and tab delimiters. Semicolon and tab exports that contain decimal-comma values are parsed as locale accounting CSV by default; pass explicit options when the source format is known. Integer-looking fields with leading zeros are kept as text so account numbers, routing numbers, invoice IDs, and similar identifiers are not silently changed.