How do I auto size columns through the Excel interop objects?

Question

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();
        }
    }
}

score 122 · Accepted Answer · answered May 21 '10 at 18:33

122

Add this at your TODO point:

aRange.Columns.AutoFit();

answered May 21 '10 at 18:33

Igby Largeman

16,495
3
60
86

2

There is also a `aRange.Rows.AutoFit()` as well. – krillgar May 01 '13 at 14:08
5

Use the @nawfal answer: aRange.EntireColumn.AutoFit(); – Eduardo Cuomo Mar 19 '15 at 17:39
3

Just in case you didn't know: `AutoFit()` does not put the columns into a mode where they automatically adjust to content that you add later, but does a once only fit with the current data. So the `Range` must be completely populated before calling `AutoFit()`. – JonP Aug 11 '16 at 13:38
4

Here is another good one: workSheet.UsedRange.Columns.AutoFit(); – thedrs Jun 27 '17 at 14:16

score 46 · Answer 2 · answered May 05 '15 at 14:53

46

This might be too late but if you add

 worksheet.Columns.AutoFit();

or

 worksheet.Rows.AutoFit();

it also works.

answered May 05 '15 at 14:53

Matheus Shita

534
5
11

2

I like this better. This is more useful if you don't care about specific columns that need to be resized and just want the whole sheet to be autofit. – ThePersonWithoutC May 18 '16 at 20:35

score 30 · Answer 3 · edited May 23 '17 at 12:02

30

Also there is

aRange.EntireColumn.AutoFit();

See What is the difference between Range.Columns and Range.EntireColumn.

edited May 23 '17 at 12:02

Community

1
1

answered Feb 07 '14 at 03:43

nawfal

70,104
56
326
368

score 3 · Answer 4 · answered Jun 24 '15 at 06:55

This method opens already created excel file, Autofit all columns of all sheets based on 3rd Row. As you can see Range is selected From "A3 to K3" in excel.

 public static void AutoFitExcelSheets()
    {
        Microsoft.Office.Interop.Excel.Application _excel = null;
        Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
        try
        {
            string ExcelPath = ApplicationData.PATH_EXCEL_FILE;
            _excel = new Microsoft.Office.Interop.Excel.Application();
            _excel.Visible = false;
            object readOnly = false;
            object isVisible = true;
            object missing = System.Reflection.Missing.Value;

            excelWorkbook = _excel.Workbooks.Open(ExcelPath,
                   0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                   true, false, 0, true, false, false);
            Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            foreach (Microsoft.Office.Interop.Excel.Worksheet currentSheet in excelSheets)
            {
                string Name = currentSheet.Name;
                Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(Name);
                Microsoft.Office.Interop.Excel.Range excelCells =
(Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A3", "K3");
                excelCells.Columns.AutoFit();
            }
        }
        catch (Exception ex)
        {
            ProjectLog.AddError("EXCEL ERROR: Can not AutoFit: " + ex.Message);
        }
        finally
        {
            excelWorkbook.Close(true, Type.Missing, Type.Missing);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            releaseObject(excelWorkbook);
            releaseObject(_excel);
        }
    }

score 2 · Answer 5 · answered May 21 '10 at 18:12

2

Have a look at this article, it's not an exact match to your problem, but suits it:

Craig Murphy - Excel – wordwrap row autosize issue

answered May 21 '10 at 18:12

Kyle Rosendo

25,001
7
80
118

How do I auto size columns through the Excel interop objects?

5 Answers5

Linked

Related