Bug 45025 - Error in setFormula with VLOOKUP in 3.1-beta1 and 3.1-beta2
Summary: Error in setFormula with VLOOKUP in 3.1-beta1 and 3.1-beta2
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-05-17 13:12 UTC by David
Modified: 2008-05-23 00:06 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description David 2008-05-17 13:12:17 UTC 
When using setFormula in a HSSFCell with an inner VLOOKUP function as: 

    dailyCost.setCellFormula("VLOOKUP(\""+t.getRol()+"\";'Tarifas venta personal'!$D$12:$E$30;2;false)");
        
I receive the following exception:

org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Integer Expected
	at org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:137)
	at org.apache.poi.hssf.model.FormulaParser.parseNumber(FormulaParser.java:602)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:569)
	at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:526)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:513)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:727)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:805)
	at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:790)
	at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:747)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:503)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:326)
	at org.apache.poi.hssf.model.FormulaParser.parseIdent(FormulaParser.java:236)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:566)
	at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:526)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:513)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:727)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:805)
	at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:790)
	at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:747)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:844)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:641)

This problem doesn't happen in 3.0 final release, I'm trying to use 3.1 since I need VLOOKUP function working with a reference in the first argument instead of a literal.
Comment 1 Josh Micich 2008-05-23 00:06:33 UTC 
The main problem is use of semicolons (;) for argument separators when POI expects commas (,).  (Similar to first part of bug 45041).  I guess if comma is used as a decimal separator, something else is required to delimit arguments.  

For the moment, all POI can do is present a better error message.  Internationalization would require a big effort.

I added a fix for this in svn r659455.  Now POI will give a message like:
Parse error near char 13 ';' in specified formula 'VLOOKUP("123";'Tarifas venta personal'!$D$12:$E$30;2;false)'. Expected ',' or ')'

After changing the semicolons to commas, POI seems to output a spreadsheet with a well encoded VLOOKUP formula.