How to export from database to excel using

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)
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)
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet

        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) = _




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

    Private Sub releaseObject(ByVal obj As Object)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Class

Archived Comments

1. ________
View Tutorial          By: www mp3 juice at 2017-07-28 04:03:55

2. Billyweimb
View Tutorial          By: Billyweimb at 2017-05-15 15:02:36

3. Girls wanted, no matter where you live! - to high paid job.
If you are young and daring women

View Tutorial          By: M_Coleman at 2017-04-03 06:39:46

4. Hi Sir Can you help me, I tried your code and i worked well! but when i'm exporting a large number o
View Tutorial          By: Carl at 2016-11-14 05:37:31

5. I have an error. It says that cannot find the column 21. 1 more tan the last one. How can i add a br
View Tutorial          By: Alan at 2016-02-29 20:40:25

6. Hello
thanks for nice tutorial. I have one application, installed on client machine . when i

View Tutorial          By: Chanchal Prajapat at 2015-02-09 06:10:42

7. Thanks For uploading , Can you please guide , How can i create multiple excel sheet on single button
View Tutorial          By: Manish Choudhari at 2014-10-14 09:58:05

8. you do the same as after
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

View Tutorial          By: John McCode at 2013-05-24 07:57:31

9. you do the same as after.

Private Sub Button1_Click(ByVal sender As System.Object, By

View Tutorial          By: John McCode at 2013-05-24 07:56:04

10. Your script works beautifully. Is there a way to include the header on the first row of the workshee
View Tutorial          By: JW at 2013-05-08 22:22:44

11. I was trying to copy your coding and execute it, but no data was extracted out in excel.

View Tutorial          By: Guest at 2012-01-02 04:08:21

12. Imports Excel = Microsoft.Office.Interop.Excel
shows error. Which is the dll file to add as r

View Tutorial          By: Sinu.S at 2010-10-09 03:55:38

Most Viewed Articles (in )

Latest Articles (in

Comment on this tutorial