- Data Grid docs
- Columns and formatting
Conditional formatting
Data bars, three-colour scales and value rules that colour cells, scaled over every matching record and following the light or dark theme.
Conditional formatting makes numbers readable at a glance: a bar in each cell shows its size, a colour scale shows where it sits between the lowest and highest value, and value rules flag the cells that need attention. Bars and scales measure against every matching record, so a cell looks the same whether it is the first row or the millionth.
Open the columns example: bars, a colour scale and value rules
In the grid
Choose Conditional formatting in a column's menu. Add a rule:
- Data bar: a bar from zero to the value. Negative values extend the other way, in their own colour.
- Colour scale: a background from the low colour through the middle to the high colour.
- Value rule: a condition (greater than, at least, less than, at most, between, equals, not equal, contains) and a style: Green fill, Red fill, Amber fill, Blue fill, Bold text or a Custom colour.
A column can have several rules. The first value rule that matches colours the cell; a bar draws on top of the fill.
From code
Rules belong to a column: set them with setColumn(field, { rules }), or in state.columns[].rules. openDialog('conditionalFormatting', { field }) opens the grid's own dialog for a column.
| Rule | Fields |
|---|---|
{ type: 'bar', color? } | color: any CSS colour; the theme's bar colour by default |
{ type: 'scale', colors? } | colors: [low, mid, high]; the theme's colours by default |
{ type: 'value', op, value, value2?, style?, color?, background? } | op: gt, gte, lt, lte, between, equal, notEqual, contains; style: good, bad, warn, info, strong; or your own color and background |
The style presets follow the light and dark themes. Number and date columns compare numbers; other columns compare text, ignoring case.
JavaScript
import { mountTable } from '@8sheets/data-grid';
const grid = await mountTable(document.querySelector('#orders'), { title: 'Orders', data: orders });
await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
await grid.setColumn('Units', { rules: [{ type: 'scale', colors: ['#f8d7da', '#fff3cd', '#d1e7dd'] }] });
await grid.setColumn('Margin', {
rules: [
{ type: 'value', op: 'lt', value: 0, style: 'bad' },
{ type: 'value', op: 'gt', value: 0.4, style: 'good' },
],
});
await grid.setColumn('Status', { rules: [{ type: 'value', op: 'equal', value: 'Overdue', style: 'warn' }] });
// Or let the user pick, in the grid's own dialog:
await grid.openDialog('conditionalFormatting', { field: 'Revenue' });React
import { DataGrid } from '@8sheets/data-grid-react';
async function applyRules(grid) {
await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
await grid.setColumn('Margin', {
rules: [
{ type: 'value', op: 'lt', value: 0, style: 'bad' },
{ type: 'value', op: 'gt', value: 0.4, style: 'good' },
],
});
}
export function Orders({ options }) {
return <div style={{ height: 480 }}><DataGrid options={options} onReady={applyRules} /></div>;
}Vue
<script setup>
import { DataGrid } from '@8sheets/data-grid-vue';
defineProps({ options: Object });
async function applyRules(grid) {
await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
await grid.setColumn('Margin', {
rules: [
{ type: 'value', op: 'lt', value: 0, style: 'bad' },
{ type: 'value', op: 'gt', value: 0.4, style: 'good' },
],
});
}
</script>
<template>
<div style="height: 480px"><DataGrid :options="options" @ready="applyRules" /></div>
</template>Angular
import { Component, Input } from '@angular/core';
import { DataGridComponent, DataGridOptions, MountedDataGrid } from '@8sheets/data-grid-angular';
@Component({
selector: 'app-orders',
imports: [DataGridComponent],
template: `<eight-data-grid [options]="options" (ready)="applyRules($event)" style="height: 480px" />`,
})
export class Orders {
@Input() options: DataGridOptions = {};
async applyRules(grid: MountedDataGrid) {
await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
await grid.setColumn('Margin', {
rules: [
{ type: 'value', op: 'lt', value: 0, style: 'bad' },
{ type: 'value', op: 'gt', value: 0.4, style: 'good' },
],
});
}
}With a server source
Bars and colour scales need each column's smallest and largest value over the whole result: return them in TablePage.stats as { "Revenue": { "min": 0, "max": 98250 } }. Value rules compare the raw values of each row, so include raw in your rows. tableQueryService does both.
Limits
- Only cells on screen are styled, and formatting doesn't change the data. A flat export holds no formatting; an XLSX export with
layout: trueturns the rules into Excel's own conditional formats (see Export). - Bars and scales apply to numbers; a text cell in a number column gets none.
- Rules are not read from Excel files' own conditional formatting.