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: 463
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