Conditional formatting - color scale

The following example shows you how to apply a two-color scale rule that shades cells from red (minimum) to green (maximum).

C# example

using System;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Worksheet sheet = new Worksheet();
            sheet["A1"] = new Cell(10);
            sheet["A2"] = new Cell(40);
            sheet["A3"] = new Cell(70);
            sheet["A4"] = new Cell(30);
            sheet["A5"] = new Cell(90);

            ColorScale colorScale = new ColorScale();

            ConditionalFormatValueObject minObj = new ConditionalFormatValueObject();
            minObj.Type = ConditionalFormatValueObjectType.Minimum;
            minObj.Value = "0";
            colorScale.ConditionalFormatValueObjects.Add(minObj);

            ConditionalFormatValueObject maxObj = new ConditionalFormatValueObject();
            maxObj.Type = ConditionalFormatValueObjectType.Maximum;
            maxObj.Value = "0";
            colorScale.ConditionalFormatValueObjects.Add(maxObj);

            colorScale.Colors.Add(new DataBarColor("#FF0000")); // red = min
            colorScale.Colors.Add(new DataBarColor("#00FF00")); // green = max

            ConditionalFormattingRule rule = new ConditionalFormattingRule();
            rule.Type = ConditionalFormatType.ColorScale;
            rule.Priority = 1;
            rule.ColorScale = colorScale;

            ConditionalFormatting cf = new ConditionalFormatting();
            cf.Reference = "A1:A5";
            cf.Rules.Add(rule);
            sheet.ConditionalFormattings.Add(cf);

            Workbook book = new Workbook();
            book.Sheets.Add(sheet);
            book.Save("c:\\test\\output.xlsx", true);
        }
    }
}

VB example

Imports System
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim sheet As New Worksheet()
        sheet("A1") = New Cell(10)
        sheet("A2") = New Cell(40)
        sheet("A3") = New Cell(70)
        sheet("A4") = New Cell(30)
        sheet("A5") = New Cell(90)

        Dim colorScale As New ColorScale()

        Dim minObj As New ConditionalFormatValueObject()
        minObj.Type = ConditionalFormatValueObjectType.Minimum
        minObj.Value = "0"
        colorScale.ConditionalFormatValueObjects.Add(minObj)

        Dim maxObj As New ConditionalFormatValueObject()
        maxObj.Type = ConditionalFormatValueObjectType.Maximum
        maxObj.Value = "0"
        colorScale.ConditionalFormatValueObjects.Add(maxObj)

        colorScale.Colors.Add(New DataBarColor("#FF0000")) 'red
        colorScale.Colors.Add(New DataBarColor("#00FF00")) 'green

        Dim rule As New ConditionalFormattingRule()
        rule.Type = ConditionalFormatType.ColorScale
        rule.Priority = 1
        rule.ColorScale = colorScale

        Dim cf As New ConditionalFormatting()
        cf.Reference = "A1:A5"
        cf.Rules.Add(rule)
        sheet.ConditionalFormattings.Add(cf)

        Dim book As New Workbook()
        book.Sheets.Add(sheet)
        book.Save("c:\test\output.xlsx", True)

    End Sub
End Module

IndependentsoftProfessional software libraries for developers

Spreadsheet .NET > Tutorial > Conditional formatting - color scale

Need help? Ask our developers: