Bug 46806 - Unable to Create XSSF Workbook with formula that references column > 255
Summary: Unable to Create XSSF Workbook with formula that references column > 255
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows XP
: P2 critical with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-03-05 11:48 UTC by Tim
Modified: 2009-05-17 09:41 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 Tim 2009-03-05 11:48:05 UTC 
Creating an XSSF Workbook with a cell formula that references a cell in a column that is greater than 255 causes an exception.

In org.apache.poi.ss.util.CellReference there is the constant:

private static final String BIFF8_LAST_COLUMN = "IV";

Should be some kind of switch depending on if the workbook is HSSF or XSSF to allow for references in columns > 255.







java.lang.IllegalArgumentException: Unparsable formula 'SUM(F4:IW4)'
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:384)
	at org.vbn.hours.logic.ExcelReporter.exportTPDPlannedHoursReport(ExcelReporter.java:2751)
	at org.vbn.hours.logic.ExcelReporter.runReport(ExcelReporter.java:70)
	at org.vbn.hours.logic.VBNHoursController$1.doInBackground(VBNHoursController.java:61)
	at org.vbn.hours.logic.VBNHoursController$1.doInBackground(VBNHoursController.java:1)
	at javax.swing.SwingWorker$1.call(SwingWorker.java:278)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at javax.swing.SwingWorker.run(SwingWorker.java:317)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Specified named range 'IW4' does not exist in the current workbook.
	at org.apache.poi.ss.formula.FormulaParser.parseNameOrCellRef(FormulaParser.java:440)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:340)
	at org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:332)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:733)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:693)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:680)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:982)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1083)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1067)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1024)
	at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:665)
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:562)
	at org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:324)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:733)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:693)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:680)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:982)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1083)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1067)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1024)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1003)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1125)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:192)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:381)
	... 11 more
Comment 1 Tim 2009-04-01 16:19:45 UTC 
Does anyone have a workaround for this?  I am trying to generate a calendar year of hours worked for all employees in our organization.  I need 365 columns for the days in the year and a SUM at the end.
Comment 2 Josh Micich 2009-04-01 17:49:05 UTC 
(In reply to comment #0)
> Creating an XSSF Workbook with a cell formula that references a cell in a
> column that is greater than 255 causes an exception.
> 
> In org.apache.poi.ss.util.CellReference there is the constant:
> 
> private static final String BIFF8_LAST_COLUMN = "IV";
> 
> Should be some kind of switch depending on if the workbook is HSSF or XSSF to
> allow for references in columns > 255.

You're absolutely correct, but this change is is not a trivial one to make (there is an extensive tree of method calls to pass that parameter through).
This is something we intend to get done before 3.5-final release.

AS far as I know there is no practical work-around.
Comment 3 Yegor Kozlov 2009-04-02 00:15:59 UTC 
The problem is being fixed. As Josh said, it's not trivial and will take some time. 

Yegor

(In reply to comment #1)
> Does anyone have a workaround for this?  I am trying to generate a calendar
> year of hours worked for all employees in our organization.  I need 365 columns
> for the days in the year and a SUM at the end.
Comment 4 Yegor Kozlov 2009-05-17 09:41:06 UTC 
Fixed in r775701. 
Formulas in XSSF now support the "Big Grid" of 1 million rows and 16000 columns.

Regards,
Yegor