CODEWITHSUNDEEP
c#excelepplus
Mist
Mist

Reputation: 684

EPPlus Excel not opening after applying conditional formatting

I am generating excel with EPPlus and it was opening but the moment i applied condition formatting excel generation slow and file is opening in my pc but not opening in another pc.

For conditional formatting right font color is coming. my objective of conditional formatting is if cell value >-1 then color will be Green and if cell value is <0 then color will be red. The code i got from google search for conditional formatting which is working fine when i open excel file in my pc but when same excel open in another pc there a error is coming for conditional formatting.

Here i am sharing my conditional formatting code. please have a look and tell me does it applied properly or code is not right one.

#region Conditional Formatting
address = new ExcelAddress(AvgPeriod3 + row.ToString());
_statement1 = "=AND($" + address + ">-1)";
condition = ws.ConditionalFormatting.AddExpression(address);
condition.Formula = _statement1;
condition.Style.Font.Color.Color = System.Drawing.Color.Green;

address = new ExcelAddress(AvgPeriod3 + row.ToString());
_statement1 = "=AND($" + address + "<0)";
condition = ws.ConditionalFormatting.AddExpression(address);
condition.Formula = _statement1;
condition.Style.Font.Color.Color = System.Drawing.Color.Red;
#endregion

if the above code is not right one for my requirement then please suggest me what to change in the above code.

another things not clear that excel file is opening in my pc but throwing error regarding conditional formatting when try to open in another pc. excel version is more less same or close in two pc.

Thanks

Upvotes: 0

Views: 115

Answers (1)

user15940620
user15940620

Reputation:

This way you can add conditional formatting.

string _StartPeriod = "", _EndPeriod = "";
                //_EarningID_Periods
                for (int p = 0; p <= _EarningID_Periods.Count - 1; p++)
                {
                    Period = _EarningID_Periods[p].NewPeriod.Replace("A", "").Replace("E", ""); //ds.Tables[1].Rows[p]["old_Periods"].ToString().Replace("A", "").Replace("E", "");

                    _StartPeriod = listOfCell.Where(a => a.EarningsType == "NEW"
                    && a.PeriodType == "DELTA_PERCENTAGE_PERIOD" && a.Period.Replace("A", "").Replace("E", "") == Period
                    ).FirstOrDefault().CoorDinate + "4";

                    _EndPeriod = listOfCell.Where(a => a.EarningsType == "NEW"
                    && a.PeriodType == "DELTA_ABSOLUTE" && a.Period.Replace("A", "").Replace("E", "") == Period
                    ).FirstOrDefault().CoorDinate + row.ToString();

                    if (_StartPeriod != "" && _EndPeriod != "")
                    {
                        ExcelAddress formatRangeAddress = new ExcelAddress(_StartPeriod + ":" + _EndPeriod);
                        var cond1 = ws.ConditionalFormatting.AddLessThan(formatRangeAddress);
                        cond1.Style.Font.Color.Color = CSMUtils._RedColor;
                        cond1.Formula = "0";

                        formatRangeAddress = new ExcelAddress(_StartPeriod + ":" + _EndPeriod);
                        var cond2 = ws.ConditionalFormatting.AddGreaterThan(formatRangeAddress);
                        cond2.Style.Font.Color.Color = CSMUtils._GreenColor; //CSMUtils.SetRGBColor(0, 97, 0);
                        cond2.Formula = "0";
                    }
                }

Upvotes: 0

Related Questions