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

Tagged as

Export SQL Data to Excel using VB.NET

, 8 May 2013 CPOL
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
‘ADD BUTTON
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles Button1.Click
        Try
            '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
            dscmd.Fill(ds)
‘COLUMN NAME ADD IN EXCEL SHEET OR HEADING 
            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
                'Column
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    ' this i change to header line cells >>>
                    xlWorkSheet.Cells(i + 3, j + 1) = _
                    ds.Tables(0).Rows(i).Item(j)
                Next
            Next
            '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
            xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            '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)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

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

License

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

Share

About the Author

saimm

Pakistan Pakistan
No Biography provided

Comments and Discussions

 
SuggestionThere also another efficient way of exporting data to excel PinprofessionalSenthil Sambandam14-Nov-14 20:57 
GeneralMy vote of 1 PinmemberMember 108325475-Jun-14 9:15 
Questionnice article..thank you Pinmembermazharkhan12327-May-14 22:57 
SuggestionNote PinmemberMember 474289526-Sep-13 10:49 
GeneralMy vote of 5 PinmemberMihai MOGA13-Jun-13 21:52 
GeneralMy vote of 2 PinmemberFabricio Dos Santos Antunes11-May-13 4:40 
GeneralRe: My vote of 2 Pinmembersaimm13-May-13 0:45 
GeneralRe: My vote of 2 PinmemberFabricio Dos Santos Antunes9-Aug-13 4:59 
QuestionPerformance PinmemberSung Shing9-May-13 23:27 
AnswerRe: Performance Pinmembersaimm13-May-13 0: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
Web02 | 2.8.141220.1 | Last Updated 8 May 2013
Article Copyright 2013 by saimm
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid