Conditional formatting

Rules are an array in ws["!condfmt"]. Rules are read and written when cellStyles is set.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1, 5], [2, 6], [3, 7], [4, 8]]);
ws["!condfmt"] = [
  { ref: "A1:A4", t: "val", op: "GT", v: 2, s: { color: { rgb: "9C0006" }, bgColor: { rgb: "FFC7CE" } } },
  { ref: "B1:B4", t: "scale",
    cmin: { t: "percent", v: 25, color: { rgb: "FF7128" } },
    cmax: { t: "percent", v: 75, color: { rgb: "FFEF9C" } } },
];
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["!condfmt"].map((r) => r.t));

Output

[ 'val', 'scale' ]

Key	Meaning
ref	range string, { r, c } or { s, e }. Several ranges are one string separated by spaces: "A2:A4 C1:D5"
t	rule type (see the table below)
s	differential style (for the rule types that have one)
op	operator (depends on the type)
f	formula, written as in Excel’s formula box, without a leading =
min, max, v	bounds or value
color	data bar color
cmin, cmid, cmax	thresholds for color scales and data bars
thresh	thresholds for icon sets
p	priority (lower is evaluated first)

t	Excel rule	Has s	Returned by the XLSX reader
val	cell value	yes	yes
formula	use a formula	yes	yes
scale	2 or 3 color scale	no	yes
bar	data bar	no	yes
avg	above or below average	yes	yes
text	specific text	yes	yes
date	dates occurring	yes	yes
error	errors or no errors	yes	yes
blank	blanks or no blanks	yes	yes
rank	top or bottom ranked	yes	yes
dup, unique	duplicate or unique values	yes	yes
icon	icon set	no	yes

The XLSX reader and writer support these rule families. For min / max thresholds without v, a round trip returns v: 0; set it explicitly when comparing read-back objects. See Known differences. To preserve existing content without rebuilding the workbook, use template editing.

Differential styles

For rules that have s, the style is applied on top of the cell’s own style. A property set to null switches that feature off. The background is bgColor, not fgColor.

const rule = { t: "avg", ref: "A1:A10", op: "GT", s: { bold: true, left: null, bgColor: { rgb: "FFC7CE" } } };
console.log(rule.t);

Output

avg

Value rules: val

op is one of the validation operators. IN and OT use min and max. EQ, NE, GT, LT, GE and LE use v.

const rule = { ref: "A1:A10", t: "val", op: "IN", min: 3, max: 8, s: { bold: true } };
console.log(rule.t);

Output

val

Text rules: text

v is the text. op is IN (containing), OT (not containing), ST (beginning with) or ND (ending with). The writer supports these text operators.

Date rules: date

op is one of: YS yesterday, TD today, LS last 7 days, LW last week, TW this week, NW next week, LM last month, NM next month, TM this month. There is no separate code for tomorrow. Date rules store the chosen relative period. Use a formula rule for a date condition not covered by these codes.

Errors and blanks: error, blank

v: true means “errors” or “blanks”. Leave v out for “no errors” or “no blanks”.

Rank rules: rank

v is the count. op is TV (top N), BV (bottom N), TP (top N percent) or BP (bottom N percent).

Average rules: avg

op is GT (above), LT (below), GE (equal or above), LE (equal or below), G1, G2, G3 (one, two or three standard deviations above) or L1, L2, L3 (below).

Duplicates and unique values

t: "dup" formats duplicates. t: "unique" formats unique values. Both use s.

Formula rules: formula

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1], [2], [3], [4]]);
ws["!condfmt"] = [{ ref: "A1:A4", t: "formula", f: "MOD(ROW(),2)=1", s: { bgColor: { rgb: "ECECEC" } } }];
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["!condfmt"][0].f);

Output

MOD(ROW(),2)=1

Color scales: scale

cmin and cmax are required. cmid adds the midpoint of a three-color scale. Each threshold is { t, v, color }, or f instead of v for a formula.

Threshold t	Meaning	Allowed in
min	lowest value	cmin
max	highest value	cmax
num	a number (v)	all
percent	a percent (v)	all
percentile	a percentile (v)	all
formula	a formula (f)	all

const rule = { ref: "B1:B10", t: "scale",
  cmin: { t: "min", color: { rgb: "F8696B" } },
  cmid: { t: "percentile", v: 50, color: { rgb: "FFEB84" } },
  cmax: { t: "max", color: { rgb: "63BE7B" } } };
console.log(rule.t);

Output

scale

Data bars: bar

color is the bar color. cmin and cmax are thresholds with the same t values as color scales, but no color.

const rule = { ref: "C1:C10", t: "bar", color: { rgb: "638EC6" }, cmin: { t: "min" }, cmax: { t: "max" } };
console.log(rule.t);

Output

bar

Icon sets: icon

v is the icon set name. thresh holds the thresholds ({ t, v } with t of num, percent, percentile or formula). hidden: true shows the icons only. The first threshold must be { t: "percent", v: 0 }.

Icon set	Thresholds	Icon set	Thresholds
3Arrows, 3ArrowsGray, 3Flags	3	4Arrows, 4ArrowsGray, 4RedToBlack	4
3TrafficLights1, 3TrafficLights2	3	4Rating, 4TrafficLights	4
3Signs, 3Symbols, 3Symbols2	3	5Arrows, 5ArrowsGray, 5Rating, 5Quarters	5
3Stars, 3Triangles (newer Excel)	3	5Boxes (newer Excel)	5

const rule = { ref: "E2:E9", t: "icon", v: "3TrafficLights1",
  thresh: [{ t: "percent", v: 0 }, { t: "percent", v: 33 }, { t: "percent", v: 67 }] };
console.log(rule.t);

Output

icon