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.!