Unable to Evaluate Formula while referencing an external workbook

[hitensam]

NPOI Version

2.7.1

File Type

XLSX

Issue Description

When I attempt to evaluate a formula using NPOI, I encounter the following error:

NPOI.Util.RuntimeException
  HResult=0x80131500
  Message=Could not resolve external workbook name 'FileName.xlsx'. Workbook environment has not been set up.
  Source=NPOI.Core
  StackTrace:
   at NPOI.SS.Formula.OperationEvaluationContext.CreateExternSheetRefEvaluator(ExternalSheet externalSheet)
   at NPOI.SS.Formula.OperationEvaluationContext.CreateExternSheetRefEvaluator(String firstSheetName, String lastSheetName, Int32 externalWorkbookNumber)
   at NPOI.SS.Formula.OperationEvaluationContext.GetRef3DEval(Ref3DPxg rptg)
   at NPOI.SS.Formula.WorkbookEvaluator.GetEvalForPtg(Ptg ptg, OperationEvaluationContext ec)
   at NPOI.SS.Formula.WorkbookEvaluator.EvaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs)
   at NPOI.SS.Formula.WorkbookEvaluator.EvaluateAny(IEvaluationCell srcCell, Int32 sheetIndex, Int32 rowIndex, Int32 columnIndex, EvaluationTracker tracker)
   at NPOI.SS.Formula.WorkbookEvaluator.Evaluate(IEvaluationCell srcCell)
   at NPOI.XSSF.UserModel.BaseXSSFFormulaEvaluator.EvaluateFormulaCellValue(ICell cell)
   at NPOI.SS.Formula.BaseFormulaEvaluator.Evaluate(ICell cell)
   at Program.Main() in E:\EXPERIMENT\EditExcel\EditExcel\Program.cs:line 61

Inner Exception 1:
WorkbookNotFoundException: Could not resolve external workbook name 'FileName.xlsx'. Workbook environment has not been set up.

Formula

Formula as read by NPOI:

INDEX([1]Data!$A$2:'[1]Data'!$N$1494,MIN(IF([1]Data!$A$2:'[1]Data'!$N$1494=A2,ROW([1]Data!$A$2:'[1]Data'!$N$1494)-ROW([1]Data!$A$2)+1)),MATCH(A2,INDEX([1]Data!$A$2:'[1]Data'!$N$1494,MIN(IF([1]Data!$A$2:'[1]Data'!$N$1494=A2,ROW([1]Data!$A$2:'[1]Data'!$N$1494)-ROW([1]Data!$A$2)+1)),0),0)+2)

Formula as seen in the Excel UI:

=INDEX(Data!$A$2:Data!$N$1494,MIN(IF(Data!$A$2:Data!$N$1494=A876,ROW(Data!$A$2:Data!$N$1494)-ROW(Data!$A$2)+1)),MATCH(A876,INDEX(Data!$A$2:Data!$N$1494,MIN(IF(Data!$A$2:Data!$N$1494=A876,ROW(Data!$A$2:Data!$N$1494)-ROW(Data!$A$2)+1)),0),0)+3)

Steps to Reproduce

1. Open the workbook in NPOI.
2. Try to evaluate the formula using NPOI.

Expected Behavior

The formula should evaluate correctly without errors.

Actual Behavior

I get the following error message:
"Could not resolve external workbook name 'FileName.xlsx'. Workbook environment has not been set up."

Temporary Workaround

To remove the error, I manually remove [1] from the entire workbook using NPOI. However, after doing this, the formula no longer throws an error but also does not return any result in the cell.

Additional Notes

• The formula evaluates correctly in Excel (using Introp).
• The issue seems to be related to the handling of external references with NPOI (I am not using any external workbook, all the values and formula resides in the same workbook's different sheets).

[tonyqus]

What's FileName.xlsx here? Is it another Excel file?

Please provide the original Excel file. Otherwise, it's hard to reproduce the issue.

[hitensam]

Hey @tonyqus,

Thanks for your response!

Currently, I’m using a template with all the formulas built into it. I have another Excel file containing the data. First, I make a copy of the template, and then I paste the values into the copy to evaluate the results. However, it seems that Excel is referencing the other file (template) and giving me an error. As mentioned earlier, this doesn’t happen when we do the same thing with Microsoft.Office.Interop.Excel, possibly because Excel handles it automatically in that case

[tonyqus]

NPOI is NOT Microsoft Office. The formula engine is reimplement in C# (originally implemented in Aapche POI in Java). And the logic of the embeded formula calculation engine is very complicated. We cannot guarantee all the behaviors are same.

If the formula is too complicated, it is possible that NPOI cannot parse or evaluate it.

[tonyqus]

But again, if you cannot provide the original Excel, I can't guess what's wrong.

[hitensam]

I understand. I also find the formula quite confusing. I added a workaround by manually inserting the formula using the SetCellFormula method. This resolved the errors, but now it's showing #N/A, which suggests there might be an issue with the formula. I'll first try to simplify the formula on my own. If the issue persists, I'll reach out again with more details and the file.