ColorScale

[craigraelharris]

How do I add a formatting rule to a range or cell that uses a 2 color scale for percentages?
I can't find an example anywhere

I realize it's something like

var format1 = range.ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.ColorScale;

But then how do I set the colorscale and how do I tie it to the %?

[Betsy.jiang]

Dear craigraelharris,

Thanks for your inquiry.
Here is sample code for your reference.

Code: Select all

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Range["A1"].NumberValue = 0.1;
            sheet.Range["A2"].NumberValue = 0.4;
            sheet.Range["A3"].NumberValue = 0.5;
            sheet.Range["A4"].NumberValue = 0.6;
            sheet.Range["A5"].NumberValue = 0.8;
            //set the number format as percentage
            sheet.Range["A1:A5"].NumberFormat = "0.00%";
            ConditionalFormatWrapper format = sheet.Range["A1:A5"].ConditionalFormats.AddCondition();
            format.FormatType = ConditionalFormatType.ColorScale;
            //set the format as 2-Color Scale
            format.ColorScale.SetConditionCount(2);
            IList<ColorConditionValue>  values =  format.ColorScale.Criteria;
            //set the color
            values[0].FormatColor = Color.FromArgb(0, 255, 255);                   
            workbook.SaveToFile("ColorScale9469.xlsx", ExcelVersion.Version2010);

Hope this help you. If this doesn't meet your requirement, please provide us a target document( you can create it by MS excel) for further investigation.

Sincerely,
Betsy
E-iceblue support team

[craigraelharris]

Perfect! Thanks for the quick reply.

[Betsy.jiang]

Dear craigraelharris,

Thanks for your feedback.
Please feel free to contact us if there is any question. We are here for help.

Sincerely,
Betsy
E-iceblue support team

[tlnorwood]

I am receiving a warning that this method is obsolete. Can you please post updated code?

[Triste.Dai]

I am receiving a warning that this method is obsolete. Can you please post updated code?

Hi,

Thanks for your feedback.
Please see the following code for reference.

Code: Select all

            //Create a workbook.
            Workbook workbook = new Workbook();

            //Get the first worksheet.
            Worksheet sheet = workbook.Worksheets[0];

            //Insert data to cell range from A1 to C4.
            sheet.Range["A1"].NumberValue = 582;
            sheet.Range["A2"].NumberValue = 234;
            sheet.Range["A3"].NumberValue = 314;
            sheet.Range["A4"].NumberValue = 50;
            sheet.Range["B1"].NumberValue = 150;
            sheet.Range["B2"].NumberValue = 894;
            sheet.Range["B3"].NumberValue = 560;
            sheet.Range["B4"].NumberValue = 900;
            sheet.Range["C1"].NumberValue = 134;
            sheet.Range["C2"].NumberValue = 700;
            sheet.Range["C3"].NumberValue = 920;
            sheet.Range["C4"].NumberValue = 450;
            sheet.AllocatedRange.RowHeight = 15;
            sheet.AllocatedRange.ColumnWidth = 17;

            //Create conditional formatting rule.
            XlsConditionalFormats xcfs1 = sheet.ConditionalFormats.Add();
            xcfs1.AddRange(sheet.AllocatedRange);
            IConditionalFormat format1 = xcfs1.AddCondition();
            format1.FormatType = ConditionalFormatType.CellValue;
            format1.FirstFormula = "800";
            format1.Operator = ComparisonOperatorType.Greater;
            format1.FontColor = Color.Red;
            format1.BackColor = Color.LightSalmon;

            //Create conditional formatting rule.
            XlsConditionalFormats xcfs2 = sheet.ConditionalFormats.Add();
            xcfs2.AddRange(sheet.AllocatedRange);
            IConditionalFormat format2 = xcfs1.AddCondition();
            format2.FormatType = ConditionalFormatType.CellValue;
            format2.FirstFormula = "300";
            format2.Operator = ComparisonOperatorType.Less;
            format2.FontColor = Color.Green;
            format2.BackColor = Color.LightBlue;

            String result = "Result-ApplyConditionalFormattingToDataRange.xlsx";

            //Save to file.
            workbook.SaveToFile(result, ExcelVersion.Version2013);

If you have any other questions, just feel free to contact us.

Sincerely,
Triste
E-iceblue support team