The fastest way to generate Excel from datagridview with formatting

Question

I have code to export data from datagridviewto an Excel worksheet, but the problem is that it is very slow because it inserts data and formats each cell .

How to improve the 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 the Open XML SDK if you want.

I used Open XML to export data to an Excel spreadsheet (.XLSX format), and I can assure that the performance is excellent.

• 50000 2, 3

• 1 60 [ 10 000 100 ]

:

• .
• [ PRO, ]
• Open XML Productivity;

:

• Excel Office .
• , .
• , InterOp, , Excel .!

Answer 2

Microsoft.Office.Interop.Excel, , .

• 1
• *
• DataGridView, , : Excel

Btw, GC.Collect COM-, , COM- #, MS Office

Answer 3

MS Office Interop , Microsoft Interop . , Microsoft OLE Automation.

Microsoft Excel XLSX Office 2007 OpenXML SDK Interop.

Excel XLS, Excel, EasyXLS.

. DataGridView 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");

ExcelAutoFormat. datagridview Excel # .