You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by KiLiCoOl <ki...@gmail.com> on 2009/10/09 10:48:44 UTC

Problem with IF function in FormulaControllerImpl.java

Hi,

I've a problem with IF Function since I use APACHE POI 3.2 version instead
of 3.0.2 VERSION

This is the error :

10:43:12 FormulaControllerImpl.java 135 => Can't set formula:
IF(AND(E12="Groupe";AD12="53789556J");AB12;AE12)
org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Parse error
near char 19 ';' in specified formula
'IF(AND(E12="Groupe";AD12="53789556J");AB12;AE12)'. Expected ',' or ')'
        at
org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:203)
        at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:608)
        at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
        at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
        at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
        at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
        at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
        at
org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
        at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
        at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
        at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
        at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:603)
        at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
        at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
        at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
        at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
        at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
        at
org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
        at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
        at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
        at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
        at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1043)
        at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
        at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:165)
        at
org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:47)
        at
org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:593)
        at
net.sf.jxls.formula.FormulaControllerImpl.writeFormulas(FormulaControllerImpl.java:133)
        at
net.sf.jxls.transformer.XLSTransformer.updateFormulas(XLSTransformer.java:443)

Somebody can help wy or tell my the reason why this worked with old 3.0.2
version and not with 3.2 ?

Best regards,
Julien

Re: Problem with IF function in FormulaControllerImpl.java

Posted by Bruno Girin <br...@gmail.com>.
Hi Julien,

In your formula, replace all instances of the semi-colon character (;)
with a comma (,):

IF(AND(E12="Groupe",AD12="53789556J"),AB12,AE12)

POI 3.2 is correct in enforcing the use of a comma instead of a
semi-colon as Excel itself does the same (well at least the UK English
version I have on my machine does). If POI 3.0.2 was more lenient and
allowed a semi-colon, that was incorrect IMHO as it would allow you to
create spreadsheets that can't be read by Excel.

Bruno

2009/10/9 KiLiCoOl <ki...@gmail.com>:
> Hi,
>
> I've a problem with IF Function since I use APACHE POI 3.2 version instead
> of 3.0.2 VERSION
>
> This is the error :
>
> 10:43:12 FormulaControllerImpl.java 135 => Can't set formula:
> IF(AND(E12="Groupe";AD12="53789556J");AB12;AE12)
> org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Parse error
> near char 19 ';' in specified formula
> 'IF(AND(E12="Groupe";AD12="53789556J");AB12;AE12)'. Expected ',' or ')'
>        at
> org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:203)
>        at
> org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:608)
>        at
> org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
>        at
> org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
>        at
> org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
>        at
> org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
>        at
> org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
>        at
> org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
>        at
> org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
>        at
> org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
>        at
> org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
>        at
> org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:603)
>        at
> org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
>        at
> org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
>        at
> org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
>        at
> org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
>        at
> org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
>        at
> org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
>        at
> org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
>        at
> org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
>        at
> org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
>        at
> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1043)
>        at
> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
>        at
> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:165)
>        at
> org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:47)
>        at
> org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:593)
>        at
> net.sf.jxls.formula.FormulaControllerImpl.writeFormulas(FormulaControllerImpl.java:133)
>        at
> net.sf.jxls.transformer.XLSTransformer.updateFormulas(XLSTransformer.java:443)
>
> Somebody can help wy or tell my the reason why this worked with old 3.0.2
> version and not with 3.2 ?
>
> Best regards,
> Julien
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org