Click here to Skip to main content
Click here to Skip to main content

How to Export Data to Excel in VB.NET

, 17 Oct 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alireza Chegini
Web Developer
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
I like programming because when you design a software you create a thing that improve your life.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
Questionhow to format particular column to number PinmemberNirmala Saravanan14-Oct-14 4:04 
GeneralThanks PinmemberAbderrazzak Zahir1-Aug-14 9:35 
QuestionUnable to cast object of type 'System.Data.DataTable' to type 'Microsoft.Office.Interop.Excel.DataTable'. Pinmembermitchiee12267-Jul-14 20:26 
AnswerRe: Unable to cast object of type 'System.Data.DataTable' to type 'Microsoft.Office.Interop.Excel.DataTable'. PinmemberAlireza Chegini10-Jul-14 1:47 
Questionextended query [modified] Pinmemberp gosney15-May-14 6:53 
AnswerRe: extended query PinmemberAlireza Chegini10-Jul-14 1:49 
QuestionCannot create ActiveX component Pinmemberalbchinsh8-Mar-14 23:42 
Questionexcel export PinmemberMember 1048023428-Feb-14 23:47 
AnswerRe: excel export PinmemberAlireza Chegini1-Mar-14 1:13 
QuestionVariables i and j?? Pinmembermoordoom14-Nov-13 4:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 17 Oct 2013
Article Copyright 2013 by Alireza Chegini
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid