Programming Tutorials

How to export from database to excel using VB.net

By: Issac in VB.net Tutorials on 2009-02-21  

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