Export DataTable to Excel Sheet using VB.Net

By Shailendrasinh Parmar

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)
             Return
        End If
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                 .Workbooks.Add()
                 .Worksheets(1).Select()

                .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
                 Next
                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
                     Next
                    intK += 1
                 Next
                 If Mid$(strPath, strPath.Length, 1) <> "\" Then
                    strPath = strPath & "\"
                 End If
                strFilename = strPath & "Excel.xls"
                 .ActiveCell.Worksheet.SaveAs(strFilename)
             End With
           System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Data's are exported to Excel Succesfully in '" & strFilename & "'", MsgBoxStyle.Information)
        Catch ex As Exception
             MsgBox(ex.Message)
        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
            i.Kill()
        Next
    End Sub

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