Template: cells, styles and formulas

template_set_aoa(wb, sheet, range, rows[, { formula }]) takes an array of arrays.

• An array hole or undefined leaves the cell untouched.
• null clears the cell.
• The worksheet object in wb.Sheets is updated too.

const XLSX = require("@agent-sheet/wasm");
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, XLSX.utils.aoa_to_sheet([["a", "b", "c"], [1, 2, 3], [4, 5, 6]]), "Sheet1");
const buf = XLSX.write(src, { type: "buffer", bookType: "xlsx" });

const wb = XLSX.read(buf, { template: true });
XLSX.utils.template_set_aoa(wb, "Sheet1", "A2:C2", [[10, , 30]]);   // B2 untouched
XLSX.utils.template_set_aoa(wb, 0, "A3", [[null]]);                  // clear A3
const out = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true }));
const s = out.Sheets.Sheet1;
console.log(s.A2.v, s.B2.v, s.C2.v, s.A3);

Output

10 2 30 undefined

Erase a range

Write a grid of null values. This helper does it for any range:

function erase(XLSX, wb, sheet, range) {
  if (typeof range === "string") range = XLSX.utils.decode_range(range);
  const row = Array.from({ length: range.e.c - range.s.c + 1 }, () => null);
  const aoa = Array.from({ length: range.e.r - range.s.r + 1 }, () => row);
  XLSX.utils.template_set_aoa(wb, sheet, range, aoa);
}

const XLSX = require("@agent-sheet/wasm");
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, XLSX.utils.aoa_to_sheet([["a", "b"], [1, 2], [3, 4]]), "Sheet1");
const wb = XLSX.read(XLSX.write(src, { type: "buffer", bookType: "xlsx" }), { template: true });
erase(XLSX, wb, "Sheet1", "A2:B3");
const out = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true }));
console.log(out.Sheets.Sheet1["!ref"], out.Sheets.Sheet1.A2);

Output

A1:B3 undefined

Formulas, styles and rich text

Cell objects carry formulas, styles and rich text.

Cell object	Effect
{ t: "n", f: "A2*10" }	set a formula
{ t: "n", v: 123, f: null }	remove the formula
{ t: "n", v: 1, s: { bold: true } }	set a style
{ t: "s", v: "text", R: [ ...runs ] }	rich text

const XLSX = require("@agent-sheet/wasm");
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, XLSX.utils.aoa_to_sheet([["a", "b", "c"], [1, 2, 3]]), "Sheet1");
const buf = XLSX.write(src, { type: "buffer", bookType: "xlsx" });

const wb = XLSX.read(buf, { template: true, cellStyles: true });
XLSX.utils.template_set_aoa(wb, "Sheet1", "A2", [[{ t: "n", v: 1, s: { bold: true } }]]);                     // style
XLSX.utils.template_set_aoa(wb, "Sheet1", "D2", [[{ t: "n", f: "A2*10" }]]);                                  // set formula
XLSX.utils.template_set_aoa(wb, "Sheet1", "C2", [[{ t: "n", v: 123, f: null }]]);                            // remove formula
XLSX.utils.template_set_aoa(wb, "Sheet1", "E2", [[{ t: "s", v: "This is Bold", R: [
  { t: "s", v: "This is ", s: {} }, { t: "s", v: "Bold", s: { bold: true } } ] }]]);                           // rich text
const out = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true }), { cellStyles: true });
const s = out.Sheets.Sheet1;
console.log(s.A2.s.bold, s.D2.f, s.C2.f, s.E2.R.length);

Output

1 A2*10 undefined 2

Styles: replace or merge

If you read the workbook without cellStyles: true, a style in template_set_aoa replaces the styling of the cell. With cellStyles: true, the style is merged on top of the existing one.

Strings that start with =

The optional fifth argument { formula: true } is accepted, but strings that start with = are still stored as text. Use { f } cell objects to set formulas.