CODEWITHSUNDEEP
asp.netexcelsqldatasource
russds
russds

Reputation: 875

Export sqldatasource to excel not just list or gridview

I'm trying to export my listview, but not just the visible listview, I want to export the entire contents of the sqldatasource. The query returns 20 columns, only a small set (5) are displayed on the listview. Is there a way i can export the entire 20 columns to excel file?

I have this so far:

protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("content-disposition", "attachment;filename=SearchResults.xls");
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    this.EnableViewState = false;
    StringWriter stringWriter = new StringWriter();
    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

    //I want to chagne this:
    this.ResultsListView.RenderControl(htmlTextWriter);

    Response.Write(stringWriter.ToString());
    Response.End();
 }

instead of this line:

    this.ResultsListView.RenderControl(htmlTextWriter);

I would like to do something like:

    this.sqldatasource.RenderControl(htmlTextWriter);

But obviously SqlDataSource doesn't have a RenderControl method. Is there a clean way of going about this?

Upvotes: 0

Views: 2978

Answers (2)

jle
jle

Reputation: 9489

I prefer to use CSV rather than excel for plain data, but the new version of Excel does have the advantage of compression.

You can use the OpenXMLSDK to directly export to XLSX or the FileHelpers library to export to CSV.

Upvotes: 0

Icarus
Icarus

Reputation: 63962

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.

Example:

DataTable dt = ((DataView) this.sqldatasource.Select(DataSourceSelectArguments.Empty)).ToTable();

Now take this DataTable and export that. Google export DataTable to Excel.

If you are fine with using third-party libraries, look into EPPLUS. You can export a DataTable to Excel in, literally, 2 lines of code. The rest would be the code to set the headers and flush the Response.

Here's an example: http://epplus.codeplex.com/wikipage?title=WebapplicationExample

Upvotes: 2

Related Questions