Cheatsheet

The shortest path from “I want to do X” to working code. Each row names the exact functions you import. The snippets below the index expand the most common patterns. For prose context see Recipes; for every export see API reference.

Each function lives in exactly one subpath — @office-kit/xlsx/io, /node, /streaming, /workbook, /worksheet, /cell, /styles, /chart, /drawing. Function name → subpath is unique, so once you know the name you know where to import from.

Index

Read

Stream-read (huge sheets)

Write

Stream-write (huge sheets)

Cells, formulas, links

Worksheet structure

Drawings

Snippets

Each snippet below corresponds to one row above. Imports are explicit so you can copy-paste a single block into your file.

Read xlsx file (Node) → Workbook

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

const wb = await loadWorkbook(fromFile('in.xlsx'));

Read xlsx Buffer (Node) → Workbook

import { loadWorkbook } from '@office-kit/xlsx/io';
import { fromBuffer } from '@office-kit/xlsx/node';

const wb = await loadWorkbook(fromBuffer(buf));

Read xlsx from fetch (browser) → Workbook

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

const wb = await loadWorkbook(fromResponse(await fetch('/sheet.xlsx')));

Read xlsx from <input type="file"> (browser) → Workbook

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

const wb = await loadWorkbook(fromBlob(file));

Iterate every cell of a worksheet → 2D array

import { iterValues } from '@office-kit/xlsx/worksheet';

const rows = [...iterValues(ws)]; // CellValue[][]

Find the used range of a worksheet (formatting included)

import { getCellExtent, getMaxCol, getMaxRow } from '@office-kit/xlsx/worksheet';

const lastRow = getMaxRow(ws);
const lastCol = getMaxCol(ws);
const box = getCellExtent(ws); // { minRow, maxRow, minCol, maxCol } | undefined

A cell that carries only formatting counts towards all three, which is what Excel calls the used range.

Iterate only the rows that hold values

import { getValueExtent, iterValues } from '@office-kit/xlsx/worksheet';

const box = getValueExtent(ws); // undefined when the sheet holds no value
if (box) {
  for (const row of iterValues(ws, box)) {
    // no leading or trailing rows of null, however far the formatting runs
  }
}

Stream-read: iterate a huge sheet without loading it

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

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

Stream-read: only rows N..M of a huge sheet

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

const wb = await loadWorkbookStream(fromFile('big.xlsx'));
const sheet = wb.openWorksheet(wb.sheetNames[0] ?? '');
for await (const row of sheet.iterRows({ minRow: 1_000_000, maxRow: 1_000_100 })) {
  // …
}
await wb.close();

Build a small workbook in memory → Uint8Array

import { workbookToBytes } from '@office-kit/xlsx/io';
import { addWorksheet, createWorkbook } from '@office-kit/xlsx/workbook';
import { setCell } from '@office-kit/xlsx/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Sheet1');
setCell(ws, 1, 1, 'Hello');
const bytes = await workbookToBytes(wb);

Build a small workbook → save to file (Node)

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

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Sheet1');
setCell(ws, 1, 1, 'Hello');
await saveWorkbook(wb, toFile('out.xlsx'));

Build a small workbook → Buffer (Node)

import { workbookToBuffer } from '@office-kit/xlsx/node';
import { addWorksheet, createWorkbook } from '@office-kit/xlsx/workbook';
import { setCell } from '@office-kit/xlsx/worksheet';

const wb = createWorkbook();
const ws = addWorksheet(wb, 'Sheet1');
setCell(ws, 1, 1, 'Hello');
const buf = await workbookToBuffer(wb); // Buffer

Edit one cell of an existing file (Node)

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

const wb = await loadWorkbook(fromFile('in.xlsx'));
const sheet = wb.sheets[0];
if (sheet?.kind === 'worksheet') {
  setCell(sheet.sheet, 1, 1, 'updated');
}
await saveWorkbook(wb, toFile('out.xlsx'));

Append rows to a worksheet

import { appendRows } from '@office-kit/xlsx/worksheet';

appendRows(ws, [
  ['name', 'qty'],
  ['apple', 3],
  ['pear', 7],
]);

Reach a cell without overwriting its value

import { setFormula } from '@office-kit/xlsx/cell';
import { ensureCell } from '@office-kit/xlsx/worksheet';

