spreadsheetgear - 如何获得SpreadsheetGear.IRange.AutoFit()为每列赋予正确的宽度？

Question

我使用Spreadsheetgear生成Excel工作簿。

我的工作簿非常简​​单:

我在

单元格中插入字符串

第一行是粗体

我在标题周围和列之间绘制边框。

在过程结束时，我调用 myRange.Columns.AutoFit()，然后保存工作簿。

当我使用Excel打开它时，自动拟合效果很好，但是在每列中仍然缺少大约1.2宽度单位。

这是 AutoFit()实现中的错误还是我错过了一些东西？

Answer 1

大约6个月前，我在构建实现程序时，实际上向SpreadsheetGear寻求支持。我提到b/c他们的AutoFit()并不完全正确，我必须在自动调整大小后为每列添加一个“软键因子”。
支持人员的回答实质上是与Excel的已知差异，他们没有将其归类为错误。这是他们不得不对我说的话:

The reason for auto-fit columns being slightly off between SpreadsheetGear and Excel is that the font metrics provided to us by .NET and GDI+ differ from those used in Excel. Because column dimensions are tied to fonts used in the workbook, and therefore font metrics, attempting to match Excel exactly just isn’t possible, unfortunately. We do our best to match Excel, but it is impossible to exactly match them in all cases.

Interestingly, Excel’s actually quite inconsistent with their font metric calculations; try using SpreadsheetGear to create a workbook with long runs of text, perhaps adding 40-50 “a” characters to a cell. Now save that workbook to disk and open it in Excel 2007 or 2010 use their zoom slider in the lower-right-hand corner to zoom in and out at different points and note that the text shifts around relative to the column width quite a bit. SpreadsheetGear is much more consistent with this type of scenario.

Regardless, your method of adding a little “fudge factor” to the column widths is the best workaround we can suggest. It is “hackish” but there’s simply nothing we can do to improve it since you’re dealing with two different environments and an inconsistent metrics in Excel itself.

这是我用来根据需要加宽列的“忽悠因素”:

ws.UsedRange.Columns.AutoFit()
For col As Integer = 0 To ws.UsedRange.ColumnCount - 1
    ws.Cells(1, col).ColumnWidth *= 1.
Next

在C#中

ws.UsedRange.Columns.AutoFit()
for (int col = 0; col < ws.UsedRange.ColumnCount; col++)
    ws.Cells[1, col].ColumnWidth *= 1.15;