How do I auto size columns through the Excel interop objects?
        Posted  
        
            by norlando02
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by norlando02
        
        
        
        Published on 2010-05-21T17:53:56Z
        Indexed on 
            2010/05/21
            18:00 UTC
        
        
        Read the original article
        Hit count: 533
        
Below is the code I'm using to load the data into an Excel worksheet, but I'm look to auto size the column after the data is loaded. Does anyone know the best way to auto size the columns?
using Microsoft.Office.Interop;
public class ExportReport
{
    public void Export()
    {
        Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbook wb;
        Excel.Worksheet ws;
        Excel.Range aRange;
        object m = Type.Missing;
        string[,] data;
        string errorMessage = string.Empty;
        try
        {
            if (excelApp == null)
                throw new Exception("EXCEL could not be started.");
            // Create the workbook and worksheet.
            wb = excelApp.Workbooks.Add(Office.Excel.XlWBATemplate.xlWBATWorksheet);
            ws = (Office.Excel.Worksheet)wb.Worksheets[1];
            if (ws == null)
                throw new Exception("Could not create worksheet.");
            // Set the range to fill.
            aRange = ws.get_Range("A1", "E100");
            if (aRange == null)
                throw new Exception("Could not get a range.");
            // Load the column headers.
            data = new string[100, 5];
            data[0, 0] = "Column 1";
            data[0, 1] = "Column 2";
            data[0, 2] = "Column 3";
            data[0, 3] = "Column 4";
            data[0, 4] = "Column 5";
            // Load the data.
            for (int row = 1; row < 100; row++)
            {
                for (int col = 0; col < 5; col++)
                {
                    data[row, col] = "STUFF";
                }
            }
            // Save all data to the worksheet.
            aRange.set_Value(m, data);
            // Atuo size columns
            // TODO: Add Code to auto size columns.
            // Save the file.
            wb.SaveAs("C:\Test.xls", Office.Excel.XlFileFormat.xlExcel8, m, m, m, m, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m, m, m, m, m);
            // Close the file.
            wb.Close(false, false, m);
        }
        catch (Exception) { }
        finally
        {
            // Close the connection.
            cmd.Close();
            // Close Excel.
            excelApp.Quit();
        }
    }
}
© Stack Overflow or respective owner