Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

C# Excel Export from ASP.NET Page

 

-----------------

--web Page - ExcelExport.aspx

-----------------

 

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="ExcelExport.aspx.cs" Inherits="Forms_Techniques_ExcelExport" Title="Untitled Page" %>

 

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

    <asp:GridView ID="GridView1" runat="server">

    </asp:GridView>

    <br />

   <asp:LinkButton ID="LinkButton1" runat="server"

       

        onclientclick="window.open('ExcelExport.aspx?showExcel=true', 'Show_Excel','width=700,height=500,resizable=1,status=1,toolbar=1,menubar=1,scrollbars=1,location=0');"

        Font-Size="Small">Export Data to Excel</asp:LinkButton>

    </asp:Content>

 

-----------------

--Code Behind

-----------------

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

//using System.Xml.Linq;

 

public partial class Forms_Techniques_ExcelExport : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        dsContacts ds = new dsContacts();

        dsContacts.ContactDataTable dt = ds.Contact;

        //dsContactsTableAdapters.ContactTableAdapter ta = new dsContactsTableAdapters.ContactTableAdapter();

        //ta.Fill(dt);

        Guid g = new Guid();

        if (Request.QueryString["showExcel"] == null)

            {

                //------------------------------------------------

                //- First Time load, show Export Link Button

                //------------------------------------------------

                //string excelHTML = "<a style=\"font-size: xx-small; font-family: Verdana\" href=\"Javascript:void(0)\" onclick=\"window.open('ExcelExport.aspx?showExcel=true', 'Show_Excel','width=700,height=500,resizable=1,status=1,toolbar=1,menubar=1,scrollbars=1,location=0');\"><font color=blue><u>Export Data to Excel</u></font></a>&nbsp;&nbsp;";

                //Response.Write(excelHTML);

 

                this.GridView1.DataSource = dt;

                this.GridView1.DataBind();

            }

            else

            {

                //------------------------------------------------

                //User clicked Export Link Button

                //------------------------------------------------

               

 

                RenderExcel objRenderExcel = new RenderExcel();

                //string[] arr = {

                //     "a",

                //         "B",

                //         "C",

                //     "D",

                //     "e"};

 

                //objDatasetToExcel.AddRow("Coupon Bar Chart",

                //    "Coupon;Start Date;End Date;Prints;Etc",

                //    arr);

 

                //objDatasetToExcel.AddTable(dt);

                objRenderExcel.ConvertDataSet(Response, ds);

            }

 

    }

}

 

-----------------

--Class

-----------------

 

using System;

using System.Data;

using System.Configuration;

using System.IO;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

//using System.Xml.Linq;

 

/// <summary>

/// Summary description for RenderExcel

/// </summary>

public class RenderExcel

{

    public RenderExcel()

    {

        //

        // TODO: Add constructor logic here

        //

    }

    public void ConvertDataSet(HttpResponse response, DataSet ds)

    {

        try

        {

            //first let//s clean up the response.object

            response.Clear();

            response.Charset = "";

            //set the response mime type for excel

            response.ContentType = "application/vnd.ms-excel";

            //create a string writer

            //instantiate a datagrid

            DataGrid dg = new DataGrid();

            //set the datagrid datasource to the dataset passed in

 

            for (int i = 0; i < ds.Tables.Count; i++)

            {

                //---------------------------------------------------------------------------

                //table name

                //---------------------------------------------------------------------------

                DataSet littleDs = new DataSet();

                DataTable dataTable = new DataTable(ds.Tables[i].TableName);

                DataColumn column = new DataColumn();

                column.DataType = System.Type.GetType("System.String");

                column.ColumnName = "Table Name";

                //column.ReadOnly = True

                //column.Unique = True

 

                // Add the Column to the DataColumnCollection.

                dataTable.Columns.Add(column);

 

                //DataColumn column2 = new DataColumn();

                //column2.DataType = System.Type.GetType("System.String");

                //column2.ColumnName = "Row Count";

                //column.ReadOnly = True

                //column.Unique = True

 

                //// Add the Column to the DataColumnCollection.

                //dataTable.Columns.Add(column2);

 

                littleDs.Tables.Add(dataTable);

                String[] arr = { ds.Tables[i].TableName + " - " + ds.Tables[i].Rows.Count.ToString() + " rows"};

                //String[] arr = { ds.Tables[i].TableName, ds.Tables[i].Rows.Count.ToString() };

                littleDs.Tables[0].Rows.Add(arr);

                StringWriter stringWrite = new StringWriter();

                //create an htmltextwriter which uses the stringwriter

                HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

                dg.DataSource = littleDs.Tables[0];

                //bind the datagrid

                dg.DataBind();

                //tell the datagrid to render itself to our htmltextwriter

                dg.RenderControl(htmlWrite);

                response.Write(stringWrite.ToString());

 

                //---------------------------------------------------------------------------

                //actual data

                //---------------------------------------------------------------------------

                stringWrite = new System.IO.StringWriter();

                //create an htmltextwriter which uses the stringwriter

                htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

                dg.DataSource = ds.Tables[i];

                //bind the datagrid

                dg.DataBind();

                //tell the datagrid to render itself to our htmltextwriter

                dg.RenderControl(htmlWrite);

                //all that//s left is to output the html

                response.Write(stringWrite.ToString());

            }

            response.End();

            response.Write(""); //prevents "thread aborted" message

        }

        catch (Exception ex)

        {

            string x = ex.ToString();

            System.Diagnostics.Debug.WriteLine(ex.Message);

            //Throw ex

        }

        finally

        {

       

        }

 

    }

}

 

 

Posted on Sunday, April 20, 2008 1:13 PM Excel | Back to top


Comments on this post: C# Excel Export from ASP.NET Page

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
thanks a lot dude
Left by Jitendra on Jun 06, 2008 12:04 AM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
Look at this:
http://www.gemboxsoftware.com/WebDemo/Index.aspx
Left by Barba on Jun 12, 2008 1:07 AM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
Hello, why is it that mine has a .html exe when downloading... not .exel or .xls
Left by Julpo on Nov 05, 2008 7:59 AM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
Hello, where can I just the file name?
Mine always displays users.html in mozilla and users.xls in internet explorer.
Left by korinai on Nov 05, 2008 8:50 AM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
thanks for the resource. it's really helful
Left by Jeevan K Augustin on Feb 03, 2009 9:40 PM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
It nice, it means we are exporting Gridview to Excel.
Let say I've 65001 row. How that would be exported to Excel?
Also Binding 65001 rows to Grid takes a while. Isn't there any whay that we export data directly to Exell sheet.?
Left by Internet solutions team on Apr 13, 2009 10:36 PM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
Here is a way how to create an Excel file from
c# / a DataTable

http://www.schonebeck.net/?p=127

Enjoy !

Regards
David
Left by David Schonebeck on Jan 24, 2011 3:05 PM

# re: C# Excel Export from ASP.NET Page
Requesting Gravatar...
Here's a really neat way of doing it with an html table (which is more likely with MVC):

http://lottemae.wordpress.com/2011/11/24/asp-net-c-mvc-export-an-html-table-to-excelcsv/
Left by XOS on Nov 24, 2011 12:56 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net | Join free