How to get more than 255 columns in an excel sheet using Apache POI 3.6

Question

I'm creating a calendar in excel. Column 1 is 01.01.2010 (dd.MM.yyyy), Column 2 is 02.01.2010 and so on.

This is my code:

int day_cell = 0;

for(int i = 0; i < days.size(); i++)
{
     Date day = days.get(i);

     HSSFCell cell = row.createCell(day_cell++);
     cell.setCellValue(day.toString());
}

When I get to column 256 POI throws this exception:

java.lang.IllegalArgumentException: Invalid column index (256).  Allowable column range for BIFF8 is (0..255) or ('A'..'IV')
at org.apache.poi.hssf.usermodel.HSSFCell.checkBounds(HSSFCell.java:926)
at org.apache.poi.hssf.usermodel.HSSFCell.<init>(HSSFCell.java:162)
at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:141)
at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:119)

I found this bug-report suggesting it was fixed in the 3.6 release. We were using 3.5 but changing to 3.6 doesn't seem to do any good. Anyone got any tips?

https://issues.apache.org/bugzilla/show_bug.cgi?id=46806

Edit: Seems like the bug-issue was about formulas..

Answer 1

Pre-2007 excel sheets had a limitation of 256 columns. Those sheets also generate ".xls" extension. Excel 2007 and onwards can accommodate 16K columns, are based on an XML format and generate ".xlsx" format. The POI object model for pre-2007 sheets is org.apache.poi.hssf.usermodel while the object model for 2007 onwards sheets is org.apache.poi.xssf.usermodel The package org.apache.poi.ss.usermodel provides a uniform interface covering both the object models. So to create more than 256 columns, you will have to use classes within the org.apache.poi.xssf.usermodel package or within org.apache.poi.ss.usermodel.

Answer 2

Your problem may be with Excel, not your API.

The maximum number of columns in a pre-Excel 2007 spreadsheet is 256.