Taking a Hybrid Automatic and Manual Approach to Set Precise Excel Column Widths in C#

Have your AutoFit and Eat it Too

It's irritating to have to 2-click in the column widths of a spreadsheet so that the columns will widen out to display all their content.

You can take care of this by calling AutoFit, like so:

_xlSheet.Columns.AutoFit();

However, sometimes one or two "rogue" values in a column make that column go ultra-wide, and you have to drag the column way over to the left so as to see more of the data. You can overcome this Catch-22 by using both AutoFit and then, afterwards, specifying the width of any problematic columns. Here's the code for how to do that, which assumes column 1 is the one to be reined in, and 42 is the width you want it to assume:

    private Worksheet _xlSheet;
    private static readonly int ITEMDESC_COL = 1;
    private static readonly int WIDTH_FOR_ITEM_DESC_COL = 42;
    . . .
    _xlSheet.Columns.AutoFit();
    // Now take back the wider-than-the-ocean column
    ((Range)_xlSheet.Cells[ITEMDESC_COL, ITEMDESC_COL]).EntireColumn.ColumnWidth =  _
	WIDTH_FOR_ITEM_DESC_COL;

Note: As an added nicety, you can have the over-long content wrap (especially useful if they are in a Merged (multi-row) range) like so (where "range" is the Range you defined when populating the column):

range.WrapText = true;

Note: You need to add the Microsoft.Office.Interop.Excel assembly for this code to work.