Monday 22 September 2014

Export GridView selected rows to Excel or word in ASP.NET using C#/VB.NET

Hi friends,in this I will explain about how to Export GridView selected rows to Excel or word in ASP.NET using C#/VB.NET
To explain further about how to export selected row data from asp.net gridview, we need to create database table to read data and bind retrieved resultset to gridview, so simply execute following script to sql query editor to create database table and then add few records manually or download complete example code with script at the end of the page.

 Here is the script to create “User_Details” table:

CREATE TABLE [dbo].[User_Details](
     [Username] [nvarchar](100) NULL,    
     [USER_ID] [int] IDENTITY(1,1) NOT NULL,
     [Gender] [varchar](15) NULL,
     [Country] [varchar](50) NULL   
)

Create web page as ExportSelectRrcordsGridview.aspx and write the following code in it.
ASP.NET:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Export GridView selected rows to Excel or word in ASP.NET</title>
    <style type="text/css">
        .btn
        {
            width: 150px;
            padding: 2px 5px;
            font-weight: bold;
            font-size: 13px;
            font-family: Tahoma, Arial;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    <asp:GridView ID="GridData" runat="server" BackColor="White" BorderColor="#CC9966"
                        BorderStyle="Solid" AutoGenerateColumns="False" BorderWidth="1px" CellPadding="4"
                        Font-Names="Georgia" DataKeyNames="User_ID" Font-Size="Small">
                        <Columns>
                            <asp:TemplateField>
                                <ItemTemplate>
                                    <asp:CheckBox ID="chkSelect" runat="server" />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="User_ID" HeaderText="User_ID" SortExpression="User_ID" />
                            <asp:BoundField DataField="UserName" HeaderText="User Name" SortExpression="UserName" />
                            <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                            <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                        </Columns>
                        <FooterStyle BackColor="Tan" />
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                    </asp:GridView>
                </td>
                <td>
                  
                   
                    <asp:Button ID="btnExportToExcel" runat="server" Text="Export To Excel" CssClass="btn"
                        OnClick="btnExportToExcel_Click"></asp:Button><br />
                    <br />
                    <asp:Button ID="btnExportToWord" runat="server" Text="Export To Word" CssClass="btn"
                        OnClick="btnExportToWord_Click"></asp:Button><br />
                    <br />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>


C#.NET:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Collections;

public partial class ExportSelectRrcordsGridview : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridData();
        }
    }

    private void BindGridData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        SqlCommand cmd = new SqlCommand("select * from User_Details ORDER BY USER_ID ASC", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        ViewState["ds"] = ds;
        GridData.DataSource = ds;
        GridData.DataBind();
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        /*Verifies that the control is rendered */
    }
  
    protected void GridData_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        CheckedRecordsData();
        GridData.PageIndex = e.NewPageIndex;
        BindGridData();
    }

    private void ExportSelectedData(string header, string contentType)
    {
        CheckedRecordsData();
        Response.ClearContent();
        Response.AddHeader("content-disposition", header);
        Response.ContentType = contentType;
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        if (ViewState["Checked_records"] != null)
        {
            ArrayList CheckBoxArray = (ArrayList)ViewState["Checked_records"];

            for (int i = 0; i < GridData.Rows.Count; i++)
            {
                GridViewRow row = GridData.Rows[i];
                row.Visible = false;
                int index = (int)GridData.DataKeys[row.RowIndex].Value;
                if (CheckBoxArray.Contains(index))
                {
                    row.Visible = true;
                    row.Cells[0].Visible = false;
                }
                else
                {
                    row.Visible = false;
                    row.Cells[0].Visible = true;
                }
            }
        }
        GridData.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.End();
    }

    private void CheckedRecordsData()
    {
        ArrayList userdetails = new ArrayList();
        int rowindex = -1;
        foreach (GridViewRow row in GridData.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                rowindex = (int)GridData.DataKeys[row.RowIndex].Value;
                bool result = (row.FindControl("chkSelect") as CheckBox).Checked;

                if (ViewState["Checked_records"] != null)
                    userdetails = (ArrayList)ViewState["Checked_records"];
                if (result)
                {
                    if (!userdetails.Contains(rowindex))
                        userdetails.Add(rowindex);
                }
                else
                    userdetails.Remove(rowindex);
            }
        }

        // if (userdetails != null && userdetails.Count > 0)
        ViewState["Checked_records"] = userdetails;
    }

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        ExportSelectedData("attachment;filename=GridViewExport.xls", "application/vnd.ms-excel");

    }
 
    protected void btnExportToWord_Click(object sender, EventArgs e)
    {
        ExportSelectedData("attachment;filename=GridViewExport.doc", "application/vnd.ms-word");

    }
}

VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Collections

Partial Public Class ExportSelectRrcordsGridview
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            BindGridData()
        End If
    End Sub

    Private Sub BindGridData()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
        Dim cmd As New SqlCommand("select * from User_Details ORDER BY USER_ID ASC", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        ViewState("ds") = ds
        GridData.DataSource = ds
        GridData.DataBind()
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        'Verifies that the control is rendered

    End Sub

    Protected Sub GridData_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        CheckedRecordsData()
        GridData.PageIndex = e.NewPageIndex
        BindGridData()
    End Sub

    Private Sub ExportSelectedData(header As String, contentType As String)
        CheckedRecordsData()
        Response.ClearContent()
        Response.AddHeader("content-disposition", header)
        Response.ContentType = contentType
        Dim sw As New StringWriter()
        Dim hw As New HtmlTextWriter(sw)
        If ViewState("Checked_records") IsNot Nothing Then
            Dim CheckBoxArray As ArrayList = DirectCast(ViewState("Checked_records"), ArrayList)

            For i As Integer = 0 To GridData.Rows.Count - 1
                Dim row As GridViewRow = GridData.Rows(i)
                row.Visible = False
                Dim index As Integer = CInt(GridData.DataKeys(row.RowIndex).Value)
                If CheckBoxArray.Contains(index) Then
                    row.Visible = True
                    row.Cells(0).Visible = False
                Else
                    row.Visible = False
                    row.Cells(0).Visible = True
                End If
            Next
        End If
        GridData.RenderControl(hw)
        Response.Output.Write(sw.ToString())
        Response.[End]()
    End Sub

    Private Sub CheckedRecordsData()
        Dim userdetails As New ArrayList()
        Dim rowindex As Integer = -1
        For Each row As GridViewRow In GridData.Rows
            If row.RowType = DataControlRowType.DataRow Then
                rowindex = CInt(GridData.DataKeys(row.RowIndex).Value)
                Dim result As Boolean = TryCast(row.FindControl("chkSelect"), CheckBox).Checked

                If ViewState("Checked_records") IsNot Nothing Then
                    userdetails = DirectCast(ViewState("Checked_records"), ArrayList)
                End If
                If result Then
                    If Not userdetails.Contains(rowindex) Then
                        userdetails.Add(rowindex)
                    End If
                Else
                    userdetails.Remove(rowindex)
                End If
            End If
        Next

        ' if (userdetails != null && userdetails.Count > 0)
        ViewState("Checked_records") = userdetails
    End Sub

    Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)
        ExportSelectedData("attachment;filename=GridViewExport.xls", "application/vnd.ms-excel")

    End Sub

    Protected Sub btnExportToWord_Click(sender As Object, e As EventArgs)
        ExportSelectedData("attachment;filename=GridViewExport.doc", "application/vnd.ms-word")

    End Sub
End Class























The output of the above page as shown in the below figure

Export GridView selected rows to Excel or word in ASP.NET using C#/VB.NET

When you click on Export To Excel then selected rows will be export to excel as shown in the below figure
Export GridView selected rows to Excel or word in ASP.NET using C#/VB.NET


When you click on Export To Word then selected rows will be export to Word as shown in the below figure
Export GridView selected rows to Excel or word in ASP.NET using C#/VB.NET

You can download the code by clicking on the below Download image. 
Posted by
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.