Export DataTable to Excel Sheet using VB.Net
By SP
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 (7741 Views)