Excel column width question again

[David Thielen]

Hi;

We're back on this. Because of how XLSX stores the image size, giving the
cell/offset of the start and end, we have to be able to convert the cell
width to the exact number of twips (or better EMUs) it is, including the
border, padding, etc.

So my questions are:

1) How do I determine what font and point size to use?

2) Does it always use the character 0 at that width?

3) What is the correct formula to get the exact full width?

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

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

 

[Jialiang Ge [MSFT]]

Hello Dave,

1) How do I determine what font and point size to use?
The definition of 'Width' tells us that column width is 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*. (Please notice the bold part). We
do not need to determine what font and point size to use. It is decided by
the Excel setting of 'Normal Font', namely, normal style's font. When Excel
renders width to pixel in runtime, it will read the setting for 'Normal
Font' and use the formula '=Truncate(((256 * {width} +
Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})' to
calculate the pixel. 'Normal Font' can be set at Format->Styles¡­->Choose
'Normal' in the style name list.

2) Does it always use the character 0 at that width?
No, it uses the maximum character width of the normal font among 0,1,2,¡­,9.

3) What is the correct formula to get the exact full width?
As we discussed in the post "XLSX cell width - in twips?",
The correct method to convert width to pixel is:
Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum Digit
Width}))/256)*{Maximum Digit Width})
The correct method to convert pixel to width is:
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.

If you have any other questions or concerns, please feel free to let me
know.

Happy New Year!

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]

Hi;

Our problem is when parsing an XLSX file, and we get a drawing, who's size
is set bu the from and to anchor, we need to know the font at that time so we
can determine the drawing (bitmap) width. So we need to know the normal font
name and size for that file.

Also, the formula below is off by a couple of pixels. Over 5 columns (width
of a test bitmap) it gets the size off by 5%. I think the formula below is
close, but not exact.

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

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

Hello Dave,

1) How do I determine what font and point size to use?
The definition of 'Width' tells us that column width is 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*. (Please notice the bold part). We
do not need to determine what font and point size to use. It is decided by
the Excel setting of 'Normal Font', namely, normal style's font. When Excel
renders width to pixel in runtime, it will read the setting for 'Normal
Font' and use the formula '=Truncate(((256 * {width} +
Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})' to
calculate the pixel. 'Normal Font' can be set at Format->Styles¡­->Choose
'Normal' in the style name list.

2) Does it always use the character 0 at that width?
No, it uses the maximum character width of the normal font among 0,1,2,¡­,9.

3) What is the correct formula to get the exact full width?
As we discussed in the post "XLSX cell width - in twips?",
The correct method to convert width to pixel is:
Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum Digit
Width}))/256)*{Maximum Digit Width})
The correct method to convert pixel to width is:
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.

If you have any other questions or concerns, please feel free to let me
know.

Happy New Year!

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.

 

[Jialiang Ge [MSFT]]

Hello Dave,

So we need to know the normal font name and
size for that file.

To get the Normal Font and Size, we can call
ActiveWorkbook.Styles("Normal").Font.Name and
ActiveWorkbook.Styles("Normal").Font.Size. In order to decide the maximum
character with, I think we can use the MeasureString method:
http://msdn2.microsoft.com/en-us/library/system.drawing.graphics.measurestri
ng.aspx

Also, the formula below is off by a couple of pixels

Because your measure is over 5 columns, have you deducted the border width
between the columns? As far as I know, the column width does not count the
border in.
However, I also find a couple of pixels off when I measure the width of one
column. Below is my test steps:

1. Open Excel 2003, Format->Styles, and set the normal font as Calibri 11,
so the maximum digit width is 7pixel (at 96dpi).
2. Run the macro: Columns("A:A").ColumnWidth = 8.7109375 to set the column
A width as 8.7109375.
3. Print the screen and measure the column A's width in windows Paint.

According to the spec, it should be 61pixel
(Truncate(((256*8.7109375+Truncate(128/7))/256)*7)) width. However, it
turns out to be 64pixel when I measure it in the screen print. I have
submitted the issue to the product team and am waiting for their response.
I will get back to you as soon as possible.

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]

Hi;

