Search code examples
c#excelepplusvba

EPPlus:User define function calculate

I want to use EPPlus to input data and calculate it. Here is my excel files and user defined function Here is my code:

ExcelWorksheet sheet = ep.Workbook.Worksheets["input"];
sheet.Cells[1, 1].Value = 10;
ep.Workbook.Calculate();
string test = sheet.Cells[1, 5].Text;
ep.Save();

The string test is "#NAME?"

It seems that EPPlus did not load user define function.

When I open the saved file, the calculation will be done automatically.

What should I do to make the user defined function work?

(I'll use this feature later in the ASP.NET to call User define functions in exist excel file.I tried Interop, it can achieve what I want, but a lot slower.)

Thanks!

Solution

  • The formula calc engine in EPPlus cannot execute VBA functions in the workbook. It supports approx. 150 common built in Excel formulas and nothing more than that.

    However, you can implement your VBA functions in .NET code. Each function should inherit the EPPlus class ExcelFunction and be registred to EPPlus via the Workbook.FormulaParserManager given the same function name as your VBA functions.

    There are samples that illustrates this (create custom functions) in the EPPlus Samples project which can be downloaded from Codeplex.

    For version 4.1, you can download the solution "EPPlus with samples" here:

    https://epplus.codeplex.com/releases/view/625020

    Then goto the "EPPlusSamples" project and check out SampleAddFormulaFunction.cs