Export DataTable to Excel Sheet using VB.Net


The following code will give you the method to export the data of DataTable to Excel sheet using Vb.Net

Imports Microsoft.Office.Interop.Excel

'You need to implement the above namespace for following code to work.

Private Sub ExportToExcel(ByVal objDT As DataTable)
        Dim Excel As Object = CreateObject("Excel.Application")
        Dim strFilename As String
        Dim intCol, intRow As Integer
Dim strPath as String="c:\"

        If Excel Is Nothing Then
            MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
        End If
            With Excel
                .SheetsInNewWorkbook = 1

                .cells(1, 1).value = "Heading" 'Heading of the excel file
                .cells(1, 1).EntireRow.Font.Bold = True            

                 Dim intI As Integer = 1
                 For intCol = 0 To objDT.Columns.Count - 1
                    .cells(2, intI).value = objDT.Columns(intCol).ColumnName
                    .cells(2, intI).EntireRow.Font.Bold = True
                    intI += 1
                intI = 3
                 Dim intK As Integer = 1
                 For intCol = 0 To objDT.Columns.Count - 1
                    intI = 3
                     For intRow = 0 To objDT.Rows.Count - 1
                 .Cells(intI, intK).Value = objDT.Rows(intRow).ItemArray(intCol)
                        intI += 1
                    intK += 1
                 If Mid$(strPath, strPath.Length, 1) <> "\" Then
                    strPath = strPath & "\"
                 End If
                strFilename = strPath & "Excel.xls"
             End With
            Excel = Nothing
            MsgBox("Data's are exported to Excel Succesfully in '" & strFilename & "'", MsgBoxStyle.Information)
        Catch ex As Exception
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
    End Sub

Export DataTable to Excel Sheet using VB.Net  (7184 Views)