EPPlus: user definition function calculates

Question

I want to use EPPlus for data entry and calculation. 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();

String test: "#NAME?"

It seems that EPPlus did not load the user definition function.

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

What should I do to get a custom function to work?

(I will use this function later in ASP.NET to call user definition functions in an existing excel file. I tried Interop, it can achieve what I want, but much slower.)

Thank!

Answer 1

EPPlus VBA . . 150 Excel .

VBA .NET. EPPlus ExcelFunction EPPlus Workbook.FormulaParserManager , VBA.

, ( ) EPPlus Samples, Codeplex.

4.1 "EPPlus " :

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

"EPPlusSamples" SampleAddFormulaFunction.cs

Answer 2

vba Epplus, . - :

var fileinfo = new FileInfo(@"c:\temp\UserDefinedFunctionTest.xlsm");
if (fileinfo.Exists)
    fileinfo.Delete();

var sb = new StringBuilder();
sb.AppendLine("Function test(a)");
sb.AppendLine("    test = a * 3");
sb.AppendLine("End Function");

using (var package = new ExcelPackage(fileinfo))
{
    var workbook = package.Workbook;
    var worksheet = workbook.Worksheets.Add("Sheet1");

    workbook.CreateVBAProject();
    var mod1 = workbook.VbaProject.Modules.AddModule("Module1");
    mod1.Code = sb.ToString();

    worksheet.Cells["A1"].Value = 5;
    worksheet.Cells["A2"].Formula = "=test(A1)";

    package.Save();
}

:

Answer 3

You need to set the formula to ExcelWorkSheet

sheet.Cells[5, 5].Formula = "=A1*3";

This is only for the first line, if you want to do this in a large number of lines, then you need to use the loop formula inside the loop too.