How to export dataGridView data Instantly to Excel

问题:

I have 10k rows and 15 column in my data grid view. I want to export this data to an excel sheet o button click. I have already tried with the below code.

private void btExport_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);        
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
        app.Visible = true;
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;                  
        for(int i=1;i<dataGridView1.Columns.Count+1;i++)
        {
             worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
        }    
        for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
        {
            for(int j=0;j<dataGridView1.Columns.Count;j++)
            {
                if (dataGridView1.Rows[i].Cells[j].Value != null)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                else
                {
                    worksheet.Cells[i + 2, j + 1] = "";
                }
            }
        }
    }

This is working for me but it is taking lots of time to complete exporting process.

Is it possible to export from dataGridView (with 10k rows)to excel instantly on a button click?

Other than this, when I tried copy all dataGridview contents to clip board and then paste it to excel sheet manually, it happen almost instantly.

So is there a way to copy all dataGridView cells to clip board and paste it to excel sheet(with cell formatting) on a button click?

I have code for copy to clipboard as below, but I don't know how to paste it in to a new excel sheet by opening it.

        private void copyAllToolStripMenuItem_Click(object sender, EventArgs e)
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

Please help with an example. I am new to C#.

回答1:

I solved this by simple copy and paste method. I don't know it is the best way to do this but,for me it works good and almost instantaneously. Here is my code.

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Thanks.

回答2:

This is a great question and I was surprised at how difficult it was to find a clear and complete answer, most of the answers I found were either sudo-code or not 100% complete.

I was able to create a complete solution to copy and save the data from my DataGridView to an excel file based on Jake's answer so I'm posting my complete solution in the hopes that it can help other new comers to c# like myself :)

First off, you will need the Microsoft.Office.Interop.Excel reference in your project. See MSDN on how to add it.

My Code:

using Excel = Microsoft.Office.Interop.Excel;

private void btnExportToExcel_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Documents (*.xls)|*.xls";
    sfd.FileName = "Inventory_Adjustment_Export.xls";
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        // Copy DataGridView results to clipboard
        copyAlltoClipboard();

        object misValue = System.Reflection.Missing.Value;
        Excel.Application xlexcel = new Excel.Application();

        xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
        Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
        Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        // Format column D as text before pasting results, this was required for my data
        Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
        rng.NumberFormat = "@";

        // Paste clipboard results to worksheet range
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

        // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
        // Delete blank column A and select cell A1
        Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
        delRng.Delete(Type.Missing);
        xlWorkSheet.get_Range("A1").Select();

        // Save the excel file under the captured location from the SaveFileDialog
        xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlexcel.DisplayAlerts = true;
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);

        // Clear Clipboard and DataGridView selection
        Clipboard.Clear();
        dgvItems.ClearSelection();

        // Open the newly saved excel file
        if (File.Exists(sfd.FileName))
            System.Diagnostics.Process.Start(sfd.FileName);
    }
}

