Bug 34021 - FormulaParser doesn't like dots and blanks in sheet names
Summary: FormulaParser doesn't like dots and blanks in sheet names
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: PC Windows 2000
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2005-03-15 19:01 UTC by Thomas Herre
Modified: 2005-04-22 07:11 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Herre 2005-03-15 19:01:03 UTC
I'm trying to set a cell formula using method HSSFCell.setCellFormula(String).

The FormulaParser fails parsing these formulas:
cell.setCellFormula("Sheet 1!A1");

When I remove all blanks and dots from the referenced sheet name everything
works fine.

Here's the stack trace:

java.lang.StringIndexOutOfBoundsException: String index out of range: 0
	at java.lang.String.charAt(String.java:444)
	at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:44)
	at org.apache.poi.hssf.record.formula.ReferencePtg.<init>(ReferencePtg.java:50)
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:291)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:474)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:548)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:700)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:678)
Comment 1 Avik Sengupta 2005-04-22 13:39:14 UTC
Can someone confirm that excel accepts sheet names with dots and spaces. I'll
fix this then. 
Comment 2 Amol Deshmukh 2005-04-22 14:42:24 UTC
(In reply to comment #1)
> Can someone confirm that excel accepts sheet names with dots and spaces. I'll
> fix this then. 

This does not appear to be a bug. The line in bug report:
          cell.setCellFormula("Sheet 1!A1");
is incorrect. It should be:
          cell.setCellFormula("'Sheet 1'!A1");
(Use single quotes around sheet name containing spaces/dots, thats how excel
does it anyway) If you dont use single quotes in formula, even excel complains
and throws up :)

Following is example of code that works:

public static void main(String[] args) {
 HSSFWorkbook sb = new HSSFWorkbook();
 HSSFSheet sheet = sb.createSheet("Sheet a.1");
 sheet = sb.createSheet();
 sheet.createRow(1).createCell((short) 2).setCellFormula("'Sheet a.1'!A1");
 sb.write(new FileOutputStream("/somedir/test34021.xls"));
 // close the FileOutputStream...

Comment 3 Avik Sengupta 2005-04-22 15:11:27 UTC
Added a testcase in TestFormulas. Works fine. 

thanks Amol.