Formula to convert .NET pixels to Excel width in OpenXML format

Question

I have spent many hours trying to determine a formula to convert .NET pixels to an Excel column width using the OpenXML format. I am using EPPlus to generate xmls documents. I'm trying to determine the width for a column that is to be Auto-sized. I am getting the number of pixels by measuring the string and then trying to convert that to the column width for OpenXML, which is measured in characters I think.

I've read Microsoft's documentation on how to convert it and tried the formula they suggest, but it isn't even close to being accurate:

http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.aspx

Here's my code using their formula:

    public double GetCharacterWidth(string Text, Font f, Graphics g)
    {
        float MaxDigitWidth = g.MeasureString("0", f).Width;
        float Pixels = g.MeasureString(Text, f).Width;

        return ((Pixels - 5) / MaxDigitWidth * 100 + 0.5) / 100;
    }

Any ideas?

Answer 1

First we need to establish how Excel does the conversion (as we manually change the size within the Excel application):

//Use 7d to promote to double, otherwise int will truncate.
ExcelWidth = (Pixels - 12) / 7d +  1;//From pixels to inches.

Caveat: The formulas do not work for 0 (zero) Width or Pixels.
For those scenarios, use an "if" statement to check if zero and return zero.

It threw me for a loop too. Instead of trying to figure out Pixels per Width, I looked at the difference in Pixels between each Width whole number and found it was always 7. The exception was 0 to 1 Width; where there were 12 Pixels for Width 1.00 . From there I was able to come up with the formulas above, easy-peasy.

Now those numbers hold up in the world of Excel, but if you're setting column widths directly in the OpenXml document, it's a slightly different story. Here's why: In the documentation you link to is says this about the 5 pixels:

There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.

Now all that means is Excel is assuming you've factored in these 5 pixels into the widths you are providing. When you open up your document in excel and resize the columns, you will see that the width is 5 pixels less than what you set it to.

To adjust for this you can update the formulas as follows:

//Use 7d to promote to double, otherwise int will truncate.
OpenXmlWidth = (Pixels - 12 + 5) / 7d + 1;//From pixels to inches.

This answers the title of your question:
"Formula to convert .NET pixels to Excel width in OpenXML format"

If you want to know how to figure an approximation of the column width to auto-fit a column based on the contents (and font) of a single cell then that's an entirely different question. The formulas supplied by the microsoft link you provided will not work. Type in 10 periods in one cell and 10 capital-M's in another column. You will see that auto-fit gives you 41 pixels and 129 pixels, respectively. The formulas provided by ms do not take into account the width of individual characters. In other words; they sent you on a wild goose chase.

The only way to Auto-Fit a column is to scan through every row in the column and calculate the width of the text based on the characters and font used. You would use something like what I found here. Then take the max value of that and pad with 5. I would avoid this approach when processing spreadsheets in a web environment because you could be exporting hundreds of thousands of rows with tens of columns - you get the idea. The best approach is to set a best-guess width for your columns and train your users on how to auto-fit from excel.

Sincerely,
"The Common Man"


Edit - 10/26/2011:

Because I'm feeling generous, here's an example of how to get an approximate width for your column. I'd prefer to avoid doing this for all rows in a column, so let's just base our width (by default) on the value in your header cell. Below is how you could do this using the example I linked to earlier. Note: These are approximations, but close enough.

using System.Drawing;
using System.Windows.Forms;//Add Reference.  Test on webserver first.
Font font = new Font("Calibri", 11.0f, FontStyle.Regular);
string header  = "Hello There World!";
int pxBaseline = TextRenderer.MeasureText("__", font).Width;
int pxHeader   = TextRenderer.MeasureText("_" + header + "_"), font).Width;
int pxColumnWidth = pxHeader - pxBaseline + 5;//Pad with 5 for Excel.

Caveat: If you use this code in the same place you're using OpenXml, then you may need to remove the "using" for System.Drawing and fully qualify "Font" and "FontStyle" as System.Drawing.Font and System.Drawing.FontStyle. Otherwise your compiler may mistake these classes as belonging to DocumentFormat.OpenXml.Spreadsheet .