How to export from DataGridView to excel using VB.net

By: Issac  

For exporting data from Datagridview to Excel, connect the database and load data from the database to Datagridview and then create a new excel file and write the data from Datagridview to Excel file.

  vb.net_export_datagridview_toexcel.GIF

First step is to Load the Product table data to DataGridView, and create new Excel file and write the data from Datagridview to Excel file.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class




Archived Comments

1. Hello,
Thanks for sharing this valuable information.
I have a situation:
My Dat

View Tutorial          By: Miriam at 2016-07-27 14:26:29

2. Hello,
Thanks for sharing this valuable information.
I have a situation:
My Dat

View Tutorial          By: Miriam at 2016-07-27 14:02:53

3. when I exported the data to excel there is a value in the first column false. how can I delete this
View Tutorial          By: mizhelle at 2015-10-10 13:17:56

4. i got error for below line

xlApp = New Excel.ApplicationClass

Error

View Tutorial          By: Satheyaraaj at 2015-04-28 07:47:42

5. ' REFERENCIA EN EL PROYECTO (Microsoft Excel 12.0 Object Library)
' BUSCARLA EN PESTA&Ati

View Tutorial          By: Anonymous at 2015-04-21 03:10:41

6. Only first row is exported, help to export all the row
View Tutorial          By: Achyut at 2014-12-18 04:26:26

7. Imports Excel = Microsoft.Office.Interop.Excel
Above command not working i could not get offi

View Tutorial          By: Nilesh at 2014-12-01 13:45:25

8. sorry guys: how can i retrieve a report or large text from the data base?
View Tutorial          By: aidid at 2013-05-04 06:07:42

9. Nice article for beginners. You should also try the below link for export data to excel in Asp.Net.
View Tutorial          By: arun at 2013-01-16 12:53:03

10. I am using VS 2010 Express and MS Excel 2007 wherein I am trying to export data from the DataGridVie
View Tutorial          By: Alphin at 2012-07-06 10:57:07

11. Thanks very much ,ur code more usefull me thank u babaree
View Tutorial          By: Anand patil at 2012-04-20 10:03:31

12. what is DG_ENCOURS ?
View Tutorial          By: jonas at 2012-02-17 02:52:35

13. I have a problem , i cant see the Microsoft.Office.Interop. in add reference -> .net -> .. i a
View Tutorial          By: Japz at 2012-01-16 09:51:11

14. Thank you... I have the same problem , I have date and time in my datagridview, could not exported a
View Tutorial          By: makoy at 2012-01-16 09:06:34

15. Thanks for your code. Nice working but I have one problem. I have time in DataGridview , In that con
View Tutorial          By: Ravinder Kumar at 2012-01-02 18:01:21

16. hi,
while exporting data i am getting error on below statement saying "Null Reference Ex

View Tutorial          By: akash at 2011-12-22 07:28:05

17. add reference -> .net -> 'Microsoft.Office.Interop.Excel
then import Microsoft.Office.

View Tutorial          By: Ravi at 2011-08-27 12:17:36

18. I have use the above code, Am getting the below error

Unable to cast COM Object of ty

View Tutorial          By: RameshKumar at 2011-07-25 05:02:12

19. Hello all..
can anyone please help me.. i just got this error..

xlWorkBook =

View Tutorial          By: Christoven at 2011-06-15 05:55:16

20. error :

Object reference not set to an instance of an object.

View Tutorial          By: Ramnath at 2011-05-30 04:01:32

21. Thanks to OP and Zarno for finishing off this code
View Tutorial          By: Soapy Balls at 2011-05-27 05:26:13

22. It too good code
View Tutorial          By: Srinivas at 2011-02-20 03:58:19

23. Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xl

View Tutorial          By: Zarno at 2010-12-24 07:11:19

24. Hello,

Is there anyway the datagridview can export the column name too?


View Tutorial          By: Douglas at 2010-05-13 22:10:09

25. It's very helpful to me... Thanks...
View Tutorial          By: Nicks Patel at 2010-03-24 23:37:04

26. Nista ja tu ne bi dirao
View Tutorial          By: boris tadic at 2009-11-13 03:38:05


Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial