CODEWITHSUNDEEP
c#excelepplusvba
Chao
Chao

Reputation: 43

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!

Upvotes: 4

Views: 2266

Answers (3)

swmal
swmal

Reputation: 171

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

Upvotes: 2

Ernie S
Ernie S

Reputation: 14250

You need to create a vba project in Epplus which is where you would define the function. Something like this:

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();
}

Which will look like this:

enter image description here

Upvotes: 1

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

You need to set formula in ExcelWorkSheet

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

This is for first row only , if you want to do in Large number of rows then you have to use loop and inside loop use formula.

Upvotes: 0

Related Questions