Error executing formula (contain special character) from an excel cell in Apache Poi (Java)

Question

I have a formula in a cell in a sheet as shown. The special thing here is that because the sheet name is written in Japanese, it contains a special character: "【". Example: Formula

When using the "evaluateInCell(cell)" function provided by Apache Poi, it didn't seem to work with that special character.

The reference code is below.

case FORMULA: {
                String formula = cell.getCellFormula();
                
                try {
                    System.out.println(formula +" excute...");
                    evaluator.evaluateInCell(cell);
                    System.out.println(formula+" excute success...");
                } catch (Exception e) {
                    System.out.println(String.format("Paste the value error in the Cell: %s", cell.getAddress()) + "   :");

                    System.out.println(formula+" failed");
                }
                break;
            }

Example Run result: Result

I have tried some ways like adding quotes, slashes but it seems to just turn the formula into String. How can I deal with this kind of problem?

Extra:

Here the error if not have try...catch (or using e.printStackTrace()):

org.apache.poi.ss.formula.FormulaParseException: Parse error near char 7 '【' in specified formula 'SUM(E4,【一日】!B2)'. Expected cell ref or constant literal

I want somehow without affecting the Excel file (Like change name of the sheet by hand) but still can execute the formula in that excel cell and get the result.

Answer 1

This is one of the cases where Microsoft contradicts its own rules. If we look at Create or change a cell reference -> Create a cell reference to another worksheet, we find:

Note: If the name of the other worksheet contains nonalphabetical characters, you must enclose the name (or the path) within single quotation marks (').

But if the worksheet name is 【一日】 Excel will not enclose the name within single quotation marks ('). Why not? Clearly neither 【 nor 】are alphabetical characters, are they?

So Apache POI fails evaluating this, because it relies on the Microsoft rules. See FormulaParser.java : // unquoted sheet names must start with underscore or a letter.

You can check this using following test:

...
  String formula = cell.getCellFormula();
  //formula = formula.replace("【一日】", "'【一日】'"); cell.setCellFormula(formula); //set the sheet name to be a quoted sheet name
  try {
   System.out.println("Evaluate formula " + formula + " ...");
   //evaluator.evaluateInCell(cell);
   CellValue cellValue = evaluator.evaluate(cell);
   System.out.println("Success! Result: "+ cellValue);
  } catch (Exception e) {
   System.out.println(String.format("Error in the cell: %s, reason: %s", cell.getAddress(), e.getMessage()));
  }
...

If you uncomment the code line:

  //formula = formula.replace("【一日】", "'【一日】'"); cell.setCellFormula(formula); //set the sheet name to be a quoted sheet name

then formula evaluation should work because the sheet name 【一日】 is quoted now.

Me tested using current Apache POI version 5.2.3.

---

The above is the description of the cause of the issue. It is not a solution. It is not even a workaround. To replace unquoted sheet names with quoted ones one would must know all possible affected sheet names. That's not possible.

The solution would be that Apache POI changes their code in FormulaParser.java. Beginning in private ParseNode parseSimpleFactor(), to private ParseNode parseRangeExpression(), up to private ParseNode parseRangeable(). But that would need a reliable rule of what sheet names may be unquoted and what must be quoted. But that rule seems not to exist. So: you don't have any chance, use it! ;-).