Bug 53441 - Column formatting not respected when creating new cells
Summary: Column formatting not respected when creating new cells
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-06-20 10:54 UTC by Martin Studer
Modified: 2015-10-29 09:19 UTC (History)
1 user (show)

Template file used for reproduction of issue (9.05 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-06-20 10:54 UTC, Martin Studer

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Studer 2012-06-20 10:54:36 UTC
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);

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

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.