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: