User603616845 posted
Hi,
You can create a DataTable from the DataView returned by the SqlDataSource and export that to Excel. The resulting DataTable will contain all the columns.
Since you are using a SqlDataSource then you can get a DataTable out of it like this:
var dv = new DataView();
var dt = new DataTable();
dv = (DataView)mySQLDataSource.Select(DataSourceSelectArguments.Empty);
dt = dv.ToTable();
I wrote a set of extension methods to do DataTable to CSV, which you should be able to use easily.
The rest of your code would be pretty simple then:
var csv = dt.ToCSV();
Here is a full example using this very method.
The Markup:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="SO.WebUI._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeId"
DataSourceID="SqlDataSourceLocal">
<Columns>
<asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeId" Visible="False" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Birthdate" HeaderText="Birthdate" SortExpression="Birthdate" />
</Columns>
</asp:GridView>
<!-- This is the button to export CSV! -->
<asp:Button ID="btnToCSV" runat="server" Text="Export to CSV" OnClick="GetCSV" />
<asp:SqlDataSource ID="SqlDataSourceLocal" runat="server"
ConnectionString="<%$ ConnectionStrings:StackOverflowExamplesConnectionString %>"
SelectCommand="SELECT * FROM [Employee]"></asp:SqlDataSource>
</asp:Content>
And the code-behind:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public void GetCSV(object sender, EventArgs e)
{
DataView dv = (DataView)SqlDataSourceLocal.Select(DataSourceSelectArguments.Empty);
var dt = dv.ToTable();
var csv = dt.ToCSV();
WriteToOutput(csv, "export.csv", "text/csv");
}
private void WriteToOutput(String csv, String fileName, String mimeType)
{
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
Response.Write(csv);
Response.End();
}
}
There you go. The only thing not included here is the extension method source, but since I've linked to it, you should have no problems getting that into a static class. Here is
thread
on StackOverFlow.
Hope this will help you.
thanks