We have to get the Normal font and font size from the xlsx file because our
engine does not use Excel - it runs by itself.

It looks like the way it works is in styles.xml you need to do the following:
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
means go to the first cellXfs:

<cellXfs count="8">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
means go to the first font:

<fonts count="5">
<font>
<sz val="13"/>
<name val="Times New Roman"/>
</font>
Means it is 13pt Times New Roman.

This appears to work based on testing - is this correct?

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

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

Hi;

Our problem is when parsing an XLSX file, and we get a drawing, who's size
is set bu the from and to anchor, we need to know the font at that time so we
can determine the drawing (bitmap) width. So we need to know the normal font
name and size for that file.

Also, the formula below is off by a couple of pixels. Over 5 columns (width
of a test bitmap) it gets the size off by 5%. I think the formula below is
close, but not exact.

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

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

Hello Dave,

1) How do I determine what font and point size to use?
The definition of 'Width' tells us that column width is 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*. (Please notice the bold part). We
do not need to determine what font and point size to use. It is decided by
the Excel setting of 'Normal Font', namely, normal style's font. When Excel
renders width to pixel in runtime, it will read the setting for 'Normal
Font' and use the formula '=Truncate(((256 * {width} +
Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})' to
calculate the pixel. 'Normal Font' can be set at Format->Styles¡­->Choose
'Normal' in the style name list.

2) Does it always use the character 0 at that width?
No, it uses the maximum character width of the normal font among 0,1,2,¡­,9.

3) What is the correct formula to get the exact full width?
As we discussed in the post "XLSX cell width - in twips?",
The correct method to convert width to pixel is:
Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum Digit
Width}))/256)*{Maximum Digit Width})
The correct method to convert pixel to width is:
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.

If you have any other questions or concerns, please feel free to let me
know.

Happy New Year!

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.

 

[Jialiang Ge [MSFT]]

Hello Dave,

Yes, you are right.

xfId="0" in <cellStyle name="Normal" xfId="0" builtinId="0"/> tells Excel
to use the first xf.
fontId="0" in <xf numFmtId="0" fontId="0" fillId="0" borderId="0"
xfId="0"/> tells to use the first font.

Therefore, it is
<fonts count="5"><font><sz val="13"/><name val="Times New Roman"/></font>

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,

I suddenly realize that I made a mistake in the previous reply. the
ColumnWidth property does not equal the with attribute in xlsx's xml. In
the last test, I set the Columns("A:A").ColumnWidth = 8.7109375, however,
it turns out to be 9.42578125 in the sheet1.xml. When I change the width to
be 8.7109375 directly in sheet1.xml, open the workbook, and measure the
column width in the screen print again, it shows the same value as the spec
illustrates.

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]

This is more complex for us because we do this in our server code, so Excel
is not running and the DPI on the server can be different from the DPI on the
user's computer.

So another question here - a character is not exactly 7 pixels wide when we
get the font metrics. It is say 7.13452 or 7.9813. Do we truncate, round, or
keep the full value?

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

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

 

[David Thielen]

We're still slightly off. Please note that we save in twips but I always
convert to pixels at 96 DPI.

Normal font is Calibri 11 pt.

The value charWid is the widht of '0' in twips:
float wid = (parser.charWid / 1440f) * 96f;
// 3.3.1.12 in OpenXML width_pixels=Truncate(((256 * {width} +
Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
int twips = (int) ((((256f * Float.parseFloat(attrs.getValue("width")) +
(int)(128f / wid)) / 256f) * wid) * (1440f / 96f));

results here are:
attrs.getValue("width") = 11.5703125
wid = 7.4335938
twips = 1297

So we have a column width of 11.5703125 = 1297 twips.

Then to convert back we have:
// because of the truncate we need to go from twips to pixels at 96 DPI
float wid = (charWid / 1440f) * 96f;
// 3.3.1.12 in OpenXML num_chars=Truncate(({pixels}-5)/{Maximum Digit
Width} * 100+0.5)/100
// column_width = Truncate([{num_chars} * {Maximum Digit Width} + {5 pixel
padding}]/{Maximum Digit Width}*256)/256
float pixels = (elem.getCol(colOn).getWidth() * 96f) / 1440f;
float numChars = (float) Math.floor((pixels - 5) / wid * 100f + 0.5f) / 100f;
float width = (float)Math.floor((numChars * wid + 5f) / wid * 256f)/256f;
wid = 7.4335938
elem.getCol(colOn).getWidth() = 1297
pixels = 86.46667
numChars = 10.96
width = 11.628906

