Attached is a generic code I wrote to create an Excel file with x number of worksheets.
The problem I am having is that it's pretty slow, like 5 seconds a sheet. It was my understanding that using a for
loop when creating the tables was ideal, but the issue seems to be with tables containing over a thousand or so records... I still wouldn't think it should take this long.
Any pointers would be appreciated. Also, if I am completely out in left field with this code let me know; up-to-date Excel code resources seem to be hard to find.
public static string Export(string excelFileName,
string[] excelWorksheetName,
string tableStyle,
params System.Data.DataTable[] dt)
{
Application xls = new Application();
xls.SheetsInNewWorkbook = dt.Length;
// Create our new excel application and add our workbooks/worksheets
Workbooks workbooks = xls.Workbooks;
Workbook workbook = workbooks.Add();
// Hide our excel object if it's visible.
xls.Visible = false;
// Turn off calculations if set to automatic; this can help prevent memory leaks.
xls.Calculation = xls.Calculation == XlCalculation.xlCalculationAutomatic ? XlCalculation.xlCalculationManual : XlCalculation.xlCalculationManual;
// Turn off screen updating so our export will process more quickly.
xls.ScreenUpdating = false;
// Create an excel table and fill it will our query table.
int iterator = dt.Length - 1;
for (int i = 0; i <= iterator; i++)
{
// Turn off calculations if set to automatic; this can help prevent memory leaks.
Worksheet worksheet = (Worksheet)xls.Worksheets[i + 1];
worksheet.Name = excelWorksheetName[i];
worksheet.Select();
if (dt[i].Rows.Count > 0)
{
// Format this information as a table.
Range tblRange = worksheet.get_Range("$A$1");//string.Format("$A$1", dt[i].Rows.Count + 1));
tblRange.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
tblRange,
System.Type.Missing,
XlYesNoGuess.xlYes,
System.Type.Missing).Name = excelWorksheetName[i];
tblRange.Select();
tblRange.Worksheet.ListObjects[excelWorksheetName[i]].TableStyle = tableStyle;
// Create a row with our column headers.
for (int column = 0; column < dt[i].Columns.Count; column++)
{
worksheet.Cells[1, column + 1] = dt[i].Columns[column].ColumnName;
}
// Export our data table information to excel.
for (int row = 0; row < dt[i].Rows.Count; row++)
{
for (int column = 0; column < dt[i].Columns.Count; column++)
{
worksheet.Cells[row + 2, column + 1] = (dt[i].Rows[row][column].ToString());
}
}
}
// Freeze our column headers.
xls.Application.Range["2:2"].Select();
xls.ActiveWindow.FreezePanes = true;
xls.ActiveWindow.DisplayGridlines = false;
// Auto fit our rows and columns.
xls.Application.Cells.EntireColumn.AutoFit();
xls.Application.Cells.EntireRow.AutoFit();
// Select the first cell in the worksheet.
xls.Application.Range["$A$2"].Select();
// Turn off alerts to prevent asking for 'overwrite existing' and 'save changes' messages.
xls.DisplayAlerts = false;
}
string SaveFilePath = string.Format(@"{0}.xls", excelFileName);
workbook.SaveAs(SaveFilePath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close();
// Release our resources.
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(xls);
Marshal.FinalReleaseComObject(xls);
return SaveFilePath;
}