|Summary:||The HSSFSheet.autoSizeColumn method doesn't handle rotated text well and truncates long decimal values|
|Product:||POI||Reporter:||Jeff Williams <kanobe>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
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