Introduction
Using the below code you can export text data ( not formatted data ) to excel.
The Code:
Imports System.Data.SqlClient
Imports System.IO
Public Class Export
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
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
End Class
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.