By Time Rider


2018-09-08 15:10:41 8 Comments

I'm using Epplus in a .NET Core application and I'd like to use names of named ranges in formulas. The issue is that when I reference a named range it takes the value of the first cell in range and don't iterate through rows:

    var data = new List<object[]>()
{
    new object[] {"Hours", "Price", "Total"},
    new object[] {0.5, 10, 0},
    new object[] {2, 100, 0},
    new object[] {3, 20, 0}
};

using (var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("Variables");
    sheet.Cells["A1"].LoadFromArrays(data);

    sheet.Names.Add("Hours", sheet.Cells["A2:A4"]);
    sheet.Names.Add("Price", sheet.Cells["B2:B4"]);
    sheet.Names.Add("Total", sheet.Cells["C2:C4"]);

    sheet.Names["Total"].Formula = "Hours * Price";

    package.Workbook.Calculate();

    for (int i = 1; i <= sheet.Dimension.End.Row; i++)
    {
        Console.WriteLine($"{sheet.Cells[i, 1].Value}\t{sheet.Cells[i, 2].Value}\t{sheet.Cells[i, 3].Value}");
    }
}

This code writes the following to the console:

Hours   Price   Total
0,5     10      5
2       100     5
3       20      5

But actually I'd like to get this:

Hours   Price   Total
0,5     10      5
2       100     200
3       20      60

Is it possible to somehow do that using names as arguments instead of cell addresses, like "Hours * Price"?

1 comments

@Alex 2018-09-28 09:03:01

EPPlus Calculate() doesn't seem to support named ranges in formulas. But if your console output is only for test, the formulas are calculated correctly if you save the file and open it. I.e. if you add using (var package = new ExcelPackage(new FileInfo(someFileName))) and package.Save(); inside the using statement.

The console output would be correct if your formula uses the cell addresses (but guessing that's not good enough for you):

for (int col = 2; col < 5; col++)
    sheet.Cells[$"C{col}"].Formula = $"A{col} * B{col}";

Related Questions

Sponsored Content

2 Answered Questions

Change the range of pivot table with EPPlus of an existing file

1 Answered Questions

[SOLVED] EPPlus Conditional Format 'AddExpression' problem

1 Answered Questions

[SOLVED] EPPlus doesn't properly range a formula calling on a different worksheet

  • 2017-11-06 20:11:04
  • Isaac Little
  • 2025 View
  • 1 Score
  • 1 Answer
  • Tags:   c# .net excel epplus

1 Answered Questions

[SOLVED] Creating an Excel report based on template

  • 2017-10-04 09:15:30
  • Ram
  • 593 View
  • 0 Score
  • 1 Answer
  • Tags:   c# epplus

1 Answered Questions

[SOLVED] Why is the Excel formula not formulating?

1 Answered Questions

2 Answered Questions

[SOLVED] Force EPPLUS to read as text

  • 2015-04-03 09:46:22
  • Luntri
  • 14123 View
  • 10 Score
  • 2 Answer
  • Tags:   c# epplus

1 Answered Questions

[SOLVED] EPPlus formula error "Input string was not in a correct format."

  • 2014-05-27 03:10:27
  • atulya
  • 1201 View
  • 0 Score
  • 1 Answer
  • Tags:   c# excel epplus

1 Answered Questions

EPPlus - How to print column captions and not column names

  • 2012-04-07 16:21:25
  • Mr W
  • 891 View
  • 0 Score
  • 1 Answer
  • Tags:   .net excel epplus

Sponsored Content