Bug 46099 - setCellFormula() and symbol ";" in formula body
Summary: setCellFormula() and symbol ";" in formula body
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.2-dev
Hardware: PC Windows XP
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-10-27 08:32 UTC by Dmitry Smirnov
Modified: 2008-10-27 09:45 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Dmitry Smirnov 2008-10-27 08:32:35 UTC
hello!
 I can't call method setCellFormula() with argument
  ADDRESS(1;1)  I means  setCellFormula("ADDRESS(1;1)");
this is method rise an exception (see below )

In previous version of POI this exception  was absent.

P.S. if i write setCellFormula("ADDRESS(1,1)"); then no exception and in excell formula =ADDRESS(1;1)



Exception in thread "main" org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Parse error near char 9 ';' in specified formula 'ADDRESS(1;1)'. 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.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 Test2.main(Test2.java:32)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)


example code:


import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Test {
    public static void main(String[] args) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("test");
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell_11 = row1.createCell(0);
        cell_11.setCellValue(100);
        HSSFCell cell_12 = row1.createCell(1);
        cell_12.setCellValue(100);
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell_21 = row2.createCell(0);
        cell_21.setCellFormula("ADDRESS(1;1)");

        final FileOutputStream out = new FileOutputStream(new File("C:\\test.xls"));
        try {
            wb.write(out);
        } finally {
            out.close();
        }

    }
}
Comment 1 Nick Burch 2008-10-27 09:45:28 UTC
You need to use commas in formulas for this sort of thing 

(Some versions of excel do allow other characters, but it's always translated into a comma internally)