Search code examples
c#asp.netopenxmlepplus

EPPlus not caluculating formula output after binding

I am using EPPlus. I am stuck at cell formulas.

My code is below:

ExcelPackage pck = new ExcelPackage(@"D:\MYSheets\EmptyFile.xlsx");
var ws = pck.Workbook.Worksheets["MySheet"];

ws.Cells["A3"].Value = "2.3";
ws.Cells["A4"].Value = "10.2";

ws.Cells["A5"].Formula = "=SUM(A3:A4)";
ws.Cells["A5"].Style.Numberformat.Format = "#,##0.00";
pck.Save();

When I open Excel, by default, A5 cell is not calculating the sum of A3 and A4. Unless I modify the A3 and/or A4 cells, the A5 cell remains not calculated.

I tried using the following code but it didn't work for me:

ws.Workbook.CalcMode = ExcelCalcMode.Automatic;
Solution

  • Try:

    ws.Cells["A3"].Value = 2.3;
ws.Cells["A4"].Value = 10.2;

    You were telling EPPlus to store the values as a string, that's why the formula failed.