ASHIS KUMAR DAS: ASP.Net GridView Export only selected columns to Excel Sheet

In this article I will describe how to export only those columns in ASP.Net GridView control that are selected or checked by the user.

Database

I am using Microsoft’s Northwind Sample Database for this article. You can download the same using the link below

Download Northwind Database

HTML Markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportToExcel.aspx.cs" enableEventValidation ="false" Inherits="ExcelApplication.ExportToExcel" ValidateRequest = "false"%>

<!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="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
            Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
            AllowPaging="true" OnPageIndexChanging="OnPaging">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol0" runat="server" Checked="true" />
                        <asp:Label ID="lblCol0" runat="server" Text="CustomerID" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol1" runat="server" Checked="true" />
                        <asp:Label ID="lblCol1" runat="server" Text="ContactName" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol2" runat="server" Checked="true" />
                        <asp:Label ID="lblCol2" runat="server" Text="City"></asp:Label>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView></div>
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
            Text="Export To Excel" />

    </form>
</body>
</html>

Designer File :

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ExcelApplication {


    public partial class ExportToExcel {

        /// <summary>
        /// form1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.HtmlControls.HtmlForm form1;

        /// <summary>
        /// GridView1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.GridView GridView1;

        /// <summary>
        /// Button1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.Button Button1;
    }
}

Code Behind:

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.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace ExcelApplication
{
    public partial class ExportToExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
                GetCheckBoxStates();

            BindGrid();
        }

        //Exporting the GridView with selected columns to Excel Sheet
        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition","attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();

            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
            GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

            ArrayList arr = (ArrayList)ViewState["States"];
            GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]);
            GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]);
            GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]);

            GridView1.HeaderRow.Cells[0].FindControl("chkCol0").Visible = false;
            GridView1.HeaderRow.Cells[1].FindControl("chkCol1").Visible = false;
            GridView1.HeaderRow.Cells[2].FindControl("chkCol2").Visible = false;

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];
                row.Cells[0].Visible = Convert.ToBoolean(arr[0]);
                row.Cells[1].Visible = Convert.ToBoolean(arr[1]);
                row.Cells[2].Visible = Convert.ToBoolean(arr[2]);
                row.BackColor = System.Drawing.Color.White;
                row.Attributes.Add("class", "textmode");

                if (i % 2 != 0)
                {
                    row.Cells[0].Style.Add("background-color", "#C2D69B");
                    row.Cells[1].Style.Add("background-color", "#C2D69B");
                    row.Cells[2].Style.Add("background-color", "#C2D69B");
                }
            }

            GridView1.RenderControl(hw);

            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

            /* Verifies that the control is rendered */

        }

        //You’ll notice I am calling one more function GetCheckBoxStates() in the Page Load event of the Page.
        //The job of this function is to maintain the states of the GridView header checkboxes in ViewState.
        //The function is described below
        private void GetCheckBoxStates()
        {

            CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol0");
            CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol1");
            CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol2");

            ArrayList arr;

            if (ViewState["States"] == null)
            {
                arr = new ArrayList();
            }
            else
            {
                arr = (ArrayList)ViewState["States"];
            }

            arr.Add(chkCol0.Checked);
            arr.Add(chkCol1.Checked);
            arr.Add(chkCol2.Checked);
            ViewState["States"] = arr;
        }

        //Binding Data
        private void BindGrid()
        {

            string strQuery = "select CustomerID,City,ContactName from customers";
            DataTable dt = new DataTable();
            String strConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            try
            {
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                sda.Dispose();
                con.Dispose();
            }
        }

        //Call when page index changed
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            BindGrid();
        }

    }
}

As you’ll notice above everything is same as the normal GridView to Excel export the only difference is that I am hiding the GridView cells based on the values of their respective checkboxes in the Header row

ASP.Net GridView with ability to select/unselect columns

Exported Excel sheet with selected columns

Hope this will help you.......

Happy learning.......:)

ASHIS KUMAR DAS

Welcome

Thursday, 2 February 2012

ASP.Net GridView Export only selected columns to Excel Sheet

No comments:

Post a Comment

Important Links