Skip to content
Agent Sheet SDK

Template: rows and columns

row and col are zero-based.

Function Effect
template_sheet_copy_row(wb, sheet, dst, src) copy a whole row, with its metadata
template_sheet_insert_rows(wb, sheet, row, count) insert rows before row. 0 inserts above the first row
template_sheet_delete_rows(wb, sheet, row, count) delete count rows from row
template_sheet_insert_cols(wb, sheet, col, count) insert columns before col
template_sheet_delete_cols(wb, sheet, col, count) delete count columns from col
template_set_row_props(wb, sheet, row, props) row properties such as { hidden }
template_set_col_props(wb, sheet, col, props) column properties such as { hidden }

When a row is copied, relative references in its formulas shift by the row difference. Absolute references such as A$2 stay.

const XLSX = require("@agent-sheet/wasm");
const src = XLSX.utils.book_new();
const ws0 = XLSX.utils.aoa_to_sheet([["a", "b", "c"], [1, 2, 3], [4, 5, 6]]);
ws0.C2.f = "A2+B2";
XLSX.utils.book_append_sheet(src, ws0, "Sheet1");
const wb = XLSX.read(XLSX.write(src, { type: "buffer", bookType: "xlsx" }), { template: true });


XLSX.utils.template_sheet_copy_row(wb, "Sheet1", 4, 1);              // copy row 2 to row 5 (Excel numbering)
XLSX.utils.template_sheet_insert_rows(wb, "Sheet1", 1, 2);           // two new rows before Excel row 2
XLSX.utils.template_set_row_props(wb, "Sheet1", 3, { hidden: true });
XLSX.utils.template_set_col_props(wb, "Sheet1", 1, { hidden: true });


const out = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true }), { cellStyles: true });
const s = out.Sheets.Sheet1;
console.log(s.C7.f);                  // the copy shifted A2+B2 to A5+B5. The insert did not change it again
console.log(s.A4.v, s.A7.v);          // the old row 2 is now row 4; the copy is now row 7
console.log(s["!cols"][1].hidden, s["!rows"][3].hidden);
Output
A5+B5
1 1
true true

The example copies row 2 to row 5 and then inserts two rows before row 2. The old row 2 is now row 4 and the copy is row 7.