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).
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'.
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.
Created attachment 21068 [details] autoSizeColumn before fix This is the output of the AutosizeRotatedTextColumns program BEFORE the proposed fix.
Created attachment 21069 [details] Sample output after implementing the fix This is the output of the AutosizeRotatedTextColumns program AFTER the proposed fix.
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
patch applied. Thanks, Yegor