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> ";
//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
{
}
}
}