How to set the width of the cell in the xlsx file created programmatically?

Question

I have this C# code which converts a dataset to xlsx. Is there a way to set the cell or column width of the sheet of the xlsx file created?

//Get the filename      
String filepath = args[0].ToString();
//Convert the file to dataset
DataSet ds = Convert(filepath.ToString(), "tblCustomers", "\t");

//Create the excell object
Excel.Application excel = new Excel.Application();
//Create the workbook
Excel.Workbook workBook = excel.Workbooks.Add();
//Set the active sheet
Excel.Worksheet sheet = workBook.ActiveSheet;


int i = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{                              
    for (int j = 0; j < row.ItemArray.Length; j++)
    {
        sheet.Cells[i + 1, j + 1] = row[j];
    }

    i++;
}

workBook.SaveAs(@"C:\fromCsv.xlsx");
workBook.Close();

Solution 1

sheet.Columns["D:D"].ColumnWidth = 17.57;

or

sheet.Columns[1].ColumnWidth = 17.57;

You can record Macros in Excel and then look to generated code (object model is the same).

Solution 2

To automaticaly set all column widths to "right-size" for their contents, 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.Offie.Interop.Excel assembly for this code to work.