top of page

Conditional Formatting in AG Grid

Updated: Jul 27, 2023

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.

20 views0 comments

I'm a lead software developer currently working at AG Grid in London.

Technologies I'm currently focused on include Salesforce, .NET Core, Angular, SQL, React and Azure.

Other than that, in my spare time I watch the Arsenal at the Emirates, work on side projects or watch sports. Oh, and I'm also a part-time body builder.

You can contact me at vh@viqas.co.uk

profile.jpg

About Viqas Hussain

bottom of page