Building sheets

A worksheet is a plain object. You can fill it by hand, but three helpers cover most cases.

Helper	Input
utils.aoa_to_sheet(rows, opts)	an array of arrays (rows of values or cell objects)
utils.json_to_sheet(objects, opts)	an array of objects; the keys become the header row
utils.table_to_sheet(element, opts)	an HTML <table> element (needs a DOM)

To add data to an existing sheet use utils.sheet_add_aoa(ws, rows, opts) and utils.sheet_add_json(ws, objects, opts).

Arrays of arrays

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([
  ["Name", "Qty", "Done"],
  ["bolt", 10, true],
  ["nut", 20, false],
  ["washer", null, "n/a"],
]);
console.log(ws["!ref"]);
console.log(ws.B2, ws.C2, ws.A4);
console.log(ws.B4);

Output

A1:C4
{ v: 10, t: 'n' } { v: true, t: 'b' } { v: 'washer', t: 's' }
undefined

A null or undefined value makes no cell. A JavaScript Date makes a number cell with a date format. Pass cellDates: true to keep it as a date cell. See Number formats and dates.

A value can also be a cell object. Use this to set a formula, a number format or a style:

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([
  [1, 2, { t: "n", f: "A1+B1" }],
  [{ t: "n", v: 0.25, z: "0%" }, { t: "s", v: "text" }],
]);
console.log(ws.C1);
console.log(XLSX.utils.format_cell(ws.A2));

Output

{ t: 'n', f: 'A1+B1' }
25%

Arrays of objects

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.json_to_sheet([{ a: 1, b: 2 }, { a: 3, c: 4 }]);
console.log(ws["!ref"]);
console.log(XLSX.utils.sheet_to_csv(ws));

// Choose the column order with `header`:
const ordered = XLSX.utils.json_to_sheet([{ a: 1, b: 2 }], { header: ["b", "a"] });
console.log(XLSX.utils.sheet_to_csv(ordered));

Output

A1:C3
a,b,c
1,2,
3,,4
b,a
2,1

The header row lists the keys of all objects in order of first appearance. Use skipHeader: true to leave the header row out.

Where to write: origin

origin accepts an address string, a cell object, a row number, or -1 to append after the last row.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["Item", "Price"]]);
XLSX.utils.sheet_add_json(ws, [{ Item: "abc", Price: 1.23 }], { origin: -1, skipHeader: true });
XLSX.utils.sheet_add_json(ws, [{ Item: "ghi", Price: 7.89 }], { origin: -1, skipHeader: true });
console.log(ws["!ref"], XLSX.utils.sheet_to_json(ws));

Output

A1:B3 [ { Item: 'abc', Price: 1.23 }, { Item: 'ghi', Price: 7.89 } ]

Several sheets in one workbook

utils.book_new() makes an empty workbook. utils.book_append_sheet(wb, ws, name) adds a sheet and returns the name it used. A name that already exists throws an error.

const XLSX = require("@agent-sheet/wasm");
const wb = XLSX.utils.book_new();
console.log(XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[1]]), "Data"));
console.log(XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[2]]), "Notes"));
console.log(wb.SheetNames);
try {
  XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[3]]), "Data");
} catch (e) {
  console.log(e.message);
}

Output

Data
Notes
[ 'Data', 'Notes' ]
Worksheet with name |Data| already exists!

Set the third argument to undefined to get an automatic name (Sheet1, Sheet2, …). Pass true as the fourth argument to roll to a free name when the name exists.

Merged cells, filters and array formulas

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2, 3], [4, 5, 6]]);
ws["!merges"] = [XLSX.utils.decode_range("A1:B1")];
ws["!autofilter"] = { ref: "A1:C2" };
XLSX.utils.sheet_set_array_formula(ws, "D1:D2", "A1:A2*2");

const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
console.log(JSON.stringify(back.Sheets.S["!merges"]));
console.log(JSON.stringify(back.Sheets.S["!autofilter"]));
console.log(back.Sheets.S.D1.F, back.Sheets.S.D1.f);

Output

[{"s":{"c":0,"r":0},"e":{"c":1,"r":0}}]
{"ref":"A1:C2"}
D1:D2 A1:A2*2

Cell addresses

Addresses are zero-based in the helpers. { r: 0, c: 0 } is A1.

const XLSX = require("@agent-sheet/wasm");
console.log(XLSX.utils.encode_cell({ r: 0, c: 27 }));
console.log(XLSX.utils.decode_cell("AB10"));
console.log(XLSX.utils.encode_range({ s: { r: 0, c: 0 }, e: { r: 9, c: 2 } }));
console.log(XLSX.utils.decode_range("A1:C10"));
console.log(XLSX.utils.encode_col(26), XLSX.utils.decode_col("AA"));
console.log(XLSX.utils.encode_row(0), XLSX.utils.decode_row("5"));
console.log(XLSX.utils.split_cell("$B$3"));

Output

AB1
{ c: 27, r: 9 }
A1:C10
{ s: { c: 0, r: 0 }, e: { c: 2, r: 9 } }
AA 26
1 4
[ '$B', '$3' ]

HTML tables

table_to_sheet, table_to_book and sheet_add_dom read an HTML table. See DOM table ingress.