Search code examples
excelepplus

EPPlus multiplying a range by a constant inside a VLookup function returning ValueError

I am using the EPPlus library in my ASP.Net application. What I am trying to do is open a spreadsheet, input some values into cells and then read another cell which contains the result of a calculation. The spreadsheet itself is confidential so I can't provide many details on it.

In order to get my calculations to work I have had to modify the source code for EPplus, changing the Compile function in the ExcelAddressExpression.cs file to ignore the ParentIsLookupFunction bool as shown at the bottom of the question.

so it was able to evaluate the term 5*$f$7

What I want to know is what situations is it useful to keep the CompileResult as an ExcelAddress, so I do not run into any incorrect calculations or errors in other parts of the spreadsheet.

For reference here are the steps I went though to get here:

My code is something like this

        using (ExcelPackage p = new ExcelPackage(FilePath, true))
        {
            ExcelWorksheet ws = p.Workbook.Worksheets["Calculations"];
            ws.Cells["b7"].Value = 50;
            ws.Cells["f9"].Value = 500000;
            ws.Cells["j216"].Calculate();
            string result = ws.Cells["j216"].Value.ToString();
        }

The formula in cell J216 is 

=VLOOKUP($B$7+$F$221+$K$13-$F$8-1,Sheet2!$A$4:$T$103,5*$F$7+2*$B$8+$B$9-5,FALSE)

and the result I got was '#VALUE!'

I have attached a log file and found the issue is in the VLookup function

Worksheet: Calculations
Address: J216
OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE!
at OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj)
at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.LookupArguments..ctor(IEnumerable`1 arguments, ArgumentParsers argumentParsers, ParsingContext context)
at OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup.VLookup.Execute(IEnumerable`1 arguments, ParsingContext context)
at OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionCompilers.LookupFunctionCompiler.Compile(IEnumerable`1 children, ParsingContext context)
at OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()

The next step I took was to download the source code for EPPlus, and debug through the code as it executed, eventually finding the problem was at line 165 of Operator.cs

l = l ?? new CompileResult(0, DataType.Integer);
r = r ?? new CompileResult(0, DataType.Integer);
if (l.DataType == DataType.Integer && r.DataType == DataType.Integer)
{
    return new CompileResult(l.ResultNumeric*r.ResultNumeric, DataType.Integer);
}
else if ((l.IsNumeric || l.IsNumericString || l.IsDateString || l.Result is ExcelDataProvider.IRangeInfo) &&
    (r.IsNumeric || r.IsNumericString || r.IsDateString || r.Result is ExcelDataProvider.IRangeInfo))
{
    return new CompileResult(l.ResultNumeric*r.ResultNumeric, DataType.Decimal);
}
return new CompileResult(eErrorType.Value);

When evaluating the equation 5*$F$7, the second parameter was of DataType ExcelAddress which is results in a compile result exception being thrown.

The root cause of this is in the Compile function of the ExcelAddressExpression.cs file the ParentIsLookupFunction boolean controls whether the cell is evaluated or left as an address.

    public override CompileResult Compile()
    {
        if (ParentIsLookupFunction)
        {
            return new CompileResult(ExpressionString, DataType.ExcelAddress);
        }
        else
        {
            return CompileRangeValues();
        }
    }

I have modified my version of the code to simply be

    public override CompileResult Compile()
    {
        return CompileRangeValues();
    }

As said at the top of the question, what I want to know is why would you want to return the ExcelAddress CompileResult, It was obviously put there for a reason and I do not want to break some other calculations in my spreadsheet.

I can confirm though that for at least this calculation it is now working correctly.

Solution

  • I wrote the formula engine of EPPlus some years ago, but don't work much on the project these days. If I recall it correctly there are cases where you don't want to compile the excel address in the expression, but rather pass it on to the executing function. Have you tried to run the unit tests? The formula engine is covered by hundreds of tests and the test result could provide some guidance.