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.

Yegor

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++) {
			sheet.autoSizeColumn(i);
		}
	}

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);
        font.setFontName("Verdana");
        style.setFont(font);
        HSSFSheet sheet = workBook.createSheet();
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell;

        cell = row.createCell(0);
        cell.setCellValue("ABCDEFGHIJKLMNOPQRSTUVWXYZ");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("1234");

        sheet.autoSizeColumn(0);

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

}

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. 

Yegor