Column widths and row heights

Key	Meaning
ws["!cols"][i]	{ wch } characters, { wpx } pixels or { width } character-width units; also hidden and level (outline)
ws["!rows"][i]	{ hpt } points or { hpx } pixels; also hidden and level
ws["!sheetFormat"]	defaults for the sheet: { row: { hpx }, col: { wpx } } (hpt, wch and width also work)

Set wpx: 0 in the default to hide all columns. The default column width cannot be automatic.

Note

Column and row metadata is only read back when you pass cellStyles: true to read.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2, 3], [4, 5, 6]]);
ws["!cols"] = [{ wch: 20 }, { wpx: 100 }, { width: 12, hidden: true }];
ws["!rows"] = [{ hpt: 30 }, { hpx: 40 }];
ws["!sheetFormat"] = { row: { hpx: 36 }, col: { wpx: 100 } };
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["!cols"][0].wch, s["!rows"][0].hpt, s["!sheetFormat"].row.hpx);

Output

20 30 36

Notes

• { auto: 1 } asks the XLSX writer to estimate a best-fit column width from its cell contents. An explicit wch, wpx or width takes precedence. Read with cellStyles: true to retrieve bestfit and the computed width.
• Column defaults can include a style (!cols[i].s) and a number format (!cols[i].z). Read with cellStyles: true to load column presentation metadata.
• The PPI read and write option sets the screen density used to convert pixel sizes (72, 96 default, 120, 144, "osx", "win"). Other values throw the string "Unsupported PPI <value>".
• XLSX.cycle_width(w) rounds a width to the nearest value that file formats can store.

Automatic column widths

const XLSX = require("@agent-sheet/wasm");
const assert = require("node:assert/strict");
const ws = XLSX.utils.aoa_to_sheet([["A long column heading", "Fixed width"], [123, 456]]);
ws["!cols"] = [{ auto: 1 }, { auto: 1, wch: 12 }];
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx", cellStyles: true }), { cellStyles: true });
assert.equal(back.Sheets.S["!cols"][0].bestfit, "1");
assert.ok(back.Sheets.S["!cols"][0].width > 0);
assert.equal(back.Sheets.S["!cols"][1].wch, 12);

Hide rows and columns

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2], [3, 4]]);
ws["!cols"] = [{}, { hidden: true }];
ws["!rows"] = [{ hidden: true }];
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["!cols"][1].hidden, s["!rows"][0].hidden);
console.log(XLSX.utils.sheet_to_csv(s, { skipHidden: true }));

Output

true true
3