// setCell always writes its value argument, so `setCell(ws, 4, 1, null)` over a
// populated cell blanks it. ensureCell allocates only when the cell is missing.
setFormula(ensureCell(ws, 4, 1), 'SUM(A1:A3)');

Empty a cell but keep its formatting

import { setCell } from '@office-kit/xlsx/worksheet';

// `null` is a CellValue, so this is the explicit "no value here" write. The
// cell stays in the sheet and keeps its fill, border and number format, the
// same as pressing Delete in Excel.
setCell(ws, 4, 1, null);

Remove a cell, formatting included

import { clearRange, deleteCell } from '@office-kit/xlsx/worksheet';

deleteCell(ws, 4, 1); // the cell is gone, not blank
clearRange(ws, 'A4:H20'); // same across a rectangle, returns the count removed

Same bytes every time for the same input

import { workbookToBytes } from '@office-kit/xlsx/io';

// Without `mtime`, every ZIP entry carries the wall clock and two renders of
// the same payload differ. Set the core properties from your payload too.
// The stamp records the date in UTC (year 1980-2099), so the bytes match on
// any machine.
wb.properties = { created: payload.generatedAt, modified: payload.generatedAt };
const bytes = await workbookToBytes(wb, { mtime: new Date(payload.generatedAt) });

Stream-write millions of rows to a file

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

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

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

Bold + font size + fill on a header cell

import { setBold, setCellBackgroundColor, setFontSize } from '@office-kit/xlsx/styles';
import { setCell } from '@office-kit/xlsx/worksheet';

const c = setCell(ws, 1, 1, 'Header');
setBold(wb, c);
setFontSize(wb, c, 14);
setCellBackgroundColor(wb, c, 'FFEFEFEF');

Change several font fields, keeping the rest

import { patchCellFont } from '@office-kit/xlsx/styles';
import { setCell } from '@office-kit/xlsx/worksheet';

// setCellFont(wb, c, makeFont({ bold: true })) would drop the workbook default
// (Calibri 11); patchCellFont merges over whatever the cell already had.
patchCellFont(wb, setCell(ws, 1, 1, 'Title'), { bold: true, size: 13 });

// A field set to undefined is removed rather than kept.
patchCellFont(wb, setCell(ws, 2, 1, 'Subtitle'), { bold: undefined, size: 9 });

Build a style once, apply it on every write

import { makeBorder, makeSide, registerCellStyle } from '@office-kit/xlsx/styles';
import { appendRows } from '@office-kit/xlsx/worksheet';

const thin = makeSide({ style: 'thin' });
const box = makeBorder({ left: thin, right: thin, top: thin, bottom: thin });
const TEXT = registerCellStyle(wb, { border: box });
const INT = registerCellStyle(wb, { border: box, numberFormat: '#,##0' });

// styleIds are column-indexed, and appendRows reuses them for every row.
appendRows(ws, [['de', 71_579], ['fr', 12_004]], { styleIds: [TEXT, INT] });

Number format: currency / percent

import { setCellAsCurrency, setCellAsPercent } from '@office-kit/xlsx/styles';
import { setCell } from '@office-kit/xlsx/worksheet';

setCellAsCurrency(wb, setCell(ws, 1, 1, 1234.5));
setCellAsPercent(wb, setCell(ws, 1, 2, 0.125));

Number format: date

import { FORMAT_DATE_DATETIME, setCellNumberFormat } from '@office-kit/xlsx/styles';
import { setCell } from '@office-kit/xlsx/worksheet';

setCellNumberFormat(wb, setCell(ws, 1, 1, new Date()), FORMAT_DATE_DATETIME);

Add a formula (with cached value)

import { makeArrayFormula, makeFormula } from '@office-kit/xlsx/cell';
import { setCell } from '@office-kit/xlsx/worksheet';

setCell(ws, 3, 1, makeFormula('SUM(A1:A2)', { cachedValue: 42 }));

// Same shape for the other <f> kinds: makeArrayFormula, makeSharedFormula,
// makeDataTableFormula. A leading `=` is stripped from all of them.
setCell(ws, 1, 3, makeArrayFormula('C1:C3', 'TRANSPOSE(A1:A3)'));

Recalculate the workbook on open

import { setFullCalcOnLoad } from '@office-kit/xlsx/workbook';

// Excel, LibreOffice and Sheets otherwise trust the cached values in the file.
// Set this when you wrote formulas with no cached value, or cached ones that
// may be stale. Viewers that never calculate ignore it and show the cache, so
// supply a cachedValue where you can as well.
setFullCalcOnLoad(wb, true);

