Bug 68305 - Invalid sheetIndex Error in formulaEvaluator.evaluate(cell) for Cell Formula with Escaped Apostrophe in Sheet Name
Summary: Invalid sheetIndex Error in formulaEvaluator.evaluate(cell) for Cell Formula ...
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.3-FINAL
Hardware: Macintosh other
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-12-07 06:48 UTC by Haider
Modified: 2024-02-25 12:44 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Haider 2023-12-07 06:48:31 UTC
Issue Summary:
When using Apache POI's formulaEvaluator.evaluate(cell) method, an Invalid sheetIndex: -1 error is thrown for a cell formula referencing a sheet name that contains an apostrophe. The sheet name is properly escaped as per standard Excel syntax, yet the error persists.

Detailed Description:
We have an Excel sheet named (2) 4-Tension Bolt MC's. In our Java application using Apache POI, we're attempting to evaluate a cell that references this sheet. The cell formula is written as ='(2) 4-Tension Bolt MC''s'!C4, with the apostrophe in the sheet name correctly escaped using a double apostrophe (''). This formula works as expected when used directly in Microsoft Excel.

However, when evaluating this cell using Apache POI's FormulaEvaluator, it results in the following error: Invalid sheetIndex: -1. This suggests an issue with how Apache POI is handling sheet names with escaped apostrophes in formulas.

Steps to Reproduce:

Create an Excel sheet named (2) 4-Tension Bolt MC's.
In a cell of another sheet, input the formula ='(2) 4-Tension Bolt MC''s'!C4.
Use Apache POI's FormulaEvaluator to evaluate this cell in a Java application.
Observe the Invalid sheetIndex: -1 error.

Expected Behavior:
The formula should be evaluated without errors, similar to how it functions in Microsoft Excel.

Actual Behavior:
An Invalid sheetIndex: -1 error is thrown, indicating an issue with parsing the sheet name with an escaped apostrophe.

Code:
CellValue formulaResult = formulaEvaluator.evaluate(cell);
Comment 1 PJ Fanning 2023-12-10 13:33:49 UTC
I added a test using r1914511 - this test may not be enough to reproduce the issue but so far there appears that formula evaluator can handle double apostrophes.

Also, could you explain why you need to do formula evaluation? Excel files have the evaluated values stored in the file along with the formulas. POI will read the stored values - so you don't need to use the FormulaEvaluator. POI is unlikely to ever have full formula support.
Comment 2 Haider 2023-12-17 05:06:14 UTC
Thank you for the test case in r1914511. We're encountering an issue in our application, which allows users to modify Excel files stored on S3 via a front-end interface, as well as through MS Online and SpreadJS. The problem arises during formula recalculation in sheets with names containing apostrophes, resulting in an 'Invalid sheetIndex: -1' error. It's not entirely clear if the error is due to modifications made through MS Online/SpreadJS or another factor.

To provide more context, our POI service receives the Excel file URL and the specific cell that needs to be updated. We locate and update the cell in the workbook and then initiate formula evaluation. Despite the formulas appearing correct in our logs, POI consistently returns the 'sheetIndex: -1' error. We would greatly appreciate any insights or suggestions for alternative approaches to this problem.
Comment 3 Dominik Stadler 2024-02-25 12:44:46 UTC
Can you provide sample files and code to reproduce this?