How to Export Data to Excel in VB.NET

• Download db - 292 B
• Download source - 16.5 KB

Introduction

There are many DLL files that help you to export data to Excel but always there are some problems with them that causes error or you should pay some money to use them with all features.

I want to show that how we can use Microsoft Office original DLL file in order to export data to standard Excel file. In this tip, we use Microsoft.Office.Interop.Excel.dll that is in your PC when you install Microsoft Office.

Background

I use Microsoft.Office.Interop.Excel namespace. The way I use is very simple. If you want to learn more about it, you can see Microsoft.Office.Interop.Excel namespace page in Microsoft website.

Using the Code

This is all the code you need to do in your form. First of all, you need to declare references of objects we want to use. The important thing is about using Microsoft.Office.Interop.Excel.dll. You should first install the latest version of Microsoft Office, then set the reference to the Microsoft.Office.Interop.Excel.

There are two zip files that you can download and run. If you have any favorite query, use it. But if you don't have any database or query, don't worry you can download Create_Pubs_DB.zip and run it in SQL Server. After that, you can extract the source and run it.

'References that we need
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO.Directory
Imports Microsoft.Office.Interop.Excel 'Before you add this reference to your project,
' you need to install Microsoft Office and find last version of this file.
Imports Microsoft.Office.Interop
Public Class Form1
 
    Private Sub btnBrowse_Click(sender As System.Object, _
    	e As System.EventArgs) Handles btnBrowse.Click
        'Initialize the objects before use
        Dim dataAdapter As New SqlClient.SqlDataAdapter()
        Dim dataSet As New DataSet
        Dim command As New SqlClient.SqlCommand
        Dim datatableMain As New System.Data.DataTable()
        Dim connection As New SqlClient.SqlConnection
 
        'Assign your connection string to connection object
        connection.ConnectionString = "Data Source=.;_
        Initial Catalog=pubs;Integrated Security=True"
        command.Connection = connection
        command.CommandType = CommandType.Text
        'You can use any command select
        command.CommandText = "Select * from Authors"
        dataAdapter.SelectCommand = command
 

        Dim f As FolderBrowserDialog = New FolderBrowserDialog
        Try
            If f.ShowDialog() = DialogResult.OK Then
                'This section help you if your language is not English.
                System.Threading.Thread.CurrentThread.CurrentCulture = _
                System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CreateObject("Excel.Application")
                oBook = oExcel.Workbooks.Add(Type.Missing)
                oSheet = oBook.Worksheets(1)
 
                Dim dc As System.Data.DataColumn
                Dim dr As System.Data.DataRow
                Dim colIndex As Integer = 0
                Dim rowIndex As Integer = 0
 
                'Fill data to datatable
                connection.Open()
                dataAdapter.Fill(datatableMain)
                connection.Close()
 

                'Export the Columns to excel file
                For Each dc In datatableMain.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(1, colIndex) = dc.ColumnName
                Next
 
                'Export the rows to excel file
                For Each dr In datatableMain.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In datatableMain.Columns
                        colIndex = colIndex + 1
                        oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                    Next
                Next
 
                'Set final path
                Dim fileName As String = "\ExportedAuthors" + ".xls"
                Dim finalPath = f.SelectedPath + fileName
                txtPath.Text = finalPath
                oSheet.Columns.AutoFit()
                'Save file in final path
                oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
 
                'Release the objects
                ReleaseObject(oSheet)
                oBook.Close(False, Type.Missing, Type.Missing)
                ReleaseObject(oBook)
                oExcel.Quit()
                ReleaseObject(oExcel)
                'Some time Office application does not quit after automation: 
                'so i am calling GC.Collect method.
                GC.Collect()
 
                MessageBox.Show("Export done successfully!")
 
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
        End Try
    End Sub
 
    Private Sub ReleaseObject(ByVal o As Object)
        Try
            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
            End While
        Catch
        Finally
            o = Nothing
        End Try
    End Sub
End Class