Sheet and workbook properties

Freeze panes

ws["!freeze"] is the cell that becomes the top-left cell of the scrolling pane. It is the cell you would select before you choose “Freeze Panes” in Excel. Use an A1 string.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2], [3, 4]]);
ws["!freeze"] = "A2"; // freeze the first row
ws["!freeze"] = "B1"; // freeze the first column
ws["!freeze"] = "B2"; // freeze the first row and column
console.log(ws["!freeze"]);

Output

B2

A zero-based { r, c } address is also accepted; reading returns an A1 string.

Tab color

ws["!tabcolor"] = { rgb: "FF0000" } colors the sheet tab. Use a string for rgb.

Gridlines and headings

ws["!gridlines"] = false hides gridlines. ws["!headings"] is the matching switch for row and column headings. Both switches can be written to XLSX.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1]]);
ws["!freeze"] = "B2";
ws["!tabcolor"] = { rgb: "FF0000" };
ws["!gridlines"] = false;
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const s = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }), { cellStyles: true }).Sheets.S;
console.log(s["!freeze"], JSON.stringify(s["!tabcolor"]), s["!gridlines"]);

Output

B2 {"rgb":"FF0000"} false

Zoom

Zoom (10 to 400, in percent) is stored for each sheet in wb.Workbook.Views. The array is indexed like SheetNames. Create the chain of objects before you assign a value.

const XLSX = require("@agent-sheet/wasm");
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[1]]), "A");
if (!wb.Workbook) wb.Workbook = {};
if (!wb.Workbook.Views) wb.Workbook.Views = [];
if (!wb.Workbook.Views[0]) wb.Workbook.Views[0] = {};
wb.Workbook.Views[0].zoom = 200;                     // first sheet at 200%
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
console.log(back.Workbook.Views[0].zoom);

Output

200

Views[i].RTL = true switches a view to right-to-left.

Each view can carry its own zoom. Use the matching sheet index in Views.

Sheet visibility

wb.Workbook.Sheets[i].Hidden is 0 for visible, 1 for hidden and 2 for very hidden (not listed in Excel’s Unhide dialog). utils.book_set_sheet_visibility(wb, sheetNameOrIndex, value) sets it for you. The constants are utils.consts.SHEET_VISIBLE, SHEET_HIDDEN and SHEET_VERY_HIDDEN.

const XLSX = require("@agent-sheet/wasm");
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[1]]), "Main");
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[2]]), "Notes");
XLSX.utils.book_set_sheet_visibility(wb, "Notes", XLSX.utils.consts.SHEET_HIDDEN);
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
console.log(back.Workbook.Sheets.map((s) => s.Hidden));

Output

[ 0, 1 ]

Defined names

wb.Workbook.Names is an array of { Name, Ref, Sheet?, Comment? }. Sheet (zero-based) limits a name to one sheet. When it is left out, the name is global.

const XLSX = require("@agent-sheet/wasm");
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[1, 2], [3, 4]]), "Data");
wb.Workbook = { Names: [{ Name: "Totals", Ref: "Data!$A$1:$B$2", Comment: "all cells" }] };
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
console.log(back.Workbook.Names);

Output

[ { Name: 'Totals', Comment: 'all cells', Ref: 'Data!$A$1:$B$2' } ]

Document properties

wb.Props holds Title, Subject, Author, Manager, Company, Category, Keywords, Comments, LastAuthor and CreatedDate. wb.Custprops holds custom name and value pairs. Both are written with the workbook. See Writing files for an example.

Custom XML

wb.CustomXML is an array of { data, props }: the XML payload and its item-properties XML. The reader returns it for existing files. The XLSX writer stores these custom XML parts. Use template editing when you want existing parts kept untouched.