I searched high and low today looking for the answer to what I thought was a simple question: if I know the width of a column in pixels, how do I convert that to a Double for use with the Column.Width property of a column in OpenXML?
I found this article by Vincent that I thought would be useful. The problem, though, is that the Graphics.MeasureString method he mentioned returns a length that includes padding (I kept on getting about 12.5px for Calibri 11, which I know from the Column description should be 7px). I couldn't get the Graphics.MeasureCharacterRanges to give me anything at all useful (as recommended at this link by one of the commentator's on Vincent's post).
Finally I stumbled upon a useful comment by AtmaWeapon. His comment to use a different overload of Graphics.MeasureString did not work for me; however, using TextRenderer.MeasureText was a brilliant suggestion! Finally something that works!
Here's my final code (including the conversion I simplified from the Column description):
Public Function ConvertPxToXLSWidth(ByVal dblPxWidth As Double, ByVal strFontName As String, ByVal dblFontSize As Double) As Double
'thanks to http://polymathprogrammer.com/2010/01/18/calculating-column-widths-in-excel-open-xml/ for some initial ideas
Dim dblWidthOfZero As Double = 0.0
Dim dblTruncWidth As Double = 0.0
Try
Dim drawfont As System.Drawing.Font = New System.Drawing.Font(strFontName, dblFontSize)
'need a graphics object to measure the font size
Dim g As Graphics = Graphics.FromImage(New Bitmap(200, 200))
g.PageUnit = GraphicsUnit.Pixel
dblWidthOfZero = TextRenderer.MeasureText(g, "0", drawfont, New Size(Integer.MaxValue, Integer.MaxValue), TextFormatFlags.NoPadding).Width
g.Dispose()
' Excel help says: width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
'the numerator is just to get the total number of pixels
'so, since I know the total number of pixels, my calculation becomes:
'Truncate({pixel width}/{Maximum Digit Width}/256) * 256
dblTruncWidth = Math.Truncate(dblPxWidth / dblWidthOfZero * 256) / 256
Catch ex As Exception
dblTruncWidth = 8.43 'default width for most Excel installations
End Try
Return dblTruncWidth
End Function
No comments:
Post a Comment