Bug 52186 - groupColumn with setColumnWidth not working
Summary: groupColumn with setColumnWidth not working
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-dev
Hardware: PC All
: P2 major with 2 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-15 11:14 UTC by Alesya
Modified: 2013-09-05 12:10 UTC (History)
1 user (show)



Attachments
test and xlsx-file with unsuccessful column group (7.82 KB, application/x-zip-compressed)
2011-11-15 11:14 UTC, Alesya
Details
Patch to set column width when grouping columns (3.96 KB, patch)
2013-07-07 18:40 UTC, vladk
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Alesya 2011-11-15 11:14:02 UTC
Created attachment 27938 [details]
test and xlsx-file with unsuccessful column group

If we set the width of the columns and add group on the columns, the columns do not appear. 

Attachment contains a file with a test that generates xlsx-file with unsuccessful group and generated file.

Included libraries:
•	poi-3.8-beta4-20110826.jar 
•	poi-examples-3.8-beta4-20110826.jar 
•	poi-excelant-3.8-beta4-20110826.jar 
•	poi-ooxml-3.8-beta4-20110826.jar 
•	poi-ooxml-schemas-3.8-beta4-20110826.jar 
•	poi-scratchpad-3.8-beta4-20110826.jar
Comment 1 vladk 2013-05-13 20:04:29 UTC
I can confirm the problem is still present in POI 3.9. Below is the simplest client to reproduce the problem:

        Workbook wb = new XSSFWorkbook();
        Sheet sheet1 = wb.createSheet("new sheet");

        sheet1.setColumnWidth(4, 5000);
        sheet1.setColumnWidth(5, 5000);
       
        sheet1.groupColumn((short) 4, (short) 7);
        sheet1.groupColumn((short) 9, (short) 12);

        FileOutputStream fileOut = new FileOutputStream("outlining.xlsx");
        wb.write(fileOut);
        fileOut.close();
Comment 2 vladk 2013-06-21 19:30:01 UTC
It appears that Excel 2010 doesn't like column definitions with undefined "width" attribute like follows:
<cols>
	<col min="5" max="5" width="20.0" customWidth="true" collapsed="false" outlineLevel="1"/>
	<col min="6" max="8" collapsed="false" outlineLevel="1"/>
</cols>

The Excel 2010 would create the following column configuration:
<cols>
	<col min="5" max="5" width="20.0" customWidth="1" outlineLevel="1"/>
	<col min="6" max="8" width="11.42578125" customWidth="1" outlineLevel="1"/>
</cols>
whereas width="11.42578125" seems to be the default width for my system.

According to the specification width is an optional attribute with unspecified default value: http://www.schemacentral.com/sc/ooxml/e-ssml_col-1.html


The mentioned column configuration was produced by the following code:
	sheet1.setColumnWidth(4, 20*256);
	sheet1.groupColumn( (short)4, (short)7 );


A workaround is to set explicit width for all grouped columns after the grouping:
	sheet1.setColumnWidth(4, 20*256);
	sheet1.groupColumn( (short)4, (short)7 );
	sheet1.setColumnWidth(5, sheet1.getDefaultColumnWidth() * 256);
	sheet1.setColumnWidth(6, sheet1.getDefaultColumnWidth() * 256);
	sheet1.setColumnWidth(7, sheet1.getDefaultColumnWidth() * 256);

Does anyone have an idea what where the proper class/method to modify POI to make it setting "width" attribute always?
Comment 3 Nick Burch 2013-06-25 21:30:19 UTC
In Excel, if you group the columns, does it auto-calculate the width for you? 

(I'm just wondering if we can safely just read the widths of the columns to be grouped to set the overall grouping width, or if we'd need to do an auto-calculation first)

As for where to put the code, search for CTCols and CTCol, those should be the names of the xmlbeans classes for <cols> and <col> - any width setting code will want to go with/near similar modifications of those beans.
Comment 4 vladk 2013-06-26 07:55:27 UTC
Excel 2010 creates <cols> tag with the following content for:

- Excel sheet with one column having custom width
<cols>
	<col min="2" max="2" width="26.625" customWidth="1"/>
</cols>

- Excel sheet with one column having custom width. The column is part of a column group "2..4". The column group is expanded.
<cols>
	<col min="2" max="2" width="26.625" customWidth="1" outlineLevel="1"/>
	<col min="3" max="4" width="11" outlineLevel="1"/>
</cols>

- Excel sheet with one column having custom width. The column is part of a column group "2..4". The column group is collapsed.
<cols>
	<col min="2" max="2" width="26.625" hidden="1" customWidth="1" outlineLevel="1"/>
	<col min="3" max="4" width="0" hidden="1" customWidth="1" outlineLevel="1"/>
	<col min="5" max="5" width="11" collapsed="1"/>
</cols>

Excel 2010 never omits the "width" attribute in <col> tags. If the column width is not defined explicitly, it takes "0" for hidden columns and "11" for visible ones. "11" appears to be the default width. To behave like Excel 2010, the POI has to set "width" attribute whenever it creates a <col> tag.

By the way, Excel 2010 and POI have different default widths: Excel 2010 - "11", POI - "8". Why "8"? May the XSSFSheet.getDefaultColumnWidth() method be modified, to return 11 instead of 8?
Comment 5 vladk 2013-07-07 18:40:51 UTC
Created attachment 30563 [details]
Patch to set column width when grouping columns
Comment 6 Tim Allison 2013-08-05 16:22:53 UTC
Should be fixed as of r1510587.