large datagridview export to excel

Question

im attempting to export the datagridview to excel(.xls) in a winforms application using visual studio 2010 in C#, the problem being it is taking forever to save, so far i have 4220 rows and 20 columns. Is there a faster way to do this. NOTE: I am populating the datagridview from the saved excel file. I appreciate your help....my save code is as follows:

private void btnSave_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        // Get the Header from dataGridView
        for (int h = 1; h < dataGridView1.Columns.Count + 1; h++)
        {
            xlWorkSheet.Cells[1, h] = dataGridView1.Columns[h - 1].HeaderText;
        }

        // Get the Cell Values
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                DataGridViewCell cell = dataGridView1[j, i];
                xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
            }
        }

        //xlWorkBook.SaveCopyAs("\\FORM TEST.xlsx");
        xlWorkBook.SaveAs("\\GB STOCK.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        xlApp = null;
        xlWorkBook = null;
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();      
    }

score 3 · Accepted Answer · edited Oct 24 '13 at 13:17

After having read through various searches and through the answers above i came across this code, it works extremely faster (almost instantaneous) as compared with my orginal where it took just under 2 minutes. I am very grateful for your answers above and i will look into these especially the copy and paste method which was an interesting read. I am a relatively new to this (new hobby) and am only beginning to understand some concepts with regards to exporting datasets etc. I know this is by no means the best way of acheiving what i set out to but it does what i want it to at the moment. Once again thanks to all who have helped, i am learning alot.

            int cols;
            //open file 
            StreamWriter wr = new StreamWriter("GB STOCK.csv", false, Encoding.UTF8);

            //determine the number of columns and write columns to file 
            cols = dgvStock.Columns.Count;
            for (int i = 0; i < cols; i++)
                { 
                    wr.Write(dgvStock.Columns[i].Name.ToString().ToUpper() + ",");
                } 
            wr.WriteLine();

            //write rows to excel file
            for (int i = 0; i < (dgvStock.Rows.Count); i++)
                { 
                    for (int j = 0; j < cols; j++)
                        { 
                            if (dgvStock.Rows[i].Cells[j].Value != null)
                                {
                                    wr.Write(dgvStock.Rows[i].Cells[j].Value + ",");
                                }
                            else 
                                {
                                    wr.Write(",");
                                }
                        }

                    wr.WriteLine();
                }
                //close file
                wr.Close();

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

You want to try to minimize the number of calls you make between your .NET code and the Excel process - those are very slow to execute, so populating cell-by-cell takes a long time.

Better to put the contents of your grid into an array: you can then dump that to the Excel sheet in a single operation.

Write Array to Excel Range

edited May 23 '17 at 12:15

Community

1
1

answered Mar 13 '12 at 19:44

Tim Williams

154,628
8
97
125

score 0 · Answer 3 · answered Mar 13 '12 at 19:45

0

Using interop to copy cell by cell is very slow. I would suggest using copy paste instead. See this msdn article for an example of how to copy the data to the clipboard; you can then use interop to paste the values into the spreadsheet.

answered Mar 13 '12 at 19:45

sga101

1,904
13
12

score 0 · Answer 4 · edited May 23 '17 at 12:15

0

Try ExcelLibrary.

"The aim of this project is provide a native .NET solution to create, read and modify Excel files without using COM interop or OLEDB connection."

COM objects are usually slower compare to native .NET libraries.

You should also take a look at this thread.

edited May 23 '17 at 12:15

Community

1
1

answered Mar 13 '12 at 19:54

Arash N

324
1
2
10

large datagridview export to excel

4 Answers4