Search This Blog

Loading...

Page Rank Check

Monday, February 6, 2012

Asp.net export GridView selected columns to Excel with style applied


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.......

Ref: try this link

3 comments :