Bug 28754 - Quotes within formula string gives "Cannot parse" error
Summary: Quotes within formula string gives "Cannot parse" error
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: Other other
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-05-03 22:54 UTC by Conrad Roche
Modified: 2008-05-05 14:41 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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.