Version

Conditional Formatting in igSpreadsheet

Purpose

This topic explains how to configure and set conditional formatting on a Worksheet in the igSpreadsheet control.

Required Background

To understand this topic you need to be familiar with the concept and topics related to the Infragistics JavaScript Excel Library.

In this Topic

This topic contains the following sections:

Conditional Formatting Configuration Summary

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 method that determines the way that the worksheet cell elements should look when the condition in that conditional format holds true. For example, you can use the methods attached to this CellFormat method such as Fill and Font to determine the background and font settings of your cells, respectively.

When a conditional format is created and a cell format 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 method 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 method, as their visualization on the worksheet cell 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 into the igSpreadsheet. The same is true for when you save the Workbook out to an Excel file.

Conditional Formatting Configuration Summary Chart

The following table briefly explains the different conditional formats supported by the worksheet and maps them to the methods off of that element's ConditionalFormats collection used to add them:

Conditional Format Method Description
AverageConditionalFormat addAverageCondition Exposes methods 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 addBlanksCondition Exposes properties which control the visual attributes of a worksheet cell based on whether the cell's value is not set.
ColorScaleConditionalFormat addColorScaleCondition 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 addDataBarCondition 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 addDateTimeCondition 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 addDuplicateCondition 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 addErrorsCondition Exposes properties which control the visual attributes of a worksheet cell based on whether the cell's value is valid.
FormulaConditionalFormat addFormulaCondition 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 addIconSetCondition Exposes properties which display icons in a worksheet cell based on the cell's value as relative to threshold values.
NoBlanksConditionalFormat addNoBlanksCondition Exposes properties which control the visual attributes of a worksheet cell based on whether the cell's value is set.
NoErrorsConditionalFormat addNoErrorsCondition Exposes properties which control the visual attributes of a worksheet cell based on whether the cell's value is valid.
OperatorConditionalFormat addOperatorCondition 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 addRankCondition 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 addTextCondition 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.
UniqueConditionalFormat addUniqueCondition Exposes properties which control the visual attributes of a worksheet cell based on whether a cell's value is unique across the associated range.

Code Example

The following code example demonstrates usage of many of the conditional formats mentioned above in the igSpreadsheet control.

In HTML:

$(function () {

        var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
        var sheet = workbook.worksheets().add('Sheet1');

        var duplicateCondition = sheet.conditionalFormats().addDuplicateCondition("A2:A15");
        duplicateCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("red"));                               
        var blanksCondition = sheet.conditionalFormats().addBlanksCondition("B2:B15");
        blanksCondition.cellFormat().fill($.ig.excel.CellFill.createSolidFill("gray"));

        var textCondition = sheet.conditionalFormats().addTextCondition("C2:C15", "Bev");
        textCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("blue"));                                 
        var uniqueCondition = sheet.conditionalFormats().addUniqueCondition("D2:D15");      
        uniqueCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("orange"));

        var notBlankCondition = sheet.conditionalFormats().addNoBlanksCondition("E2:E15");
        notBlankCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("green"));

        sheet.conditionalFormats().addDataBarCondition("F2:F15");

        var avgCondition = sheet.conditionalFormats().addAverageCondition("G2:G15");
        avgCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("red"));

        $("#spreadsheet").igSpreadsheet({
            height: "100%",
            width: "100%",
            workbook: workbook
        });          
    });

After filling the worksheet element with sample data, the above will result in an igSpreadsheet that looks like the following:

Related Content

View on GitHub