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  lt  100  row                                  for  int col   0  col  lt  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

User · Accepted Answer

Add this at your TODO point   aRange Columns AutoFit

User · Answer

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

User · Answer

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

User · Answer

This might be too late but if you add    worksheet Columns AutoFit      or    worksheet Rows AutoFit      it also works

User · Answer

Also there is   aRange EntireColumn AutoFit      See What is the difference between Range Columns and Range EntireColumn

[c#] How do I auto size columns through the Excel interop objects?

Examples related to c#

Examples related to excel-2003

Examples related to excel-interop