Click here to Skip to main content
11,582,850 members (70,542 online)
Click here to Skip to main content

Tagged as

Export SQL Data to Excel using VB.NET

, 8 May 2013 CPOL 39.4K 8
Rate this:
Please Sign up or sign in to vote.
How to export SQL data to Excel using VB.NET

Step # 01

Open Visual Studio and click project –add references.

Choose these references:

  • Microsoft Excel 12.0 object library and
  • Microsoft Excel 14.0 object library

Step # 02

Now create a new Excel file in your drive:

Step # 03

Now code in Visual Studio:

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class excel
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles Button1.Click
            'Make Connection ' Ammar
            Dim cnn As DataAccess = New DataAccess(CONNECTION_STRING)
            ' Variable ' Ammar
            Dim i, j As Integer
            'Excel WorkBook object ' Ammar
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            ' Sheet Name or Number ' Ammar
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            ' Sql QUery ' Ammar
            '  xlWorkBook.Sheets.Select("A1:A2")

            Dim sql As String = "SELECT * FROM EMP"
            ' SqlAdapter
            Dim dscmd As New SqlDataAdapter(sql, cnn.ConnectionString)
            ' DataSet
            Dim ds As New DataSet
            xlWorkSheet.Cells(1, 1).Value = "First Name"
            xlWorkSheet.Cells(1, 2).Value = "Last Name"
            xlWorkSheet.Cells(1, 3).Value = "Full Name"
            xlWorkSheet.Cells(1, 4).Value = "Salary"
            ' SQL Table Transfer to Excel
              For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    ' this i change to header line cells >>>
                    xlWorkSheet.Cells(i + 3, j + 1) = _
            'HardCode in Excel sheet
            ' this i change to footer line cells  >>>
           xlWorkSheet.Cells(i + 3, 7) = "Total"
            xlWorkSheet.Cells.Item(i + 3, 8) = "=SUM(H2:H18)"
            ' Save as path of excel sheet
            'Msg Box of Excel Sheet Path
            MsgBox("You can find the file D:\vbexcel.xlsx")
        Catch ex As Exception

        End Try

    End Sub
    ' Function of Realease Object in Excel Sheet
    Private Sub releaseObject(ByVal obj As Object)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Class

Finally, open your file and your data has been exported:


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


About the Author

Pakistan Pakistan
No Biography provided

You may also be interested in...

Comments and Discussions

SuggestionThere also another efficient way of exporting data to excel Pin
Senthil Sambandam14-Nov-14 19:57
professionalSenthil Sambandam14-Nov-14 19:57 
GeneralMy vote of 1 Pin
Member 108325475-Jun-14 8:15
memberMember 108325475-Jun-14 8:15 
Questionnice article..thank you Pin
mazharkhan12327-May-14 21:57
membermazharkhan12327-May-14 21:57 
SuggestionNote Pin
Member 474289526-Sep-13 9:49
memberMember 474289526-Sep-13 9:49 
GeneralMy vote of 5 Pin
Mihai MOGA13-Jun-13 20:52
memberMihai MOGA13-Jun-13 20:52 
GeneralMy vote of 2 Pin
Fabricio Dos Santos Antunes11-May-13 3:40
memberFabricio Dos Santos Antunes11-May-13 3:40 
GeneralRe: My vote of 2 Pin
saimm12-May-13 23:45
membersaimm12-May-13 23:45 
GeneralRe: My vote of 2 Pin
Fabricio Dos Santos Antunes9-Aug-13 3:59
memberFabricio Dos Santos Antunes9-Aug-13 3:59 
QuestionPerformance Pin
Sung Shing9-May-13 22:27
memberSung Shing9-May-13 22:27 
AnswerRe: Performance Pin
saimm12-May-13 23:47
membersaimm12-May-13 23:47 

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.150603.1 | Last Updated 8 May 2013
Article Copyright 2013 by saimm
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid