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

Data can be exported to an excel sheet in a Windows Forms Application using Vb.Net. The namespace Interop in .net provides the necessary functions. If you have worked Excel then you must have come one of its features called the “AutoFormat”. If selected, it automatically formats the entire sheet. This popular feature is also available in the .Net framework and here in this article I’ll show you how to export data to excel using AutoFormat technique using Vb.Net.

Click this link, if you are a C# Developer.

To get access to the Excel AutoFormat features, we must first import the necessary namespaces that I have mentioned.

Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat

The Auto Format function in the application will automatically format the sheet immediately after the entire data is exported into the excel sheet.

Note: In this example, I am using the format option this ExcelAutoFormat.xlRangeAutoFormatList3. You must try other options too.

Related: Export Data from a Paging Enabled GridView to Excel in Asp.Net

Before you start designing the application, you need to add a COM library from the reference window. In the .Net IDE, click Project from the top menu list and select Add Reference… In the Add Reference window, select the COM tab and find Microsoft Excel 12.0 Object Library from the list. Select it and click OK.

Now, you are ready to design your application.

Form

Form Design (Form1.vb)

Add 3 controls on the form. A CheckListBox, Button and CheckBox control.

Ref: Create the Employee Details table in your SQL Server database.

Code (Form1.vb)
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           ' SET THE DATABASE CONNECTION.
            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

    ' 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.
    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

Set DataBase Connection

' 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

Also Read: Export data to Excel in Asp.Net - C# and Vb.Net

Final Output

Export Data to Excel in Windows Forms Application using Vb.Net

Export Data to Excel in Windows Forms Application using Vb.Net

Click this link, if you are a C# Developer.

Overview

Using few controls we are able to export data from our database to an excel sheet with a decent looking format. The Auto Format function comes with many more formatting options, which makes the sheet looks very professional.

Extend the bold part of the below highlighted code to see the list of formats available in the .Net framework.

xlAppToUpload.ActiveCell.Worksheet.Cells(4, 1).AutoFormat(
ExcelAutoFormat.xlRangeAutoFormatList3)

Excel AutoFormat Options in Windows Forms

That's it. Thanks for reading.

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: