GridView Rows to Excel in ASP.NET


In this post I am going to discuss on Export all GridView Rows to Excel in asp.net. We oftenly come across the situation to bind the data and export rows into a Microsoft Excel file. Export all Gridview Rows to Excel in ASP.NET 2.0,3.5,4.0, in C#. It is bit complex task. We can learn how to bind the GridView manually, How to get the rows into an ArrayList, Change the Controls to value and finally Export Selected GridView Rows to Excel. GridView Rows to Excel in ASP.NET. GridView Rows to Excel in C#.NET

Below is the .ASPX code to implement the ASP.NET GridView Control to export the data into an Excel file.




<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication7.WebForm1" %>

<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GrvExportExcel" runat="server" AllowPaging="True"
DataKeyNames="StudentID" AutoGenerateColumns="False"
onpageindexchanging="GrvExportExcel_PageIndexChanging"
onrowdatabound="GrvExportExcel_RowDataBound" EnableViewState="true" >
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Student ID">
<ItemTemplate>
<asp:LinkButton ID="lbStudent" runat="server" Text='<%#Eval("StudentID") %>' PostBackUrl="~/WebForm1.aspx" >
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="StudentName" HeaderText="Student Name"></asp:BoundField>
<asp:BoundField DataField="FatherName" HeaderText="Father Name"></asp:BoundField>
<asp:BoundField DataField="City" HeaderText="City"></asp:BoundField>
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>

<asp:Button ID="btnExport" runat="server" Text="Export" onclick="btnExport_Click" />
</div>
</form>
</body>
</html>


Below is the .cs file to upload the data from GridView in to Microsoft Excel file.



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.IO;
using System.Web.UI.HtmlControls;
using System.Data;

namespace WebApplication7
{
public partial class WebForm1 : System.Web.UI.Page
{

///
/// We are listing out the checked griview rows ID's in an ArrayList
///

private void FindSelectedRows()
{
ArrayList chkRowsList;
if (ViewState["chkRowsList"] != null)
{
chkRowsList = (ArrayList)ViewState["chkRowsList"];
}
else
{
chkRowsList = new ArrayList();
}

foreach (GridViewRow gvMyRow in GrvExportExcel.Rows)
{
if (gvMyRow.RowType == DataControlRowType.DataRow)
{
string rowCurrentIndex =Convert.ToString(GrvExportExcel.DataKeys[gvMyRow.RowIndex]["StudentID"]);

Convert.ToInt32(GrvExportExcel.PageIndex);
CheckBox chkSelect =(CheckBox)gvMyRow.FindControl("chkSelect");
//if ((chkSelect.Checked) && (!chkRowsList.Contains(rowCurrentIndex)))
//{
chkRowsList.Add(rowCurrentIndex);
//}
//else if ((!chkSelect.Checked) && (chkRowsList.Contains(rowCurrentIndex)))
//{
// chkRowsList.Remove(rowCurrentIndex);
//}
}

}
ViewState["chkRowsList"] = chkRowsList;
}

protected void GrvExportExcel_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
FindSelectedRows();
}

protected void GrvExportExcel_RowDataBound(object sender, GridViewRowEventArgs e)
{
//if (ViewState["chkRowsList"] != null)
//{
// ArrayList checkedRowsList =(ArrayList)ViewState["chkRowsList"];
// GridViewRow gvRow = e.Row;
// if (gvRow.RowType == DataControlRowType.DataRow)
// {
// CheckBox chkSelect =(CheckBox)gvRow.FindControl("chkSelect");
// string rowIndex =Convert.ToString(GrvExportExcel.DataKeys[gvRow.RowIndex]["StudentID"]);
// //int rowIndex = Convert.ToInt32(gvRow.RowIndex) +

// Convert.ToInt32(GrvExportExcel.PageIndex);
// if (checkedRowsList.Contains(rowIndex))
// {
// chkSelect.Checked = true;
// }

// }
//}

}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = bindGridView();
GrvExportExcel.DataSource = dt;
GrvExportExcel.DataBind();
}
}
public DataTable bindGridView()
{
DataTable table = new DataTable();

table.Columns.Add("StudentID", typeof(int));
table.Columns.Add("StudentName", typeof(string));
table.Columns.Add("FatherName", typeof(string));
table.Columns.Add("City", typeof(string));
table.Columns.Add("Country", typeof(string));

table.Rows.Add(101, "Satwik", "Mohan", "Hyderabad","India");
table.Rows.Add(102, "Siddu", "Mahesh", "Hyderabad", "India");
table.Rows.Add(104, "Sravan", "Sujan", "Hyderabad", "India");
table.Rows.Add(105, "Nishanth", "Venkat", "Hyderabad", "India");
table.Rows.Add(106, "Tejaswi", "Veera", "Hyderabad", "India");
table.Rows.Add(107, "Sushanth", "Swaroop", "Secbad", "India");
table.Rows.Add(108, "Swetha", "Chanti", "Secbad", "India");
table.Rows.Add(109, "Niraja", "Venkat", "Secbad", "India");
table.Rows.Add(110, "Nisha", "Naga", "Secbad", "India");
table.Rows.Add(111, "Satwik", "Mohan", "Hyderabad", "India");
table.Rows.Add(112, "Siddu", "Mahesh", "Hyderabad", "India");
table.Rows.Add(114, "Sravan", "Sujan", "Hyderabad", "India");

return table;
}

