Recipes

// Open a workbook and walk every cell on the first sheet.

import { loadWorkbook } from 'xlsx-kit/io';
import { fromFile } from 'xlsx-kit/node';

const wb = await loadWorkbook(fromFile('input.xlsx'));
const first = wb.sheets[0];
if (first?.kind === 'worksheet') {
  for (const row of first.sheet.rows.values()) {
    for (const cell of row.values()) {
      console.log(`${cell.row},${cell.col}: ${String(cell.value)}`);
    }
  }
}

// Read an xlsx, mutate one cell, write it back.
//
// This file is imported as ?raw into the docs site so the snippet shown to
// readers is exactly what svelte-check / tsc compiled — if an API rename
// breaks this import, the docs build fails before deploy.

import { loadWorkbook, workbookToBytes } from 'xlsx-kit/io';
import { fromBuffer } from 'xlsx-kit/node';
import { setCell } from 'xlsx-kit/worksheet';
import { readFile, writeFile } from 'node:fs/promises';

const wb = await loadWorkbook(fromBuffer(await readFile('input.xlsx')));
const ref = wb.sheets[0];
if (ref?.kind === 'worksheet') {
  setCell(ref.sheet, 1, 1, 'Hello from xlsx-kit');
}
await writeFile('output.xlsx', await workbookToBytes(wb));

// Build a one-sheet workbook from scratch and write it to disk.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Quarterly');

setCell(ws, 1, 1, 'Quarter');
setCell(ws, 1, 2, 'Revenue');
setCell(ws, 2, 1, 'Q1');
setCell(ws, 2, 2, 12_400);
setCell(ws, 3, 1, 'Q2');
setCell(ws, 3, 2, 15_900);

await saveWorkbook(wb, toFile('quarterly.xlsx'));

// Build several worksheets in one workbook and use named ranges
// to refer between them.

import { setFormula } from 'xlsx-kit/cell';
import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addDefinedName, addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const inputs = addWorksheet(wb, 'Inputs');
const summary = addWorksheet(wb, 'Summary');

setCell(inputs, 1, 1, 'Revenue');
setCell(inputs, 1, 2, 100_000);
setCell(inputs, 2, 1, 'Cost');
setCell(inputs, 2, 2, 65_000);

addDefinedName(wb, { name: 'Revenue', value: 'Inputs!$B$1' });
addDefinedName(wb, { name: 'Cost', value: 'Inputs!$B$2' });

setCell(summary, 1, 1, 'Margin');
setFormula(setCell(summary, 1, 2), '(Revenue - Cost) / Revenue', { cachedValue: 0.35 });

await saveWorkbook(wb, toFile('multi-sheet.xlsx'));

// One-shot read + save direct from / to disk via the xlsx-kit/node
// helpers, no manual fs glue needed.

import { loadWorkbook, saveWorkbook } from 'xlsx-kit/io';
import { fromFile, toFile } from 'xlsx-kit/node';

const wb = await loadWorkbook(fromFile('input.xlsx'));
// ...mutate wb...
await saveWorkbook(wb, toFile('output.xlsx'));

// Apply font, fill, alignment, and a thin border to a header row.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import {
  centerCell,
  setBold,
  setCellBackgroundColor,
  setCellBorderAll,
  setFontSize,
} from 'xlsx-kit/styles';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Report');

const header = setCell(ws, 1, 1, 'Total revenue');
setBold(wb, header);
setFontSize(wb, header, 12);
setCellBackgroundColor(wb, header, 'FFE0E7FF');
centerCell(wb, header);
setCellBorderAll(wb, header, { style: 'thin' });

await saveWorkbook(wb, toFile('styled.xlsx'));

// Apply number formats: currency, percentage, and a date-time.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import {
  FORMAT_DATE_DATETIME,
  setCellAsCurrency,
  setCellAsPercent,
  setCellNumberFormat,
} from 'xlsx-kit/styles';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Numbers');

setCellAsCurrency(wb, setCell(ws, 1, 1, 12_400), { symbol: '$' });
setCellAsPercent(wb, setCell(ws, 1, 2, 0.187), 1);

