将 .NET 像素转换为 OpenXML 格式的 Excel 宽度的公式 [英] Formula to convert .NET pixels to Excel width in OpenXML format

问题描述

我花了很多时间试图确定使用 OpenXML 格式将 .NET 像素转换为 Excel 列宽的公式.我正在使用 EPPlus 生成 xmls 文档.我正在尝试确定要自动调整大小的列的宽度.我通过测量字符串来获取像素数，然后尝试将其转换为 OpenXML 的列宽，我认为这是以字符为单位测量的.

我已经阅读了微软关于如何转换它的文档并尝试了他们建议的公式，但它甚至不接近准确:

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

这是我使用他们公式的代码:

 public double GetCharacterWidth(string Text, Font f, Graphics g){float MaxDigitWidth = g.MeasureString("0", f).Width;浮动像素 = g.MeasureString(Text, f).Width;返回 ((Pixels - 5)/MaxDigitWidth * 100 + 0.5)/100;}

有什么想法吗?

解决方案

首先我们需要确定 Excel 如何进行转换(因为我们在 Excel 应用程序中手动更改大小):

//使用7d提升为double，否则int会截断.ExcelWidth = (Pixels - 12)/7d + 1;//从像素到英寸.

警告:这些公式不适用于 0(零)宽度或像素.
对于这些情况，请使用if"语句来检查是否为零并返回零.

它也让我陷入了困境.我没有尝试计算每个宽度的像素数，而是查看了每个宽度整数之间的像素数差异，发现它始终为 7.例外是 0 到 1 宽度；其中 Width 1.00 有 12 个像素.从那里我能够想出上面的公式，很容易.

现在这些数字在 Excel 世界中仍然存在，但如果您直接在 OpenXml 文档中设置列宽，情况就略有不同了.原因如下:在您链接到的文档中，这是关于 5 个像素的说明:

<块引用>

有 4 个像素的边距填充(每边两个)，加上网格线的 1 个像素填充.

现在这意味着 Excel 假设您已将这 5 个像素计入您提供的宽度.当您在 Excel 中打开文档并调整列大小时，您会看到宽度比您设置的要小 5 个像素.

要对此进行调整，您可以按如下方式更新公式:

//使用7d提升为double，否则int会截断.OpenXmlWidth = (Pixels - 12 + 5)/7d + 1;//从像素到英寸.

这回答了您的问题的标题:
将 .NET 像素转换为 OpenXML 格式的 Excel 宽度的公式"

如果您想知道如何计算列宽的近似值以根据单个单元格的内容(和字体)自动适应列，那么这是一个完全不同的问题.您提供的微软链接提供的公式将不起作用.在一个单元格中输入 10 个句点，在另一列中输入 10 个大写字母 M.您将看到自动适应分别为您提供 41 像素和 129 像素.ms 提供的公式没有考虑单个字符的宽度.换句话说;他们让你疯狂追逐.

自动调整列的唯一方法是扫描列中的每一行并根据使用的字符和字体计算文本的宽度.你会使用我发现的类似的东西 这里.然后取其最大值并用 5 填充.在 Web 环境中处理电子表格时，我会避免这种方法，因为您可能会导出具有数十列的数十万行 - 您明白了.最好的方法是为您的列设置最佳猜测宽度，并培训您的用户如何从 excel 中自动适应.

真诚的，
普通人"


编辑 - 2011 年 10 月 26 日:

因为我觉得很慷慨，这里有一个示例，说明如何为您的列获得近似宽度.我宁愿避免对列中的所有行执行此操作，因此让我们将宽度(默认情况下)基于标题单元格中的值.下面是如何使用我之前链接的示例来执行此操作.注意:这些是近似值，但足够接近.

使用 System.Drawing;using System.Windows.Forms;//添加引用.首先在网络服务器上测试.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;//Excel 用 5 填充.

警告:如果您在使用 OpenXml 的同一位置使用此代码，那么您可能需要删除 System.Drawing 的使用"并完全限定字体"和字体样式"" 作为 System.Drawing.Font 和 System.Drawing.FontStyle.否则您的编译器可能会将这些类误认为属于 DocumentFormat.OpenXml.Spreadsheet .

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?

解决方案

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 .

这篇关于将 .NET 像素转换为 OpenXML 格式的 Excel 宽度的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！