I want to calculate the formula of a cell and retrieve the calculated value. But it gives #NAME? as the cell value when cell.Calculate() is executed.
var package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Test");
ExcelRange cellTest = ws.Cells[1, 1];
cellTest.Formula = "TRIM( Hello World )";
cellTest.Calculate();
var cellCalculatedValue = cellTest.Value;
Here the cellTest.Value = #NAME?
Remember the Epplus doesnt actually have access to the excel engine, it just generates raw XML files that excel interprets when the file is first opened.
So for Formula, the documentation does say you can use Calculate() to get a value like you could in Excel. But what it doesnt tell you is that it does not support all excel built in functions. And understand that the functions in Epplus/C# are NOT the exact same as what will be ran in Excel. They are simply Epplus's interpretation/copies of what excel has.
If you look at https://github.com/JanKallman/EPPlus/blob/master/EPPlus/FormulaParsing/Excel/Functions/BuiltInFunctions.cs:
namespace OfficeOpenXml.FormulaParsing.Excel.Functions
{
public class BuiltInFunctions : FunctionsModule
{
public BuiltInFunctions()
{
// Text
Functions["len"] = new Len();
Functions["lower"] = new Lower();
Functions["upper"] = new Upper();
Functions["left"] = new Left();
Functions["right"] = new Right();
Functions["mid"] = new Mid();
Functions["replace"] = new Replace();
Functions["rept"] = new Rept();
Functions["substitute"] = new Substitute();
Functions["concatenate"] = new Concatenate();
Functions["char"] = new CharFunction();
Functions["exact"] = new Exact();
Functions["find"] = new Find();
Functions["fixed"] = new Fixed();
Functions["proper"] = new Proper();
Functions["search"] = new Search();
Functions["text"] = new Text.Text();
Functions["t"] = new T();
Functions["hyperlink"] = new Hyperlink();
Functions["value"] = new Value();
// Numbers
Functions["int"] = new CInt();
// Math
Functions["abs"] = new Abs();
Functions["asin"] = new Asin();
Functions["asinh"] = new Asinh();
Functions["cos"] = new Cos();
Functions["cosh"] = new Cosh();
Functions["power"] = new Power();
Functions["sign"] = new Sign();
Functions["sqrt"] = new Sqrt();
Functions["sqrtpi"] = new SqrtPi();
Functions["pi"] = new Pi();
Functions["product"] = new Product();
Functions["ceiling"] = new Ceiling();
Functions["count"] = new Count();
Functions["counta"] = new CountA();
Functions["countblank"] = new CountBlank();
Functions["countif"] = new CountIf();
Functions["countifs"] = new CountIfs();
Functions["fact"] = new Fact();
Functions["floor"] = new Floor();
Functions["sin"] = new Sin();
Functions["sinh"] = new Sinh();
Functions["sum"] = new Sum();
Functions["sumif"] = new SumIf();
Functions["sumifs"] = new SumIfs();
Functions["sumproduct"] = new SumProduct();
Functions["sumsq"] = new Sumsq();
Functions["stdev"] = new Stdev();
Functions["stdevp"] = new StdevP();
Functions["stdev.s"] = new Stdev();
Functions["stdev.p"] = new StdevP();
Functions["subtotal"] = new Subtotal();
Functions["exp"] = new Exp();
Functions["log"] = new Log();
Functions["log10"] = new Log10();
Functions["ln"] = new Ln();
Functions["max"] = new Max();
Functions["maxa"] = new Maxa();
Functions["median"] = new Median();
Functions["min"] = new Min();
Functions["mina"] = new Mina();
Functions["mod"] = new Mod();
Functions["average"] = new Average();
Functions["averagea"] = new AverageA();
Functions["averageif"] = new AverageIf();
Functions["averageifs"] = new AverageIfs();
Functions["round"] = new Round();
Functions["rounddown"] = new Rounddown();
Functions["roundup"] = new Roundup();
Functions["rand"] = new Rand();
Functions["randbetween"] = new RandBetween();
Functions["rank"] = new Rank();
Functions["rank.eq"] = new Rank();
Functions["rank.avg"] = new Rank(true);
Functions["quotient"] = new Quotient();
Functions["trunc"] = new Trunc();
Functions["tan"] = new Tan();
Functions["tanh"] = new Tanh();
Functions["atan"] = new Atan();
Functions["atan2"] = new Atan2();
Functions["atanh"] = new Atanh();
Functions["acos"] = new Acos();
Functions["acosh"] = new Acosh();
Functions["var"] = new Var();
Functions["varp"] = new VarP();
Functions["large"] = new Large();
Functions["small"] = new Small();
Functions["degrees"] = new Degrees();
// Information
Functions["isblank"] = new IsBlank();
Functions["isnumber"] = new IsNumber();
Functions["istext"] = new IsText();
Functions["isnontext"] = new IsNonText();
Functions["iserror"] = new IsError();
Functions["iserr"] = new IsErr();
Functions["error.type"] = new ErrorType();
Functions["iseven"] = new IsEven();
Functions["isodd"] = new IsOdd();
Functions["islogical"] = new IsLogical();
Functions["isna"] = new IsNa();
Functions["na"] = new Na();
Functions["n"] = new N();
// Logical
Functions["if"] = new If();
Functions["iferror"] = new IfError();
Functions["ifna"] = new IfNa();
Functions["not"] = new Not();
Functions["and"] = new And();
Functions["or"] = new Or();
Functions["true"] = new True();
Functions["false"] = new False();
// Reference and lookup
Functions["address"] = new Address();
Functions["hlookup"] = new HLookup();
Functions["vlookup"] = new VLookup();
Functions["lookup"] = new Lookup();
Functions["match"] = new Match();
Functions["row"] = new Row();
Functions["rows"] = new Rows();
Functions["column"] = new Column();
Functions["columns"] = new Columns();
Functions["choose"] = new Choose();
Functions["index"] = new Index();
Functions["indirect"] = new Indirect();
Functions["offset"] = new Offset();
// Date
Functions["date"] = new Date();
Functions["today"] = new Today();
Functions["now"] = new Now();
Functions["day"] = new Day();
Functions["month"] = new Month();
Functions["year"] = new Year();
Functions["time"] = new Time();
Functions["hour"] = new Hour();
Functions["minute"] = new Minute();
Functions["second"] = new Second();
Functions["weeknum"] = new Weeknum();
Functions["weekday"] = new Weekday();
Functions["days360"] = new Days360();
Functions["yearfrac"] = new Yearfrac();
Functions["edate"] = new Edate();
Functions["eomonth"] = new Eomonth();
Functions["isoweeknum"] = new IsoWeekNum();
Functions["workday"] = new Workday();
Functions["networkdays"] = new Networkdays();
Functions["networkdays.intl"] = new NetworkdaysIntl();
Functions["datevalue"] = new DateValue();
Functions["timevalue"] = new TimeValue();
// Database
Functions["dget"] = new Dget();
Functions["dcount"] = new Dcount();
Functions["dcounta"] = new DcountA();
Functions["dmax"] = new Dmax();
Functions["dmin"] = new Dmin();
Functions["dsum"] = new Dsum();
Functions["daverage"] = new Daverage();
Functions["dvar"] = new Dvar();
Functions["dvarp"] = new Dvarp();
//Finance
Functions["pmt"] = new Pmt();
}
}
}
You will see that Trim is not in there, unfortunately. You could, in theory, add it yourself like this:
[TestMethod]
public void Test1()
{
using (var package = new ExcelPackage())
{
package.Workbook.FormulaParserManager.LoadFunctionModule(new MyFunctionModule());
var ws = package.Workbook.Worksheets.Add("Test");
var cellTest = ws.Cells[1, 1];
cellTest.Formula = "TRIM(\"Hello World\")";
ws.Calculate();
var cellCalculatedValue = cellTest.Value; //Will now show a proper value
}
}
public class TrimFunction : ExcelFunction
{
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 1);
var result = arguments.ElementAt(0).Value.ToString().Trim();
return CreateResult(result, DataType.String);
}
}
public class MyFunctionModule : FunctionsModule
{
public MyFunctionModule()
{
Functions.Add("trim", new TrimFunction());
}
}
But, again, it has nothing to do with the actual function in excel - they are completely separate. The TRIM function above will only existing in the C# world. When excel opens the file, it will apply its own TRIM function.