Here is the code to export Data from a database to Excel in ASP .NET.
Protected Sub ExpExl_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim fieldnum As Integer
Dim cellstring As String
Dim headstring As String
Dim dt As Date
Dim rnd As New Random, fn, nf As String
Try fn = rnd.Next(10000, 1000000).ToString
fn = "~/All Status/" & fn & ".xls"
nf = fn
fn = MapPath(fn)
headstring = ""
cellstring = ""
rdr = DbRet("Select * from student")
If rdr.FieldCount >= 1 Then
For fieldnum = 0 To rdr.FieldCount - 1
If headstring = "" Then
headstring = rdr.GetName(fieldnum) & vbTab
Else
headstring = headstring & rdr.GetName(fieldnum).ToString & vbTab
End If
Next
headstring = headstring & vbCrLf
Dim fs As FileStream
fs = File.Open(fn, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite)
fs.Close()
FileIO.FileSystem.WriteAllText(fn, headstring, True)
Else
End If
Do
If rdr.Read() Then
For fieldnum = 0 To rdr.FieldCount - 1
Dim st As String
If rdr.GetDataTypeName(fieldnum) = "datetime" And Not IsDBNull(rdr.Item(fieldnum)) Then
dt = rdr.Item(fieldnum)
If dt.Date = #12/30/1899# Or dt.Date = #1/1/1900# Then
st = Format(dt, "h:m:s t")
Else
st = Format(dt, "dd-MMM-yyyy")
End If
Else
st = Replace(rdr.Item(fieldnum).ToString, vbCrLf, "")
End If
cellstring = cellstring & st & vbTab
Next
cellstring = cellstring & vbCrLf
FileIO.FileSystem.WriteAllText(fn, cellstring, True)
cellstring = ""
Else
Exit Do
End If
Loop
Catch ex As Exception
label1.Text=ex.Message
Exit Sub
End Try
DownloadFile(nf, True)
End Sub
Private Sub DownloadFile(ByVal fname As String, ByVal forceDownload As Boolean)
Dim path As String = MapPath(fname)
Dim name As String = System.IO.Path.GetFileName(path)
Dim ext As String = System.IO.Path.GetExtension(path)
Dim type As String = ""
If Not ext Is Nothing Then
Select Case ext.ToLower()
Case ".htm"
Case ".html"
type = "text/HTML"
Case ".txt"
type = "text/plain"
Case ".doc"
Case ".rtf"
type = "Application/msword"
Case ".xls"
type = "Application/msexcel"
Case ".csv"
type = "Application/msexcel"
End Select
End If
If (type <> "") Then
Response.ContentType = type
End If
If (forceDownload) Then
Response.AppendHeader("content-disposition", "attachment; filename=" + name)
End If
Response.WriteFile(path)
Response.End()
End Sub
In the above code "All Status" refers to a directory in my website path. "DBRet" is a function which connects to the actual database(SQL Server Database) and retrieves data from a table basing on the query supplied, it returns an SQL datareader object.
