What is the fastest way to export dataGridView rows to Excel or into an SQL Server database

Question

What is the fastest way to export DataGridView rows in the range of 460328 - 800328 to Excel or into an SQL Server database table with out using Microsoft office interop as interop is quite slow and heavy on system resources?

Answer 1

For exporting to Excel, if you aren't using the XML based 2007 or 2010, Interop is pretty much the only way to go. It's not as bad as it's reputation though. I'll list a few solutions.

1 To Excel

First add a Microsoft.Office.Interop.Excel component reference to your project. This should be under the .NET tab in Project -> Add Reference. add the using statement to your form:

using Excel = Microsoft.Office.Interop.Excel;

add a button control, and add this code to it's body:

    private void btnExport_Click(object sender, EventArgs e)
    {

        Excel.Application app = new Excel.Application();
        app.Visible = true;
        Excel.Workbook wb = app.Workbooks.Add(1);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
        // changing the name of active sheet
        ws.Name = "Exported from gridview";

        ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
        // storing header part in Excel
        for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
        {
            ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
        }


        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
            }
        }

        // sizing the columns
        ws.Cells.EntireColumn.AutoFit();

        // save the application
        wb.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
       app.Quit();
    }
}

2 - to SQL Server

This requires no interop. For ease of use, pass your List object to the event executing the inserts. If you have tables set up to correspond to your grid view column, it's easy. Here, I use a sproc.

    private void btnToSQL_Click(object sender, EventArgs e)
    {
        string connStr = @"Data Source=(local)\sqlexpress;Initial Catalog=rTALIS;Integrated Security=True";
        var cn = new SqlConnection(connStr);
        var cm = new SqlCommand("exec usp_InsertRecord", cn);
        cm.CommandType = System.Data.CommandType.StoredProcedure;
        try
        {
            cn.Open();
            foreach (Row r in rows)
            {
                cm.Parameters.Clear();
                cm.Parameters.AddWithValue("@Number1", r.Number1);
                cm.Parameters.AddWithValue("@Number2", r.Number2);
                cm.Parameters.AddWithValue("@Number3", r.Number3);
                cm.Parameters.AddWithValue("@Number4", r.Number4);
                cm.Parameters.AddWithValue("@Number5", r.Number5);
                cm.Parameters.AddWithValue("@Number6", r.Number6);
                cm.Parameters.AddWithValue("@Number7", r.Number7);
                cm.Parameters.AddWithValue("@Date1", r.Date1);
                cm.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
    }

Let me know if I need to tweak this for you. In the original example, I had List rows = new List(); declared in the form_Load method. This worked for that solution, but it's scope is now too limited. I have moved it up/out into the class, so that in can be called anywhere on the form (specifically btnToSQL_Click). I have commented it out below:

    List<Row> rows = new List<Row>();

    private void Form1_Load(object sender, EventArgs e)
    {
        //var rows = new List<Row>();  //limited scope
        var sr = new StreamReader(@"C:\so_test.txt");
        while (!sr.EndOfStream)
        {
            string s = sr.ReadLine();
            if (!String.IsNullOrEmpty(s.Trim()))
            {
                rows.Add(new Row(s));
            }
        }
        sr.Close();
        dataGridView1.DataSource = rows;
    }

Answer 2

For transferring to Excel, this is the fastest method that I've found (although it does use Office InterOp). Loop through each cell in the DataGridView and assign it to an array of objects. Then assign the entire array to an Excel Range. This is much faster than assigning a value to each Excel cell individually because it only invokes InterOp once. Pardon the VB:

Sub Export()
    Dim xlApp As New Excel.Application
    Dim wb As Excel.Workbook = xlApp.Workbooks.Add
    Dim ws As Excel.Worksheet = wb.Worksheets(1)
    Dim dgv as DataGridView = MyDataGridView

    Dim ExportArray(dgv.Rows.Count, dgv.Columns.Count - 1) As Object
    Dim j, i As Integer

    For j = 0 To dgv.Columns.Count - 1
        ExportArray(0, j) = dgv.Columns(j).Name
        For i = 1 To dgv.Rows.Count
            ExportArray(i, j) = dgv(j, i - 1).Value
        Next
    Next

    Dim col As String = ColNumtoLetter(j)
    ws.Range("A1:" & col & i).Value = ExportArray
End Sub

Private Function ColNumtoLetter(ByVal iCol As Integer) As String
    Dim Result As String = ""

    Dim iAlpha As Integer = Int(iCol / 26.001)
    Dim iRemainder As Integer = iCol - (iAlpha * 26)

    If iAlpha > 0 Then
        Result = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
        Result = Result & Chr(iRemainder + 64)
    End If

    Return Result
End Function

The second method just translates the final column number to the corresponding Excel column name.

See "Fast Exporting from DataSet to Excel" and "Export Data to Excel Much Faster" for more info.

Answer 3

Check out Create Excel files from C# without office as this refers to using EPPlus which works very well - I was able to create my CSV data from the data table and the bulk load in memory the Excel file to stream out. Simply a few lines of code. Varible csvData is string value of all your csvData.

    using( ExcelPackage pck = new ExcelPackage( ) )
    {
      //Create the worksheet
      ExcelWorksheet ws = pck.Workbook.Worksheets.Add( "Sheet1" );

      // set the delimiter
      etf.Delimiter = ',';
      etf.EOL = "\n";
      etf.TextQualifier = "\"";

      //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
      ws.Cells["A1"].LoadFromText( csvData, etf );
      return pck.GetAsByteArray( );
   }

Answer 4

One option would be to write data to a CSV file instead of an Excel file. Excel would have no problem reading it afterwards.

If you're not familiar, in CSV (i.e. Comma Separated) files the fields are separated by commas and rows are separated by newlines (\n or \r\n).

Something like (may not compile!):

private void WriteData() {
    using (var file = System.IO.StreamWriter(@"C:\Path\To\File.csv")) {
        foreach (var row in dataGrid.Rows) {
             foreach (var cell in row.Cells) {
                 // Note that if some cells contain commas, 
                 // you'd need to wrap them in quotes.
                 file.Write(cell.Value).Write(",");
             }
        }
        file.Write("\n");
    }
}

For faster performance, it may also be a good idea collect a few hundred (or thousand) rows into a single string and then write it to a file, instead of writing cell-by-cell.