Bug 51622

Summary: autoSizeColumn incorrectly sizes columns containing leading whitespace
Product: POI Reporter: J2G <naveeniq>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: onealj, st.mailinglists
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Sample java file.
Output file
SheetUtil.java fix
SheetUtil.java fix and corresponding unit test

Description J2G 2011-08-05 11:22:06 UTC
Created attachment 27352 [details]
Sample java file.

autoSizeColumn does not sizes text when we have leading space in the cell.
Reference to bug #47082.

Checked with jre6 and poi-3.7-20101029.jar. Can use the attached for reference.
Comment 1 J2G 2011-08-05 11:23:12 UTC
Created attachment 27353 [details]
Output file
Comment 2 Javen O'Neal 2015-11-04 03:28:53 UTC
Created attachment 33253 [details]
SheetUtil.java fix

This problem still exists in POI 3.13 and the latest 3.14 dev build. Cell width computation is done in org.apache.poi.ss.util.SheetUtil, so this bug is common to HSSF, XSSF, and SXSSF workbooks.

The problem is here[1]:
> private static double getCellWidth(int defaultCharWidth, int colspan,
>         CellStyle style, double width, AttributedString str) {
> ...
>     width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + style.getIndention());

Should be (bounds.getX() + bounds.getWidth()), since the bounding box starts at the first non-whitespace character.

This patch is untested and needs a corresponding unit tests before it can be committed.

[1] https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java?view=markup#l180
Comment 3 Javen O'Neal 2015-11-04 03:31:40 UTC
In your example (attachment 27352 [details]), the cell in the 4th row has a bounding rectangle of
java.awt.geom.Rectangle2D$Float[x=58.570312,y=-7.703125,w=118.79785,h=7.890625]
Comment 4 Javen O'Neal 2015-11-04 03:34:39 UTC
(In reply to Javen O'Neal from comment #3)
> java.awt.geom.Rectangle2D$Float[x=58.570312,y=-7.703125,w=118.79785,h=7.
> 890625]
Correction: java.awt.geom.Rectangle2D$Float[x=28.570312,y=-7.703125,w=118.79785,h=7.890625]

The defaultCharWidth is 6.323242 on my system, so assuming a space character is half the width of a "0" character in Arial, 9 leading spaces should have a width of 28.4546. Math checks out.
Comment 5 Javen O'Neal 2015-11-04 06:38:58 UTC
Created attachment 33254 [details]
SheetUtil.java fix and corresponding unit test
Comment 6 Javen O'Neal 2015-11-04 06:46:13 UTC
Applied attachment 33254 [details] in r1712477 to trunk.

Updated site docs in r1712479.