Template helper reference

Every function below is in XLSX.utils. Read an existing XLSX or XLSM with template: true, use the helpers to record changes, then write with template: true. All mutators change the supplied template workbook; none returns a replacement workbook.

Common parameters

• workbook: a workbook returned by a template-mode read.
• sheet: a sheet name, zero-based index, or worksheet object, except that template_add_pivot accepts only a name or index.
• range: an A1 range string, { s, e } range, or { r, c } cell address. A single A1 cell is a one-cell range.
• row, column, source, destination, and selection index: zero-based.
• count: the number of rows or columns to insert or delete.

For template mode with skipParse: true, ordinary worksheet objects are not constructed. Prefer names or indexes to select sheets in that mode.

const XLSX = require("@agent-sheet/wasm");
const assert = require("node:assert/strict");
const source = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(source, XLSX.utils.aoa_to_sheet([["Item", "Qty"], ["Pens", 2]]), "Data");
const input = XLSX.write(source, { type: "buffer", bookType: "xlsx" });
const workbook = XLSX.read(input, { template: true });
XLSX.utils.template_set_aoa(workbook, "Data", "B2", [[7]]);
const output = XLSX.write(workbook, { type: "buffer", bookType: "xlsx", template: true });
assert.equal(XLSX.read(output).Sheets.Data.B2.v, 7);

Write cell values

template_set_aoa(workbook, sheet, range, rows, options?)

Returns: no result to consume.

rows is an array of row arrays containing primitives or cell objects. Entries map from the top-left of range. Gaps and undefined leave the corresponding cells untouched. null clears a cell. Parsed cells in workbook.Sheets are updated too when present.

Cell objects can supply v, t, f, z, s, and rich-text R. Use f without a leading = for formulas. To remove a formula while keeping a value, use { t: "n", v: 123, f: null }.

The options.formula flag is accepted, but strings starting with = remain text in this package. Use formula cell objects instead. If the workbook was read with cellStyles: true, passed styles merge differentially. Without that read option, they replace existing cell styling.

const XLSX = require("@agent-sheet/wasm");
const assert = require("node:assert/strict");
const source = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(source, XLSX.utils.aoa_to_sheet([[1, 2, 3], [4, 5, 6]]), "Data");
const workbook = XLSX.read(XLSX.write(source, { type: "buffer", bookType: "xlsx" }), { template: true });
XLSX.utils.template_set_aoa(workbook, "Data", "A1:C1", [[10, , 30]]);
XLSX.utils.template_set_aoa(workbook, "Data", "A2", [[null]]);
const result = XLSX.read(XLSX.write(workbook, { type: "buffer", bookType: "xlsx", template: true }));
assert.equal(result.Sheets.Data.B1.v, 2);
assert.equal(result.Sheets.Data.C1.v, 30);
assert.equal(result.Sheets.Data.A2, undefined);

See Template cells for styles, formulas and clearing ranges.

Worksheet structure

Signature	Parameters and semantics	Returns
template_book_append_sheet(workbook, worksheet, name?, roll?)	Append an ordinary worksheet to the template. roll: true allows a new name if the requested one is taken	No result to consume
template_book_replace_sheet(workbook, sheet, worksheet)	Replace cell content, keeping the old name and position	No result to consume
template_book_delete_sheet(workbook, sheet)	Delete the sheet and defined names referring to it	No result to consume
template_sheet_set_visibility(workbook, sheet, visibility)	0 visible, 1 hidden, 2 very hidden	No result to consume

book_append_sheet also recognizes template workbooks. Finish styling the new worksheet before appending or replacing: required styles are added at that point and are not recomputed after later direct edits.

Deleting a sheet does not rewrite formulas or other references. Confirm that references are safe before deleting. Replacing keeps the sheet identity so cross-sheet formulas can retain their target.

The runtime function is template_sheet_set_visibility, not the original documentation’s spelling template_set_sheet_visibility.

See Template sheets.

Rows and columns

