Schema Builder
Conditional Styles
Apply native Excel conditional formatting with the same formula DSL used by formula columns.
Use conditionalStyle when styling should stay live in Excel and react to formula results or later user edits.
Unlike style, which is resolved during export, conditionalStyle emits native Excel conditional formatting rules for the column range.
Why this is different from dynamic style
style: (row) => ...runs once during export in JavaScriptconditionalStyleruns later in Excel because the emitted workbook contains conditional formatting rules
That makes conditionalStyle the right tool for:
- styling formula columns by their computed result
- highlighting cells that should react to manual edits in Excel
- keeping workbook logic and workbook styling live together
Formula-based conditional styling
conditionalStyle reuses the same formula condition DSL as formula columns:
row.ref(...)fx.and(...)fx.or(...)fx.not(...)- comparison operators like
.lt(),.gte(),.eq()
import { createExcelSchema } from "@chronicstone/typed-xlsx";
type Deal = {
amount: number;
quota: number;
status: "open" | "won" | "at-risk";
};
const schema = createExcelSchema<Deal>()
.column("amount", {
accessor: "amount",
style: {
numFmt: "$#,##0.00",
alignment: { horizontal: "right" },
},
})
.column("quota", {
accessor: "quota",
style: {
numFmt: "$#,##0.00",
alignment: { horizontal: "right" },
},
})
.column("status", {
accessor: "status",
})
.column("attainment", {
formula: ({ row, fx }) =>
fx.if(row.ref("quota").gt(0), row.ref("amount").div(row.ref("quota")), 0),
style: {
numFmt: "0.0%",
alignment: { horizontal: "right" },
},
conditionalStyle: (conditional) =>
conditional
.when(({ row }) => row.ref("attainment").lt(0.5), {
fill: { color: { rgb: "FEE2E2" } },
font: { color: { rgb: "991B1B" }, bold: true },
})
.when(({ row, fx }) => fx.and(row.ref("attainment").gte(1), row.ref("status").eq("won")), {
fill: { color: { rgb: "DCFCE7" } },
font: { color: { rgb: "166534" }, bold: true },
}),
})
.build();
Layering with base styles
style remains the base style for the column. Matching conditional rules overlay on top of that base formatting.
That means a column can keep:
- number formats
- alignment
- borders
while conditionalStyle changes only the fields you specify, such as fill and font.
Summary cells
Summary cells support conditionalStyle too.
- reducer summaries support static
style, dynamicstyle(value), andconditionalStyle - formula summaries support static
styleandconditionalStyle
For summary cells, the condition context is ({ cell, fx }) rather than ({ row, fx }):
import { createExcelSchema } from "@chronicstone/typed-xlsx";
const schema = createExcelSchema<{ amount: number }>()
.column("amount", {
accessor: "amount",
summary: (summary) => [
summary.formula("sum", {
style: {
numFmt: "$#,##0.00",
font: { bold: true },
},
conditionalStyle: (conditional) =>
conditional.when(({ cell }) => cell.current().gte(1000), {
fill: { color: { rgb: "DCFCE7" } },
font: { color: { rgb: "166534" }, bold: true },
}),
}),
],
})
.build();