const dateCell = setCell(ws, 1, 3, new Date('2026-05-08T09:30:00Z'));
setCellNumberFormat(wb, dateCell, FORMAT_DATE_DATETIME);

await saveWorkbook(wb, toFile('numbers.xlsx'));

// Set a formula. Optionally cache its evaluated value so Excel renders
// the result before recalculating on open.

import { setFormula } from 'xlsx-kit/cell';
import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Sheet1');

setCell(ws, 1, 1, 12);
setCell(ws, 2, 1, 18);
setCell(ws, 3, 1, 30);

setFormula(setCell(ws, 4, 1), 'SUM(A1:A3)', { cachedValue: 60 });

await saveWorkbook(wb, toFile('with-formulas.xlsx'));

// Merge a header range across the top row and freeze the first row so
// it stays visible while scrolling.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { centerCell, setBold } from 'xlsx-kit/styles';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import {
  makeFreezePane,
  makeSheetView,
  mergeCells,
  setCell,
} from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Report');

const title = setCell(ws, 1, 1, 'Q2 financial summary');
setBold(wb, title);
centerCell(wb, title);
mergeCells(ws, 'A1:E1');

ws.views.push(makeSheetView({ pane: makeFreezePane('A2') }));

await saveWorkbook(wb, toFile('merged-frozen.xlsx'));

// Make a cell clickable. The text is whatever you set on the cell;
// hyperlink wires up the URL underneath.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell, setHyperlink } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Links');

setCell(ws, 1, 1, 'Project home');
setHyperlink(ws, 'A1', {
  target: 'https://github.com/baseballyama/xlsx-kit',
  tooltip: 'View on GitHub',
});

await saveWorkbook(wb, toFile('with-links.xlsx'));

// Promote a range to an Excel Table (named range with banded styling and
// a built-in filter dropdown on every header).

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { addExcelTable, setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Inventory');

const headers = ['SKU', 'Name', 'Price'];
headers.forEach((h, i) => setCell(ws, 1, i + 1, h));
const rows: ReadonlyArray<readonly [string, string, number]> = [
  ['A-001', 'Widget', 19.95],
  ['A-002', 'Gadget', 24.5],
  ['A-003', 'Doohickey', 7.25],
];
rows.forEach((row, r) => row.forEach((v, c) => setCell(ws, r + 2, c + 1, v)));

addExcelTable(wb, ws, {
  name: 'Inventory',
  ref: 'A1:C4',
  columns: headers,
  style: 'TableStyleMedium2',
});

await saveWorkbook(wb, toFile('inventory.xlsx'));

// Add a list-type data validation — gives the user a dropdown of
// allowed values when they click into the range.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { addDataValidation, makeDataValidation, setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Form');

setCell(ws, 1, 1, 'Status');
addDataValidation(
  ws,
  makeDataValidation({
    type: 'list',
    sqref: 'B1:B100',
    formula1: '"Open,In progress,Closed"',
    prompt: 'Pick a status',
    errorTitle: 'Invalid value',
    error: 'Pick one of the listed values.',
  }),
);

await saveWorkbook(wb, toFile('with-dropdown.xlsx'));

// Color-scale rule: red for low values, yellow for the middle, green
// for high. Excel's classic 3-color heat-map.

import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import {
  addConditionalFormatting,
  makeCfRule,
  makeConditionalFormatting,
  setCell,
} from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Heat');

for (let r = 1; r <= 10; r++) setCell(ws, r, 1, Math.round(Math.random() * 100));

addConditionalFormatting(
  ws,
  makeConditionalFormatting({
    sqref: 'A1:A10',
    rules: [
      makeCfRule({
        type: 'colorScale',
        priority: 1,
        formulas: [],
        innerXml:
          '<colorScale>' +
          '<cfvo type="min"/><cfvo type="percentile" val="50"/><cfvo type="max"/>' +
          '<color rgb="FFF8696B"/><color rgb="FFFFEB84"/><color rgb="FF63BE7B"/>' +
          '</colorScale>',
      }),
    ],
  }),
);

await saveWorkbook(wb, toFile('heatmap.xlsx'));

// Add a clustered column chart driven by a data range on the same sheet.

import { makeBarChart, makeBarSeries, makeChartSpace } from 'xlsx-kit/chart';
import { addChartAt } from 'xlsx-kit/drawing';
import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { setCell } from 'xlsx-kit/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Sales');

