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

I'm creating a cale开发者_如何学Pythonndar 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..

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.

Your problem may be with Excel, not your API.

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

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.

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 its own sheet.

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

That's because Excel has limited number columns See this: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

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

Workbook workbook = new XSSFWorkbook()

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>`enter code here`
   <version>3.15</version>
  </dependency>

  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.15</version>
  </dependency>