invoke formula in excel via epplus

Question

I have an excel sheet in ASP.NET MVC4 C# project and I am able to read from excel sheet successfully using EPPlus. Now, I want to be able to pass in 2 numbers into cell C:2 and C:3 and be able to invoke formula in C:4 which is =SUM(C2:C3).

So from C# I want to pass in 4 and 6 and invoke the formula and be able to get the result back from C:4 which is 40 (SUM of 10 and 30). How do I accomplish that in C#.

In the following code, I get back zero for d.Average

d.Average = Convert.ToDouble(currentWorksheet.Cells["C4"].Value);

Here is my following code in c# so far to traverse a row.

        using (var package = new ExcelPackage(existingFile))
        {
            ExcelWorkbook workBook = package.Workbook;
            var currentWorksheet = workBook.Worksheets.First();
            currentWorksheet.Workbook.CalcMode = ExcelCalcMode.Automatic;
            currentWorksheet.Cells["C4"].Formula = "=SUM(C2:C3)";
            currentWorksheet.Cells["C2"].Value = 10;
            currentWorksheet.Cells["C3"].Value = 30;
            package.Save();


        }

        using (var package = new ExcelPackage(existingFile))
        {
            ExcelWorkbook workBook = package.Workbook;
            var currentWorksheet = workBook.Worksheets.First();
            d.Average = Convert.ToDouble(currentWorksheet.Cells["C4"].Value);
        }

What is d.FinalResult? What is the problem you are facing? – Patrick Hofman Jan 17 '14 at 15:16 — Patrick Hofman, Jan 17 '14 at 15:16

score 25 · Answer 1 · edited Jul 20 '15 at 10:09

25

Skip the = in the formula string.

Replace currentWorksheet.Cells["C4"].Formula = "=SUM(C2:C3)";

with

currentWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";

edited Jul 20 '15 at 10:09

Kᴀτᴢ

2,146
6
29
57

answered Jun 02 '14 at 11:42

ogborstad

2,309
2
19
22

score 10 · Answer 2 · answered Dec 23 '14 at 04:55

As of EpPlus 4.0.1.1, there is an extension method Calculate(this ExcelRangeBase range). Invoke it before accessing Value property:

currentWorksheet.Cells["C4"].Calculate();

and currentWorksheet.Cells["C4"].Value will return the expected value of 40 after that.

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

I was reviewing my previous answer and found out that the calculation engine of EPPLUS is still in development.

See this discussion on CodePlex. (dating from 2011)

A cite from the roadmap page:

Version 3.2

Formula parser, calc engine

Replacing the Package API with DotNetZip

New cellstore to imporve insert, delete performance and memory consumtion

Last edited Feb 14, 2013

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 17 '14 at 15:18

Patrick Hofman

153,850
22
249
325

invoke formula in excel via epplus

3 Answers3

Linked