{"id":125,"date":"2013-03-28T15:20:27","date_gmt":"2013-03-28T20:20:27","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=125"},"modified":"2013-03-28T15:20:27","modified_gmt":"2013-03-28T20:20:27","slug":"encodedna-excel-export-data-to-excel-in-vb-net-with-auto-format-technique","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/03\/28\/encodedna-excel-export-data-to-excel-in-vb-net-with-auto-format-technique\/","title":{"rendered":"Export data to Excel in Vb.Net with Auto format technique"},"content":{"rendered":"

Data can be exported to an excel sheet in Vb.Net using Microsoft.Office.Interop.Excel namespace. Though it\u2019s a classic example, exporting data into an excel file can be useful when you want to share your data in a well formatted way. Ms-Excel 2003 had an in built feature which allowed users to \u201cAuto format\u201d the entire sheet with ease. This feature is still available in the .Net framework for developers to work with excel formatting.<\/p>\n

The \u201cAuto Format<\/strong>\u201d function in the application will automatically format the sheet immediately after the entire data is export into the excel sheet.<\/p>\n

Note:\u00a0ExcelAutoFormat.xlRangeAutoFormatList3<\/strong>, comes with a variety of formats which you should try.<\/p>\n

Before starting the designing of the application, we need to add a \u201cCOM<\/strong>\u201d library from the\u00a0reference<\/strong>\u00a0window. In the .Net IDE, click \u201cProject\u201d from the top menu list and select \u201cAdd Reference\u2026\u201d In the \u201cAdd Reference\u201d window, select \u201cCOM\u201d tab and find \u201cMicrosoft Excel 12.0 Object Library\u201d from the list. Select it and click OK.<\/p>\n

We are ready to design our application.<\/p>\n

\"form\"<\/p>\n

<\/div>\n
Form Design (Form1.vb)<\/div>\n

Add 3 controls on the form. A CheckListBox, Button and CheckBox control.<\/p>\n

Ref: Create the\u00a0Employee Details<\/a>\u00a0table in your database.<\/p>\n

Code (Form1.vb)<\/div>\n
\n
Option Explicit On\n\nImports System.Data.SqlClient                                           ' FOR SQL CONNECTION AND COMMAND.\nImports Excel = Microsoft.Office.Interop.Excel                                  ' EXCEL APPLICATION.\nImports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat      ' TO AUTOFORMAT THE SHEET.\n\nPublic Class Form1\n    Private myConn As SqlConnection\n    Private sqComm As SqlCommand\n\n    Dim ds As New DataSet\n    Dim SqlAdapter As System.Data.SqlClient.SqlDataAdapter\n\n    Dim sSql As String = \"\"                         ' SQL QUERIES.\n    Dim iRowCnt As Integer = 0                      ' JUST A COUNTER.\n\n    ' FORM LOAD.\n    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load\n        If setConn() Then\n            sSql = \"SELECT EmpName FROM dbo.EmployeeDetails\"\n\n            Try\n                SqlAdapter = New System.Data.SqlClient.SqlDataAdapter(sSql, myConn)\n                SqlAdapter.Fill(ds, \"EmpDet\")\n\n                FillListbox()\n\n            Catch ex As Exception\n                MessageBox.Show(ex.Message, \"Connection Error\", MessageBoxButtons.OK, MessageBoxIcon.Error)\n            Finally\n                sSql = \"\"\n            End Try\n        End If\n    End Sub\n\n    Private Sub btExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _\n            Handles btExport.Click\n        ' EXPORT EMPLOYEE DETAILS TO EXCEL.\n\n        Cursor.Current = Cursors.WaitCursor\n        Dim sEmpList As String = \"\"\n\n        ' FIRST CHECK IF ANY EMPLOYEES ARE SELECTED.\n        For iCnt = 0 To lstEmpDet.CheckedItems.Count - 1\n            If Trim(sEmpList) = \"\" Then\n                sEmpList = \"'\" & lstEmpDet.CheckedItems(iCnt) & \"'\"\n            Else\n                sEmpList = sEmpList & \", '\" & lstEmpDet.CheckedItems(iCnt) & \"'\"\n            End If\n        Next\n\n        ' QUERY TO FETCH RECORDS FROM THE DATABASE.\n        sSql = \"SELECT *FROM dbo.EmployeeDetails \" & _\n            IIf(Trim(sEmpList) <> \"\", \" WHERE EmpName IN (\" & Trim(sEmpList) & \")\", \"\")\n\n        Dim sdrGetEmpDetails As SqlDataReader\n        sdrGetEmpDetails = GetDataReader(sSql)\n\n        Dim xlAppToUpload As New Excel.Application\n        xlAppToUpload.Workbooks.Add()\n\n        Dim xlWorkSheetToUpload As Excel.Worksheet\n        xlWorkSheetToUpload = xlAppToUpload.Sheets(\"Sheet1\")\n\n        ' SHOW EXCEL APPLICATION. (ALSO, SET IT TRUE WHEN THE DATA IS EXPORTED TO THE EXCEL SHEET.) \n        xlAppToUpload.Visible = True                    \n\n        Try\n            If sdrGetEmpDetails.HasRows Then\n                iRowCnt = 4                             ' ROW AT WHICH PRINT WILL START.\n\n                With xlWorkSheetToUpload\n                    ' SHOW AN HEADER.\n                    .Cells(1, 1).value = \"Employee Details\" : .Cells(1, 1).FONT.NAME = \"Calibri\"\n                    .Cells(1, 1).Font.Bold = True : .Cells(1, 1).Font.Size = 20\n\n                    .Range(\"A1:H1\").MergeCells = True   ' MERGE CELLS OF THE HEADER.\n\n                    ' SHOW COLUMNS ON THE TOP.\n                    .Cells(iRowCnt - 1, 1).value = \"Employee Name\"\n                    .Cells(iRowCnt - 1, 2).value = \"Mobile\"\n                    .Cells(iRowCnt - 1, 3).value = \"PresentAddress\"\n                    .Cells(iRowCnt - 1, 4).value = \"Area\"\n                    .Cells(iRowCnt - 1, 5).value = \"City\"\n                    .Cells(iRowCnt - 1, 6).value = \"Country\"\n                    .Cells(iRowCnt - 1, 7).value = \"Qualification\"\n                    .Cells(iRowCnt - 1, 8).value = \"Email Address\"\n\n                    While sdrGetEmpDetails.Read\n                        .Cells(iRowCnt, 1).value = sdrGetEmpDetails.Item(\"EmpName\")\n                        .Cells(iRowCnt, 2).value = sdrGetEmpDetails.Item(\"Mobile\")\n                        .Cells(iRowCnt, 3).value = sdrGetEmpDetails.Item(\"PresentAddress\")\n                        .Cells(iRowCnt, 4).value = sdrGetEmpDetails.Item(\"Area\")\n                        .Cells(iRowCnt, 5).value = sdrGetEmpDetails.Item(\"City\")\n                        .Cells(iRowCnt, 6).value = sdrGetEmpDetails.Item(\"Country\")\n                        .Cells(iRowCnt, 7).value = sdrGetEmpDetails.Item(\"Qualification\")\n                        .Cells(iRowCnt, 8).value = sdrGetEmpDetails.Item(\"Email\")\n\n                        iRowCnt = iRowCnt + 1\n                    End While\n\n                End With\n\n                ' FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.\n                xlAppToUpload.ActiveCell.Worksheet.Cells(4, 1).AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3)\n\n            End If\n        Catch ex As Exception\n            MessageBox.Show(ex.Message)\n        Finally\n            xlAppToUpload = Nothing\n            sdrGetEmpDetails.Close() : sdrGetEmpDetails = Nothing\n        End Try\n\n        Cursor.Current = Cursors.Default\n    End Sub\n\n    'FILL THE \"CHECKLISTBOX\" WITH EMPLOYEE NAMES\n    Private Sub FillListbox()\n        Dim row As DataRow\n\n        lstEmpDet.Items.Clear()\n\n        For Each row In ds.Tables(\"EmpDet\").Rows\n            lstEmpDet.Items.Add(ds.Tables(\"EmpDet\").Rows(iRowCnt).Item(0))\n            iRowCnt = iRowCnt + 1\n        Next\n    End Sub\n\n    ' CALL THIS FUNCTION ON FORM LOAD TO SET THE DATABASE CONNECTION.\n    Private Function setConn() As Boolean\n        Try\n            s_ConnString = \"Data Source=dna;Persist Security Info=False;\" & _\n                \"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;\"\n\n            myConn.Open()\n\n            sqComm = New SqlCommand\n        Catch ex As Exception\n            MessageBox.Show(\"Error while connection database.\", \"Connection Error\", MessageBoxButtons.OK, _\n                MessageBoxIcon.Error)\n            Return False\n        End Try\n\n        Return True\n    End Function\n\n    ' SQL DATA READER TO FETCH DATA.\n    Private Function GetDataReader(Optional ByVal sQuery As String = \"\") As SqlDataReader\n        Try\n            sqComm.Connection = myConn\n            sqComm.CommandText = sQuery\n            sqComm.ExecuteNonQuery()\n            GetDataReader = sqComm.ExecuteReader\n            sqComm.Dispose()\n        Catch ex As Exception\n            MessageBox.Show(ex.Message)\n        End Try\n    End Function\n\n    ' SELECT \/ UNSELECT ALL EMPLOYEES FROM THE \"CHECKLISTBOX\".\n    Private Sub chkAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _\n            Handles chkAll.CheckedChanged\n\n        Cursor.Current = Cursors.WaitCursor\n        If chkAll.Checked Then\n            For iCnt = 0 To lstEmpDet.Items.Count - 1\n                lstEmpDet.SetItemCheckState(iCnt, CheckState.Checked)\n                lstEmpDet.SetSelected(iCnt, True)\n            Next\n            chkAll.Text = \"Unselect All\"\n        Else\n            For iCnt = 0 To lstEmpDet.Items.Count - 1\n                lstEmpDet.SetItemCheckState(iCnt, CheckState.Unchecked)\n                lstEmpDet.SetSelected(iCnt, False)\n            Next\n            chkAll.Text = \"Select all from the list\"\n        End If\n        Cursor.Current = Cursors.Default\n    End Sub\nEnd Class<\/pre>\n<\/div>\n
The final result.<\/strong><\/p>\n
\"output\"<\/p>\n
\"employee<\/p>\n
\n
Overview.<\/strong><\/p>\n
\n
Using few controls we are able to export data from our database to an excel sheet with a decent looking format. The \u201cAuto Format\u201d function comes with many more formatting options, which makes the sheet looks very professional.<\/p>\n
Extend the\u00a0bold<\/strong>\u00a0part of the below highlighted code to see the list of formats available in the .Net framework.<\/p>\n
xlAppToUpload.ActiveCell.Worksheet.Cells(4, 1).AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3)<\/strong><\/p>\n
\"employee<\/p>\n
 <\/p>\n
http:\/\/www.encodedna.com\/2012\/12\/export-data-to-excel.htm<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Data can be exported to an excel sheet in Vb.Net using Microsoft.Office.Interop.Excel namespace. Though it\u2019s a classic example, exporting data into an excel file can… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-125","post","type-post","status-publish","format-standard","hentry","category-vb-net"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=125"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/125\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}