Fastest way to generate Excel from datagridview with formatting

Question

I have a code to export data from datagridview to Excel sheet but the problem is it is very slow because it is inserting data and formatting each cell.

How can I improve performance of this operation?

Below is my code

public static void ExcelExport(DataGridView Dg, string TypePass)
{
    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    ExcelApp.Application.Workbooks.Add(Type.Missing);
    Excel_12.ApplicationClass oExcel_12 = null;           //Excel_12 Application
    Excel_12.Workbook oBook = null;                       // Excel_12 Workbook
    Excel_12.Sheets oSheetsColl = null;                   // Excel_12 Worksheets collection
    Excel_12.Worksheet oSheet = null;                     // Excel_12 Worksheet
    Excel_12.Range oRange = null;                         // Cell or Range in worksheet
    Object oMissing = System.Reflection.Missing.Value;
    oExcel_12 = new Excel_12.ApplicationClass();
    oExcel_12.UserControl = true;
    oBook = oExcel_12.Workbooks.Add(oMissing);
    oSheetsColl = oExcel_12.Worksheets;
    oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");

    oRange = (Excel_12.Range)oSheet.Cells[1, 1];
    oRange.Value2 = "";
    oRange.Font.Name = "Tahoma";
    oRange.Font.Size = 12;
    (oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
    (oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);

    if (TypePass.Trim().Length > 0)
    {
        oRange = (Excel_12.Range)oSheet.Cells[2, 1];
        oRange.Value2 = TypePass;
        oRange.Font.Name = "Tahoma";
        oRange.Font.Size = 10;
    }

    int c = 0;

    if (Dg.ColumnHeadersVisible == true)
    {
        for (int j = 0; j < Dg.Columns.Count; j++)
        {
            if (Dg.Columns[j].Visible == true)
            {
                oRange = (Excel_12.Range)oSheet.Cells[4, c + 1];
                oRange.Value2 = Dg.Columns[j].HeaderText + "  ";
                oRange.Font.Bold = true;
                oRange.Font.Name = "Tahoma";
                oRange.Font.Size = 9;
                (oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                (oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Teal);
                oExcel_12.Columns.AutoFit();
                c++;
            }
        }
    }

    c = 0;

    for (int i = 0; i < Dg.Rows.Count; i++)
    {

        for (int j = 0; j < Dg.Columns.Count; j++)
        {
            if (Dg.Columns[j].Visible == true)
            {
                oRange = (Excel_12.Range)oSheet.Cells[i + 5, c + 1];
                if (Dg[j, i].Value == null)
                {
                    oRange.Value2 = " ";
                }
                else
                {
                    oRange.Value2 = Dg[j, i].Value.ToString().Replace('\n', ' ') + "  ";
                }

                oRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                oRange.Font.Name = "Tahoma";
                oRange.Font.Size = 8;
                oExcel_12.Columns.AutoFit();
                // oRange.NumberFormat = "dd/MM/yyyy";
                c++;
            }
        }
        c = 0;
    }

    oExcel_12.Visible = true;
    oBook = null;
    oExcel_12 = null;
    GC.Collect();
}

Answer 1

You can use Open XML SDK if you like.

I have used Open XML for export data to Excel spreadsheet (.XLSX format) and i can assure that performances are great.

• I can generate 50,000 cell spreadsheet within 2, 3 seconds

• 1 Million cell spreadsheet within 60 seconds [That's 10,000 Row 100 Column spreadsheet]

What you need to know :

• Lean how spreadsheet is structured
• Follow given guides here and here
• Learn about Styling [kind of PRO level which enables many possibilities]
• Work with Open XML Productivity tool ; Will ease your learning curve guide

Advantage :

• you can create well formatted Excel sheets without having Office package installed.
• Also you can expand spreadsheet generating even to server side if you like.
• At first you will feel it's hard compared to InterOp , but once you have properly implemented you will be able to use same Excel spreadsheet function for ANY project.!

Answer 2

MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side. For more details see what Microsoft stated on why not to use OLE Automation.

Microsoft Excel released XLSX file format with Office 2007 and recommends the usage of OpenXML SDK instead of Interop.

If you must save Excel files in XLS file format, you can use an Excel library like EasyXLS.

See the following code sample as alternative of exporting DataGridView to Excel:

// Create a DataSet and add the DataTable of DataGridView 
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)dataGridView);//or ((DataTable)dataGridView.DataSource).Copy() to create a copy

// Export Excel file 
ExcelDocument workbook = new ExcelDocument();
workbook.easy_WriteXLSFile_FromDataSet(filePath, dataSet, 
       new EasyXLS.ExcelAutoFormat(EasyXLS.Constants.Styles.AUTOFORMAT_EASYXLS1), 
       "Sheet1");

For exporting the formatting that you need you can create your own ExcelAutoFormat. Check this code sample on how to export datagridview to Excel in C# with formatting.

Answer 3

If you decide to stick in Microsoft.Office.Interop.Excel, you can utilize the code by setting the format and data in range properly.

1. Set header styles for 1 row
2. Set content styles from columns * row
3. Build array from the DataGridView cell values then write it in range is a very quick way: Write Array to Excel Range

Btw, GC.Collect cannot serve the purpose for close the COM object, please reference to Proper disposal of COM interop objects in C# particularly MS Office applications