Conditional Formatting
Colour scales, data bars, duplicates, top N and expression rules. Colours mix through color-mix,
so a theme token works, and the rules export into a workbook as Excel's own.
column: { measure: 0 }. It paints every crossing of that measure and scales it across the whole crossing.Colour Scales
A gradient across the column's own range, from its lowest value to its highest. Every colour
below is a theme token, mixed through color-mix,
so the scale follows light and dark without a second set of values. Switch the site theme and
watch it move.
import { conditionalFormatting } from '@sv5ui/datagrid';
conditionalFormatting({
rules: [
{
kind: 'colorScale',
column: 'score',
from: 'var(--color-error)',
via: 'var(--color-warning)',
to: 'var(--color-success)'
}
]
});
// Fixing the ends stops the scale following the data, which is what two
// grids read against each other need:
{ kind: 'colorScale', column: 'score', min: 0, max: 100 }
// Colours mix through CSS color-mix, so a theme token works and the result
// follows light and dark on its own.Data Bars
A bar drawn behind the value rather than instead of it, so the number stays readable and stays copyable. Two regions below are negative, and their bars grow the other way in their own colour.
conditionalFormatting({
rules: [
{
kind: 'dataBar',
column: 'revenue',
// Mix with transparent, the way the built-in default does. A solid fill
// covers the number the bar is drawn behind.
color: 'color-mix(in oklab, var(--color-primary) 35%, transparent)',
negativeColor: 'color-mix(in oklab, var(--color-error) 35%, transparent)'
},
{ kind: 'dataBar', column: 'budget', min: 0, max: 250_000 }
]
});
// min is the column's smallest value, or zero when every value is positive:
// a bar measured from the smallest positive value reads as a comparison it
// is not. The bar itself is drawn through cellDecoration.style, because a
// width is a value a class cannot name.cellDecoration.style, which is a public extension point. Duplicates and Top N
North and Coastal below share an email address, and both cells are marked. The three
highest scores are green and the two lowest red, from two topN rules
on one column.
conditionalFormatting({
rules: [
{ kind: 'duplicates', column: 'email', class: 'bg-error/15' },
{ kind: 'topN', column: 'score', n: 3, class: 'font-semibold text-success' },
{ kind: 'topN', column: 'score', n: 2, bottom: true, class: 'text-error' }
]
});
// unique inverts duplicates to the values appearing exactly once:
{ kind: 'duplicates', column: 'email', unique: true, class: 'bg-success/15' }Expression Rules
An expression rule uses the same language as a calculated column, so anything a formula can say, a rule can test. That includes reading other columns, which a colour scale cannot do: the rule below marks a region whose revenue came in under its budget, by comparing the two.
Leave column off and the rule paints the whole row. The two suspended regions below are dimmed that way.
// An expression rule uses the same language as a calculated column, so
// anything a formula can say, a rule can test. Both rules below are the ones
// the grid above is running.
// It reads other columns, which is what a colour scale cannot do:
{ kind: 'expression', column: 'revenue',
when: '[revenue] < [budget]', class: 'text-error' }
// Leave column off and the rule paints the whole row:
{ kind: 'expression', when: '[status] = "suspended"', class: 'opacity-50' }
// An expression resolves COLUMNS, not row fields. [status] needs a status
// column on the grid; without one the rule parses, matches nothing, and
// paints nothing, with no error raised to say why.
// A rule that will not parse is reported once and then skipped, rather than
// failing every cell:
conditionalFormatting({
rules,
onParseError: (ruleId, message, at) => console.warn(ruleId, message, at)
});
// The failure stays readable afterwards, by rule id:
getConditionalFormatting(grid)!.errorOf('expression:1'); // RuleParseError | null
// skippedRules is a different thing: under rowModel: 'server' every rule that
// needs whole-column statistics (colour scale, data bar, duplicates, top N) is
// put aside, because the grid holds one page. An expression rule reads one row
// and is unaffected. On a client row model the list is always empty.A Rule That Will Not Compile
It is reported once, through onParseError, and then skipped. The rules around it keep working, which is why the grid below still marks
the low scores from its first rule. The failure stays readable afterwards through errorOf(id), where the id is the one the panel shows.
expression:1: "<" needs a value before it (at 9)
2 rules registered, 1 of them refused at
compile time. Read one back with errorOf(id).
// An expression rule uses the same language as a calculated column, so
// anything a formula can say, a rule can test. Both rules below are the ones
// the grid above is running.
// It reads other columns, which is what a colour scale cannot do:
{ kind: 'expression', column: 'revenue',
when: '[revenue] < [budget]', class: 'text-error' }
// Leave column off and the rule paints the whole row:
{ kind: 'expression', when: '[status] = "suspended"', class: 'opacity-50' }
// An expression resolves COLUMNS, not row fields. [status] needs a status
// column on the grid; without one the rule parses, matches nothing, and
// paints nothing, with no error raised to say why.
// A rule that will not parse is reported once and then skipped, rather than
// failing every cell:
conditionalFormatting({
rules,
onParseError: (ruleId, message, at) => console.warn(ruleId, message, at)
});
// The failure stays readable afterwards, by rule id:
getConditionalFormatting(grid)!.errorOf('expression:1'); // RuleParseError | null
// skippedRules is a different thing: under rowModel: 'server' every rule that
// needs whole-column statistics (colour scale, data bar, duplicates, top N) is
// put aside, because the grid holds one page. An expression rule reads one row
// and is unaffected. On a client row model the list is always empty.The Five Rule Kinds
Every rule names a column, except an expression rule, which may leave it off to paint the whole row.
| Kind | What it paints |
|---|---|
colorScale | A gradient across the column's own range. Fix min and max and it stops following the data, which is what two grids read side by side need |
dataBar | A bar behind the value, so the number stays readable and stays copyable. A negative bar grows the other way. min is the column's smallest value, or zero when every value is positive, because a bar measured from the smallest positive value reads as a comparison it is not |
duplicates | Every value appearing more than once in the column. unique inverts it to the values appearing exactly once |
topN | The highest or lowest n by the column's own value. n is 10 unless you say otherwise, and bottom flips which end |
expression | The formula language, so a rule can read other columns. Leave column off and it paints the whole row |
Class and Style
duplicates, topN and expression carry their own paint. A colour scale and a data bar compute theirs, so they take none.
conditionalFormatting({
rules: [
{
kind: 'topN',
column: 'score',
n: 3,
class: 'font-semibold',
style: { 'background-color': 'var(--color-success-container)' }
},
{ kind: 'duplicates', column: 'email', class: 'bg-error/15' },
{ kind: 'expression', when: '[status] = "suspended"', class: 'opacity-50' }
]
});
// A colour scale and a data bar compute their own paint, so they take none.
// Several rules on one cell merge per property, the later one winning.Aiming a Rule at a Pivot Measure
The columns of a pivot are generated from the data, so their ids cannot be written into a rule. A measure's place in values can. Give column an object naming that place, and the rule reads and paints every crossing of that measure at
once.
import { conditionalFormatting, pivot } from '@sv5ui/datagrid';
createDataGrid<Sale>({
columns,
data: sales,
getRowId,
features: [
pivot<Sale>({
rows: ['region'],
columns: ['quarter'],
values: [{ field: 'amount', agg: 'sum' }],
totalColumn: 'Total',
totalRow: 'All regions'
}),
conditionalFormatting<Sale>({
rules: [
{
kind: 'colorScale',
// The first entry of values, wherever its columns end up.
column: { measure: 0 },
from: 'var(--color-error)',
via: 'var(--color-warning)',
to: 'var(--color-success)'
}
]
})
]
});Two things differ from a rule aimed at a column. Each cell is ranked against the whole crossing rather than against its own column, which is what makes a heat map of a pivot readable: the same colour means the same amount in any quarter. And the totals stand outside it. Neither the total row nor a total or subtotal column is painted or counted, because a total is not one of the things being compared, and it would push every other cell to the pale end of the scale.
Any of the five kinds takes this form, and FormatTarget is the type that covers both: a column id, or { measure: number }. The panel lists a rule aimed this way by the measure's position.
The Panel
<script lang="ts">
import { DataGrid, Grid } from '@sv5ui/datagrid';
</script>
<!-- The panel lists the rules as chips, adds and removes them, and marks the
ones that could not be compiled. -->
<Grid.FormatPanel {grid} />
<DataGrid {grid} />Under a Server Row Model
A colour scale, a data bar, duplicates and top N all need the whole column to answer: the
lowest and highest value, which values repeat, which rows are in the top three. Under rowModel: 'server' the grid holds one page, so those four are skipped rather than computed against a page and quietly
wrong.
An expression rule is unaffected, because it reads one row and needs no statistics. skippedRules lists what was put aside, and the grid warns once in the console. On a client row model it is
always empty.
Five rules are registered on the grid below and one of them paints. The badges are skippedRules read back, not a list written by hand.
No data
The last few requests
- Nothing yet. Scroll the grid.
const grid = createDataGrid<Person>({
columns, data: [], getRowId,
rowModel: 'server',
features: [
conditionalFormatting({
rules: [
{ kind: 'colorScale', column: 'salary', from: red, via: amber, to: green },
{ kind: 'dataBar', column: 'progress', color: indigo },
{ kind: 'duplicates', column: 'team', class: 'bg-error/15' },
{ kind: 'topN', column: 'salary', n: 3, class: 'font-semibold text-success' },
{ kind: 'expression', column: 'salary', when: '[salary] > 120000', class: 'text-primary' }
]
}),
virtualization({ rowHeight: 40, overscan: 6 }),
serverRowModel(source, { mode: 'infinite', blockSize: 100, placeholder })
]
});
const format = getConditionalFormatting(grid)!;
format.skippedRules; // the four that need the whole column
// The lowest salary, which values repeat, which rows are in the top three:
// none of those can be answered from a hundred rows out of five thousand, so
// they are put aside rather than computed against the block and drawn as if
// they meant something. The expression rule reads one row, and still paints.
//
// To colour by rank under a server row model, rank on the server and send it
// as a column, then write an expression rule against that.onParseError and read back with errorOf. See Server Row Model. Into a Workbook
The rules export as Excel's own conditional formatting, not as flattened colours: open the file and the rules are still rules, still live, and they recalculate when a value changes. A rule aimed at a pivot measure goes across as one rule over every column of that measure, so the scale is shared in the workbook as it is on screen.
import { downloadGridXlsx } from '@sv5ui/datagrid/xlsx';
import { downloadGridXlsx, DEFAULT_FORMAT_COLORS } from '@sv5ui/datagrid/xlsx';
await downloadGridXlsx(grid, {
filename: 'scores.xlsx',
sheetName: 'Scores',
conditionalFormatting: true
});
await downloadGridXlsx(grid, {
conditionalFormatting: { ...DEFAULT_FORMAT_COLORS, bar: '#638EC6' }
});Driving It From Code
import { getConditionalFormatting } from '@sv5ui/datagrid';
const format = getConditionalFormatting(grid)!;
format.addRule({ kind: 'topN', column: 'score', n: 5, class: 'text-success' });
format.removeRule(id);
format.setRules(rules);
format.clearRules();
format.rules; // readonly FormatRule[]
format.errorOf(id); // the parse failure for one rule, or null
format.skippedRules; // put aside because the row model is 'server'
// And on grid.api:
grid.api.addFormatRule?.(rule);
grid.api.getFormatRules?.();
// Rules travel in a snapshot, so a saved view brings its formatting back.
// A rule read from storage is sanitized on the way in: an unknown kind is
// dropped rather than throwing, and at most 64 rules are kept.