setCell(ws, 1, 1, 'Region');
setCell(ws, 1, 2, 'Revenue');
setCell(ws, 2, 1, 'NA');
setCell(ws, 2, 2, 12_400);
setCell(ws, 3, 1, 'EU');
setCell(ws, 3, 2, 9_800);
setCell(ws, 4, 1, 'APAC');
setCell(ws, 4, 2, 7_300);

const chart = makeBarChart({
  barDir: 'col',
  grouping: 'clustered',
  series: [
    makeBarSeries({
      idx: 0,
      tx: { kind: 'literal', value: 'Revenue' },
      cat: { ref: 'Sales!$A$2:$A$4' },
      val: { ref: 'Sales!$B$2:$B$4' },
    }),
  ],
});

const space = makeChartSpace({
  plotArea: { chart },
  title: 'Revenue by region',
  legend: { position: 'r' },
});

addChartAt(ws, 'D2', { space }, { widthPx: 480, heightPx: 320 });

await saveWorkbook(wb, toFile('chart.xlsx'));

// Insert a PNG / JPEG image at a cell anchor. Format and dimensions
// are auto-detected from the bytes, so loadImage is the only call.

import { addImageAt, loadImage } from 'xlsx-kit/drawing';
import { saveWorkbook } from 'xlsx-kit/io';
import { toFile } from 'xlsx-kit/node';
import { addWorksheet, createWorkbook } from 'xlsx-kit/workbook';
import { readFile } from 'node:fs/promises';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Cover');

const image = loadImage(await readFile('logo.png'));
addImageAt(ws, 'B2', image, { widthPx: 200, heightPx: 80 });

await saveWorkbook(wb, toFile('with-image.xlsx'));

// Stream millions of rows to disk in a fixed memory budget. Each row is
// deflated as it arrives — no intermediate workbook in memory.

import { toFile } from 'xlsx-kit/node';
import { createWriteOnlyWorkbook } from 'xlsx-kit/streaming';

const sink = toFile('big.xlsx');
const wb = await createWriteOnlyWorkbook(sink);
const ws = await wb.addWorksheet('Data');
ws.setColumnWidth(1, 24); // must precede the first appendRow

for (let r = 0; r < 10_000_000; r++) {
  await ws.appendRow([r, `row-${r}`, r * Math.PI]);
}
await ws.close();
await wb.finalize();

// Iterate huge sheets without loading the full workbook. iterRows is a SAX
// pass — it walks the file once and yields each row's cells.

import { fromFile } from 'xlsx-kit/node';
import { loadWorkbookStream } from 'xlsx-kit/streaming';

const wb = await loadWorkbookStream(fromFile('big.xlsx'));
const sheet = wb.openWorksheet(wb.sheetNames[0] ?? '');
for await (const row of sheet.iterRows({ minRow: 1, maxRow: 100 })) {
  console.log(row.map((c) => c.value));
}
await wb.close();

// Browser: pipe a fetch Response straight into the loader. fromResponse is
// streaming, so the workbook starts parsing before the download is done.

import { fromResponse, loadWorkbook } from 'xlsx-kit/io';

const response = await fetch('/sheet.xlsx');
const wb = await loadWorkbook(fromResponse(response));
const ref = wb.sheets[0];
if (ref?.kind === 'worksheet') {
  console.log(ref.sheet.title);
}

// Browser: parse the xlsx the user just selected via <input type="file">.
// fromBlob is streaming, so the workbook starts parsing while the file
// is still being read.

import { fromBlob, loadWorkbook } from 'xlsx-kit/io';

export async function loadFromInput(input: HTMLInputElement) {
  const file = input.files?.[0];
  if (!file) return null;
  const wb = await loadWorkbook(fromBlob(file));
  return wb;
}