Getting cell value from forumla

Apr 13, 2014 at 7:12 PM
Hi!

I need a cell value, what defined by a formula. It is possible with epplus?
I need the value from cell(1,1)
ExcelPackage package = new ExcelPackage();
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("Test Page");

            ws.Cells[2, 1].Value = 1;
            ws.Cells[3, 1].Value = 2;
            ws.Cells[1, 1].Formula = "=SZUM(A2:A3)";

            ws.Calculate();
            ws.Cells[1, 1].Calculate();
            MessageBox.Show(ws.Cells[1,1].Value.ToString());

            package.SaveAs(new FileInfo("app.xlsx"));
            MessageBox.Show("Done");
            Application.Exit();
Developer
Apr 14, 2014 at 4:45 AM
Hi,

yes, it is possible with EPPlus 4.0 (currently in beta). But you must use the english function names, in your example SUM instead of hungarian SZUM. The localization of function names is something that Excel handles clientside, in the xlsx-file they are always in english.

Also - to improve performance - make sure you call Calculate() only once.

/Mats
Apr 14, 2014 at 8:01 AM
Thanks for your answer awmal,

It have tried, but I got In the messagebox "#VALUE!" text instead of 5. Here is my new code:
            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet wv = package.Workbook.Worksheets.Add("Test Page");
            wv.Cells["A1"].Formula = "=SUM(A2:A3)";
            wv.Cells["A2"].Value = 2;
            wv.Cells["A3"].Value = 3;

            wv.Cells["A1"].Calculate();
            MessageBox.Show(wv.Cells["A1"].Value.ToString());

            package.SaveAs(new FileInfo("app.xlsx"));
I need the calculated value from the cell A1.
Thank you very much!

Tamás
Developer
Apr 14, 2014 at 9:36 PM
I think that you should remove the leading = sign. from your formula. You can also download the samples, to learn more about the new formula calculation engine in epplus.
Marked as answer by tomisy on 4/14/2014 at 11:55 PM
Apr 15, 2014 at 6:56 AM
Thank you! It works without the = sign!