英文:
Apache POI SXSSFWorkbook: date style is not applied/not working
问题
我们正在使用Apache POI库从我们的应用程序创建Excel表格。
出于内存原因（我们的应用程序创建了巨大的表格（> 150,000行），导致内存消耗飙升超过3GB），我不得不从默认的（XSSF）Workbook切换到滑动窗口变体（SXSSFWorkbook）。
大多数功能都按预期工作（除了现在必须在创建工作表后调用trackAllColumnsForAutoSizing()来自动调整列的大小之外），但存在一个非常恼人的问题：
日期样式的分配似乎不再起作用！
用于创建日期单元格的样式的代码是：
protected CellStyle createDateCellStyle() {
SXSSFCreationHelper createHelper = (SXSSFCreationHelper) workbook.getCreationHelper();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yyyy"));
return cellStyle;
}
（在我的实验中，我将原始的CreationHelper更改为SXSSFCreationHelper，但这没有改变任何事情。
在创建数据单元格后，我们只需将日期样式分配给相关单元格：
cell.setCellStyle(dateStyle);
但是，所有应该格式化为日期的单元格不是以日期形式显示，而是以整数格式显示。 
相同的代码在使用POI表格的“全内存”变体时可以正常工作。
这是否是已知的错误？是否存在修复或解决方法？
我们使用Java 8，POI版本5.2.3（截至本文撰写时的最新版本）。
我们的运行时是Tomcat v7（但我怀疑这在这里无关紧要）。
英文:
We are using the Apache POI library to create Excel sheets from our application.
For memory reasons (our application creates huge sheets (>150'000 lines) and this caused memory consumption spikes of >3GB) I had to switch from the default (XSSF)Workbook to the sliding window variant (SXSSFWorkbook).
Most features worked as expected (except for the autosizing of columns for which one now has to call trackAllColumnsForAutoSizing() after creation of the sheet) but for one most annoying bug:
The assignment of date-styles does not seem to work anymore!
The code that creates the style we use for date-cells is:
protected CellStyle createDateCellStyle() {
SXSSFCreationHelper createHelper = (SXSSFCreationHelper)workbook.getCreationHelper();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yyyy"));
return cellStyle;
}
(I changed the original CreationHelper to SXSSFCreationHelper during my experiments to get this working, but it didn't change anything.
After creation of a data cell we simply assign the date-style to the relevant cells:
cell.setCellStyle(dateStyle);
But instead of being displayed as dates all cells that are supposed to be formatted as dates are instead formatted as integers.
The very same code works without glitch using the "all-in-memory" variant of POI sheets.
Is that a known bug? Do there exist fixes or workarounds for this?
We are using Java 8, POI version 5.2.3 (the latest at the time of this writing).
Our runtime is Tomcat v7 (but I doubt this is relevant here).
答案1
得分: 0
经过调查，单元格未正确显示为日期的原因是createCell()和getCell()的调用顺序不正确。我们的代码在相同的行和列上意外地使用了MissingCellPolicy.CREATE_NULL_AS_BLANK调用getCell()，并在调用createCell()方法之前为其指定了所需的样式（因此覆盖了以前获取的没有样式的单元格）。这就是为什么该单元格随后以数字/整数格式显示的原因。
让我感到困惑的是：这段代码（在这个特定顺序下！）多年来都“运行”正常，我没有对其进行任何更改。显然，在早期版本中，setValue(Date)方法必须默认分配了日期格式。否则，我无法解释为什么在迁移到最新的POI版本和工作簿的流式变体后，值突然以数字的形式显示而不再以日期的形式显示。
英文:
As turned out the resaon for the cells not properly shown as dates was an invalid sequence of createCell() and getCell(). Our code accidentally called getCell() with MissingCellPolicy.CREATE_NULL_AS_BLANK and assigned it the desired style BEFORE calling the createCell()-method on the same row and column (thus overwriting the previously obtained cell with a cell without style). That was the reason why the cell was then formatted as number/integer.
What beats me is: this code (in that very sequence!) has "worked" for years and I hadn't changed anything in it. Apparently the setValue(Date) method must have assigned a Date-format by default in earlier versions. Or else I have no explanation why Date-cells always showed up properly formatted as date until I had migrated to the latest POI version and the streaming variant of Workbooks where the values suddenly showed up as numbers.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论