export datagridview to excel with interop

Question

I want to ask if there is another way to export from datagridview to excel. Because i did it with interop and it's very slow for big file. Now i want to do it with interop too but it should load data quicker.

the code i used:

 for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                xlWorkSheet1.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                for (int j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet1.Cells[i + 2, j + 1] = cell.Value;
                }
            }

Now i would like to export not cell for cells, but row for rows. Something with object[,] values1

my code for export from excel to datatable:

            object[,] values1 = (object[,])xlWorksheet1.UsedRange.Value2; // excelTb1
            for (int k = 0; k < values1.GetLength(1); )
            {
               excelTb1.Columns.Add((string)values1[1, ++k]);
            }

            object[] singleDValue = new object[values1.GetLength(1)];


            for (int i = 1; i < values1.GetLength(0); i++)
            {
                for (int k = 0; k < values1.GetLength(1); )
                {
                    singleDValue[k] = values1[i + 1, ++k];
                }


                excelTb1.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
            }

Can someone show me how to do it from datagridview to excel?

if it's too slow you could write it directly into a CSV file — kzhen
How About via an OledbConnection? I think you could bind your datagrid to a DataTable in thebackground, and use an OledbConnection insert data back to Excel spreadsheet? — Derek
no, OLEDB can write just 255 chars, my datagridview has more than 255chars — Uni Le

Unknown Unknown · Accepted Answer · 2012-11-16T05:34:34

I think this article may help you much, it can export datagridview to excel with the max data of 6000 rows relatively fast in speed.

9 Solutions to Export Data to Excel for ASP.NET

export datagridview to excel with interop

1 Answers