React Spreadsheet Conditional Formatting

    The React Spreadsheet component allows you to conditionally format the cells of a worksheet. This allows you to highlight different pieces of your data based on a condition.

    React Spreadsheet Conditional Formatting Example

    Conditional Formatting Overview

    You can configure the conditional formatting of a particular worksheet by using the many Add methods exposed on the conditionalFormats collection of that worksheet. The first parameter of these Add methods is the string region of the worksheet that you would like to apply the conditional format to.

    Many of the conditional formats that you can add to your worksheet have a cellFormat property that determines the way that the cells should look when the condition in that conditional format holds true. For example, you can use the properties attached to this cellFormat property such as fill and font to determine the background and font settings of your cells, respectively.

    When a conditional format is created and a cellFormat applied, there is a subset of properties that are currently supported by the worksheet cell. The properties that are currently honored off of the cellFormat are fill, Border properties, formatString, and some font properties such as strikethrough, underline, italic, bold, and color. Many of these can be seen from the code snippet below.

    There are a few conditional formats that do not have a cellFormat property, as their visualization on the cells behaves differently. These conditional formats are the DataBarConditionalFormat, ColorScaleConditionalFormat, and IconSetConditionalFormat.

    When loading a pre-existing workbook from Excel, the formats will be preserved when that workbook is loaded. The same is true for when you save the workbook out to an Excel file.

    The following lists the supported conditional formats in the React IgrSpreadsheet control:

    • AverageConditionalFormat: Added using the addAverageCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s value is above or below the average or standard deviation for the associated range.
    • BlanksConditionalFormat: Added using the addBlanksCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value is not set.
    • ColorScaleConditionalFormat: Added using the addColorScaleCondition method, this conditional format exposes properties which control the coloring of a worksheet cell based on the cell’s value as relative to minimum, midpoint, and maximum threshold values.
    • DataBarConditionalFormat: Added using the addDataBarCondition method, this conditional format exposes properties which display data bars in a worksheet cell based on the cell’s value as relative to the associated range of values.
    • DateTimeConditionalFormat: Added using the addDateTimeCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s date value falls within a given range of time.
    • DuplicateConditionalFormat: Added using the addDuplicateCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s value is unique or duplicated across the associated range.
    • ErrorsConditionalFormat: Added using the addErrorsCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value is valid.
    • FormulaConditionalFormat: Added using the addFormulaCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value meets the criteria defined by a formula.
    • IconSetConditionalFormat: Added using the addIconSetCondition method, this conditional format exposes properties which display icons in a worksheet cell based on the cell’s value as relative to threshold values.
    • NoBlanksConditionalFormat: Added using the addNoBlanksCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value is set.
    • NoErrorsConditionalFormat: Added using the addNoErrorsCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value is valid.
    • OperatorConditionalFormat: Added using the addOperatorCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether the cell’s value meets the criteria defined by a logical operator.
    • RankConditionalFormat: Added using the addRankCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s value is within the top of bottom rank of values across the associated range.
    • TextOperatorConditionalFormat: Added using the addTextCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s text value meets the criteria defined by a string and a FormatConditionTextOperator value as placed in the addTextCondition method’s parameters.
    • UniqueConditionalFormat: Added using the addUniqueCondition method, this conditional format exposes properties which control the visual attributes of a worksheet cell based on whether a cell’s value is unique across the associated range.

    Dependencies

    In order to add conditional formatting to the IgrSpreadsheet control, you will need to import the following dependencies:

    import { CellFill } from "igniteui-react-excel";
import { Color } from 'igniteui-react-core';
import { ColorScaleType } from "igniteui-react-excelScaleType";
import { FormatConditionAboveBelow } from 'igniteui-react-excel';
import { FormatConditionIconSet } from 'igniteui-react-excel';
import { FormatConditionOperator } from 'igniteui-react-excel';
import { FormatConditionTextOperator } from 'igniteui-react-excel';
import { FormatConditionTimePeriod } from 'igniteui-react-excel';
import { FormatConditionTopBottom } from "igniteui-react-excel";
import { WorkbookColorInfo } from 'igniteui-react-excel';

    Code Snippet

    The following code snippet demonstrates how to add each of the conditional formats to a spreadsheet in the IgrSpreadsheet control:

    let sheet = this.spreadsheet.activeWorksheet;

let red = new Color();
red.colorString = "#ff0000";

let blue = new Color();
blue.colorString = "#0000ff";

let green = new Color();
green.colorString = "#00ff00";

let gray = new Color();
gray.colorString = "#d3d3d3";

let avgFormat = sheet.conditionalFormats().addAverageCondition("A1:A10", FormatConditionAboveBelow.AboveAverage);
avgFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(red);

let blanksFormat = sheet.conditionalFormats().addBlanksCondition("B1:B10");
blanksFormat.cellFormat.fill = CellFill.createSolidFill(new WorkbookColorInfo(gray));

let colorFormat = sheet.conditionalFormats().addColorScaleCondition("C1:C10", ColorScaleType.ThreeColor);
colorFormat.minimumThreshold.formatColor = new WorkbookColorInfo(red);
colorFormat.midpointThreshold.formatColor = new WorkbookColorInfo(blue);
colorFormat.maximumThreshold.formatColor = new WorkbookColorInfo(green);

let dataBarFormat = sheet.conditionalFormats().addDataBarCondition("D1:D10");

let dateTimeFormat = sheet.conditionalFormats().addDateTimeCondition("E1:E10", FormatConditionTimePeriod.NextWeek);
dateTimeFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(red);

let duplicateFormat = sheet.conditionalFormats().addDuplicateCondition("F1:F10");
duplicateFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(blue);

let errorFormat = sheet.conditionalFormats().addErrorsCondition("G1:G10");
errorFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(green);

let formulaFormat = sheet.conditionalFormats().addFormulaCondition("H1:H10", "=H1>2");
formulaFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(red);

let iconFormat = sheet.conditionalFormats().addIconSetCondition("I1:I10", FormatConditionIconSet.IconSet3TrafficLights1);

let noBlanksFormat = sheet.conditionalFormats().addNoBlanksCondition("J1:J10");
noBlanksFormat.cellFormat.fill = CellFill.createSolidFill(new WorkbookColorInfo(gray));

let noErrorFormat = sheet.conditionalFormats().addErrorsCondition("K1:K10");
noErrorFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(red);

let operatorFormat = sheet.conditionalFormats().addOperatorCondition("L1:L10", FormatConditionOperator.Greater);
operatorFormat.setOperand1(500);
operatorFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(blue);

let rankFormat = sheet.conditionalFormats().addRankCondition("M1:M10", FormatConditionTopBottom.Top, 5);
rankFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(green);

let textFormat = sheet.conditionalFormats().addTextCondition("N1:N10", "A", FormatConditionTextOperator.Contains);
textFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(red);

let uniqueFormat = sheet.conditionalFormats().addUniqueCondition("O1:O10");
uniqueFormat.cellFormat.font.colorInfo = new WorkbookColorInfo(blue);

    API References