Signature	Semantics	Returns
template_set_row_props(workbook, sheet, row, properties)	Update row metadata, such as hidden, hpx, hpt, or level	No result to consume
template_set_col_props(workbook, sheet, column, properties)	Update column metadata, such as hidden, width, wpx, wch, or level	No result to consume
template_sheet_copy_row(workbook, sheet, destination, source)	Copy a row, including row metadata; shift relative formula references by the row difference	No result to consume
template_sheet_insert_rows(workbook, sheet, row, count)	Insert before the specified row; row: 0 inserts above Excel row 1	No result to consume
template_sheet_delete_rows(workbook, sheet, row, count)	Delete count rows starting at row	No result to consume
template_sheet_insert_cols(workbook, sheet, column, count)	Insert before the specified column	No result to consume
template_sheet_delete_cols(workbook, sheet, column, count)	Delete count columns starting at column	No result to consume

When copying, row-absolute references such as A$2 do not shift. properties uses the same row/column fields as !rows and !cols metadata. See Template rows and columns.

Data validations

Signature	Semantics	Returns
template_sheet_add_dval(workbook, sheet, validation)	Add a validation object using the same schema as !validations entries	No result to consume
template_sheet_remove_dval_range(workbook, sheet, range)	Remove every validation overlapping any part of range	No result to consume

Removal deletes the whole matching validation, not just the overlap. A list validation can be { ref: "A1:A3", t: "List", l: ["Yes", "No"] }. See Template validations and Data validations for the full schema and limits.

Defined names

A defined name is { Name, Ref, Sheet?, Comment? }. Name is its name, Ref is a string reference or expression, and optional Sheet is the zero-based sheet scope. Omit Sheet for workbook scope.

Signature	Semantics	Returns
template_book_add_name(workbook, name)	Add a defined name, or update its template entry	No result to consume
template_book_set_name(workbook, name)	Update an existing name’s Ref; match the given scope	No result to consume
template_book_remove_name(workbook, specification)	Remove by { Name, Sheet? }; Sheet: -1 removes every scope with that name	No result to consume

For set/remove, omitting Sheet selects only the workbook-scoped name, not all same-named sheet-scoped entries. Removing a name does not rewrite formulas that use it. Ref must be a string, such as "Data!$A$1:$A$3" or "3".

The runtime removal name is template_book_remove_name, not the original documentation’s template_book_delete_name. See Template names.

Worksheet protection

Signature	Semantics	Returns
template_sheet_set_password(workbook, sheet, password?)	Non-empty string sets a password; "" clears only the password; null/undefined removes protection	No result to consume
template_sheet_set_protection(workbook, sheet, properties?)	Change permission flags; null for the whole object removes protection	No result to consume

For permission keys, true disables the action, false allows it, and null clears that key to its default. Supported keys include formatCells, formatColumns, formatRows, insertColumns, insertRows, insertHyperlinks, deleteColumns, deleteRows, sort, autoFilter, pivotTables, objects, scenarios, selectLockedCells, and selectUnlockedCells.

Worksheet protection is not file encryption. The runtime accepts null for these removal operations even though the bundled declarations are narrower. See Template protection.

PivotTables

template_add_pivot(workbook, sheet, pivot)

Returns: the zero-based index of the newly added pivot table.

Here sheet is a name or zero-based index. pivot supplies source, origin, fields, rows, cols, filters, values, style, and props as needed. Value fields refer to the zero-based index in pivot.fields.

Use this helper to add a pivot directly to an existing template package. See Template pivots for a complete example and Pivot tables for the schema.

Form controls

These functions need a template worksheet containing form-control drawings. On a worksheet without them they can throw a TypeError, as in the original package; they do not reliably return an empty list.

A returned control object has type, optional top-left loc: { r, c }, raw VML text, and an optional property-part path. Do not edit raw directly. Pass the returned objects to the control helpers.

Signature	Parameters and semantics	Returns
template_get_ctrls(workbook, sheet)	List all controls in worksheet document order	Control array
template_find_ctrls(workbook, sheet, cell)	Find controls whose top-left anchor is at an A1 cell or { r, c }	Control array
template_set_ctrl_prop(workbook, control, property, value)	Set a supported property on a returned control	No result to consume
template_get_radio_group(workbook, sheet, control)	Infer the radio group containing this radio control	Control array
template_set_radio_sel(workbook, group, index)	Clear the group and select its zero-based entry at index	No result to consume

Checked is supported for Checkbox and Radio controls: 0 unchecked, 1 checked, 2 mixed. Prefer template_set_radio_sel when selecting one radio button so the rest of its group is cleared.

See Template controls for the editing workflow.