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 me, the best improvement to increase the speed was using this way.

private const string connectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYDBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SERVERID;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DBName"; 

object misValue = System.Reflection.Missing.Value;
Excel.Application xlsApp = new Excel.Application();
Excel.Workbook xlsWorkbook = xlsApp.Workbooks.Add(misValue);
Excel.Worksheet myWS = xlsWorkbook.Sheets[1];

 Excel.ListObject lo = myWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcQuery, connectionString, true, 
 Excel.XlYesNoGuess.xlGuess, myWS.Range["A5"]);
 
//A5 is the starting cell

lo.QueryTable.CommandText = "SELECT * FROM TABLE";
lo.Refresh();

I tried DataReader/Datatable with "for" loop (writting each cell) and both were too much slow recovering a query with 5800 rows (and 40 columns). In numbers:

DataReader - 5:16 minutes
This way - 13 seconds

Answer 2

This may be faster way,

    using Excel = Microsoft.Office.Interop.Excel;

    public static void SaveGridToExcel(DataGridView DGV)
    {

        if (DGV.Rows.Count > 0)
        {
            string filename = "";
            SaveFileDialog SV = new SaveFileDialog();
            SV.Filter = "EXCEL FILES|*.xlsx;*.xls";
            DialogResult result = SV.ShowDialog();


            if (result == DialogResult.OK)
            {

                filename = SV.FileName;
                bool multiselect = DGV.MultiSelect;
                DGV.MultiSelect = true;
                DGV.SelectAll();
                DGV.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
                Clipboard.SetDataObject(DGV.GetClipboardContent());
                var results = System.Convert.ToString(Clipboard.GetData(DataFormats.Text));
                DGV.ClearSelection();
                DGV.MultiSelect = multiselect;
                Microsoft.Office.Interop.Excel.Application XCELAPP = null;
                Microsoft.Office.Interop.Excel.Workbook XWORKBOOK = null;
                Microsoft.Office.Interop.Excel.Worksheet XSHEET = null;
                object misValue = System.Reflection.Missing.Value;
                XCELAPP = new Excel.Application();
                XWORKBOOK = XCELAPP.Workbooks.Add(misValue);
                XCELAPP.DisplayAlerts = false;
                XCELAPP.Visible = false;
                XSHEET = XWORKBOOK.ActiveSheet;
                XSHEET.Paste();
                XWORKBOOK.SaveAs(filename, Excel.XlFileFormat.xlOpenXMLWorkbook);
                XWORKBOOK.Close(false);
                XCELAPP.Quit();
                try
                {                      
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XSHEET);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XWORKBOOK);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XCELAPP);
                }
                catch { }
            }
        }
    }

Answer 3

Here I used DataTable to write data to excel file. I think data Grid View also same to the DataTable.

First get data from Database:

db.GetData(sqlgetprint);

It's call for method that method is:

class DataBaseConnection
{
    private OdbcConnection conn1 = new OdbcConnection(@"FILEDSN=C:/OTPub/Ot.dsn;" + "Uid=sa;" + "Pwd=otdata@123;"); //"DSN=Ot_DataODBC;" + "Uid=sa;" +  "Pwd=otdata@123;"

    //select
    public System.Data.DataTable GetData(string sql)
    {
        try
        {

            conn1.Open();
            OdbcDataAdapter adpt = new OdbcDataAdapter(sql, conn1);
            DataTable dt = new DataTable();
            adpt.Fill(dt);
            conn1.Close();
            return dt;

        }
        catch (Exception ex)
        {
            conn1.Close();
            throw ex;
        }
    }    
}

After that create Object for DataBaseConncetion Class in your working form

 DataBaseConnection db = new DataBaseConnection();

In your Button Click Event you can Write this Code to Write to the Excel file

string sqlgetprint = "SELECT  Service_No,Full_name, Acc_No, OP_date, On_time, Off_time, OP_hours, Payment  FROM   Print_Op ORDER BY Service_No , OP_date";
            DataTable dtall = db.GetData(sqlgetprint);

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Excel Documents (*.xls)|*.xls";
            saveFileDialog1.FileName = "Employee Details.xls";

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string fname = saveFileDialog1.FileName;


                StreamWriter wr = new StreamWriter(fname);
                for (int i = 0; i < dtall.Columns.Count; i++)
                {
                    wr.Write(dtall.Columns[i].ToString().ToUpper() + "\t");
                }

                wr.WriteLine();

                //write rows to excel file
                for (int i = 0; i < (dtall.Rows.Count); i++)
                {
                    for (int j = 0; j < dtall.Columns.Count; j++)
                    {
                        if (dtall.Rows[i][j] != null)
                        {
                            wr.Write(Convert.ToString(dtall.Rows[i][j]) + "\t");
                        }
                        else
                        {
                            wr.Write("\t");
                        }
                    }
                    //go to next line
                    wr.WriteLine();
                }
                //close file
                wr.Close();
                if (File.Exists(fname))
                {
                    System.Diagnostics.Process.Start(fname);
                }

            }
        }

        catch (Exception)
        {

            MessageBox.Show("Error Create Excel Sheet!");
        }

Answer 4

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 5

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 6

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 7

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.