Bug 45105 - Column width not correctly mapped to xls file (office 2003), while defaultColumnWidth is
Summary: Column width not correctly mapped to xls file (office 2003), while defaultCol...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-05-30 11:26 UTC by Dominic Adatia
Modified: 2008-06-09 02:11 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Dominic Adatia 2008-05-30 11:26:10 UTC
I would like confirmation that this is not a problem due to my local excel setup-

When I generate a blank workbook and look inside, the columns are all 64 pixels wide - this corresponds to the defaultColumnWidth value in a HSSFSheet.

A call to getColumnWidth((short)1) results in 2048, which is as expected (2048/32 = 64) 

If, however, I do "setColumnWidth((short)1, (short)2048)", this results in column(1) being 56 pixels wide.

I tested a few widths, and each time, the pixel width in the spreadsheet is = 
(width/32) * 7/8 (an eighth goes missing!)


setColumnWidth(1,2048) -> Column is 56 pixels (expected 64)
setColumnWidth(1, 4096) -> Column is 112 pixels (expected 128)
Comment 1 Yegor Kozlov 2008-06-09 02:11:33 UTC
This is how Excel works.
The width of 1 pixel measured in Excel units (1/256th of a character) is different for columns width default and modified width.

For columns with the default width 1px=32, so 2048/32=64px
For columns with modified width 1px=36.56  and 2048/36.56=56px 

The constant 36.56 was found empirically. The Excel spec says nothing about it. 

Here is a mock up of a method which returns column width in pixels. 

    public int getColumnWidthInPixels(HSSFSheet sheet, short column){

        float px_default = 32.00f;  //width of 1px in columns with the default width
        float px_modified = 36.56f; //width of 1px in columns with modifed width

        //column width in units of 1/256th of a character
        int cw = sheet.getColumnWidth(column);

        //default column width in units of 1/256th of a character
        int dw = sheet.getDefaultColumnWidth()*256;

        float px = cw/(cw == dw ? px_default : px_modified);

        return Math.round(px);