Cell styles

A cell’s style is the plain object in its s property. Number formats live in the cell’s z property.

Caution

Pass cellStyles: true to both read and write (and readFile and writeFile). Without it, styles are neither loaded nor saved.

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

// Make a file with styles.
const ws = XLSX.utils.aoa_to_sheet([["Item", "Price"], ["abc", 1.23]]);
XLSX.utils.sheet_set_range_style(ws, "A1:B1", { bold: true });
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, ws, "Prices");
XLSX.writeFile(src, "quickstart.xlsx", { cellStyles: true });

// Read it with styles, then save a copy.
const wb = XLSX.readFile("quickstart.xlsx", { cellStyles: true });
XLSX.writeFile(wb, "styled-copy.xlsx", { cellStyles: true });
XLSX.writeFile(wb, "styled-copy.xlsb", { cellStyles: true, bookSST: true });
console.log(XLSX.readFile("styled-copy.xlsx", { cellStyles: true }).Sheets.Prices.A1.s.bold);

Output

1

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["Plain"], ["Bold"], ["Money"]]);
ws.A2.s = { bold: true, italic: true, color: { rgb: "FF0000" }, sz: 14, name: "Arial", underline: 2 };
ws.A3 = { t: "n", v: 1234.5, z: "#,##0.00", s: { fgColor: { rgb: "FFFF00" } } };
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 });
console.log(back.Sheets.S.A2.s.bold, back.Sheets.S.A2.s.name, back.Sheets.S.A3.z);

Output

1 Arial #,##0.00

For XLS, pass bookSST: true when you write to preserve rich-text runs. For XLSB, the plain text survives a round trip, but rich-text runs written with bookSST: true do not come back intact; use XLSX when you need to keep formatting runs.

Colors

A color is an object. Use a six-digit RGB hex string.

Form	Meaning
{ rgb: "FF0000" }	explicit RGB (red)
{ theme: 1, tint: 0.4 }	a color from the workbook theme with an optional tint from -1 to 1
{ index: 12 }	a color from the legacy indexed palette

Prefer six-digit RGB strings for clear, portable color values. Numeric RGB values and indexed color objects are also accepted.

Text

Key	Meaning
bold, italic, strike	set to true
underline	1 single, 2 double
sz	size in points
name	font name
color	color object
valign	"sub" or "super"
scheme	"minor" or "major": the font follows the workbook theme’s body or heading font instead of name

Set a property to false to remove it from a cell that has it.

Keeping styles when you copy a workbook

Read and write with cellStyles: true to keep every cell’s style, including cells that have a style but no value, such as the hidden cells of a merged range and formatted blank cells.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["Title"]]);
ws.A1.s = { bold: true, fgColor: { rgb: "280B3B" } };
ws.B1 = { t: "z", s: { fgColor: { rgb: "280B3B" } } };
ws["!ref"] = "A1:B1";
ws["!merges"] = [XLSX.utils.decode_range("A1:B1")];
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, ws, "S");
const opts = { type: "buffer", bookType: "xlsx", cellStyles: true };
const wb = XLSX.read(XLSX.write(src, opts), { cellStyles: true });
const copy = XLSX.read(XLSX.write(wb, opts), { cellStyles: true });
console.log(copy.Sheets.S.B1.s.fgColor.rgb);

Output

280B3B

Alignment and wrapping

Alignment lives in s.alignment.

Key	Values
horizontal	"left", "center", "right", "justify"
vertical	"top", "center", "bottom"
indent	0 (default) to 7
wrapText	true wraps; "\n" becomes a line break (use \n, not \r\n)
shrinkToFit	shrink text to the cell width
textRotation	degrees

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["x"]]);
ws.A1 = { t: "s", v: "a\nb\nc", s: { alignment: { horizontal: "left", vertical: "top", wrapText: true } } };
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 });
console.log(JSON.stringify(back.Sheets.S.A1.s.alignment));

Output

{"vertical":"top","horizontal":"left","wrapText":true}

Pattern fills

Key	Excel name	Meaning
patternType	Pattern Style	the pattern; when it is left out, the fill is solid
fgColor	Background Color	the main color (the solid color for solid)
bgColor	Pattern Color	the second color of a pattern

Pattern names: solid, darkGray, mediumGray, lightGray, gray125, gray0625, darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis, lightHorizontal, lightVertical, lightDown, lightUp, lightGrid, lightTrellis.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["solid"], ["pattern"]]);
ws.A1.s = { fgColor: { rgb: "FFFF00" } };
ws.A2.s = { patternType: "darkGray", fgColor: { rgb: "00FF00" }, bgColor: { rgb: "0000FF" } };
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 });
console.log(back.Sheets.S.A1.s.patternType, back.Sheets.S.A2.s.patternType);

Output

solid darkGray

Gradient fills

A gradient style has angle (degrees; 0 runs left to right, 90 top to bottom) and stops. Each stop has a position v between 0 and 1 and a color.

