Excel:Color code values in a Column based on condition

[creminjuse1009]

Team,

I have a excel file that i generate and data loaded to excel file is dynamic but the column names are static and column order is not static.
so i am looking to color code the values in a column. And number of rows is not static as well and i am having trouble passing the range as well as setting color.

sample data in excel looks like below

student-name percentage
TOM 65.8%
Jack 85.7%
Robert 45.2%
Harry 97.2%

so here i am trying to color code all the students that has greater than 80% in Green Color and leave all others without color coding.

Thanks for the help
Chris

[rachel.lei]

Hello,

Thanks for your inquiry.
Please refer to the following code. If this is not what you want, to help us understand your requirement accurately, please provide your input file as well as your expected output.

Code: Select all

    Workbook workbook = new Workbook();
    workbook.LoadFromFile("test.xlsx");
    Worksheet sheet = workbook.Worksheets[0];
    XlsConditionalFormats xcfs1 = sheet.ConditionalFormats.Add();
    xcfs1.AddRange(sheet.Range["B2:B5"]);
    IConditionalFormat cf1 = xcfs1.AddCondition();
    cf1.FormatType = ConditionalFormatType.CellValue;
    cf1.FirstFormula = "80.0%";
    cf1.Operator = ComparisonOperatorType.Greater;
    cf1.BackColor = Color.Green;
    workbook.SaveToFile("out.xlsx", ExcelVersion.Version2013);


My output:

My_output.png (6.54 KiB) Viewed 1262 times

Sincerely,
Rachel
E-iceblue support team

[creminjuse1009]

Thanks Rachel for responding quickly to my request.

I tried you code it worked for my requirement. I wanted to see if we have flexibility of passing the Range dynamically as in
instead of passing it as Range["B2:B5"] in below example to identify what is the last cell in column. it can be B5 or B20 etc.

i tried this below code in my program and when i do ComparisonOperatorType.Less it even applies color green to the values with blank or empty.
Not sure how to avoid blanks. here in below case Chris doesn't have score and still it is color coding chris percentage to green.

also can you try once less than 65% and Greater than 65% and see if you get correct results.i noticed when you have 100.0% in data and when you do less than 65% even 100.0% is getting color coded.

TOM 65.8%
Jack 85.7%
Robert 100.0%
Chris
Harry 97.2%

Code: Select all

Workbook wrk = new Workbook();
                wrk.LoadFromFile(fileName);
                Worksheet sht = wrk.Worksheets[0];
                sht.Name = Config.worksheetName;
                XlsConditionalFormats xcfs1 = sht.ConditionalFormats.Add();
                xcfs1.AddRange(sht.Range["B2:B6"]);
                IConditionalFormat cf1 = xcfs1.AddCondition();
                cf1.FormatType = ConditionalFormatType.CellValue;
                cf1.FirstFormula = "65.0%";               
                cf1.Operator = ComparisonOperatorType.Less;
                cf1.BackColor = Color.Green;
                wrk.Save();
                return true;

[rachel.lei]

Hello,

Thanks for your feedback.
You can refer to the below code to get the index of the last row.

Code: Select all

            XlsConditionalFormats xcfs1 = sht.ConditionalFormats.Add();
            int rowIndex = sht.LastRow;
            xcfs1.AddRange(sht.Range[2, 2, rowIndex, 2]);
            //......

As for "it even applies color green to the values with blank or empty" you mentioned, our Spire.XLS is based on Microsoft Excel. I tried to add the same conditional formatting rule in Microsoft Excel and got the same result, please see the attached picture. Hope you can understand. However, you could refer to the following code to achieve the effect you want.

Code: Select all

            IConditionalFormat cf1 = xcfs1.AddCondition();
            cf1.FormatType = ConditionalFormatType.CellValue;
            //Value between 0.0001% and 65.0%
            cf1.FirstFormula = "0.0001%"; //A very small value
            cf1.SecondFormula = "65.0%";
            cf1.Operator = ComparisonOperatorType.Between;
            cf1.BackColor = Color.Green;

And about "less than 65% and Greater than 65%" you said, what's your testing code? Could you please share it with us for further investigation?

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Hope you are doing well.
How is your issue now? Could you please give us some feedback at your convenience?
Thanks in advance.

Sincerely,
Rachel
E-iceblue support team