Bug 51622 - autoSizeColumn incorrectly sizes columns containing leading whitespace
Summary: autoSizeColumn incorrectly sizes columns containing leading whitespace
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2011-08-05 11:22 UTC by J2G
Modified: 2015-11-04 06:46 UTC (History)
2 users (show)

Sample java file. (1.09 KB, application/octet-stream)
2011-08-05 11:22 UTC, J2G
Output file (4.50 KB, application/vnd.ms-excel)
2011-08-05 11:23 UTC, J2G
SheetUtil.java fix (1.75 KB, patch)
2015-11-04 03:28 UTC, Javen O'Neal
Details | Diff
SheetUtil.java fix and corresponding unit test (6.68 KB, patch)
2015-11-04 06:38 UTC, Javen O'Neal
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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
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.