Bug 51554

Summary: autoSizeColumn sets column width too small for some capital letters
Product: POI Reporter: bryan_coleman
Component: HSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: enhancement    
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: sample file generated using the latest build from trunk

Description bryan_coleman 2011-07-25 20:58:17 UTC
Using version 3.7 and 3.8-beta3

HSSFSheet.autoSizeColumn calculates the size of a column with capital letters too small.  Unfortunately, the test is visual.  With the test below, notice how the "TEST" and "HIPS" columns are too small.

To reproduce:

import org.apache.poi.hssf.usermodel.*;
import java.io.*;

public class AutoSizeTest {

    public static void main(String args[]) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell0 = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        HSSFCell cell3 = row.createCell(3);
        HSSFCell cell4 = row.createCell(4);
        cell0.setCellValue("TEST");
        cell1.setCellValue("test");
        cell2.setCellValue("ZZZZ");
        cell3.setCellValue("zzzz");
        cell4.setCellValue("HIPS");
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);

        try {
            FileOutputStream out = new FileOutputStream("working.xls");
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    } // main
}
Comment 1 Yegor Kozlov 2011-07-26 07:40:41 UTC
Created attachment 27315 [details]
sample file generated using the latest build from trunk
Comment 2 Yegor Kozlov 2011-07-26 07:45:43 UTC
There have been updates in HSSFSheet.autoSizeColumn since 3.8-beta3, please try with the latest build from trunk. Daily builds can be downloaded from here:
http://encore.torchbox.com/poi-cvs-build/


See attached sample generated with the latest build from trunk. All columns including TEST and HIPS are properly re-sized.

Yegor
Comment 3 bryan_coleman 2011-07-27 12:48:24 UTC
I tried poi-3.8-beta4-20110727.jar and it looks the same as version 3.7 / 3.8-beta3.  I opened the provided attached file and it looks good.  What version should I be trying?  Is there any way to verify the poi version in the output (xls) file?
Comment 4 Nick Burch 2011-07-27 12:51:58 UTC
See the FAQ for how to check what POI version you're running - http://poi.apache.org/faq.html#faq-N10006

Also, are you sure you have the correct fonts installed? Sizing columns requires the font to work out how big letters render - can you confirm you have appropriate fonts installed?
Comment 5 bryan_coleman 2011-07-27 17:06:56 UTC
Thank you for the information on finding the poi version.  It doesn't really show how to obtain it from the output; however, the information was valuable.

Correct fonts installed?

  Are you referring to the fonts of the underlying system?  Or, in my java classpath/libraries/etc?  I know I didn't install any fonts specifically for this purpose.  For reference: I am running linux Fedora version 15.
Comment 6 Nick Burch 2011-07-27 17:21:54 UTC
(In reply to comment #5)
> Thank you for the information on finding the poi version.  It doesn't really
> show how to obtain it from the output; however, the information was valuable.

You should get a jar filename, which will have the version string in it (unless you've done something strange and renamed the jar that is!)
 

> Correct fonts installed?
> 
>   Are you referring to the fonts of the underlying system?  Or, in my java
> classpath/libraries/etc?  I know I didn't install any fonts specifically for
> this purpose.  For reference: I am running linux Fedora version 15.

The fonts need to be available and usable by Java. You firstly need to ensure that all the required fonts are installed on your system, then you need to ensure that Java looks for them. Installing the required true type fonts is distro specific. Once installed, either set the JAVA_FONTS environment variable to point to the directory containing them, or edit the JRE font properties file to list the directory in something like the "appendedfontpath" entry

Try something like the code on http://silveiraneto.net/2010/01/05/java-font-list/ to list the fonts that Java can see. If the one excel uses isn't there, or at least one very close, then POI doesn't know how big your text is so can't correctly size
Comment 7 bryan_coleman 2011-07-27 17:54:55 UTC
I see that makes the problem more interesting.  We are using poi to generate reports via linux system and distributing those reports to windows based clients.  There may be a list that matches fonts from linux to microsoft ones that would get us close.  I don't know what the licensing requirements would be to use the microsoft ones directly from linux.  Thank you for the explanation.
Comment 8 David Fisher 2011-07-27 18:02:25 UTC
It might be worthwhile discussing the use of Adobe Font Metric files when fonts are not available. For years this was not possible due to licensing issues, but now if you look at http://www.apache.org/legal/resolved.html you will see that the Adobe AFM license is considered similar to the Apache License 2.0.