Created attachment 23532 [details] sample java file which demonstrates the issue HSSFSheet autoSizeColumn method do not auto size column perfectly when there is leading white spaces in any cell. Suppose there is few leading whitespaces in cell, and when we call autoSizeColumn on the particular column, we can see the generated excel do not auto size perfectly. It looks like the autoSizeColumn method discards the leading zeros in the text cell before finding the maximum width of each cell. run the attached java code, which generates autoSizeColumn.xls, and see the autosize is not perfect.
Exact reason is POI uses TextLayout to find the width, for which, layout.getBounds().getWidth() return same value for "A", " A", "A ". layout = new TextLayout("A", font, frc); "A" width = 0.26875 layout = new TextLayout(" A", font, frc); " A" width = 0.26875 layout = new TextLayout("A ", font, frc); "A " width = 0.26875 layout = new TextLayout("AA", font, frc); "AA" width = 0.535546875
Created attachment 23539 [details] sample output which does not exhibit the problem
Created attachment 23540 [details] screenshot from Excel 2007 - row3 is resized correctly
Works fine to me. See attached output and a screenshot. Try the latest trunk. Daily builds can be downloaded from http://encore.torchbox.com/poi-svn-build/ Yegor
I have tried with poi-3.5-beta6-20090424.jar and still able to reproduce the issue. May I know which version of java you are using. I tried with java 1.5. java version "1.5.0_16" Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_16-b02) Java HotSpot(TM) Client VM (build 1.5.0_16-b02, mixed mode, sharing) Muhammed
Looks like working fine with java 1.6. java version "1.6.0_04" Java(TM) SE Runtime Environment (build 1.6.0_04-b12) Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode, sharing) Any chance to get a fix for POI 3.2, where we are using it with java 1.4.2 Thanks Muhammed
(In reply to comment #6) > Looks like working fine with java 1.6. > > java version "1.6.0_04" > Java(TM) SE Runtime Environment (build 1.6.0_04-b12) > Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode, sharing) > > Any chance to get a fix for POI 3.2, where we are using it with java 1.4.2 > > Thanks > Muhammed (In reply to comment #6) > Looks like working fine with java 1.6. > > java version "1.6.0_04" > Java(TM) SE Runtime Environment (build 1.6.0_04-b12) > Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode, sharing) > > Any chance to get a fix for POI 3.2, where we are using it with java 1.4.2 > > Thanks > Muhammed It might be a jdkspecific bug, your code works with java 1.5.13 and java 1.6.13. Try a different version of Java. I don't think there will be a special fix for JDK 1.5.0_16. Yegor
I have a workaround for this. If I replace all whitespaces with some character(say ‘I’ – the one which take least width), autoSize seems to be working fine. For those who uses jdk 1.4 & 1.5, is it possible to provide a patch like this? See the line below - txt = txt.replaceAll(" ", "i"); public void autoSizeColumn(short column, boolean useMergedCells) { AttributedString str; TextLayout layout; /** * Excel measures columns in units of 1/256th of a character width * but the docs say nothing about what particular character is used. * '0' looks to be a good choice. */ char defaultChar = '0'; /** * This is the multiple that the font height is scaled by when determining the * boundary of rotated text. */ double fontHeightMultiple = 2.0; FontRenderContext frc = new FontRenderContext(null, true, true); HSSFWorkbook wb = new HSSFWorkbook(book); HSSFFont defaultFont = wb.getFontAt((short) 0); str = new AttributedString("" + defaultChar); copyAttributes(defaultFont, str, 0, 1); layout = new TextLayout(str.getIterator(), frc); int defaultCharWidth = (int)layout.getAdvance(); double width = -1; rows: for (Iterator it = rowIterator(); it.hasNext();) { HSSFRow row = (HSSFRow) it.next(); HSSFCell cell = row.getCell(column); if (cell == null) { continue; } int colspan = 1; for (int i = 0 ; i < getNumMergedRegions(); i++) { CellRangeAddress region = getMergedRegion(i); if (containsCell(region, row.getRowNum(), column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. continue rows; } cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); } } HSSFCellStyle style = cell.getCellStyle(); HSSFFont font = wb.getFontAt(style.getFontIndex()); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { HSSFRichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { String txt = lines[i] + defaultChar; txt = txt.replaceAll(" ", "i"); ... ...
I'm closing it as "wontfix". The issue with leading white spaces is JDK-specific. Users of JDK 1.5.0_16 are advised to upgrade to a newer version. Yegor