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 }
Created attachment 27315 [details] sample file generated using the latest build from trunk
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
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?
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?
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.
(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
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.
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.