{ "angle": 90, "stops": [ { "v": 0, "rgb": "FFFFFF" }, { "v": 0.5, "rgb": "FF0000" }, { "v": 1, "rgb": "FFFFFF" } ] }

The XLSX reader and writer support this gradient shape. Use cellStyles: true for both operations.

const XLSX = require("@agent-sheet/wasm");
const assert = require("node:assert/strict");
const ws = XLSX.utils.aoa_to_sheet([[1]]);
ws.A1.s = { angle: 90, stops: [{ v: 0, rgb: "FFFFFF" }, { v: 1, rgb: "FF0000" }],
  up: { style: "thin", color: { rgb: "0000FF" } }, hidden: true, editable: true, style: "Total" };
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.A1.s.angle, 90);
assert.equal(back.Sheets.S.A1.s.up.style, "thin");
assert.equal(back.Sheets.S.A1.s.hidden, true);
assert.equal(back.Sheets.S.A1.s.editable, true);
assert.equal(back.Sheets.S.A1.s.style, "Total");

Borders

top, bottom, left and right take { style, color }. up and down describe the diagonal borders. Excel keeps one diagonal style per cell, so when you give both, the up style is used for both.

Styles: thin, hair, medium, thick, double, dotted, dashDotDot, mediumDashDotDot, dashed, dashDot, slantDashDot, mediumDashed, mediumDashDot.

When a sheet is exported to HTML, thin and hair become 1px solid, medium 2px solid, thick 3px solid, double 3px double, the dotted styles dotted, and the dashed styles dashed.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["x"]]);
ws.A1.s = {
  top: { style: "thin" },
  bottom: { style: "thick", color: { rgb: "FF0000" } },
  left: { style: "dashed", color: { rgb: "00FF00" } },
};
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 });
console.log(JSON.stringify(back.Sheets.S.A1.s.bottom), back.Sheets.S.A1.s.left.style);

Output

{"style":"thick","color":{"rgb":"FF0000"}} dashed

Diagonal borders (up, down) are written to XLSX. When both are set, they share the up border style.

Named styles

Set style on the style object to name a cell style (ws.A1.s.style = "Total"). The XLSX writer stores the name.

Per-cell protection

Cell protection only takes effect when the sheet is protected (ws["!protect"] = {}).

Key	Meaning
hidden	hide the formula when the sheet is protected
editable	allow edits (cells are locked by default)

These flags are written to XLSX and returned when styles are read. Protect the sheet for them to affect editing. See Protection.

Active selection

ws["!sel"] = { cell: "A2", range: "A1:B3" } selects A1:B3 with A2 active.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2], [3, 4], [5, 6]]);
ws["!sel"] = { cell: "A2", range: "A1:B3" };
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" }));
console.log(JSON.stringify(back.Sheets.S["!sel"]));

Output

{"cell":"A2","range":"A1:B3"}

Style a whole range

utils.sheet_set_range_style(ws, range, style) applies a style to every cell of a range. The range is a string or a range object. Text, fill and number-format keys go to each cell. The top, bottom, left and right borders go on the outer edges of the range. Two extra keys are accepted:

Key	Meaning
z	number format for each cell
incol	interior vertical border (between neighbors in a row)
inrow	interior horizontal border (between neighbors in a column)

Cells that do not exist are created as blank styled cells. A style value of false removes it.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12], [13, 14, 15, 0]]);
XLSX.utils.sheet_set_range_style(ws, "B2:C3", {
  fgColor: { rgb: "0000FF" },
  color: { rgb: "FFFFFF" },
  top: { style: "thick", color: { rgb: "FFFF00" } },
  incol: { style: "thin" },
  z: "0.00",
});
console.log(JSON.stringify(ws.B2.s.right), ws.C3.z, ws.A1.s);
XLSX.utils.sheet_set_range_style(ws, "B2", { bold: true });
XLSX.utils.sheet_set_range_style(ws, "B2", { bold: false });
console.log(ws.B2.s.bold);

Output

{"style":"thin"} 0.00 undefined
false

utils.apply_style_delta(style, delta) merges a differential style into style in place. Keys in delta overwrite. A key set to null removes that property.

const XLSX = require("@agent-sheet/wasm");
const style = { bold: true, left: { style: "thin" } };
XLSX.utils.apply_style_delta(style, { bold: null, left: null, italic: true });
console.log(JSON.stringify(style));

Output

{"italic":true}

utils.get_computed_style(ws, address) returns a copy of the cell’s style. For ordinary sheets this is the cell’s own style (an empty object when the cell has none). Conditional-format rules are not evaluated by this function.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([["x"]]);
ws.A1.s = { bold: true };
console.log(JSON.stringify(XLSX.utils.get_computed_style(ws, "A1")), JSON.stringify(XLSX.utils.get_computed_style(ws, { r: 0, c: 0 })));

Output

{"bold":true} {"bold":true}