Evaluate, calculate expressions

Mar 24, 2014 at 11:30 AM

I'm using epplus version In the worksheet i am using a formula, what I want to evaluate. As I know the is able to calculate the results of the formula, but it doesn't work at me. What is the problem?
        ExcelPackage package = new ExcelPackage();
        ExcelWorksheet ws = package.Workbook.Worksheets.Add("Test Page");

        for (int i = 0; i < 35; i++)
            ws.Cells[i+1, 1].Value = Convert.ToInt32(i+1);

        ws.Cells[2, 2, 2, 2].Formula = "=(" + "A" + "5-SZUM(" + "A" + "6:" + "A" + "22))-(("
                + "A" + "4+" + "A" + "24)*" + "A" + "31/60)";
        //ws.Cells[2, 2, 2, 2].Style.Numberformat.Format = "0.00";
        ws.Cells[2, 2, 2, 2].Calculate();
        ws.Column(2).Width = 20;

        //ws.Calculate(ws.Cells[2, 2, 2, 2].Formula);

        package.SaveAs(new FileInfo("app.xlsx"));
Thank you!

Mar 24, 2014 at 12:04 PM
I know the base problem: In formula string I'm using the hungarian function name: SZUM istead of SUM. When I change it to SUM, then works well. But how can I use the function name with other languages? Is there any option, where I am able to adjust the language/culture of the xlsx?
Mar 24, 2014 at 1:08 PM
Edited Mar 24, 2014 at 1:08 PM
No, there is no option for that. In Excel, the browser in a Hungarian locale might use the Hungarian for SUM while the file is saved using the standard English name. In EPPlus, the formula property is a string and there is no string processing to save it with another name.

Mar 25, 2014 at 5:06 AM
Exactly, just to clarify: the language/culture is not set on the file, it is in your local Excel installation.

So always use the english formula names when using epplus and you will see the hungarian versions when you open the file in Excel. If you send the file to me I will see it in swedish.