Resources » Code Snippets » VB.NET Syntax

Export datatable to excel

Updated: Category: VB.NET Syntax
Author: Member Level: GoldPoints: 10

This code exports datatable to excel.Pass datatable as parameter to the sub routine ExportToExcel

Imports Microsoft.Office.Interop.Excel

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

Related Resources:

Read related articles: Export to Excel   

Did you like this resource? Share it with your friends and show your love!

Responses to "Export datatable to excel"
Author: harjit  01 Oct 2009   Member Level: Bronze   Points : 2
If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If

'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()

Dim dt As System.Data.DataTable = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0

For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName

For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)


Dim strFileName As String = "employee_evaluation.xls"
Dim blnFileOpen As Boolean = False
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(strFileName) Then
End If

excel.Visible = True

Author: Programmer  22 Oct 2009   Member Level: Gold   Points : 0
Is this alternative code for my code snippet ?


Post Comment:

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)

    Type the numbers and letters shown on the left.

    Submit Article     Return to Article Index
    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    sridhar thota
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India


    Click the "Follow" button above to follow Tony John