protected void btnExport_Click(object sender, EventArgs e)
{
FindSelectedRows();
GrvExportExcel.ShowHeader = true;
GrvExportExcel.GridLines = GridLines.Both;
GrvExportExcel.AllowPaging = false;
// GrvExportExcel.DataBind();
DataTable dt = bindGridView();
GrvExportExcel.DataSource = dt;
GrvExportExcel.DataBind();
GrvExportExcel.HeaderRow.Cells.RemoveAt(0);
if (ViewState["chkRowsList"] != null)
{
ArrayList checkedRowsList =(ArrayList)ViewState["chkRowsList"];
foreach (GridViewRow gvRow in GrvExportExcel.Rows)
{
gvRow.Visible = false;
if (gvRow.RowType == DataControlRowType.DataRow)
{
string rowIndex =Convert.ToString(GrvExportExcel.DataKeys[gvRow.RowIndex]["StudentID"]);
if(checkedRowsList.Contains(rowIndex))
{
gvRow.Visible = true;
gvRow.Cells[0].Visible = false;

}
}
}
}

ChangeControlsToValue(GrvExportExcel);
Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");

Response.ContentType = "application/excel";

StringWriter sWriter = new StringWriter();

HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

HtmlForm hForm = new HtmlForm();

GrvExportExcel.Parent.Controls.Add(hForm);

hForm.Attributes["runat"] = "server";

hForm.Controls.Add(GrvExportExcel);

hForm.RenderControl(hTextWriter);

Response.Write(sWriter.ToString());

Response.End();

}
private void ChangeControlsToValue(Control gridViewExcel)
{
Literal literal = new Literal();

for (int i = 0; i < gridViewExcel.Controls.Count; i++)
{
if (gridViewExcel.Controls[i].GetType() == typeof(LinkButton))
{

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

gridViewExcel.Controls.Remove(gridViewExcel.Controls[i]);

gridViewExcel.Controls.AddAt(i, literal);

}
else if (gridViewExcel.Controls[i].GetType() == typeof(CheckBox))
{
literal.Text = (gridViewExcel.Controls[i] as CheckBox).Checked ? "True" : "False";
gridViewExcel.Controls.Remove(gridViewExcel.Controls[i]);
gridViewExcel.Controls.AddAt(i, literal);
}
if (gridViewExcel.Controls[i].HasControls())
{

ChangeControlsToValue(gridViewExcel.Controls[i]);

}

}

}
}
}


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: