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?
Add a comment
|
2 Answers
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.
3 Comments
user1342164
Thanks, I got it working but the file isnt very clean, it has a ton of ;;;;;;; and everything thing is in columnA anyway to make this cleaner?
IvanH
The result is .csv not .xls. Problem occurs when the separator character in windows is not ; but e.g. , (default in English?).
user1342164
I changed csv to xls and it helped a little but now column names a split up just really messy. Yes english
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.
5 Comments
user1342164
Yes, I already used that code but the problem is it will only export what is shown in the gridview. Not all rows and columns since I have paging on and not all columns shown
Cherian M Paul
No. You don't have to use the Gridview on the page. You can create a Gridview in the Code behind and get all the records... Check the updated code above.
Nikhil G
I have applied the same code, still it doesn't work on my machine. Is there any need to change the above code to successfully run on .NET version 4.5 Thanks!
Nikhil G
i am using it in asp.net environment
Nikhil G
update: it worked with a little change- "I didn't need to have a
datagrid on .aspx page. A datagrid created at runtime i.e. .aspx.cs works too." Ps: Corrections are always welcome though.