ASP.NET - SQL to Excel: easy as 1, 2, 3!

Want to make some SQL generate an Excel file with formatting? easy!

What you have to understand with this example is that you must set a Session variable before calling the page. This variable (ExcelSQL) holds the SQL which will be executed when the page is called. This example is for MySQL but could easily be adapted for other database types.


Place the following code on the Page_Load event:

Dim ExcelSQL As String
ExcelSQL = Session("ExcelSQL")


If ExcelSQL <> "" Then
Dim conn As New MySql.Data.MySqlClient.MySqlConnection
Dim mycommand As New MySql.Data.MySqlClient.MySqlCommand
Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter
Dim ds As New System.Data.DataSet
Dim dg As New DataGrid
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim stringWrite As New System.IO.StringWriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

conn.ConnectionString = "Server=MYSERVER;User Id=MYUSER;Password=MYPASS;Persist Security Info=True;Database=MYDATABASE"

conn.Open()
mycommand.Connection = conn
mycommand.CommandText = ExcelSQL
da.SelectCommand = mycommand
da.Fill(ds)
dg.DataSource = ds.Tables(0)
dg.DataBind()
'//set how it should look
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center
dg.HeaderStyle.BackColor = Drawing.Color.LightSkyBlue
'//write file
dg.RenderControl(htmlWrite)
Dim FileName
FileName = "Results.xls"
Response.AddHeader("Content-Disposition", "attachment; filename=""" & FileName & """")
Response.Write(stringWrite.ToString)
Response.End()

conn.Close()
Else
'//no data in table, do nothing
Response.Write("")
End If

Be sure to set the connection string correctly (highlighted in bold) and to set the session variable correctly by doing the following BEFORE you call the page, for example:

Session("ExcelSQL") = "SELECT * FROM MyTable WHERE ID > 0;"