15
\$\begingroup\$

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;
}
\$\endgroup\$
13
\$\begingroup\$

Comments

Your code has way too many comments, mostly redundant ones - some are even obsolete!

Good comments should say why, not what.

// Create our new excel application and add our workbooks/worksheets
Workbooks workbooks = xls.Workbooks;
Workbook workbook = workbooks.Add();

This comment says what the code is doing, and it's lying - you're not creating a new Excel application, you're using an existing instance. Remove it.

// Hide our excel object if it's visible.
xls.Visible = false;

This comment adds no value, and is lying to a certain extent: you don't actually care whether it's already visible or not. Better just remove it.

// Turn off calculations if set to automatic; this can help prevent memory leaks.
xls.Calculation = xls.Calculation == XlCalculation.xlCalculationAutomatic ? XlCalculation.xlCalculationManual : XlCalculation.xlCalculationManual;

This one is accurate and informative - it says why you're turning off automatic calculations.. but it also says what the code is doing. Better rephrase it:

// turning off automatic calculations improves performance and can help prevent memory leaks.
xls.Calculation = (xls.Calculation == XlCalculation.xlCalculationAutomatic) 
                                    ? XlCalculation.xlCalculationManual 
                                    : XlCalculation.xlCalculationManual;

Notice how readability is improved by splitting the ternary operation into 3 lines. It appears the condition is moot, since both ends produce the same assignation. The instruction should be rewritten as simply:

xls.Calculation = XlCalculation.xlCalculationManual;
// Turn off screen updating so our export will process more quickly.
xls.ScreenUpdating = false;

Again, says what - the why makes it closely related to the previous statement. Thus:

// turning off automatic calculations and screen updating 
// improves performance and can help prevent memory leaks.
xls.Calculation = XlCalculation.xlCalculationManual;
xls.ScreenUpdating = false;
// Create an excel table and fill it will our query table.

Remove. This one adds no value.

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

Apparently the code under that comment was moved, but the comment remained.


As for performance, @Will's comment is accurate - COM interop is hurting you here. What's slowing it down is the many calls to the Excel object model (COM interop in itself incurs a performance penalty, but Excel interop somehow makes it even worse). The only way is to limit the number of times you're accessing the Excel object model.

An alternative approach could be to write the data in a .csv file, and use the Excel object model to import the .csv data all in one shot (rather than looping into rows and columns to write the data "manually"), and then to format the workbook and save it in .xlsx format.

\$\endgroup\$
1
\$\begingroup\$

Completed the new code, runs much faster than before without switching to XML options. This is almost instantaneous creation even with large files.

public static string Export(string excelFileName, 
                            string[] excelWorksheetName, 
                            string tableStyle, 
                            params System.Data.DataTable[] dt)
{
    Application excel = new Application();
    excel.DisplayAlerts = false;
    excel.Visible = false;
    excel.ScreenUpdating = false;

    Workbooks workbooks = excel.Workbooks;
    Workbook workbook = workbooks.Add(Type.Missing);

    // Count of data tables provided.
    int iterator = dt.Length;
    for (int i = 0; i < iterator; i++)
    {
        Sheets worksheets = workbook.Sheets;
        Worksheet worksheet = (Worksheet)worksheets[i + 1];
        worksheet.Name = excelWorksheetName[i];

        int rows = dt[i].Rows.Count;
        int columns = dt[i].Columns.Count;
        // Add the +1 to allow room for column headers.
        var data = new object[rows + 1, columns];

        // Insert column headers.
        for (var column = 0; column < columns; column++)
        {
            data[0, column] = dt[i].Columns[column].ColumnName;
        }

        // Insert the provided records.
        for (var row = 0; row < rows; row++)
        {
            for (var column = 0; column < columns; column++)
            {
                data[row + 1, column] = dt[i].Rows[row][column];
            }
        }

        // Write this data to the excel worksheet.
        Range beginWrite = (Range)worksheet.Cells[1, 1];
        Range endWrite = (Range)worksheet.Cells[rows + 1, columns];
        Range sheetData = worksheet.Range[beginWrite, endWrite];
        sheetData.Value2 = data;

        // Additional row, column and table formatting.
        worksheet.Select();
        sheetData.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
                                           sheetData,
                                           System.Type.Missing,
                                           XlYesNoGuess.xlYes,
                                           System.Type.Missing).Name = excelWorksheetName[i];
        sheetData.Select();
        sheetData.Worksheet.ListObjects[excelWorksheetName[i]].TableStyle = tableStyle;
        excel.Application.Range["2:2"].Select();
        excel.ActiveWindow.FreezePanes = true;
        excel.ActiveWindow.DisplayGridlines = false;
        excel.Application.Cells.EntireColumn.AutoFit();
        excel.Application.Cells.EntireRow.AutoFit();

        // Select the first cell in the worksheet.
        excel.Application.Range["$A$2"].Select();
    }

    // Turn off alerts to prevent asking for 'overwrite existing' and 'save changes' messages.
    excel.DisplayAlerts = false;

    // Save our workbook and close excel.
    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(false, Type.Missing, Type.Missing);
    excel.Quit();

    // Release our resources.
    Marshal.ReleaseComObject(workbook);
    Marshal.ReleaseComObject(workbooks);
    Marshal.ReleaseComObject(excel);
    Marshal.FinalReleaseComObject(excel);

    return SaveFilePath;
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.