C# Color Scale SpreadsheetGear API Sample

Excel-Compatible Samples for .NET

Description

Just like a thermal camera exposes hot and cold spots in your home, Excel's Color Scale Conditional Format feature visually reveals patterns and trends in your data. See the below sample and description for creating Excel-compatible Color Scales using SpreadsheetGear:

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 C# 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();