Export Table data to Excel

Shows how to export Table data to Excel

This is the sub procedure for Table data Export to Excel.


Public Sub ExportToExcel(ByVal FileName As String, ByVal SavePath As String, ByVal objDataReader As DataTable)
' Dim i As Integer
Dim sb As New System.Text.StringBuilder
Try
Dim intColumn, intColumnValue As Integer
Dim row As DataRow
For intColumn = 0 To objDataReader.Columns.Count - 1
sb.Append(objDataReader.Columns(intColumn).ColumnName)
If intColumnValue <> objDataReader.Columns.Count - 1 Then
sb.Append(vbTab)
End If
Next
sb.Append(vbCrLf)
For Each row In objDataReader.Rows
For intColumnValue = 0 To objDataReader.Columns.Count - 1
sb.Append(StrConv(IIf(IsDBNull(row.Item(intColumnValue)), "", row.Item(intColumnValue)), VbStrConv.ProperCase))
If intColumnValue <> objDataReader.Columns.Count - 1 Then
sb.Append(vbTab)
End If
Next
sb.Append(vbCrLf)
Next
SaveExcel(SavePath & "\" & FileName & ".xls", sb)
Catch ex As Exception
Throw
Finally
objDataReader = Nothing
sb = Nothing
End Try
End Sub


This procedure call under ExportToExcel procedure To Write Excel File.

Private Sub SaveExcel(ByVal fpath As String, ByVal sb As System.Text.StringBuilder)
Dim fsFile As New FileStream(fpath, FileMode.Create, FileAccess.Write)
Dim strWriter As New StreamWriter(fsFile)
Try
With strWriter
.BaseStream.Seek(0, SeekOrigin.End)
.WriteLine(sb)
.Close()
End With
Catch e As Exception
Throw
Finally
sb = Nothing
strWriter = Nothing
fsFile = Nothing
End Try
End Sub


Related Articles

Export Data to Excel in CSV format using .NET

Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

Client Side Script to Export to Excel 2007

This article explains the work around for Export to Excel 2007. The earlier vbScript we used for Exporting to Excel 2003 relied on HTMLProject which is no more supported in Excel 2007. Here is the work around.

More articles: Export to Excel

Comments

Author: naren22 Apr 2008 Member Level: Bronze   Points : 0

Its very nice and working properly.. how can i bold the header row



  • 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:
    Email: