Bug 53441

Created attachment 28967 [details]
Template file used for reproduction of issue

When defining column formatting in an Excel template and populating the template using POI by creating new rows and cells, the corresponding column formatting is not respected with XSSF.

See the following example in combination with the attached xlsx template file:

<---

Workbook wbx = WorkbookFactory.create(new File("template.xlsx"));

Sheet sheet1 = wbx.getSheet("Sheet1");
Sheet sheet2 = wbx.getSheet("Sheet2");

Cell c1 = sheet1.createRow(1).createCell(1);
c1.setCellValue(123456789.12345);

Cell c2 = sheet2.getRow(1).getCell(1);
c2.setCellValue(123456789.12345);

wbx.write(new FileOutputStream("out.xlsx"));

--->

Sheet1 is a sheet where column formatting has been applied to column B. On Sheet2 on the other hand, the formatting has been applied to the cells directly.

When writing cell B2 on Sheet1, corresponding column formatting is not respected. Cell B2 on Sheet2 however is correctly formatted (obviously, since the cell style for the cell is specifically set).

Looking into the underlying XML for Sheet1, it looks like the style information from  <col min="2" max="2" style="3" width="9.140625" collapsed="false"/> is not applied to the cell when newly creating it. So instead of <c r="B2" s="3" t="n"><v>10000.0</v></c> we only get <c r="B2" t="n"><v>10000.0</v></c> (note the missing s="3").

The ECMA specification for the column formatting element <cols> says about the style attribute:

"Default style for the affected column(s). Affects cells not yet allocated in the column(s). In other words, this style applies to new columns."

So I guess this implies that the s="3" attribute should explicitly be specified with any newly created cell.

Note that the above example works fine with HSSF but not with XSSF.