Introduction
Microsoft Excel provides some great functionality to conditionally format cells to help visualise the data. AG Grid doesn’t do this out of the box but provides you all the means to do so which I will go into detail in this blog. While Excel does provide many different ways to conditionally format your data such as data bars, colour scales and icon sets, I’ll be concentrating on specifically colour scales in this blog, i.e. where the colour contrasts from one to another based on the value:
I’ll be using AG Grid’s classic Olympic Winners data within my grid.
How it works
AG Grid provides the cell style property on a column in order to provide a dynamic style for each cell:
Used to provide CSS styles directly (not using a class) to the cell. Can be either an object of CSS styles, or a function returning an object of CSS styles. AG Grid
This will be the basis of our conditional formatting function. It’s simply really; you need to work out the following for each cell to determine towards which spectrum the colour should be:
How many unique values are there in total for this column (or columns if you are conditionally formatting multiple columns)? This will determine the number of colours you require.
For your current value, how far along is it (the value) towards the top end of the spectrum? Basically the index of the value within all the values. This will determine the colour it should be, if it’s higher then it will be towards the greener colour, while if it’s low, then it will be towards the redder colour.
Generate the colour for the value. This will be based off a JavaScript function I have written which can create a spectrum between 2 colours. The number of colours within this spectrum will be the number of unique values across the column(s).
Demo
The following code demonstrates this ability. I’m retrieving the Olympic winners data from ag-grid.com to display in the grid, then applying the conditional formatting. You can use any colours for the gradient; I’m using the colours red (#FF0000) and green (#00FF00). The gradient will be ap-plied to which ever columns you supply the conditionalFormattingCellStyle function to as a cell style. I’ve implemented this functionality in a simple vanilla javascript example:
JavaScript:
const gradientLowValueColour = '#FF0000'
const gradientHighValueColour = '#00FF00'
var columnDefs = [{
field: 'athlete'
}, {
field: 'country'
}, {
field: 'gold',
cellStyle: conditionalFormattingCellStyle,
sortable: true
}, {
field: 'silver',
cellStyle: conditionalFormattingCellStyle,
sortable: true,
}, {
field: 'bronze',
cellStyle: conditionalFormattingCellStyle,
sortable: true,
}, ]; // specify the data
var rowData = [];
let allValuesInTable = [];
let valuesForTableOrdered;
function conditionalFormattingCellStyle(params) { // the index, or how far along the value is in the gradient
const valueIndex = valuesForTableOrdered.indexOf(params.value); // get the colour for the cell, depending on its index
const bgColour = generateColor(gradientHighValueColour, gradientLowValueColour, valuesForTableOrdered.length, valueIndex);
return {
backgroundColor: '#' + bgColour
};
} // let the grid know which columns and what data to use
var gridOptions = {
columnDefs: columnDefs,
rowData: rowData
}; // setup the grid after the page has finished loading
document.addEventListener('DOMContentLoaded', function() {
var gridDiv = document.querySelector('#myGrid');
new agGrid.Grid(gridDiv, gridOptions);
agGrid.simpleHttpRequest({
url: 'https://www.ag-grid.com/example-assets/olympic-winners.json',
}).then(function(data) {
rowData = data.slice(0, 10); // rowData = data;
initialiseValuesRequiredForConditionalFormatting(gridOptions);
gridOptions.api.setRowData(rowData);
});
});
function initialiseValuesRequiredForConditionalFormatting(gridOptions) {
const columnDefs = gridOptions.api.getColumnDefs();
const columnDefFieldForConditionalFormatting = columnDefs.filter((x) => x.cellStyle?.name === 'conditionalFormattingCellStyle').map((x) => {
if (x.field) {
return x.field;
}
});
rowData.forEach((x) => {
columnDefFieldForConditionalFormatting.forEach(field => {
allValuesInTable.push(x[field]);
})
});
const uniquValues = [...new Set(allValuesInTable)];
valuesForTableOrdered = uniquValues.sort(function(a, b) {
return a - b;
});
}
function hex(c) {
var s = '0123456789abcdef';
var i = parseInt(c);
if (i == 0 || isNaN(c)) return '00';
i = Math.round(Math.min(Math.max(0, i), 255));
return s.charAt((i - (i % 16)) / 16) + s.charAt(i % 16);
} /* Convert an RGB triplet to a hex string */
function convertToHex(rgb) {
return hex(rgb[0]) + hex(rgb[1]) + hex(rgb[2]);
} /* Remove '#' in color hex string */
function trim(s) {
return s.charAt(0) == '#' ? s.substring(1, 7) : s;
} /* Convert a hex string to an RGB triplet */
function convertToRGB(hex) {
var color = [];
color[0] = parseInt(trim(hex).substring(0, 2), 16);
color[1] = parseInt(trim(hex).substring(2, 4), 16);
color[2] = parseInt(trim(hex).substring(4, 6), 16);
return color;
}
function generateColor(colorStart, colorEnd, colorCount, index) { // The beginning of your gradient
var start = convertToRGB(colorStart); // The end of your gradient
var end = convertToRGB(colorEnd); // The number of colors to compute
var len = colorCount; //Alpha blending amount var alpha = 0.0;
var saida = [];
for (i = 0; i < len; i++) {
var c = [];
alpha += 1.0 / len;
c[0] = start[0] * alpha + (1 - alpha) * end[0];
c[1] = start[1] * alpha + (1 - alpha) * end[1];
c[2] = start[2] * alpha + (1 - alpha) * end[2];
saida.push(convertToHex(c));
}
return saida[index];
}HTML:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Ag-Grid Basic Example</title>
<script src="https://unpkg.com/ag-grid-community/dist/ag-grid-community.min.js"></script>
<script src="main.js"></script>
</head>
<body>
<div id="myGrid" style="height: 1000px; width: 1000px" class="ag-theme-alpine"></div>
</body>
</html>The final result will look like this:
You can find a fully working demo on Plunker here.
Comments