Number formats and dates

A cell has a raw value v, a number format z and formatted text w. The format is an Excel format string such as #,##0.00, or the index of a built-in format.

Set a format

utils.cell_set_number_format(cell, fmt) sets z. utils.format_cell(cell) returns the formatted text.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1234.5, 0.256]]);
XLSX.utils.cell_set_number_format(ws.A1, "#,##0.00");
XLSX.utils.cell_set_number_format(ws.B1, "0.0%");
console.log(XLSX.utils.format_cell(ws.A1), XLSX.utils.format_cell(ws.B1));

Output

1,234.50 25.6%

A format can also be set in a cell object: { t: "n", v: 0.25, z: "0%" }. Or for a range, use sheet_set_range_style(ws, "B2:B9", { z: "0.00" }). See Cell styles.

Format a value directly

XLSX.SSF is the number-format engine. It has no connection to a workbook.

const XLSX = require("@agent-sheet/wasm");
console.log(XLSX.SSF.format("yyyy-mm-dd", 45000));
console.log(XLSX.SSF.format("0.00;[Red]-0.00", -3.14159));
console.log(XLSX.SSF.format("@", "text"));
console.log(XLSX.SSF.is_date("yyyy-mm-dd"), XLSX.SSF.is_date("0.00"));
console.log(JSON.stringify(XLSX.SSF.parse_date_code(45000.5)));

Output

2023-03-15
-3.14
text
true false
{"D":45000,"T":43200,"u":0,"y":2023,"m":3,"d":15,"H":12,"M":0,"S":0,"q":3}

See SSF for all functions.

Dates

Spreadsheet files store a date as a number: the days since 1900 (or 1904). By default read gives you that number and the formatted text in w. Set cellDates: true to get JavaScript Date objects in t: "d" cells.

const XLSX = require("@agent-sheet/wasm");

const ws = XLSX.utils.aoa_to_sheet([[new Date(2024, 0, 15, 12)]], { cellDates: true });
console.log(ws.A1.t, ws.A1.z, ws.A1.w);

const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx", cellDates: true });

const asDate = XLSX.read(buf, { cellDates: true }).Sheets.S.A1;
console.log(asDate.t, asDate.v instanceof Date, asDate.v.getFullYear(), asDate.v.getMonth(), asDate.v.getDate());

const asNumber = XLSX.read(buf).Sheets.S.A1;
console.log(asNumber.t, Math.floor(asNumber.v), asNumber.w);

Output

d m/d/yy 1/15/24
d true 2024 0 15
n 45306 1/15/24

aoa_to_sheet turns a Date into a number with a date format unless you pass cellDates: true. Ambiguous text dates are read as UTC by default. Set UTC: false to read them in the local time zone.

Keep the format string

Use cellNF: true when you read to keep the format in z:

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1234.5]]);
XLSX.utils.cell_set_number_format(ws.A1, "#,##0.00");
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx" });
console.log(XLSX.read(buf, { cellNF: true }).Sheets.S.A1.z);

Output

#,##0.00

The default date format

Format 14 is the locale’s short date. dateNF on read sets a format for dates that have none. XLSX.set_date_style(fmt) replaces the built-in format 14 for the realm. See Locale support for locale effects.

Date serial numbers

SSF.parse_date_code(n) splits a serial number into parts: y, m, d, H, M, S, fractional seconds u and the day of week q (0 is Sunday).