XLSX cell width - in twips?

[David Thielen]

Hi;

I am trying to convert from the XLSX col width to twips and back. I am using
the example from the Open XML spec. To go from the width to twips I am using:

float width = ((((((float) elem.getCol(colOn).getWidth() / 1440f) * 96f) /
7f) * 256f) - (int) (128f/7f)) / 256f;

Where getWidth() is the value from the XLSX file and 7 is the pixel width of
the default font.

For a column width of 15.123 the above gives me 1672 twips. But a screenshot
of the spreadsheet is 108 pixels wide. At 96 dpi that's 1620 twips. So
there's a difference of 52 twips.

And then to go from twips to width I do the inverse:
float width = ((((((float) elem.getCol(colOn).getWidth() / 1440f) * 96f) /
7f) * 256f) - (int) (128f/7f)) / 256f;

But that gets me 15.853 which is a bit different from the original 15.123.

What am I getting wrong on this?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jialiang Ge [MSFT]]

Hello Dave,

Would you let me know the unit of your 'width'? Why did you add "/ 7f) *
256f) - (int) (128f/7f)) / 256f"?

If the dpi is 96, I am sure that: 1 twip = 1/1440 inch = 96/1440 pixel
We may also dynamically determine the TwipsPerPixelX and TwipsPerPixelY
according to the current resolution with the sample codes in
http://support.microsoft.com/kb/94927/en-us.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

I am following from "Office Open XML - Part 4" The <col width="9.140625"/>
element:

Column width measured as the number of characters of the maximum digit width
of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There
are 4 pixels of margin padding (two on each side), plus 1 pixel padding for
the gridlines.

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

Using the Calibri font as an example, the maximum digit width of 11 point
font size is 7 pixels (at 96 dpi). In fact, each digit is the same width for
this font. Therefore if the cell width is 8 characters wide, the value of
this attribute shall be Truncate([8*7+5]/7*256)/256 = 8.7109375.

To translate the value of width in the file into the column width value at
runtime (expressed in terms of pixels), use this calculation:

=Truncate(((256 * {width} + Truncate(128/{Maximum Digit
Width}))/256)*{Maximum Digit Width})

Using the same example as above, the calculation would be
Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels

To translate from pixels to character width, use this calculation:
=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100

Using the example above, the calculation would be
Truncate((61-5)/7*100+0.5)/100 = 8 characters.

Note: when wide borders are applied, part of the left/right border shall
overlap with the 2 pixel padding on each side. Wide borders do not affect the
width calculation of the column.

Note: When the sheet is in the mode to view formulas instead of values, the
pixel width of the column is doubled.

The possible values for this attribute are defined by the XML Schema double
datatype.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jialiang Ge [MSFT]]

Hello Dave,

According to the formula
pixel =Truncate(((256 * {width} + Truncate(128/{Maximum Digit
Width}))/256)*{Maximum Digit Width})

If the width is 15.123, Maximum Digit Width is 7, then the pixel should be:

pixcel = Truncate(((256 * 15.123 + Truncate(128 / 7))/256)*7) =
Truncate(((256 * 15.123 + 18)/256)*7) = Truncate(((3871.488 + 18)/256)*7)
= Truncate(15.1933125 * 7) = Truncate(106.3531875) = 106pixel.

According to your screenshot, you said the cell is 108pixel wide.

Therefore, the difference is 2 pixel.

Is it possible that you cound the border (1 on each side, so 2 pixels) in
when you measure the width from screenshort?

Besides, there seems to be something wrong with your formula
float width = ((((((float) elem.getCol(colOn).getWidth() / 1440f) * 96f) /
7f) * 256f) - (int) (128f/7f)) / 256f;
It does not reverse the truncate correctly. So you get 1672twips from
15.123, which is 111pixel.

Regards,
Jialiang Ge

 

[David Thielen]

2 pixels could be a measurement problem - or not understanding how the cell
border counts.

Where you say "Besides, there seems to be something wrong with your formula"
- what do you think is wrong with it?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jialiang Ge [MSFT]]

Hello Dave,

Your formula is:
width = ((({pixel} / 7f) * 256f) - (int) (128f/7f)) / 256f

The formula in the spec is:
pixel =(int)(((256 * {width} + (int)(128/7))/256)*7)

The problem is:

If C=(int)(A*7), we cannot say A=C/7. For instance, 8=(int)(1.2*7), but 1.2
!= 8/7

The formula in the spec can be regarded as pixel = (int)(A*7), where A
means ((256 * {width} + (int)(128/7))/256), however, when you reverse the
formula, you directly set A = pixel / 7. Therefore, it should be incorrect.

The correct method to convert pixel to width should be:
1. use the formula =Truncate(({pixels}-5)/{Maximum Digit Width} *
100+0.5)/100 to convert pixel to character number.
2. use the formula width = Truncate([{Number of Characters} * {Maximum
Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 to convert
the character number to width.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hello Dave,

If you need further assistance on this issue, please feel free to let me
know.

Happy New Year!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

I think we have it now - thanks
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm