#43 Formula experssion containing ']' cannot be parsed

[Aron Gombas]

This expression (function call) works:
$[TEXT(39300.625,"h")]

...but this results in an exception:
$[TEXT(39300.625,"[h]")]

As you see the difference is only the two square bracket characters, which are totally valid in Excel.

The exception (see the stack trace in the bottom) suggests that the POI formula parser receives the wrong formula string from JETT. The "[h]" substring conflicts with formula's closing "]", I think.
If the error message is correct, then JETT recognizes:
$[TEXT(39300.625,"

...although it should recognize:
$[TEXT(39300.625,"[h]")]

So the stack trace is:

org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '$' in specified formula '$[TEXT(39300.625,"'. Expected number, string, or defined name
at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:219)
at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:549)
at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429)
at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:444)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:424)
at net.sf.jett.transform.SheetTransformer.replaceFormulas(SheetTransformer.java:300)
at net.sf.jett.transform.ExcelTransformer.replaceFormulas(ExcelTransformer.java:916)
at net.sf.jett.transform.ExcelTransformer.postTransformation(ExcelTransformer.java:722)
at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:413)
at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:386)

[Randy Gettman]

JETT doesn't take into account the fact that a bracket may appear inside an Excel formula. JETT formulas are determined by finding $[ and the first ] afterward, so the presence of a ] character in a JETT formula exposes this bug.

I will need to add code to JETT to recognize that a ] character may appear inside the JETT formula, so that the formula gets parsed correctly.

[Randy Gettman]

This was included in JETT 0.9.1, which was released on Friday, August 28, 2015.