Bug 48408

Summary: HSSFWorkbook setColumnWidth not working correctly
Product: POI Reporter: benicely
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal CC: najh.ru
Priority: P2    
Version: 3.5-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

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.