How to get SpreadsheetGear.IRange.AutoFit() give the right width to each column?

Question

I Use Spreadsheetgear to generate an Excel Workbook.

My Workbook is very simple:

• I insert Strings in cells
• The first line is Bold
• I draw borders around the headers and between the columns.

At the end of the process, i call myRange.Columns.AutoFit(), then I save the workbook.

When I open it with Excel, the autofit is almost good, but it still miss around 1.2 width units in each columns.

Is this a bug in AutoFit() implementation or am I missing something ?

Answer 1

I actually asked SpreadsheetGear support about this as I was building my implementation about 6 months ago. I mentioned that b/c their AutoFit() was not fitting exactly right, that I was having to add a "fudge factor" to each column after autosizing. Support's response was essentially that it was a known discrepancy with Excel, that they dont classify as a bug. Here's what they had to say to me:

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.

And here's the "fudge factor" that I used to widen the columns as needed:

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

In C#

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

Answer 2

Building on the answer from @Stephen ... Excel has a column width limit of 255, so I did this:

    private static void AutoFitColumns(SpreadsheetGear.IWorksheet worksheet)
    {
        worksheet.UsedRange.Columns.AutoFit();

        const int ExcelMaximumColumnWidth = 255;
        const double OneHundredFifteenPercent = 1.15;

        for (var i = 0; i < worksheet.UsedRange.ColumnCount; i++)
        {
            var cell = worksheet.Cells[1, i];
            var width = cell.ColumnWidth * OneHundredFifteenPercent;

            if (width > ExcelMaximumColumnWidth)
                width = ExcelMaximumColumnWidth;

            cell.ColumnWidth = width;
        }
    }

Answer 3

You may want to upgrade SpreadsheetGear to the latest version. I had installed SpreadsheetGear 2008 and I've had many problems with the AutoFit(). Read the changelog for SpreadsheetGear2010 http://www.spreadsheetgear.com/downloads/whatsnew.aspx and you will see several AutoFit refactoring has happened in this release.