CODEWITHSUNDEEP
c#excelepplusepplus-4
Binke
Binke

Reputation: 900

EPPlus formula not working without calling Calculate

I've tried to use two functions in excel INDEX and MATCH and according to EPPlus codeplex, these should be supported. Everytime I open the file, there's a warning message saying that something is broken and asks if I want to repare the document, and when it opens, all formulas are gone. When repairing the document a dialog is shown saying that the formula has been removed from /xl/worksheets/sheet1.xml-del.

I can put the formulas manually into the spreedsheet and they work, but the only way to get any data to show in the generated xlsx file is to call Calculate() on the worksheet. This will output the data correctly, but what I need is to have the formulas inside the document.

Here's a small example of formulas I've tried

private byte[] TestingFormula()
{
    using (ExcelPackage pck = new ExcelPackage(_helper.GetTemplateStream("TEST.xlsx")))
    {
        var ws = pck.Workbook.Worksheets[1];

        ws.Cells["A4"].Formula = "INDEX($C$1:$D$24;4;2)";
        ws.Cells["A6"].Formula = "INDEX(C1:D24;6;2)";

        ws.Cells["B26"].Formula = "MATCH($B$12;$C$1:$D$1;0)";

        // sum works as expected
        //ws.Cells["D25"].Formula = "SUM(D2:D24)";

        //pck.Workbook.Calculate();
        //pck.Workbook.CalcMode = ExcelCalcMode.Automatic;
        return pck.GetAsByteArray();
    }
}

The TEST.xlsx is just a template that looks like this

enter image description here

So, what am I doing wrong here?

Upvotes: 1

Views: 5959

Answers (2)

swmal
swmal

Reputation: 171

Correct answer by reasra (I can't comment yet). This is documented under the "Some common mistakes" on this page:

https://epplus.codeplex.com/wikipage?title=About%20Formula%20calculation&referringTitle=Documentation

  • Don't use localized function names. Only english names (such as SUM, IF, VLOOKUP, etc) are supported.
  • Don't use semicolon as a separator between function arguments. Only comma is supported.
  • Don't add the leading = sign in your formula. "=SUM(A1:A2)" is wrong, "SUM(A1:A2)" is correct

Upvotes: 1

UndeadBob
UndeadBob

Reputation: 1129

Use commas instead of semicolons in your formulas:

ws.Cells["A4"].Formula = "INDEX($C$1:$D$24,4,2)";
ws.Cells["A6"].Formula = "INDEX(C1:D24,6,2)";

ws.Cells["B26"].Formula = "MATCH($B$12,$C$1:$D$1,0)";

I'm guessing when you enter semicolons directly in Excel, MS fixes the formula on the fly. The actual XML probably requires commas, but I'm not sure.

Upvotes: 3

Related Questions