Monday, October 14, 2013

OpenXML: Get Column.Width Property for a Spreadsheet

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