Bug 48406 - Sheet.autoSizeColumn incorrect for Verdana, Calibri
Summary: Sheet.autoSizeColumn incorrect for Verdana, Calibri
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.6-FINAL
Hardware: PC Linux
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-12-17 06:58 UTC by Kees de Kooter
Modified: 2011-06-20 15:29 UTC (History)
1 user (show)


Note You need to log in before you can comment on or make changes to this bug.
Description Kees de Kooter 2009-12-17 06:58:46 UTC
When Sheet.autoSizeColumn is performed on a sheet with fonts like Verdana and Calibri the resulting columns widths are too narrow.
Comment 1 Yegor Kozlov 2009-12-22 00:13:05 UTC
Can you attach the source file and the file passed through Sheet.autoSizeColumn?

Make sure that Verdana and Calibri are available on the machine where Sheet.autoSizeColumn is called. The result can be 'off' if fonts are missing.

Comment 2 Kees de Kooter 2009-12-22 05:07:55 UTC
The fonts are available on the machine.

Unfortunately my customer will not allow me to share the files with you.

Here is the code doing the autosize:

	private void setColumnWidths(Sheet sheet) {
		sheet.setColumnWidth(0, 13 * 256);
		for (int i = 1; i <= lastColumnIndex; i++) {
Comment 3 Bill Stackhouse 2010-03-15 18:47:49 UTC
After setting all text cells in a column with mostly long upper case strings, calling setAutoSize usually sets it to a width that is too narrow.

It appears that HSSFSheet.setAutoSize uses '0' as the default character width. The Verdana Plain 10 font on the Macintosh has a width of 6. 'W' and 'm' both are 10 and '%' is the widest at 11.

Shouldn't this be changed from '0' to '%' or loop through the FontMetrics of the font to find the widest character?

BTW I do see it setting the width more correctly using Helvetica Plan 10, but not with Arial Plain 10 which is the default font.

Sample source

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class AutoSize {

    public static void main(String[] args) {

        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFCellStyle style = workBook.createCellStyle();
        final HSSFFont font = workBook.createFont();
        font.setFontHeightInPoints((short) 10);
        HSSFSheet sheet = workBook.createSheet();
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell;

        cell = row.createCell(0);

        cell = row.createCell(1);


        try {
            FileOutputStream stream = new FileOutputStream(new File(System.getProperty("user.home"),
        } catch (IOException error) {

Comment 4 Yegor Kozlov 2011-06-20 15:29:42 UTC
Sheet.autoSizeColumn has been improved in recent releases. Please try POI-3.8-beta3 or the latest build from trunk.

I tested for autoSizeColumn  for "Calibri", "Arial", "Verdana", "Consolas" and "Times New Roman" and it works pretty close to Excel.