How to do conditional formatting with formula using range by EPPlus

Question

_{Monday, March 18, 2019 8:17 PM}

suppose i have value from cell A1 to A11

now i want to create a range from A1 to A10 and assign formula to this range A1:A10. in the formula how to write instruction like that if A1 value is greater than cell A11 then A1 cell color will be green and if A1 value is less than cell A11 then A1 cell color will be red.

the same way A2 to A10 same this will be carried forward.

but i want to assign some formula to range instead of each cell from A1 to A10. how it will be possible with conditional formatting with formula using EPPlus. i stuck here badly because i have little knowledge in excel. so please some one help me to complete the code. so please help me with code example. thanks

All replies (4)

_{Tuesday, March 19, 2019 8:18 AM ✅Answered}

Hi Stupid_inn,

Thank you for posting here.

For your question, please try the code below. I use values from A1 to A11 for example. The A6 would be left.

 FileInfo existingFile = new FileInfo("1-1.xlsx");
            using (var package = new ExcelPackage(existingFile))
            {
                ExcelWorkbook workBook = package.Workbook;

                var currentWorksheet = workBook.Worksheets.First();
                currentWorksheet.Workbook.CalcMode = ExcelCalcMode.Automatic;

                for (int i = 1; i < 12; i++)
                {
                    ExcelAddress _formatRangeAddress = new ExcelAddress("$A$" + i);

                    int j = 11 - i;
                    string _statement = "=$A$" + i + ">$A$" + j;
                    var f = currentWorksheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
                    f.Style.Fill.BackgroundColor.Color = Color.Green;
                    f.Formula = _statement;

                    _statement = " =$A$" + i + "<$A$" + j;
                    f = currentWorksheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
                    f.Style.Fill.BackgroundColor.Color = Color.Red;
                    f.Formula = _statement;
                }


                package.Save();       }

The original excel file.

The saved excel file.

Best Regards,

Wendy

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Wednesday, March 20, 2019 1:01 PM}

you have not given code to add data to cell. you have given code for conditional formatting. so please share complete code.

i have seen that when i am setting conditional formatting during excel file preparation then it takes extra time to create excel when i have huge data.

suppose if i have 1000 records to write in excel file and when i add conditional formatting for 500 cell then it takes extra time to complete the whole job. when i comment out conditional formatting related code then excel is created much faster or quicker.

how to minimize time when working or setting conditional formatting to many cell ?

please share idea. thanks

_{Friday, March 22, 2019 4:40 PM}

@Wendy please see my second post and answer.

_{Friday, March 22, 2019 7:50 PM}

I have huge columns and rows. the way you are attaching formula that is with each cell and if i follow that approach then i have to iterate in huge cell which will slow down my program. can't we attach formula with range where i will mention relative cell address ?

please share your suggestion.