Bug 55294 - [PATCH] Make POI producing <col> elements compatible with MS Excel
Summary: [PATCH] Make POI producing <col> elements compatible with MS Excel
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-dev
Hardware: PC All
: P2 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-07-22 21:52 UTC by vladk
Modified: 2013-08-05 16:17 UTC (History)
1 user (show)

Patch with unit tests (20.41 KB, patch)
2013-07-22 21:54 UTC, vladk
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description vladk 2013-07-22 21:52:20 UTC
The OOXML spec leaves some fields optional and doesn't define what should happen if, for instance "width" attribute is not set on a "col" element.

The MS Excel 2010 never leaves the "width" attribute unset, the POI does it. As result the XLSX files created with POI can look corrupted when opened with MX Excel 2010.

I have created a patch to make POI behaves more like MS Excel and written several unit tests to verify it. The changes could be summarized as follows:
- never leave the "width" attribute unset
- never let a column being defined by more than one "col" element

In other words, avoid configuration like follows:
       <col min="3" max="3" width="20.0" customWidth="true" collapsed="false" outlineLevel="1" />
       <col min="3" max="5" collapsed="false" outlineLevel="1" />
Instead create something like this:
       <col min="3" max="3" width="20.0" customWidth="true" collapsed="false" outlineLevel="1" />
       <col min="4" max="5" width="8.0" collapsed="false" outlineLevel="1" />

I have decided to created a separate test class TestXSSFColGrouping instead of adding test methods to the TestXSSFSheet near to the testSetColumnGroupCollapsed() method, because my tests can save generated XLSX files on a disk if a system property "poi.test.xssf.output.dir" is set. It helps to verify the files interpreted as expected in MS Excel. Furthermore the tests log CTCols content via private instance of the POILogger.
Comment 1 vladk 2013-07-22 21:54:24 UTC
Created attachment 30613 [details]
Patch with unit tests
Comment 2 vladk 2013-07-22 21:55:45 UTC
The proposed patch should also resolve https://issues.apache.org/bugzilla/show_bug.cgi?id=52186.
Comment 3 Tim Allison 2013-07-26 14:34:54 UTC
  I should have time in the next few days to review and commit unless another committer wants to take this sooner.  Thank you for subitting this!
Comment 4 Tim Allison 2013-08-05 16:17:58 UTC
Committed r1510587.  Thank you, Vlad!