private void copyAlltoClipboard()
{
    dgvItems.SelectAll();
    DataObject dataObj = dgvItems.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

回答3:

I did not intend to steal @Jake and @Cornelius's answer, so i tried editing it. but it was rejected. Anyways, the only improvement I have to point out is about avoiding extra blank column in excel after paste. Adding one line dataGridView1.RowHeadersVisible = false; hides so called "Row Header" which appears on the left most part of DataGridView, and so it is not selected and copied to clipboard when you do dataGridView1.SelectAll();

private void copyAlltoClipboard()
    {
        //to remove the first blank column from datagridview
        dataGridView1.RowHeadersVisible = false;
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

回答4:

using Excel = Microsoft.Office.Interop.Excel;


private void btnExportExcel_Click(object sender, EventArgs e)
{
    try
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Visible = true;
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
        int StartCol = 1;
        int StartRow = 1;
        int j = 0, i = 0;

        //Write Headers
        for (j = 0; j < dgvSource.Columns.Count; j++)
        {
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
            myRange.Value2 = dgvSource.Columns[j].HeaderText;
        }

        StartRow++;

        //Write datagridview content
        for (i = 0; i < dgvSource.Rows.Count; i++)
        {
            for (j = 0; j < dgvSource.Columns.Count; j++)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                    myRange.Value2 = dgvSource[j, i].Value == null ? "" : dgvSource[j, i].Value;
                }
                catch
                {
                    ;
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

回答5:

Interop is slow and has other issues, using the the clipboard seems non extensible. Here are two other ways to do this

1. Work with Excel 2007+ files directly instead of working with Excel, it'll be much (much) faster. You can use OpenXML (http://openxmldeveloper.org/) which is Microsoft's SDK. The best way to learn OpenXML is to download the Productivity tool (http://www.microsoft.com/en-us/download/details.aspx?id=5124), ittakes an existing file and generates the code required to create it. Another, perhaps simpler, option is to use ClosedXML (http://closedxml.codeplex.com/). It seems a lot easier to use (look at the example http://closedxml.codeplex.com/wikipage?title=Showcase&referringTitle=Home), but I have no experience with it. I'm sure there are other libraries that wrap work with Excel.

2. Work with excel via OLEDB. This allows you to work with Excel as if it's a dababase. See http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB or Performance of OLEDB to read Excel for examples and more details.

I'd start with ClosedXML.

回答6:

The Best is use use closedxml.codeplex.com Library.Refer it @https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GetTheDataTable();//Refer documentation


wb.Worksheets.Add(dt);

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");

using (var ms = new System.IO.MemoryStream()) {
    wb.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);
    ms.Close();
}

Response.End();

回答7:

I like Jake's solution. The problem with no header is resolved by doing the following

xlWorkSheet.Cells[1, 1] = "Header 1";
xlWorkSheet.Cells[1, 2] = "Header 2";
xlWorkSheet.Cells[1, 3] = "Header 3";

of course this only works is you know what the headers should be ahead of time.

回答8:

that's what i use for my gridview, try to use it for yr data , it works perfectly :

        GridView1.AllowPaging = false;
        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)
        {
            //add separator
            sb.Append(GridView1.Columns[k].HeaderText+";");

        }


        //append new line
        sb.Append("\r\n");
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            for (int k = 0; k < GridView1.Columns.Count; k++)
            {
                sb.Append(GridView1.Rows[i].Cells[k].Text+";");
            }
            sb.AppendLine();
        }

回答9:

This answer is for the first question, why it takes so much time and it offers an alternative solution for exporting the DataGridView to Excel.

MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side and cannot be use to export large Excel files. For more details see why not to use OLE Automation from Microsoft point of view.

Interop saves Excel files in XLS file format (old Excel 97-2003 file format) and the support for Office 2003 has ended. Microsoft Excel released XLSX file format with Office 2007 and recommends the usage of OpenXML SDK instead of Interop. But XLSX files are not really so fast and doesn’t handle very well large Excel files because they are based on XML file format. This is why Microsoft also released XLSB file format with Office 2007, file format that is recommended for large Excel files. It is a binary format. So the best and fastest solution is to save XLSB files.

You can use this C# Excel library to save XLSB files, but it also supports XLS and XLSX file formats.

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_WriteXLSBFile_FromDataSet(filePath, dataSet, 
     new EasyXLS.ExcelAutoFormat(EasyXLS.Constants.Styles.AUTOFORMAT_EASYXLS1), 
     "Sheet1");

If you also need to export the formatting of the DataGridView check this code sample on how to export datagridview to Excel in C#.

回答10:

This line works only for the DataGridView Control on Windows Forms:

DataObject dataObj = dataGridView1.GetClipboardContent();

This one addresses the same issue, but for the DataGrid control for the WPF Framework:

    private void copyDataGridContentToClipboard()
    {
        datagridGrupeProductie.SelectAll();
        datagridGrupeProductie.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;

        ApplicationCommands.Copy.Execute(null, datagridGrupeProductie);
        datagridGrupeProductie.UnselectAll();
    }


    private void rightClickGrupeProductie_Click(object sender, RoutedEventArgs e)
    {
        copyDataGridContentToClipboard();
        Microsoft.Office.Interop.Excel.Application excelApp;
        Microsoft.Office.Interop.Excel.Workbook excelWkbk;
        Microsoft.Office.Interop.Excel.Worksheet excelWksht;
        object misValue = System.Reflection.Missing.Value;
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Visible = true;
        excelWkbk = excelApp.Workbooks.Add(misValue);
        excelWksht = (Microsoft.Office.Interop.Excel.Worksheet)excelWkbk.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)excelWksht.Cells[1, 1];
        CR.Select();
        excelWksht.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    }