Bug 28754

Summary: Quotes within formula string gives "Cannot parse" error
Product: POI Reporter: Conrad Roche <new_connie>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P3    
Version: unspecified   
Target Milestone: ---   
Hardware: Other   
OS: other   

Description Conrad Roche 2004-05-03 22:54:09 UTC
Excel allows quotes within string - but escapes it with a " before that.

So, the formula -
   =RIGHT("test""ing", 3)
is allowed in excel and returns the string "ing".

Trying to do the same using HSSF gives an error.

The call -
   cell.setCellFormula("RIGHT(\"test\"\"ing\", 3)");

The stacktrace -
java.lang.RuntimeException: Cannot Parse, sorry : ) Expected
Error: ) Expected
	at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:117)
	at org.apache.poi.hssf.model.FormulaParser.Expected
	at org.apache.poi.hssf.model.FormulaParser.Match(FormulaParser.java:184)
	at org.apache.poi.hssf.model.FormulaParser.function
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:265)
	at org.apache.poi.hssf.model.FormulaParser.Factor
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:548)
	at org.apache.poi.hssf.model.FormulaParser.Expression
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:700)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula
	at emr.test.Test.setSecondRow(Test.java:88)
	at emr.test.Test.main(Test.java:116)
Exception in thread "main" 


PS I'm using version 2.5 (the version combobox in poi bugzilla doesn't have 
that option!)
Comment 1 Josh Micich 2008-05-05 14:41:09 UTC
Mostly fixed in bug 44504.

One extra bug was noticed - HSSFCell.getCellFormula() did not double up the double quotes present in string literals.

Fix and junit added in svn r653608.