1. Resources
    2. .NET programming
    3. .NET Framework

Export Selected GridView Rows to Excel in ASP.NET

Posted Date: 15 Feb 2012 |Updated: 15-Feb-2012 |Category: .NET Framework |Author: |Member Level: Gold |Points: 35 |


In this post I am going to discuss on Export Selected GridView Rows to Excel in asp.net. We oftenly come across the situation to bind the data and based on the selected rows we need to export rows into a Microsoft Excel file. Export Selected 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 selected rows into an ArrayList, Change the Controls to value and finally Export Selected GridView Rows to Excel.

Export Selected GridView Rows to Excel in asp.net


ASP.NET Gridview is a flexible control, and we can do the paging, adding,updating, deleting the records, sorting and Export Selected GridView Rows to Excel.

Let's see a sample program to bind the data and export to Microsoft Excel. Now Drag the GridView from the tool box and update like as below code snippet.

ASPX Code:



<%@ 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>


From the above .ASPX page We have added one gridview ,in the template field added one CheckBox control , LinkButton control and bound field as StudentName, FatherName, City, Country as gridview columns.Here I binded the gridview like as <%#Eval("StudentID") %>. And added one button control to generate the Excel file.

You can have a look at the below code to bind the gridview, what is the method I followed. Here I have taken one DataTable object and added the columns, after that added the rows to DataTable object. Please note that to bind the gridview, I have not connected to any database or the XML file.

C#.NET Code:



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;
        }



In the Next step, I would like to find the selected/Checked rows from the gridview and assign to a ArrayList class. The ArrayList is class and it a flexible one, whose size will be increase at runtime based on the assigned data.

C#.NET Code:



/// <summary>
        /// We are listing out the checked griview rows ID's in an ArrayList
        /// </summary>
        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;
        }


From the above, FindSelectedRows() method, we have declared one ArrayList class and checked for the View state is existed or not, if view state is already assigned that data is assigning to ArrayList class. In the next step, looping each row in a gridview and finding the check box is checked or not, if yes assigning the StudentID to ArrayList Class.


C#.NET Code:



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;
                    }

                }
            }

        }

Above,on RowDataBound event we are checking the condition for the view state, if already assigned, restoring that data to Arraylist class and selecting the check boxes present in the gridview.

C#.NET Code:



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]);

                }

            }

        }


Above code is for converting the controls data into the literal controls data to easily assign to MS Excel.

C#.NET Code:



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();

        }


Above code produce the MS Excel file like as below screen shot with a file name GridViewToExcel.xls and prompt for Save or View.

Below is the total C# code for this article:



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
    {
        
        /// <summary>
        /// We are listing out the checked griview rows ID's in an ArrayList
        /// </summary>
        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]);

                }

            }

        }
    }
}


Below is the screenshots after binding the data into ASP.NET Gridview and click on the Button:


SelectedGridView
GridViewToExcel


Comments



  • 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:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

        more...

        About dotnetspider.com

        We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

        Quick Links
        General
        Services

        Digital Marketing by SpiderWorks Technologies, Kochi - India. ©