Bug 48408 - HSSFWorkbook setColumnWidth not working correctly
Summary: HSSFWorkbook setColumnWidth not working correctly
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-12-17 07:59 UTC by benicely
Modified: 2011-06-20 15:27 UTC (History)
1 user (show)


Note You need to log in before you can comment on or make changes to this bug.
Description benicely 2009-12-17 07:59:51 UTC
Reading the API, it appears that if I want a column width of 8 I would multiply that by 256 which gives me the result of 2048.

When I create my Excel spreadsheet, I get a column width of 7.29 instead.

It should be noted I am opening my document in Excel 2007.
Comment 1 Yegor Kozlov 2011-06-20 15:27:46 UTC
The value passed to  setColumnWidth  is written in the low-level format structures. This method sets the full column width which includes 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines. 

Excel shows you the actual amount of visible characters (margins and paddings are not included) and this explains why the value in Excel is smaller: 7.29 vs 8. 

The spec for the .xlsx format describes how Excel calculates the width in characters:

width = Truncate([{Number of Visible Characters} *
     {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256;

where {Maximum Digit Width} is the maximum digit width of the
numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.

Using the Arial font as an example, the maximum digit width of 10 point font size is 7 pixels (at 96 dpi). Therefore if the cell
width is 8 characters wide, then the actual value of visible characters (the value shown in Excel) is derived from the following equation:

 Truncate([numChars*7+5]/7*256)/256 = 8;

which gives 7.29.

I updated javadocs to reflect this.