How to Export Dataset to Excel Using Microsoft.Interop VB.Net & SQL Server

Hi Jack

This post is a continuation of the previous post 

How to Export Dataset to Excel Using XML VB.Net & SQL Server 

this time we will use Microsoft.Interop to export from the dataset to excel file.

Import NameSpace

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO.Directory
Imports Microsoft.Office.Interop

if you can't import namespace, visit this link to add reference into your solution.

Create Form

Load data into Dataset

Private Function load_data_into_dataset() As DataSet
        Dim value As New DataSet
        Using iKon = New SqlConnection(sql_connection_string)
            If iKon.State = ConnectionState.Open Then iKon.Close()
            iKon.Open()
            Using iCmd = New SqlCommand("Select * from cars", iKon)
                Using adapter = New SqlDataAdapter
                    adapter.SelectCommand = iCmd
                    adapter.Fill(value)
                End Using
            End Using
        End Using
        Return value
End Function

Load Data Into Datagridview

 Private Sub Btn_load_dataset_Click(sender As Object, e As EventArgs) Handles Btn_load_dataset.Click
        DataGridView1.DataSource = load_data_into_dataset.Tables(0)
 End Sub

Export to Excel Using Microsoft.Interop.Excel

Private Sub export_database_to_excel_using_office_interop(ByVal dataset As DataSet)
        Dim excel_app As Excel.Application
        Dim excel_book As Excel.Workbook
        Dim excel_sheet As Excel.Worksheet

        excel_app = CreateObject("excel.application")
        excel_book = excel_app.Workbooks.Add(Type.Missing)
        excel_sheet = excel_book.Worksheets(1)

        Dim col_index As Integer = 0
        Dim row_index As Integer = 0

        'create columns header
        For Each col As DataColumn In dataset.Tables(0).Columns
            col_index = col_index + 1
            excel_sheet.Cells(1, col_index) = col.ColumnName
        Next

        'insert row
        For Each row As DataRow In dataset.Tables(0).Rows
            row_index = row_index + 1
            col_index = 0
            For Each col As DataColumn In dataset.Tables(0).Columns
                col_index = col_index + 1
                excel_sheet.Cells(row_index + 1, col_index) = row.Item(col)
            Next
        Next

        Dim file_name As String = Application.StartupPath & "\Export" & Format(DateTime.Now, "ddMMyyHHmmss") & ".xls"
        excel_sheet.Columns.AutoFit()
        excel_book.SaveAs(file_name, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                          Excel.XlSaveAsAccessMode.xlExclusive,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

        release_object(excel_sheet)
        excel_book.Close(False, Type.Missing, Type.Missing)
        release_object(excel_book)
        excel_app.Quit()
        release_object(excel_app)
        GC.Collect()
        MsgBox("success")
        System.Diagnostics.Process.Start(file_name)
End Sub

Private Sub release_object(ByVal obj As Object)
        Try
            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0)
            End While
        Catch ex As Exception
        Finally
            obj = Nothing
        End Try
End Sub

Private Sub btn_export_using_interop_Click(sender As Object, e As EventArgs) Handles btn_export_using_interop.Click
        export_database_to_excel_using_office_interop(load_data_into_dataset)
End Sub

Dataset successfully exported into excel using Microsoft.Interop without using any format.

if you want to use this article in your blog please also include this link as a source. thank you.