CODEWITHSUNDEEP
asp.netvb.netexport-to-excel
user1342164
user1342164

Reputation: 1454

Is it possible to export a sql table to excel using asp.net

I have a webpage with an export button. Is it possible to export a sql table to excel when this button is pressed? I can do it with a gridview but would just like a simple button with code behind to do the work. Can someone point me in the direction I need?

Upvotes: 0

Views: 1384

Answers (2)

Cherian M Paul
Cherian M Paul

Reputation: 656

If you want it in excel you can use the following code. Select the data and put it in a Gridview and do the following. 

Dim GridView1 As New GridView

SqlDataSource1.SelectCommand = "SELECT * FROM TableName"
GridView1.DataSource = SqlDataSource1
GridView1.DataBind()

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

GridView1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())
Response.End()

You can also format the Gridview to make it look good on the Excel Sheet.

Upvotes: 1

Karl Anderson
Karl Anderson

Reputation: 34846

Here is a utility function you can use:

Public Shared Sub ExportToSpreadsheet(table As DataTable, filename As String)
    ' Get a hold of the HTTP context and clear it, because we are going to push the CSV data through the context
    Dim context = HttpContext.Current
    context.Response.Clear()

    ' Loop through each column in your data table
    For Each column As DataColumn In table.Columns
        ' Write column names
        context.Response.Write(column.ColumnName + ";")
    Next

    context.Response.Write(Environment.NewLine)

    ' Loop through each row in the data table
    For Each row As DataRow In table.Rows
        ' Loop through each column in row
        For i As Integer = 0 To table.Columns.Count - 1
            ' Write each column value
            context.Response.Write(row(i).ToString().Replace(";", [String].Empty) & ";")
        Next

        ' Write a new line between rows of data
        context.Response.Write(Environment.NewLine)
    Next

    ' Set the content type and headers
    context.Response.ContentType = "text/csv"
    context.Response.AppendHeader("Content-Disposition", "attachment; filename=" & filename & ".csv")
    context.Response.[End]()
End Sub

Then you can call it like this:

ExportToSpreadsheet(YourDataTable, "YourFileName")

Note: Since it is a Shared function, then you can put it in a utility class and do not need to instantiate (New) the class to use the function.

Upvotes: 3

Related Questions