Cell value added via EPPlus is not detected by formula

Apr 11, 2011 at 3:16 AM

Hi,

I have this line of code to add a formula on a given range:

using (var range = EnvSheet.Cells[49, 4, 86, 4])
                    {
                        range.Formula = "=AVERAGEIF(E49:P49,\">0\")*(1+C$47)";
                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        range.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
                    }

Now I want to add a value to a target cell as shown:

FillCurrentSheet.Cells[49, 5].Value = "00:00:25";

When I open Excel to view the result, the value 00:00:25 is there, but the formula is still #DIV/0 (Divide Zero Error).

What I've noticed though is when I manually set the value, or just retype "00:00:25" via the Excel UI, the formatting remains the same in the cell, but looking up at the Formula Bar, it changes to 12:00:25 AM and the AVERAGEIF formula now actually recognizes the cell instead of displaying #DIV/0.

I've already tried setting the Numberformat before and after setting the value using this code:

FillCurrentSheet.Cells[49, 5].Style.Numberformat.Format = "h:mm:ss";

Still unlucky though. Will try ExcelCalcMode.Automatic.Equals(true) and see how it goes.

Just want to make sure if there's any other way to mimic the behavior that Excel does using EPPlus? It's relatively strange as the formula bar does not follow the formatting of the actual cell.

Apr 11, 2011 at 3:37 AM

ExcelCalcMode didn't cut it as well.

Coordinator
Apr 11, 2011 at 10:16 AM

Hi,

If you add it as a string, it will be a string in Excel.

Add it as a date instead...

FillCurrentSheet.Cells[49, 5].Value = new DateTime(new TimeSpan(0, 0, 25).Ticks)

Jan

Apr 12, 2011 at 11:36 PM

Thanks Jan, highly helpful. Will review the source as this intrigued me.

Cheers.