How to Export Data to Excel in VB.NET
I want to show how we can export data from data table to Excel simply.
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