Search content within the blog

Tuesday, November 25, 2008

Exporting Gridview contents to Excel

ASPX Page Code

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="GridviewExportToExcel.aspx.cs"
Inherits="Gridview_Export_To_Excel_GridviewExportToExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView AllowPaging="true" PageSize="2" ID="gvUsers" runat="server" OnRowDataBound="gvUsers_RowDataBound">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
DisplayIDLink
</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton ID="MyLinkButton" runat="server" Text='<%#Eval("displayid")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="DropDownData">
<ItemTemplate>
<asp:DropDownList ID="ddlNames" runat="server">
<asp:ListItem Text="Akshay" Value="Akshay" />
<asp:ListItem Text="Ashwin" Value="Ashwin" />
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="Btn_Export" runat="server" Text="Export to Excel (Default)" OnClick="Btn_ExportClick" />
<asp:Button ID="Btn_ExportToExcelPaging" runat="server" Text="Export to Excel Paging"
OnClick="Btn_ExportExcelPaging" />
</div>
</form>
</body>
</html>

ASPX.CS
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;

public partial class Gridview_Export_To_Excel_GridviewExportToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
SqlConnection myConnection = new SqlConnection("Data Source=server02;Initial Catalog=Trial;User ID=ashwin;Password=ashwin");//change here to ur connectionstring
SqlDataAdapter ad = new SqlDataAdapter("select top 2 orderid,displayid from loobi_order", myConnection);
DataSet ds = new DataSet();
ad.Fill(ds);

gvUsers.DataSource = ds;
gvUsers.DataBind();
}

protected void Btn_ExportClick(object sender, EventArgs e)
{
//ad this section to have those dropdowns,linkbuttons in excel file
string style = @"<style> .text { mso-number-format:\@; } </script> ";

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);
// Style is added dynamically
Response.Write(style);
Response.Write(sw.ToString());
Response.End();
}

protected void Btn_ExportExcelPaging(object sender, EventArgs e)
{
//to convert link button, DropDownList etc date to simple text type
DisableControls(gvUsers);

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

private void DisableControls(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls[i] as LinkButton).Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}

else if (gv.Controls[i].GetType() == typeof(DropDownList))
{
l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}


if (gv.Controls[i].HasControls())
{
DisableControls(gv.Controls[i]);
}
}

}

public override void VerifyRenderingInServerForm(Control control)
{
//this is required while exporting to excel
}

protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
{

if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[1].Attributes.Add("class", "text");
}
}
}

No comments:

Post a Comment