Export data to Excel in Windows Forms with Auto format technique

Option Explicit On

Imports System.Data.SqlClient    ' For SQL Server connection and command.
Imports Excel = Microsoft.Office.Interop.Excel      ' Excel application.
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat    ' For automating 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 = ""             ' For SQL queries.
    Dim iRowCnt As Integer = 0          ' Its just a counter.

    ' FORM LOAD.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If setConn() Then   ' Set database connection.
            sSql = "SELECT EmpName FROM dbo.EmployeeDetails"    ' Query the table.

            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

    ' FILL THE "CHECKLISTBOX" WITH EMPLOYEE NAMES.
        ' YOU WILL HAVE OPTIONS TO EITHER EXPORT ALL EMPLOYEES DATA OR SELECTED DATA.
    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

    ' Export employee details to Excel worksheet.
    Private Sub btExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btExport.Click
        
        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 : xlWorkSheetToUpload = Nothing 
            sdrGetEmpDetails.Close() : sdrGetEmpDetails = Nothing
        End Try

        Cursor.Current = Cursors.Default
    End Sub
    
    ' 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

' 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