Assert on a workbook you just generated

import { fromArrayBuffer, loadWorkbook } from '@office-kit/xlsx/io';
import { getSheet } from '@office-kit/xlsx/workbook';
import { getRangeValues } from '@office-kit/xlsx/worksheet';

// fromArrayBuffer takes a Uint8Array directly, so no Buffer and no temp file.
const wb = await loadWorkbook(fromArrayBuffer(bytes));
const sheet = getSheet(wb, 'Leverage'); // already narrowed to Worksheet
if (sheet) console.log(getRangeValues(sheet, 'A1:B3'));

Make a cell clickable

import { setHyperlink } from '@office-kit/xlsx/worksheet';

setHyperlink(ws, 'A1', { target: 'https://example.com', display: 'Open' });

Merge cells + freeze the header row

import { mergeCells, setFreezePanes } from '@office-kit/xlsx/worksheet';

mergeCells(ws, 'A1:C1');
setFreezePanes(ws, { rows: 1, cols: 0 }); // or setFreezePanes(ws, 'A2')

Build $B$5 / $A$4:$H$20 from numbers

import { boundariesToRangeString, tupleToCoordinate } from '@office-kit/xlsx/utils';

const rate = tupleToCoordinate(2, 5, { absoluteCol: true, absoluteRow: true }); // '$B$5'
const data = boundariesToRangeString({ minRow: 4, minCol: 1, maxRow: 20, maxCol: 8 }); // 'A4:H20'

Range-taking helpers also accept those bounds directly, so there is no need to format a string the callee parses straight back:

import { setRangeBorderBox } from '@office-kit/xlsx/styles';

setRangeBorderBox(wb, ws, { minRow: 4, minCol: 1, maxRow: 20, maxCol: 8 }, { style: 'thin' });

Multiple sheets + a defined name

import { addDefinedName, addWorksheet, createWorkbook } from '@office-kit/xlsx/workbook';

const wb = createWorkbook();
addWorksheet(wb, 'Q1');
addWorksheet(wb, 'Q2');
addDefinedName(wb, { name: 'Totals', value: 'Q1!$A$1:$A$10,Q2!$A$1:$A$10' });

Promote a range to an Excel Table

import { addExcelTable, writeRange } from '@office-kit/xlsx/worksheet';

// Write the header row first: addExcelTable checks the definition against the
// sheet (column count against the range width, room for the header and totals rows,
// unique names, each header cell holding its column's name as text), because
// Excel repairs the file (dropping the table) when the two disagree.
writeRange(ws, 'A1', [['SKU', 'Name', 'Price']]);
addExcelTable(wb, ws, {
  name: 'Items',
  ref: 'A1:C4',
  columns: ['SKU', 'Name', 'Price'],
  style: 'TableStyleMedium2',
});

AutoFilter on a header row (no table styling)

import { makeAutoFilter, setAutoFilter } from '@office-kit/xlsx/worksheet';

setAutoFilter(ws, makeAutoFilter({ ref: 'A1:C1' }));

Dropdown data validation

import { addDataValidation, makeDataValidation } from '@office-kit/xlsx/worksheet';

addDataValidation(
  ws,
  makeDataValidation({
    type: 'list',
    sqref: 'B2:B100',
    formula1: '"red,green,blue"',
  }),
);

A column the recipient fills in

import { applyBuiltinStyle } from '@office-kit/xlsx/styles';
import { addDataValidation, ensureCell, makeDataValidation } from '@office-kit/xlsx/worksheet';

for (let row = 2; row <= 20; row++) {
  applyBuiltinStyle(wb, ensureCell(ws, row, 3), 'Input');
}
addDataValidation(
  ws,
  makeDataValidation({ type: 'decimal', operator: 'between', sqref: 'C2:C20', formula1: '0', formula2: '10', showErrorMessage: true, errorStyle: 'stop' }),
);

Insert an image at a cell

import { addImageAt, loadImage } from '@office-kit/xlsx/drawing';

const img = loadImage(bytes); // bytes: Uint8Array
addImageAt(ws, 'B2', img, { widthPx: 200, heightPx: 80 });

Add a clustered column chart

import { makeBarChart, makeBarSeries, makeChartSpace } from '@office-kit/xlsx/chart';
import { addChartAt } from '@office-kit/xlsx/drawing';

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 });