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.
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
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net
Exception Filtering in the Catch Block in VB.net
Using Multiple Catch Statements in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Changes in Controls from VB6 to VB.net
Unstructured Exception Handling in VB.net