CODEWITHSUNDEEP
excelformulaepplus
user2943111
user2943111

Reputation: 461

EPPlus formula: recalculated in LibreOffice, but NOT in SOME version of Excel

I've a C# database program and generate an Excel file (*.xlsx) with one sheet using EPPlus. The problem is that least a certain version of Excel does not recalculate the formulas while opening the document. I'm developing the app for somebody else and can tell you next week the exact version of Excel that shows the problem.

I'm not going to show my complete code, but a simple example that demonstrates the problem. I've copied the example from here and adepted it a bit: EPPlus not caluculating formula output after binding

Here is the code:

        using (var pck = new OfficeOpenXml.ExcelPackage())
        {
            var ws = pck.Workbook.Worksheets.Add("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";

            ws.Workbook.CalcMode = ExcelCalcMode.Automatic;
            pck.SaveAs(new FileInfo(@"c:\users\richard\tmp1.xlsx"));
        }

The document opens fine in LibreOffice. It opens also in (the yet unkown) Excel version, but the formula is not recalculated on opening.

I was reluctant to buy a license for MS Office, so I downloaded an Excel viewer first. ExcelViewer.exe, File version 12.0.4518.1071. This viewer shows the same behaviour: the formula is not calculated.

So, very relunctantly, I purchased Office 365 Home Premium and guess what: this version of Excel DOES recalculate the formulas.

I think it is a bug in the Excel version, not in EPPlus or my code. But is there any workaround to force ALL versions of Excel to recalculate formula's?

And yes, I've tried to read all other relevant postings here, this is not a duplicate.

Many thanks.

Upvotes: 1

Views: 1253

Answers (1)

Charles Williams
Charles Williams

Reputation: 23530

I don't think that is likely to be an Excel bug, its would be so fundamental that everyone would know about it.
Generating an XL XML file does not calculate formulas.
Viewing an Excel file in a viewer does not calculate formulas.
Setting a workbook calculation mode to Automatic does not neccessarily mean that Excel will be in Automatic Calculation mode:
Calculation mode is an Excel session instance setting taken from the first workbook opened so if Excel is in Manual mode when your workbook is opened it won't calculate.
(And BTW I don't see how you expect to successfully create Excel files without being able to test them)

Upvotes: 1

Related Questions