Template editing: getting started

A normal read, edit and write converts a file into the JavaScript workbook model and writes a new file. Content that is not represented by the parsed workbook model may be lost or rewritten. Template mode is useful when preserving unrelated file content matters.

Template mode keeps the original package and edits it in place. Everything you do not touch survives byte for byte. Template mode works on XLSX and XLSM files.

const XLSX = require("@agent-sheet/wasm");
// build a small input file
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, XLSX.utils.aoa_to_sheet([["a", "b", "c"], [1, 2, 3]]), "Sheet1");
XLSX.writeFile(src, "template.xlsx");

const wb = XLSX.readFile("template.xlsx", { template: true });
XLSX.utils.template_set_aoa(wb, "Sheet1", "A2:C2", [[1, 2, { t: "n", f: "A2+B2" }]]);
XLSX.writeFile(wb, "edited.xlsx", { template: true });
console.log(XLSX.readFile("edited.xlsx").Sheets.Sheet1.C2.f);

Output

A2+B2

Read and write options

Option	Effect
template: true	keep the package structure. Use it on read and on write or writeFile
skipParse: true	do not also build the JavaScript worksheets. This is faster when you only edit. wb.Sheets is then empty. wb.SheetNames is still set
recalc: false	do not ask Excel to recalculate when the file opens. By default the workbook is flagged fullCalcOnLoad so that formulas, charts and pivots refresh. The flag is only written when the file has a calculation-properties entry
cellStyles: true	with template_set_aoa, styles are applied as changes on top of the existing styling instead of replacing it

Most helpers accept a sheet as a name, a zero-based index or a worksheet object. A range is a string, a range object or a single cell address.

Caution

Rows, columns and sheet indexes are zero-based in the template_* helpers. Cell addresses in strings use A1 notation.

The helpers

All functions are in XLSX.utils. See template helpers for every signature.

Function	Guide
template_set_aoa	Cells, styles and formulas
template_book_append_sheet, template_book_replace_sheet, template_book_delete_sheet, template_sheet_set_visibility	Sheets
template_sheet_copy_row, template_sheet_insert_rows, template_sheet_delete_rows, template_sheet_insert_cols, template_sheet_delete_cols, template_set_row_props, template_set_col_props	Rows and columns
template_book_add_name, template_book_set_name, template_book_remove_name	Defined names
template_sheet_add_dval, template_sheet_remove_dval_range	Data validations
template_sheet_set_password, template_sheet_set_protection	Protection
template_get_ctrls, template_find_ctrls, template_set_ctrl_prop, template_get_radio_group, template_set_radio_sel	Form controls
template_add_pivot	Pivot tables

Faster edits with skipParse

When you only edit and never read cells, skip building the worksheets:

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]]), "Sheet1");
const buf = XLSX.write(src, { type: "buffer", bookType: "xlsx" });

const wb = XLSX.read(buf, { template: true, skipParse: true });
console.log(wb.SheetNames, Object.keys(wb.Sheets));
XLSX.utils.template_set_aoa(wb, "Sheet1", "A2", [[100]]);
const out = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true }));
console.log(out.Sheets.Sheet1.A2.v);

Output

[ 'Sheet1' ] []
100

When to use template mode

Use it when the file has features that the JavaScript model does not hold, and you want to change only a few cells or sheets. The usual case is a report template made in Excel with charts, print setup and named styles. You fill in the data. Everything else stays.

If you build a file from nothing, use the normal functions instead.