Thursday, August 30, 2012

Export gridview data to Excel using asp.net using C#

Here i will explain How to Export Gridview Data To Excel. In the Previous post Explained How to Export Gridview Data To PDF  and now While Exporting I will explain the problems how to solve them. Here i am binding Gridview In the Page_Load event 

Out Put :



Aspx Page : 
 
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" EnableEventValidation="false"
    CodeBehind="Default.aspx.cs" Inherits="ExpGvDataToExcel._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <center>
  
       <asp:Button ID="btnExpToExcel" runat="server" onclick="btnExpToExcel_Click"
           Text="Export To Excel" />
       <br />
       <br />
     
       <asp:GridView ID="ExpGvToExcelControl" runat="server" CellPadding="4" ForeColor="#333333"
           GridLines="None" OnRowCreated="ExpGvToExcelControl_RowCreated"
            OnPageIndexChanging="ExpGvToExcelControl_PageIndexChanging" AllowPaging="True">
           <AlternatingRowStyle BackColor="White" />
           <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
           <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
           <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
           <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
           <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
           <SortedAscendingCellStyle BackColor="#FDF5AC" />
           <SortedAscendingHeaderStyle BackColor="#4D0000" />
           <SortedDescendingCellStyle BackColor="#FCF6C0" />
           <SortedDescendingHeaderStyle BackColor="#820000" />
       </asp:GridView>
  
   </center>
</asp:Content>

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

namespace ExpGvDataToExcel
{
    public partial class _Default : System.Web.UI.Page
    {
        private string constring =
       ConfigurationManager.ConnectionStrings["myconstring"].ConnectionString;
        SqlDataAdapter da;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        protected void btnExpToExcel_Click(object sender, EventArgs e)
        {
            try
            {
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                     string.Format("attachment; filename={0}", "EmpInfo.xls"));
                Response.ContentType = "application/ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                ExpGvToExcelControl.AllowPaging = false;
                BindGrid();
                //Change the Header Row back to white color
                ExpGvToExcelControl.HeaderRow.Style.Add("background-color", "#FFFFFF");
                //Applying stlye to gridview header cells
                for (int i = 0; i < ExpGvToExcelControl.HeaderRow.Cells.Count; i++)
                {
                    ExpGvToExcelControl.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
                }
                int j = 1;
                //This loop is used to apply stlye to cells based on particular row
                foreach (GridViewRow gvrow in ExpGvToExcelControl.Rows)
                {
                    gvrow.BackColor = Color.White;
                    if (j <= ExpGvToExcelControl.Rows.Count)
                    {
                        if (j % 2 != 0)
                        {
                            for (int k = 0; k < gvrow.Cells.Count; k++)
                            {
                                gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                            }
                        }
                    }
                    j++;
                }
                ExpGvToExcelControl.RenderControl(htw);
                Response.Write(sw.ToString());
                Response.End();
            }
            catch (Exception ex)
            {
                string ErrMsg = ex.Message;
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            // verifies the control is rendered here
        }

        private void BindGrid()
        {
            try
            {
                DataSet ds = new DataSet();
                da = new SqlDataAdapter("select * from EMPTable", constring);
                da.Fill(ds, "GetEmpInfo");
                ExpGvToExcelControl.DataSource = ds.Tables[0];
                ExpGvToExcelControl.DataBind();
            }
            catch (Exception ex)
            {
                string ErrMsg = ex.Message;
            }
        }

        protected void ExpGvToExcelControl_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.Header)
            {
                GridView HeaderGrid = (GridView)sender;
                GridViewRow HeaderGridRow =
                     new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
                TableCell HeaderCell = new TableCell();
                HeaderCell.HorizontalAlign = HorizontalAlign.Center;
                HeaderCell.Text = "Half Part 1";
                HeaderCell.ColumnSpan = 4;
                HeaderGridRow.Cells.Add(HeaderCell);

                HeaderCell = new TableCell();
                HeaderCell.HorizontalAlign = HorizontalAlign.Center;
                HeaderCell.Text = "Half Part 2";
                HeaderCell.ColumnSpan = 4;
                HeaderGridRow.Cells.Add(HeaderCell);

                ExpGvToExcelControl.Controls[0].Controls.AddAt(0, HeaderGridRow);

            }
        }

        protected void ExpGvToExcelControl_PageIndexChanging(object sender,
                           System.Web.UI.WebControls.GridViewPageEventArgs e)
        {
            ExpGvToExcelControl.PageIndex = e.NewPageIndex;
            BindGrid();
        }
    }
}

Error 1 :
 For The type of above Error Click Here

DownLoad From Here

1 comment: