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

ArcPad 10.2 Fun

I know I haven't posted in a LONG while, but I've encountered a problem with ArcPad 10.2 that I haven't found ANYTHING on while Googling, and since this version is so new, I thought it would be good to get this information out ASAP.

First, let me set up how this problem manifested.  I have an ArcPad 'application' (read: AXF file paired with Applet) that handles new and existing feature creation to display some pre-filled information on an EDITFORM.  This information involves retrieving the actual new/existing feature shape.

Previously (i.e., ArcPad 10.0), I identified the selected feature through the Map.SelectionBookmark property, and applied that bookmark to the Map.SelectionLayer.Records (the SelectionLayer is my layer of interest).  (On a side note...I use the SelectionLayer instead of the EditLayer because if the user has two layers active for editing - e.g., a point and a line layer - ArcPad doesn't seem to be able to tell that the current open form is for one or the other.)  This worked just fine regardless of whether this was a new feature or an existing feature.  A new feature of course does not have a record yet; however, in the interests of minimizing subroutines, I dealt with that case right before retrieving the value I needed from the feature (pulling it from the EDITFORM instead), and the code execution still passed through the record selection.  Everything worked just fine.

Upgrade to 10.2. 

All of the sudden my EDITFORM was cleared of some previously filled fields when I created a new feature.  I had populated the current date and user in a DateTime control and a ComboBox control.  These were wiped as soon as I attempted to set the bookmark of the line layer's recordset.  Mind you, I hadn't DONE anything with that information yet - it was just the act of setting the recordset's bookmark to an invalid number (-1, which is what Map.SelectionBookmark is for a new feature).  My theory is that this somehow disconnected the EDITFORM from the new feature (because the bookmark of the recordset associated with the EDITFORM was set to something invalid), but I don't have the time to test it.  The odd thing is that I can test the value of the controls at any point and it always comes out correctly - it just won't actually display.

SO, the SOLUTION!

To fix this, before setting the recordset bookmark in my function designed for that purpose, I test to see whether Map.SelectionBookmark is -1 (the value for a new feature).  If it is, I do not attempt to retrieve the recordset and simply pass a value of Nothing back for my recordset.  My main code then handles the Nothing value for the recordset by pulling from the EDITFORM's shape field instead.