Bug 43751

Summary: The HSSFSheet.autoSizeColumn method doesn't handle rotated text well and truncates long decimal values
Product: POI Reporter: Jeff Williams <kanobe>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Java program that demonstrates the issues
Proposed fix to solve the two issues in the bug report
autoSizeColumn before fix
Sample output after implementing the fix

Description Jeff Williams 2007-10-31 07:51:41 UTC
The HSSFSheet.autoSizeColumn method doesn't handle rotated text well. When a
text value is entered into a column cell and the cell style is set to rotate the
text 90 degrees, the width of the generated cell is still sized to the unrotated
width of the text string. 

Additionally, the HSSFSheet.autoSizeColumn method doesn't handle numeric values
with more than 3 fractional decimal digits properly (for example the value
3.45334). For such values the generated cell is too narrow. It seems that for
Excel numeric values using the General format the method formats the number
using a java DecimalFormat object with the default constructor, which seems to
default to a pattern that only outputs three fractional digits (so 3.45334 would
be output as 3.453).
Comment 1 Jeff Williams 2007-10-31 07:53:55 UTC
Created attachment 21066 [details]
Java program that demonstrates the issues

This is a Java program that demonstrates the issues described in the bug
report. It outputs a series of cells in a single row, text then numbers, each
rotated. The output file is named 'rotated_columns.xls'.
Comment 2 Jeff Williams 2007-10-31 08:02:00 UTC
Created attachment 21067 [details]
Proposed fix to solve the two issues in the bug report

Here is an updated version of HSSFSheet.java (org.apache.poi.hssf.usermodel)
that fixes the two issues. If you would like a patch instead, let me know. 

The implemented fix solves the rotated text problem by performing some
transformations on the text before determining it's width:

1. First, the text is scaled vertically by a multiple of the font "leading"
value so that text rotated close to 90 degrees will have some whitespace around
it in the final output

2. Second, the text is rotated by the rotation specified in the cell layout

After these transformations the new width is determined from the text bounding
box.

The fix solves the numerical value problem by no longer using DecimalFormat to
format numerical cells that have the Excel "General" format, but instead using
a simple string conversion.
Comment 3 Jeff Williams 2007-10-31 08:04:04 UTC
Created attachment 21068 [details]
autoSizeColumn before fix

This is the output of the AutosizeRotatedTextColumns program BEFORE the
proposed fix.
Comment 4 Jeff Williams 2007-10-31 08:05:01 UTC
Created attachment 21069 [details]
Sample output after implementing the fix

This is the output of the AutosizeRotatedTextColumns program AFTER the proposed
fix.
Comment 5 Yegor Kozlov 2007-11-01 04:24:04 UTC
Very cool. Thanks for the patch.
I wrote autoSizeColumn without text rotation in mind but your fix extends it in
a very elegant way. 

Yegor
Comment 6 Yegor Kozlov 2007-11-03 12:39:57 UTC
patch applied.

Thanks,
Yegor