How to export dataGridView data Instantly to Excel on button click?

Question

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 ta…

Accepted Answer

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.

How to export dataGridView data Instantly to Excel on button click?

Solution

Problem

Related problems