GridView to excel in C# Asp.net

Praveen Kumar Prajapati November 15, 2021

 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="UserDetailes.WebForm1" %>


<!DOCTYPE html>


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

                BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" DataKeyNames="id"

                BorderWidth="1px" CellPadding="3" CellSpacing="2" OnRowEditing="GridView1_RowEditing"

                OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting" OnRowCancelingEdit="GridView1_RowCancelingEdit">

                <Columns>


                    <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />

                    <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />

                    <asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />

                    <asp:CommandField HeaderText="Edit" ShowEditButton="True" />

                    <asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />


                </Columns>

                <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />

                <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />

                <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />

                <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />

                <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

                <SortedAscendingCellStyle BackColor="#FFF1D4" />

                <SortedAscendingHeaderStyle BackColor="#B95C30" />

                <SortedDescendingCellStyle BackColor="#F1E5CE" />

                <SortedDescendingHeaderStyle BackColor="#93451F" />

            </asp:GridView>

            

        </div>

        <div>

            <asp:Label ID="lblResult" runat="server"></asp:Label>

        </div>

        <div>

            <asp:Button ID="btnExcel" Text="GridView TO Excel" runat="server" OnClick="btnExcel_Click" />

        </div>

    </form>

</body>

</html>


-------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace UserDetailes
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!Page.IsPostBack)
            { 
                BindingData();
            }
        }
        string Connections = ConfigurationManager.ConnectionStrings["ConnectDB"].ConnectionString;

        private void BindingData()
        {
            using (SqlConnection con = new SqlConnection(Connections))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select * from tbl_Data";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }


        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            BindingData();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            BindingData();

        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            using (SqlConnection con = new SqlConnection(Connections))
            {
                int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
                GridViewRow gvRow = (GridViewRow)GridView1.Rows[e.RowIndex];
                //Label lblId = (Label)gvRow.FindControl("lblResult");
                TextBox txtName = (TextBox)gvRow.Cells[1].Controls[0];
                TextBox txtCity = (TextBox)gvRow.Cells[2].Controls[0];

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Update tbl_Data Set Name = '"+txtName.Text+"', city = '"+ txtCity.Text + "' where Id = '"+id+"' ";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                GridView1.EditIndex = -1;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            BindingData();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            using (SqlConnection con = new SqlConnection(Connections))
            {
             
                int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
                GridViewRow gvRow = (GridViewRow)GridView1.Rows[e.RowIndex];
                
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Delete from tbl_Data where Id = '" + id + "' ";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            BindingData();
        }

        public void GridViewToExcel()
        {
            StringBuilder stringBuilder = new StringBuilder();
            string strfileName = "GridViewToExcel_" + DateTime.Now.ToShortDateString() + ".csv";
            stringBuilder.Append("Name, City" + Environment.NewLine);
            foreach (GridViewRow row in GridView1.Rows)
            {
                string name = row.Cells[1].Text;
                string City = row.Cells[2].Text;
                stringBuilder.Append(name + "," + City + Environment.NewLine);
            }

            Response.Clear();
            Response.ContentType = "text/csv";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + strfileName);
            Response.Write(stringBuilder.ToString());
            Response.End();
        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            GridViewToExcel();
        }
    }
}
-------------------------------------------------------------------------------------------------------------------



0 Comments