Export data to Excel in Vb.Net with Auto format technique

Option Explicit On

Imports System.Data.SqlClient                                           ' FOR SQL CONNECTION AND COMMAND.
Imports Excel = Microsoft.Office.Interop.Excel                                  ' EXCEL APPLICATION.
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat      ' TO AUTOFORMAT THE SHEET.

Public Class Form1
    Private myConn As SqlConnection
    Private sqComm As SqlCommand

    Dim ds As New DataSet
    Dim SqlAdapter As System.Data.SqlClient.SqlDataAdapter

    Dim sSql As String = ""                         ' SQL QUERIES.
    Dim iRowCnt As Integer = 0                      ' JUST A COUNTER.

    ' FORM LOAD.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If setConn() Then
            sSql = "SELECT EmpName FROM dbo.EmployeeDetails"

            Try
                SqlAdapter = New System.Data.SqlClient.SqlDataAdapter(sSql, myConn)
                SqlAdapter.Fill(ds, "EmpDet")

                FillListbox()

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                sSql = ""
            End Try
        End If
    End Sub

    Private Sub btExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
            Handles btExport.Click
        ' EXPORT EMPLOYEE DETAILS TO EXCEL.

        Cursor.Current = Cursors.WaitCursor
        Dim sEmpList As String = ""

        ' FIRST CHECK IF ANY EMPLOYEES ARE SELECTED.
        For iCnt = 0 To lstEmpDet.CheckedItems.Count - 1
            If Trim(sEmpList) = "" Then
                sEmpList = "'" & lstEmpDet.CheckedItems(iCnt) & "'"
            Else
                sEmpList = sEmpList & ", '" & lstEmpDet.CheckedItems(iCnt) & "'"
            End If
        Next

        ' QUERY TO FETCH RECORDS FROM THE DATABASE.
        sSql = "SELECT *FROM dbo.EmployeeDetails " & _
            IIf(Trim(sEmpList) <> "", " WHERE EmpName IN (" & Trim(sEmpList) & ")", "")

        Dim sdrGetEmpDetails As SqlDataReader
        sdrGetEmpDetails = GetDataReader(sSql)

        Dim xlAppToUpload As New Excel.Application
        xlAppToUpload.Workbooks.Add()

        Dim xlWorkSheetToUpload As Excel.Worksheet
        xlWorkSheetToUpload = xlAppToUpload.Sheets("Sheet1")

        ' SHOW EXCEL APPLICATION. (ALSO, SET IT TRUE WHEN THE DATA IS EXPORTED TO THE EXCEL SHEET.) 
        xlAppToUpload.Visible = True                    

        Try
            If sdrGetEmpDetails.HasRows Then
                iRowCnt = 4                             ' ROW AT WHICH PRINT WILL START.

                With xlWorkSheetToUpload
                    ' SHOW AN HEADER.
                    .Cells(1, 1).value = "Employee Details" : .Cells(1, 1).FONT.NAME = "Calibri"
                    .Cells(1, 1).Font.Bold = True : .Cells(1, 1).Font.Size = 20

                    .Range("A1:H1").MergeCells = True   ' MERGE CELLS OF THE HEADER.

                    ' SHOW COLUMNS ON THE TOP.
                    .Cells(iRowCnt - 1, 1).value = "Employee Name"
                    .Cells(iRowCnt - 1, 2).value = "Mobile"
                    .Cells(iRowCnt - 1, 3).value = "PresentAddress"
                    .Cells(iRowCnt - 1, 4).value = "Area"
                    .Cells(iRowCnt - 1, 5).value = "City"
                    .Cells(iRowCnt - 1, 6).value = "Country"
                    .Cells(iRowCnt - 1, 7).value = "Qualification"
                    .Cells(iRowCnt - 1, 8).value = "Email Address"

                    While sdrGetEmpDetails.Read
                        .Cells(iRowCnt, 1).value = sdrGetEmpDetails.Item("EmpName")
                        .Cells(iRowCnt, 2).value = sdrGetEmpDetails.Item("Mobile")
                        .Cells(iRowCnt, 3).value = sdrGetEmpDetails.Item("PresentAddress")
                        .Cells(iRowCnt, 4).value = sdrGetEmpDetails.Item("Area")
                        .Cells(iRowCnt, 5).value = sdrGetEmpDetails.Item("City")
                        .Cells(iRowCnt, 6).value = sdrGetEmpDetails.Item("Country")
                        .Cells(iRowCnt, 7).value = sdrGetEmpDetails.Item("Qualification")
                        .Cells(iRowCnt, 8).value = sdrGetEmpDetails.Item("Email")

                        iRowCnt = iRowCnt + 1
                    End While

                End With

                ' FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                xlAppToUpload.ActiveCell.Worksheet.Cells(4, 1).AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3)

            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            xlAppToUpload = Nothing
            sdrGetEmpDetails.Close() : sdrGetEmpDetails = Nothing
        End Try

        Cursor.Current = Cursors.Default
    End Sub

    'FILL THE "CHECKLISTBOX" WITH EMPLOYEE NAMES
    Private Sub FillListbox()
        Dim row As DataRow

        lstEmpDet.Items.Clear()

        For Each row In ds.Tables("EmpDet").Rows
            lstEmpDet.Items.Add(ds.Tables("EmpDet").Rows(iRowCnt).Item(0))
            iRowCnt = iRowCnt + 1
        Next
    End Sub

    ' CALL THIS FUNCTION ON FORM LOAD TO SET THE DATABASE CONNECTION.
    Private Function setConn() As Boolean
        Try
            s_ConnString = "Data Source=dna;Persist Security Info=False;" & _
                "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;"

            myConn.Open()

            sqComm = New SqlCommand
        Catch ex As Exception
            MessageBox.Show("Error while connection database.", "Connection Error", MessageBoxButtons.OK, _
                MessageBoxIcon.Error)
            Return False
        End Try

        Return True
    End Function

    ' SQL DATA READER TO FETCH DATA.
    Private Function GetDataReader(Optional ByVal sQuery As String = "") As SqlDataReader
        Try
            sqComm.Connection = myConn
            sqComm.CommandText = sQuery
            sqComm.ExecuteNonQuery()
            GetDataReader = sqComm.ExecuteReader
            sqComm.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function

    ' SELECT / UNSELECT ALL EMPLOYEES FROM THE "CHECKLISTBOX".
    Private Sub chkAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
            Handles chkAll.CheckedChanged

        Cursor.Current = Cursors.WaitCursor
        If chkAll.Checked Then
            For iCnt = 0 To lstEmpDet.Items.Count - 1
                lstEmpDet.SetItemCheckState(iCnt, CheckState.Checked)
                lstEmpDet.SetSelected(iCnt, True)
            Next
            chkAll.Text = "Unselect All"
        Else
            For iCnt = 0 To lstEmpDet.Items.Count - 1
                lstEmpDet.SetItemCheckState(iCnt, CheckState.Unchecked)
                lstEmpDet.SetSelected(iCnt, False)
            Next
            chkAll.Text = "Select all from the list"
        End If
        Cursor.Current = Cursors.Default
    End Sub
End Class

Overview.