Java 如何使用 Apache POI 3.6 在 Excel 表中获取超过 255 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2326668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get more than 255 columns in an excel sheet using Apache POI 3.6
提问by Tommy
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.
我正在用 excel 创建日历。第 1 列是 01.01.2010 (dd.MM.yyyy)，第 2 列是 02.01.2010，依此类推。
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:
当我到达第 256 列时，POI 抛出此异常：
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?
我发现这个错误报告表明它已在 3.6 版本中修复。我们使用的是 3.5，但更改为 3.6 似乎没有任何好处。有人有任何提示吗？
https://issues.apache.org/bugzilla/show_bug.cgi?id=46806
https://issues.apache.org/bugzilla/show_bug.cgi?id=46806
Edit: Seems like the bug-issue was about formulas..
编辑：似乎错误问题与公式有关..
采纳答案by Tommy
I figured it out. I had to switch from the org.apache.poi.hssf.usermodelto the org.apache.poi.ss.usermodel
我想到了。我不得不从org.apache.poi.hssf.usermodel切换 到org.apache.poi.ss.usermodel
This allows you to use 16k columns or something like that.
这允许您使用 16k 列或类似的东西。
回答by Tomislav Nakic-Alfirevic
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.
您可能会考虑从纵向切换到横向（换句话说，转置数据），以便一年中的几天可以使用 65k 行。这正是我对需要大量列和少量行的报告所做的。
Alternatively, you might split the year in 6-month sub periods, each one on its own sheet.
或者，您可以将一年分成 6 个月的子期间，每个子期间都在自己的工作表上。
These are both stopgap solutions, but might provide you with a "good enough" solution, depending on the requirements you have.
这些都是权宜之计，但可能会为您提供“足够好”的解决方案，具体取决于您的要求。
回答by Ed Harper
Your problem may be with Excel, not your API.
您的问题可能出在 Excel 上，而不是出在 API 上。
The maximum number of columns in a pre-Excel 2007 spreadsheet is 256.
回答by evilReiko
That's because Excel has limited number columns See this: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
那是因为 Excel 的列数有限 请参阅：http: //office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
回答by kk1010
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.usermodelwhile the object model for 2007 onwards sheets is org.apache.poi.xssf.usermodelThe package org.apache.poi.ss.usermodelprovides 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.usermodelpackage or within org.apache.poi.ss.usermodel.
2007 年之前的 Excel 工作表限制为 256 列。这些工作表还会生成“.xls”扩展名。Excel 2007 及更高版本可容纳 16K 列，基于 XML 格式并生成“.xlsx”格式。2007 年之前的工作表的 POI 对象模型是org.apache.poi.hssf.usermodel2007 年以后工作表的对象模型是org.apache.poi.xssf.usermodel该包org.apache.poi.ss.usermodel提供了一个统一的界面，涵盖了两个对象模型。因此，要创建超过 256 列，您必须在org.apache.poi.xssf.usermodel包内或org.apache.poi.ss.usermodel.
回答by Rajendra
How to get more than 255 columns in an excel sheet using Apache POI
try
如何使用 Apache POI 在 excel 表中获取超过 255 列
尝试
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>
