CODEWITHSUNDEEP
apache-poi
mmo
mmo

Reputation: 4246

Apache POI SXSSFWorkbook: date style is not applied/not working

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

Excel displaying date cells 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).

Upvotes: 0

Views: 766

Answers (1)

mmo
mmo

Reputation: 4246

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.

Upvotes: 0

Related Questions