My Blog

Export data from DataGridView to excel file

In continuation from here

Add a DataGridView and a Button. Let’s say your form looks like as shown below

Now we will create a sample data in the form’s load event.

Imports System.Data.OleDb

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '~~> Populating a DataGridView with sample data for demonstration purpose
        With DataGridView1
            .ColumnCount = 3
            .Columns(0).Name = "Sno"
            .Columns(1).Name = "Name"
            .Columns(2).Name = "Company"

            Dim row As String() = New String() {"1", "Sid", "XPIZON"}
            DataGridView1.Rows.Add(row)
            row = New String() {"2", "John Doe", "XPIZON"}
            DataGridView1.Rows.Add(row)
        End With
    End Sub
End Class

When you run the form, it look like this

Now in the Button’s code, put this

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim FileName As String = "C:\Temp\ExportedData.Xlsx"
        Dim SheetName As String = "Sheet1"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring
        olecon.Open()

        Dim sql As String = "CREATE TABLE " + SheetName + " ("
        Dim ColNames As String = "", OnlyColumnNames As String = ""

        For i = 0 To DataGridView1.Columns.Count - 1
            If ColNames = "" Then
                OnlyColumnNames = DataGridView1.Columns(i).HeaderText
                ColNames = DataGridView1.Columns(i).HeaderText + " VARCHAR"
            Else
                ColNames = ColNames & "," & DataGridView1.Columns(i).HeaderText & " VARCHAR"
                OnlyColumnNames = OnlyColumnNames & "," & DataGridView1.Columns(i).HeaderText
            End If
        Next

        sql = sql & ColNames & ")"

        '~~> sql in quickwatch --> "CREATE TABLE Sheet1 (Sno VARCHAR,Name VARCHAR,Company VARCHAR)"

        '~~> Create the Worksheet and the column headers
        Dim objCommandInsert As OleDbCommand = New OleDbCommand(sql, olecon)
        objCommandInsert.ExecuteNonQuery()

        '~~> Loop through the DataGridView's rows and columns to get data
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            sql = ""

            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                sql = sql & "'" & Convert.ToString(DataGridView1.Rows(i).Cells(j).Value).Trim & "',"
            Next

            If sql.EndsWith(",") Then  sql = sql.Substring(0, sql.Length - 1)

            sql = "INSERT INTO " & SheetName & " (" & OnlyColumnNames & ") VALUES (" & sql & ")"

            '~~> sql in quickwatch for Row 1 -->"INSERT INTO Sheet1 (Sno,Name,Company) VALUES ('1','Sid','XPIZON')"

            objCommandInsert = New OleDbCommand(sql, olecon)
            objCommandInsert.ExecuteNonQuery()
        Next

        olecon.Close()

        MessageBox.Show("Done")
    End Sub

When you click on the button, the a new excel file “C:\Temp\ExportedData.Xlsx” is created.

This is how the file looks like after being created.

Please note that the path “C:\Temp\” should exist else the code will give you an error. One way is to check for the folder and if it doesn’t exist then create the folder or better still ask the user to save the file using FolderBrowserDialog1.ShowDialog(). Also if the file exists then it will give you an error. You will also have to handle that part as well.

The complete code

Imports System.Data.OleDb

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '~~> Populating a DataGridView with sample data for demonstration purpose
        With DataGridView1
            .ColumnCount = 3
            .Columns(0).Name = "Sno"
            .Columns(1).Name = "Name"
            .Columns(2).Name = "Company"

            Dim row As String() = New String() {"1", "Sid", "XPIZON"}
            DataGridView1.Rows.Add(row)
            row = New String() {"2", "John Doe", "XPIZON"}
            DataGridView1.Rows.Add(row)
        End With
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim FileName As String = "C:\Temp\ExportedData.Xlsx"
        Dim SheetName As String = "Sheet1"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring
        olecon.Open()

        Dim sql As String = "CREATE TABLE " + SheetName + " ("
        Dim ColNames As String = "", OnlyColumnNames As String = ""

        For i = 0 To DataGridView1.Columns.Count - 1
            If ColNames = "" Then
                OnlyColumnNames = DataGridView1.Columns(i).HeaderText
                ColNames = DataGridView1.Columns(i).HeaderText + " VARCHAR"
            Else
                ColNames = ColNames & "," & DataGridView1.Columns(i).HeaderText & " VARCHAR"
                OnlyColumnNames = OnlyColumnNames & "," & DataGridView1.Columns(i).HeaderText
            End If
        Next

        sql = sql & ColNames & ")"

        '~~> sql in quickwatch --> "CREATE TABLE Sheet1 (Sno VARCHAR,Name VARCHAR,Company VARCHAR)"

        '~~> Create the Worksheet and the column headers
        Dim objCommandInsert As OleDbCommand = New OleDbCommand(sql, olecon)
        objCommandInsert.ExecuteNonQuery()

        '~~> Loop through the DataGridView's rows and columns to get data
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            sql = ""

            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                sql = sql & "'" & Convert.ToString(DataGridView1.Rows(i).Cells(j).Value).Trim & "',"
            Next

            If sql.EndsWith(",") Then  sql = sql.Substring(0, sql.Length - 1)

            sql = "INSERT INTO " & SheetName & " (" & OnlyColumnNames & ") VALUES (" & sql & ")"

            '~~> sql in quickwatch for Row 1 -->"INSERT INTO Sheet1 (Sno,Name,Company) VALUES ('1','Sid','XPIZON')"

            objCommandInsert = New OleDbCommand(sql, olecon)
            objCommandInsert.ExecuteNonQuery()
        Next

        olecon.Close()

        MessageBox.Show("Done")
    End Sub
End Class