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

        {

       

        }

 

    }

}