How to export Grid View Data to Excel using Data Table

Here is an example of how we can export data of Grid View or other DataBound Controls to an excel file. I am using Grid View to Export data in this example but by modifying code you can also export data from Data List or Repeater control. Below are the some features of this example

•You can Display desired fields in Exported Excel Sheet, by default all the fields which are displayed in GridView were exported

•You can have Full control over formatting of data

•Youcan Replace Control like Hyperlink, dropdownlist, checkbox and other controls before exporting data to excel.

I am using following table named tbemp in my example. You can create this table in your database by using following script

CREATE TABLE tbemp
(
eno int NULL ,
ename varchar (50) NULL,
eadd varchar (50) NULL,
esal int NULL
)
GO

Copy Paste Following code in your .aspx page

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="eno" HeaderText="Emp No" />
<asp:BoundField DataField="ename" HeaderText="Name" />
<asp:BoundField DataField="eadd" HeaderText="Address" />
<asp:BoundField DataField="esal" HeaderText="Salary" />
</Columns>
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">Export
to Excel</asp:LinkButton>

Now add the following code in your Page_Load Event

if (Page.IsPostBack == false)
{
DataTable tb = getdata();
GridView1.DataSource = tb;
GridView1.DataBind();
}

Now copy paste Following code below your Page_Load Event

private DataTable getdata()
{
SqlDataAdapter adp = new SqlDataAdapter("select * from tbemp", "server=.;database=database1;uid=sa;");
DataTable dt = new DataTable();
adp.Fill(dt);
return dt;
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
//Creating Data Table to store Grid View Data
DataTable tb = new DataTable("emp");
//adding column in Data Table
tb.Columns.Add(new DataColumn("Emp No.", Type.GetType("System.Int32")));
tb.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
tb.Columns.Add(new DataColumn("Address", Type.GetType("System.String")));
tb.Columns.Add(new DataColumn("Salary", Type.GetType("System.String")));
//Reding Grid View and Saving in Data Table
//you can read from another control like repeater or DataList
for (int i = 0; i < GridView1.Rows.Count; i++) { //Creating new row in data table
DataRow r = tb.NewRow();
//getting values from Grid View and Saving in current Data Row
r[0] = Convert.ToInt32(GridView1.Rows[i].Cells[0].Text);
r[1] = GridView1.Rows[i].Cells[1].Text;
r[2] = GridView1.Rows[i].Cells[2].Text;
r[3] = GridView1.Rows[i].Cells[3].Text;
//Adding Row to Data Table
tb.Rows.Add(r);
}

//Creating A temp Grid View
GridView gv = new GridView();
gv.DataSource = tb;//You can also filter Data Table to save filtered Data in excel file
gv.DataBind();

//Exporting to Excel File
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=File.xls");
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
gv = null;
}

Download Source Code

no fear choudhary is here, Saturday, July 10, 2010