CODEWITHSUNDEEP
javaexcelapache-poi
Tommy
Tommy

Reputation: 4111

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

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..

Upvotes: 20

Views: 36694

Answers (6)

Rajendra
Rajendra

Reputation: 21

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>

Upvotes: 2

Tomislav Nakic-Alfirevic
Tomislav Nakic-Alfirevic

Reputation: 10173

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. 

Upvotes: 3

kk1010
kk1010

Reputation: 296

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.

Upvotes: 28

evilReiko
evilReiko

Reputation: 20473

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

Upvotes: 2

Tommy
Tommy

Reputation: 4111

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.

Upvotes: 9

Ed Harper
Ed Harper

Reputation: 21495

Your problem may be with Excel, not your API.

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

Upvotes: 9

Related Questions