CODEWITHSUNDEEP
c#excelepplus
Jesper Nielsen
Jesper Nielsen

Reputation: 23

Cant invoke Calculate method from EPPlus using C#

I have a cell with a formula, that I need to invoke Calculate() method on, to get the result.

Somehow I cannet invoke the Calculate method on any cells in my sheet, what am I missing?

I am using EPPlus version 4.0.5.0. My code is as follows: 

ws2.Cells[failedRow, failedColumn + 1].Formula = "SUM(B20:B" + (failedRow - 1) + ")";

And I need Calculate on that same cell. Any ideas?

Upvotes: 2

Views: 1777

Answers (2)

Vijayakumar
Vijayakumar

Reputation: 140

XlsIO is a .NET library that reads and writes Excel 2003/2007/2010/2013/2016 files. Using XlsIO, you can calculated the formula of the cell very easily. The whole suite of controls is available for free (commercial applications also) through the community license program if you qualify. The community license is the full product with no limitations or watermarks.

Step 1: Create a console application Step 2: Add reference to Syncfusion.XlsIO.Base and Syncfusion.Compression.Base; You can add these reference to your project using NuGet also. Step 3: Copy & paste the following code snippet.

The following code snippet illustrates how to calculate the formula value in the cell using XlsIO

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;

  application.DefaultVersion = ExcelVersion.Excel2013;

  IWorkbook workbook = application.Workbooks.Create(1);

  IWorksheet sheet = workbook.Worksheets[0];

  //Setting values to the cells

  sheet.Range["A1"].Number = 10;

  sheet.Range["B1"].Number = 10;

  //Setting formula in the cell

  sheet.Range["C1"].Formula = "=SUM(A1,B1)";

  // Enabling CalcEngine for computing the formula
  sheet.EnableSheetCalculations();

  string computedValue = sheet.Range["C1"].CalculatedValue;

  workbook.SaveAs("Formula.xlsx");

  // Disabling the CalcEngine
  sheet.DisableSheetCalculations();
}

For further information about XlsIO, please refer our help documentation

Note: I work for Syncfusion

Upvotes: 1

Ernie S
Ernie S

Reputation: 14250

Revising my answer based on @Stewart mentioning the Formula Calculations they added.

I tried again and it DOES seem to work. So, Jesper, in you case, cell.Calculate() should do it for you. I tried this:

    public void Formula_Calc_Test()
    {
        var datatable = new DataTable("tblData");
        datatable.Columns.AddRange(new[] {new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int))});

        for (var i = 0; i < 10; i++)
        {
            var row = datatable.NewRow();
            row[0] = i;
            row[1] = i * 10;
            datatable.Rows.Add(row);
        }

        using (var pck = new ExcelPackage())
        {
            var workbook = pck.Workbook;
            var worksheet = workbook.Worksheets.Add("Sheet1");
            var cells = worksheet.Cells;

            cells.LoadFromDataTable(datatable, true);

            var failedRow = 11;
            cells["C1"].Formula = "SUM(B2:B" + (failedRow - 1) + ")";
            cells["C1"].Calculate();
            Console.WriteLine(cells["C1"].Value);

        }
    }

And did get 360 in the output.

Upvotes: 1

Related Questions