How to export from DataGridView to excel using VB.net

By: Issac Viewed: 161 times  Printer Friendly Format    


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



Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

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

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

3. Hello,

Is there anyway the datagrid

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

4. Dim xlApp As Excel.Application
Dim

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

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

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

7. error :

Object reference not set to

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

8. Hello all..
can anyone please help me.. i

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

9. I have use the above code, Am getting the below er
View Tutorial          By: RameshKumar at 2011-07-25 05:02:12

10. add reference -> .net -> 'Microsoft.Office.
View Tutorial          By: Ravi at 2011-08-27 12:17:36

11. hi,
while exporting data i am getting error

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

12. Thanks for your code. Nice working but I have one
View Tutorial          By: Ravinder Kumar at 2012-01-02 18:01:21

13. Thank you... I have the same problem , I have date
View Tutorial          By: makoy at 2012-01-16 09:06:34

14. I have a problem , i cant see the Microsoft.Office
View Tutorial          By: Japz at 2012-01-16 09:51:11

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

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

17. I am using VS 2010 Express and MS Excel 2007 where
View Tutorial          By: Alphin at 2012-07-06 10:57:07

18. Nice article for beginners. You should also try th
View Tutorial          By: arun at 2013-01-16 12:53:03

19. sorry guys: how can i retrieve a report or large t
View Tutorial          By: aidid at 2013-05-04 06:07:42

20. Imports Excel = Microsoft.Office.Interop.Excel

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

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

22. ' REFERENCIA EN EL PROYECTO (Microsoft Excel 12.0
View Tutorial          By: Anonymous at 2015-04-21 03:10:41

23. i got error for below line

xlApp

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

24. when I exported the data to excel there is a value
View Tutorial          By: mizhelle at 2015-10-10 13:17:56

25. Hello,
Thanks for sharing this valuable inf

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

26. Hello,
Thanks for sharing this valuable inf

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