Search code examples
c#excelepplus

AutoFitColumns() with EPPlus slightly short

I generate an Excel sheet with EPPlus and at the end, call AutoFitColumns() on my range:

sheet.DefaultColWidth = 10;
sheet.Cells[1,1,myRow,myCol].AutoFitColumns();

It works fine, except all the columns that are adjusted end up slightly short of the actual column width. Microsoft Excel adjusts the width properly when I double click on the column header, but with EPPlus, the last character will often by half-hidden.

The size difference between EPPlus and Excel's auto-adjust seems to vary based on the width of the contents, but it is not linear, so I assume EPPlus does not calculate the content width accurately based on the font.

Is there a way to fix this problem, other than arbitrarily increasing column width after auto-adjusting?

Solution

  • I conclude that this is a bug with EPPlus. Setting column width via the default parameter is also inaccurate (the resulting width is lower). I have not tested manually setting column width.

    As a workaround, I have calculated the difference between each column's width as set with EPPlus and Excel, and have determined that for my application, it differs by up to 6-7%. So, after calling AutoFitColumns(), I do the following:

    for(int i = 1; i < myRow; i++
    sheet.Column(i).Width *= 1.06;