How can I automatically sort columns through Excel interaction objects?

Question

Below is the code that I use to load data into an Excel worksheet, but I'm looking for an automatic column size after loading the data. Does anyone know a better way to automatically sort 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(); } } } 

Answer 1

Add this to your TODO point:

aRange.Columns.AutoFit();

Answer 2

It may be too late, but if you add

  worksheet.Columns.AutoFit(); 

or

  worksheet.Rows.AutoFit(); 

It also works.

Answer 3

Also have

 aRange.EntireColumn.AutoFit(); 

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

Answer 4

Take a look at this article, this is not an exact match for your problem, but is suitable for it:

• Craig Murphy - Excel - wordwrap string auto-save issue

Answer 5

This method opens an 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); } }