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

You might consider switching from portrait to landscape (in other words, transposing the data) so that you get to use 65k rows for days of the year. This is exactly what I did with a report requiring a large number of columns and small number of rows.

Alternatively, you might split the year in 6-month sub periods, each one on it's own sheet.

These are both stopgap solutions, but might provide you with a "good enough" solution, depending on the requirements you have.

Answer 2

Your problem may be with Excel, not your API.

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

Answer 3

I figured it out. I had to switch from the org.apache.poi.hssf.usermodel to the org.apache.poi.ss.usermodel

This allows you to use 16k columns or something like that.

Answer 4

only if the target version of excel is 2007 or above