How to export from database to excel using VB.net
By: Issac
In this article we can see how to export from database to excel. We can load the data from a database to dataset and then create a new Excel file and write the data into the Excel file.
First step is to Load the Product table data to data set, next is to create a new Excel file and write the data from dataset to Excel file.
For i = 0 to ds.Tables (0).Rows.Count - 1
For j = 0 to ds.Tables (0).Columns.Count - 1
xlWorkSheet.Cells (i + 1, j + 1) = _
ds.Tables (0).Rows (i).Item (j)
Next
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
Dim i, j As Integer
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
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)
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next
xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
cnn.Close()
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
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
Subscribe to Tutorials
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
Archived Comments
1. Imports Excel = Microsoft.Office.Interop.Excel
View Tutorial By: Sinu.S at 2010-10-09 03:55:38
2. I was trying to copy your coding and execute it, b
View Tutorial By: Guest at 2012-01-02 04:08:21
3. Your script works beautifully. Is there a way to i
View Tutorial By: JW at 2013-05-08 22:22:44
4. you do the same as after.
Private S
View Tutorial By: John McCode at 2013-05-24 07:56:04
5. you do the same as after
Private Sub Button
View Tutorial By: John McCode at 2013-05-24 07:57:31
6. Thanks For uploading , Can you please guide , How
View Tutorial By: Manish Choudhari at 2014-10-14 09:58:05
7. Hello
thanks for nice tutorial. I have one
View Tutorial By: Chanchal Prajapat at 2015-02-09 06:10:42
8. I have an error. It says that cannot find the colu
View Tutorial By: Alan at 2016-02-29 20:40:25
9. Hi Sir Can you help me, I tried your code and i wo
View Tutorial By: Carl at 2016-11-14 05:37:31
10. Girls wanted, no matter where you live! - to high
View Tutorial By: M_Coleman at 2017-04-03 06:39:46
11. Billyweimb
View Tutorial By: Billyweimb at 2017-05-15 15:02:36
12. ________
View Tutorial By: www mp3 juice at 2017-07-28 04:03:55