Bug 47082 - HSSFSheet autoSizeColumn method do not auto size column perfectly when there is leading white spaces in any cell
Summary: HSSFSheet autoSizeColumn method do not auto size column perfectly when there ...
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.2-FINAL
Hardware: PC All
: P3 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-04-22 22:17 UTC by Muhammed
Modified: 2011-08-05 11:21 UTC (History)
2 users (show)



Attachments
sample java file which demonstrates the issue (1.09 KB, application/octet-stream)
2009-04-22 22:17 UTC, Muhammed
Details
sample output which does not exhibit the problem (4.50 KB, application/vnd.ms-excel)
2009-04-24 11:56 UTC, Yegor Kozlov
Details
screenshot from Excel 2007 - row3 is resized correctly (30.22 KB, image/png)
2009-04-24 11:57 UTC, Yegor Kozlov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Muhammed 2009-04-22 22:17:44 UTC
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.
Comment 1 Muhammed 2009-04-23 08:46:37 UTC
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
Comment 2 Yegor Kozlov 2009-04-24 11:56:24 UTC
Created attachment 23539 [details]
sample output which does not exhibit the problem
Comment 3 Yegor Kozlov 2009-04-24 11:57:16 UTC
Created attachment 23540 [details]
screenshot from Excel 2007 - row3 is resized correctly
Comment 4 Yegor Kozlov 2009-04-24 11:58:42 UTC
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
Comment 5 Muhammed 2009-04-24 20:11:57 UTC
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
Comment 6 Muhammed 2009-04-24 20:18:58 UTC
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
Comment 7 Yegor Kozlov 2009-04-24 22:45:16 UTC
(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
Comment 8 Muhammed 2009-04-28 22:39:24 UTC
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");
                    ...
                    ...
Comment 9 Yegor Kozlov 2009-05-23 01:33:51 UTC
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