So a width of 1297 twips == a column width of 11.628906

So it goes 11.5703125 cw == 1297 twips == 11.628906 cw. What am I missing in
this?

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

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

 

[Jialiang Ge [MSFT]]

Hello,

How did you get the 7.13452, 7.9813, etc? Would you paste the complete code
here?

Besides, if you were using MeasureString method to measure the character
width, I am sorry that I made a mistake again. The result MeasureString is
not the accurate font metrics' width. It counts in some spaces around the
character. We should use MeasureCharacterRanges method to get the accurate
width:
http://msdn2.microsoft.com/en-us/library/system.drawing.graphics.measurechar
acterranges.aspx

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,

1. In your test, wid = 7.4335938. I think it should be 7 if you use
MeasureCharacterRanges. (See my another reply today).
2. I think you should move *(1440f/96f) outside the truncate. That is to
say:
We get the pixel with (int) ((((256f *
Float.parseFloat(attrs.getValue("width")) + (int)(128f / wid)) / 256f) *
wid)) first, then convert it to twips.
int twips = (int)(pixel * (1440f / 96f));

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]

Hi;

I wrote a small C# program to show this - it's at
http://www.windwardreports.com/temp/ExcelColumnWidth.zip - I think everything
I have in it is correct and a round trip does not return the original result.

The character width can be calculated different ways but as long as I use
the same value in all of the equations, it should round-trip to return the
same result.

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

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

 

[Jialiang Ge [MSFT]]

Hello Dave,

I have tried your sample code. I think the deviation is introduced by the
following factors.

1. Deviation cannot be avoided. As I once said: if C=(int)(A/B), we cannot
say A=C*B. And there is no method to get the original A from C and B
accurately. The ways that we have tried are reducing the errors to the
largest extent.

2. We should use double, instead of float, to get more accurate calculation
results.

3. The calculated font width is incorrect. The width of Arial 10 should be
6pixels, however, your calculation result shows 7.41 pixels
((columnWidthInTwips * 96f) / 1440f).
Here is a simple way to prove that 7.41pixel for Arial 10 is incorrect:
According to the spec, Calibri 11 is 7 pixels. You may open your
winword.exe, and type two '0'. One is formatted as Calibri 11, another is
formatted as Arial 10. Then you will find that the width of the '0'
Calibri11 is much larger than that of Arial 10 visually. Therefore, the
width of Arial 10 to be 7.41pixels is incorrect.

In addition, the 'Digit width' in the spec is defined as an integer. Below
is the method that I use to determine digit width.

// Set up string.
string measureString = "0";
Font stringFont = new Font("Arial", 16.0F);
SizeF faa = e.Graphics.MeasureString(measureString, stringFont);
float x = 50.0F;
float y = 50.0F;
float width = 35.0F;
float height = 200.0F;
RectangleF layoutRect = new RectangleF(x, y, width, height);

// Set string format.
CharacterRange[] characterRanges = { new CharacterRange(0, 1) };
StringFormat stringFormat = new StringFormat();
stringFormat.SetMeasurableCharacterRanges(characterRanges);

Region[] result = e.Graphics.MeasureCharacterRanges(measureString,
stringFont, layoutRect, stringFormat);
float charWidth = result[0].GetBounds(e.Graphics).Width - 2; // minus 2
bound borders

4. The conversion between pixel and twip.
"* (1440D / 96D)" should be moved out of the truncate:
double pixels = (double)Math.Truncate(((256f * excelColumnWidth +
(int)(128D / wid)) / 256D) * wid);
double columnWidthInTwips = (double)(pixels * (1440f / 96f));

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]]

Hi Dave,

If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.

Have a great day!

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]

Hi;

I think you're right, the number just doesn't round-trip perfectly. Oh well,
life is not perfect...

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

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