Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Sunday, October 19, 2014

Export Gridview Data to Excel in ASP.NET with C#

Export GridView to Excel in ASP.Net with Formatting:



In this Post we try to transfer grid view data to excel by C# code for this we give a very sort and efficient Example of export your data which are display in gridview and datagrid control are exported into excel format in asp.net application.

Examples related to GridView:

Create DataTable for Export gridview to excel:

Now before creating the application, let us create a DataTable with some records for the GridView, the DataTable has the following fields:
  • Product Id,
  • Product Name,
  • Price,
  • Quantity.

Create DataTable by C# code:


        DataTable dt = new DataTable();
        dt.Columns.Add("ProductId", typeof(Int32));
        dt.Columns.Add("ProductName", typeof(string));
        dt.Columns.Add("Price", typeof(string));
        dt.Columns.Add("Quantity", typeof(string));
        dt.Rows.Add(1, "Lux", "10Rs","10");
        dt.Rows.Add(2, "Mobile", "10000Rs","20");
        dt.Rows.Add(3, "Sugar", "50/kg","10");
        dt.Rows.Add(4, "Biskit", "20Rs","10");

Change the Header Row back to white color:

        gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

Export GridView data to excel using ASP.NET and c#:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportGridviewData.aspx.cs"
    Inherits="ExportGridviewData" %>

<!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>Export Gridview Data in to Excel by C#</title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        <asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" Height="186px">
            <Columns>
                <asp:BoundField DataField="ProductId" HeaderText="Product Id" ItemStyle-Width="100">
                    <ItemStyle Width="100px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="ProductName" HeaderText="Product Name" ItemStyle-Width="150">
                    <ItemStyle Width="150px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-Width="150">
                    <ItemStyle Width="150px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Quantity" HeaderText="Quantity" ItemStyle-Width="150">
                    <ItemStyle Width="150px"></ItemStyle>
                </asp:BoundField>
                <%--<asp:BoundField DataField="Quantity" HeaderText="Quantity" ItemStyle-Width="150">
                    <ItemStyle Width="150px"></ItemStyle>
                </asp:BoundField>--%>
            </Columns>
            <HeaderStyle BackColor="#990000" ForeColor="White" BorderColor="#0000CC" BorderStyle="Groove" />
        </asp:GridView>
        <br />
        <asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="btnExport_Click" />
    </div>
    </form>
</body>
</html>


C# code for convert grid data in t Excel File:


using System;
using System.Data;
using System.IO;
using System.Web.UI;

public partial class ExportGridviewData : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridview();
        }
    }
    protected void BindGridview()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("ProductId", typeof(Int32));
        dt.Columns.Add("ProductName", typeof(string));
        dt.Columns.Add("Price", typeof(string));
        dt.Columns.Add("Quantity", typeof(string));
        dt.Rows.Add(1, "Lux", "10Rs","10");
        dt.Rows.Add(2, "Mobile", "10000Rs","20");
        dt.Rows.Add(3, "Sugar", "50/kg","10");
        dt.Rows.Add(4, "Biskit", "20Rs","10");


        gvDetails.DataSource = dt;
        gvDetails.DataBind();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "ProductList.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gvDetails.AllowPaging = false;
        BindGridview();
        //Change the Header Row back to white color
        gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
        //Applying stlye to gridview header cells
        for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)
        {
            gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#990000");
        }
        gvDetails.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

}
Export Gridview Data to Excel in ASP.NET with C# by parijat
Export Gridview Data to Excel 

Asp.net related Post :

No comments:

Post a Comment