Color Scale SpreadsheetGear API Sample

Range Samples Conditional Formats Color Scale

Description

Just like a thermal camera exposes hot and cold spots in your home, a Color Scale Conditional Format visually reveals patterns and trends in your data:

A 2-Color Scale Conditional Format uses one color for the minimum value and another for the maximum value, with a gradient filling the range of values in between.
A 3-Color Scale Conditional Format adds a midpoint color (Minimum50th PercentileMaximum), giving you an extra layer of detail for values between the extreme values.

Create a Color Scale Conditional Format in SpreadsheetGear by calling the IFormatConditions.AddColorScale(int colorScaleType) method on the range you want to apply the conditional format to, passing in either 2 or 3 for the colorScaleType value, for instance:

IFormatCondition cf = worksheet.Cells["A1:D10"].FormatConditions.AddColorScale(2);

Additional tweaks to the Color Scale can be made by accessing the IColorScale object via IFormatCondition.ColorScale. Each individual "criterion" in the Color Scale is accessed through IColorScale.ColorScaleCriteria[...] collection. The below sample modifies the default 2-Color Scale behavior of automatically detecting the maximum / minimum values in the applied range to explicitly settings these values to a minimum of 0 and maximum of 100.

Sample Source Code

// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Add a title to a cell.
cells["A1"].Formula = "Color Scale";

// Get a reference to a range of cells.
SpreadsheetGear.IRange range = cells["A2:A11"];

// Load data and format as $ using multiple cell range.
range.Formula = "=ROW() * 10 - 10";
range.NumberFormat = "$#,##0_);($#,##0)";

// Get a reference to the range's conditional formats collection.
SpreadsheetGear.IFormatConditions conditions = range.FormatConditions;
            
// Add color scale conditional format.
SpreadsheetGear.IFormatCondition condition = conditions.AddColorScale(2);

// Get a reference to the color scale.
SpreadsheetGear.IColorScale colorScale = condition.ColorScale;

// Set min and max scale values.
colorScale.ColorScaleCriteria[0].Type = SpreadsheetGear.ConditionValueTypes.Number;
colorScale.ColorScaleCriteria[0].Value = 0;
colorScale.ColorScaleCriteria[1].Type = SpreadsheetGear.ConditionValueTypes.Number;
colorScale.ColorScaleCriteria[1].Value = 100;

worksheet.UsedRange.EntireColumn.AutoFit();