Apache POI generating invalid Excel XML when adding styles

Question

I'm using Apache POI and trying to add a date format style to a numeric field in my spreadsheet. But when I add a style to my Cell it fails to open in Excel (tries to repair the file).

My code looks pretty much like this:

DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook(100);
DeferredSXSSFSheet sheet1 = wb.createSheet("Sheet1");
...
Row row = sheet.createRow(0);
CellStyle style = sheet.getWorkbook().createCellStyle();
CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
short format = createHelper.createDataFormat().getFormat("m/d/yy");
style.setDataFormat(format);
...
Cell cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new Date());

I was able to unzip the xlsx file and get the generated sheet1.xml:

<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <dimension ref="A1"/>
    <sheetViews>
        <sheetView tabSelected="true" workbookViewId="0"/>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15.0"/>
    <sheetData>
        <row r="2">
            <c r="A2" s="1" t="n">
                <v>45442.657083680555</v>
            </c>
        </row>
    </sheetData>
    <pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/>
</worksheet>

And styles.xml:

<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <numFmts count="0"/>
    <fonts count="1">
        <font>
            <sz val="11.0"/>
            <color indexed="8"/>
            <name val="Calibri"/>
            <family val="2"/>
            <scheme val="minor"/>
        </font>
    </fonts>
    <fills count="2">
        <fill>
            <patternFill patternType="none"/>
        </fill>
        <fill>
            <patternFill patternType="darkGray"/>
        </fill>
    </fills>
    <borders count="1">
        <border>
            <left/>
            <right/>
            <top/>
            <bottom/>
            <diagonal/>
        </border>
    </borders>
    <cellStyleXfs count="1">
        <xf borderId="0" fillId="0" fontId="0" numFmtId="0"/>
    </cellStyleXfs>
    <cellXfs count="1">
        <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
    </cellXfs>
</styleSheet>

If I'm understanding the problem, s="1" on the cell should be referencing the date format style? However, I don't see any style that looks like the one I created in styles.xml. I should be expecting something more inside of cellStyleXfs, right?

I checked lots of examples and the Apache documentation but I'm not seeing what I'm doing wrong. Anyone run into this before or have any ideas?

Answer 1

Looks as if the cell style was not written. Cell styles are on workbook level and get shared between cells in sheets. Thus cell styles cannot be created while streaming the cells to the sheets.

See example HSSF and XSSF Examples -> XSSF-only Examples -> DeferredGeneration.java:

... // cell styles should be created outside the row generator function ...

While your code is not a complete example, I suspect you are trying to generate the cell styles in the row-generator-function. This will not generate any cell styles as cell styles are on workbook level and not on row level nor on cell level. Cells only refer to cell styles via the s attribute.

Complete example, which works:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import java.util.Date;

class CreateExcelDateDeferredSXSSFWorkbook  {

 public static void main(String[] args) throws Exception {

  try (DeferredSXSSFWorkbook workbook = new DeferredSXSSFWorkbook(100);) {

   // cell styles should be created outside the row generator function
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();         
   short format = createHelper.createDataFormat().getFormat("m/d/yy");
   cellStyle.setDataFormat(format);

   DeferredSXSSFSheet sheet = workbook.createSheet("Sheet1");
   sheet.setRowGenerator((ssxSheet) -> {
    for (int i = 0; i < 1; i++) {
     Row row = ssxSheet.createRow(i);

     // Don't do this here! It will not be stored into styles.
     //CellStyle cellStyle = ssxSheet.getWorkbook().createCellStyle();
     //CreationHelper createHelper = ssxSheet.getWorkbook().getCreationHelper();         
     //short format = createHelper.createDataFormat().getFormat("m/d/yy");
     //cellStyle.setDataFormat(format);
     
     Cell cell = row.createCell(0);
     cell.setCellStyle(cellStyle);
     cell.setCellValue(new Date());
    }
   }); 

   try (FileOutputStream fileout = new FileOutputStream("./Excel.xlsx");) {
    workbook.write(fileout);
   } finally {
    workbook.dispose();
   }
  }

 }
}