How to Export Selected rows of Gridview to Excel, Word and PDF in Asp.net | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

How to Export Selected rows of Gridview to Excel, Word and PDF in Asp.net

Introduction: In this article I have explained how to Export the Selected rows of Gridview to Excel, Word and PDF in asp.net.
Export Selected rows of Gridview

Description:

STUDENT_ID
int
STUDENT_NAME
varchar(50)
STUDENT_ADDRESS
varchar(50)
STUDENT_CLASS
varchar(50)

Here ID is autoincrement.
To Export Data we use the 3rd party Library. Here we use the iTextSharp DLL and reference. To download iTextSharp DLL click Here. Put the downloaded iTextSharp DLL in Bin folder and build the project/website and use namespace in .aspx.cs or .aspx.vb page.
In this exmaple user wil be able select/unselect all rows and can also select/unselect a single row to export. If user click on export button without select row than all data of Gridview will be exported.
Add a webform to project. Darg and drop the control Gridview, button etc. from Toolbox and desgin the .aspx as mention below:

<table align="center">  
    <tr><td>&nbsp;</td><td> <asp:GridView ID="grdstudent" runat="server"
            AutoGenerateColumns="false" DataKeyNames="STUDENT_ID"  PageSize="10" AllowPaging="True"
            onpageindexchanging="grdstudent_PageIndexChanging">           
        <Columns>       
        <asp:TemplateField>
        <HeaderTemplate>
<asp:CheckBox ID="chkSelectAll" runat="server" onclick="javascript:SelectheaderCheckboxes(this)"/>
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
        <asp:BoundField DataField="STUDENT_NAME" HeaderText="STUDENT NAME" />
        <asp:BoundField DataField="STUDENT_ADDRESS" HeaderText="STUDENT ADDRESS" />
        <asp:BoundField DataField="STUDENT_CLASS" HeaderText="STUDENT CLASS" />
        </Columns>
        </asp:GridView></td></tr></table>      
        <table align="center"> <tr><td></td><td>
            <asp:Button ID="btnword" runat="server" Text="Export To Word"
                onclick="btnword_Click" /></td><td>
        <asp:Button ID="Button1" runat="server" Text="Export To Excel"
            onclick="Button1_Click" /></td>
                    <td>
                        <asp:Button ID="btnpdf" runat="server" Text="Export to PDF"
                            onclick="btnpdf_Click" /></td></tr>                   
                    </table>

After that put the below given Javascript in between Head tag:

  <script type="text/javascript">
       function SelectheaderCheckboxes(headerchk) {
           debugger
           var gvdetail = document.getElementById('grdstudent');
           var i;
           if (headerchk.checked) {
               for (i = 0; i < gvdetail.rows.length; i++) {
                   var inputs = gvdetail.rows[i].getElementsByTagName('input');
                   inputs[0].checked = true;
               }
           }
           else {
               for (i = 0; i < gvdetail.rows.length; i++) {
                   var inputs = gvdetail.rows[i].getElementsByTagName('input');
                   inputs[0].checked = false;
               }
           }
       }
       function Selectchildcheckboxes(header) {
           var ck = header;
           var count = 0;
           var gvdetail = document.getElementById('grdstudent');
           var headerchk = document.getElementById(header);
           var rowcount = gvdetail.rows.length;
           for (i = 1; i < gvdetail.rows.length; i++) {
               var inputs = gvdetail.rows[i].getElementsByTagName('input');
               if (inputs[0].checked) {
                   count++;
               }
           }
           if (count == rowcount - 1) {
               headerchk.checked = true;
           }
           else {
               headerchk.checked = false;
           }
       }
</script>

Note: Please do not forget to add connectionString in web.config file:
<connectionStrings>
    <add name="con" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True" /> 
  </connectionStrings>

After that write the below given code on .aspx.cs page:

using System.IO;
using System.Collections;
using System.Text;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindgrid();
        }
       
    }
public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }

     private void Bindgrid()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Select * from STUDENT_DETAIL", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdstudent.DataSource = dt;
                grdstudent.DataBind();
            }
            else
            {
                dt.Rows.Add(dt.NewRow());
                grdstudent.DataSource = dt;
                grdstudent.DataBind();
                int columnCount = grdstudent.Rows[0].Cells.Count;
                grdstudent.Rows[0].Cells.Clear();
                grdstudent.Rows[0].Cells.Add(new TableCell());
                grdstudent.Rows[0].Cells[0].ColumnSpan = columnCount;
                grdstudent.Rows[0].Cells[0].Text = "<font color=Red><b><center>No Data Found !</center></b></font>";
            }
        }
        catch (Exception ex)
        {
        }
    }

protected void grdstudent_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        SaveCheckboxValues();
        grdstudent.PageIndex = e.NewPageIndex;
        Bindgrid();
       MaintainCheckedValues();
    }

  protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            ExportGridview("attachment; filename=StudentDetail.xls", "application/vnd.ms-excel");
   }
        catch (Exception ex)
        {
        }      
    }
  //Function to Export Gridview Data to Excel and Word
    private void ExportGridview(string header, string contentType)
    {
        try
        {
            SaveCheckboxValues();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", header);
            Response.Charset = "";
            Response.ContentType = contentType;
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            grdstudent.AllowPaging = false;
            this.Bindgrid();
            grdstudent.HeaderRow.Cells[0].Visible = false;
           
            foreach (TableCell cell in grdstudent.HeaderRow.Cells)
            {
                cell.BackColor = grdstudent.HeaderStyle.BackColor;
            }
            if (ViewState["CHECKED_ITEMS"] != null)
            {
                ArrayList CheckBoxArray = (ArrayList)ViewState["CHECKED_ITEMS"];
                int rowIdx = 0;
                for (int i = 0; i < grdstudent.Rows.Count; i++)
                {
                    GridViewRow row = grdstudent.Rows[i];
                    row.Visible = false;
                    int index = (int)grdstudent.DataKeys[row.RowIndex].Value;
                    if (CheckBoxArray.Contains(index))
                    {
                        row.Visible = true;
                        row.Cells[0].Visible = false;
                    }
                    rowIdx++;
                }
            }
            foreach (GridViewRow row in grdstudent.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = grdstudent.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = grdstudent.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                    List<Control> controls = new List<Control>();
                    foreach (Control control in cell.Controls)
                    {
                        controls.Add(control);
                    }
                    foreach (Control control in controls)
                    {
                        switch (control.GetType().Name)
                        {
                            case "CheckBox":
                                cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
                                cell.Visible = false;
                                break;
                        }
                        cell.Controls.Remove(control);
                    }
                }
            }

            grdstudent.RenderControl(hw);
            string style = @"<style> .textmode { } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
        }      
    }

private void SaveCheckboxValues()
    {
        ArrayList studentdetail = new ArrayList();
        int index = -1;
        foreach (GridViewRow gvrow in grdstudent.Rows)
        {
            index = (int)grdstudent.DataKeys[gvrow.RowIndex].Value;
            bool result = ((CheckBox)gvrow.FindControl("chkSelect")).Checked;
            if (ViewState["CHECKED_ITEMS"] != null)
                studentdetail = (ArrayList)ViewState["CHECKED_ITEMS"];
            if (result)
            {
                if (!studentdetail.Contains(index))
                    studentdetail.Add(index);
            }
            else
                studentdetail.Remove(index);
        }
        if (studentdetail != null && studentdetail.Count > 0)
            ViewState["CHECKED_ITEMS"] = studentdetail;
    }

    private void MaintainCheckedValues()
    {
        ArrayList userdetails = (ArrayList)ViewState["CHECKED_ITEMS"];
        if (userdetails != null && userdetails.Count > 0)
        {
            foreach (GridViewRow gvrow in grdstudent.Rows)
            {
                int index = (int)grdstudent.DataKeys[gvrow.RowIndex].Value;
                if (userdetails.Contains(index))
                {
                    CheckBox myCheckBox = (CheckBox)gvrow.FindControl("chkSelect");
                    myCheckBox.Checked = true;
                }
            }
        }
    }

    protected void btnword_Click(object sender, EventArgs e)
    {
        ExportGridview("attachment; filename=StudentDetail's.doc", "application/vnd.ms-word");
     }
//Function to Export Gridview Data to PDF
    protected void btnpdf_Click(object sender, EventArgs e)
    {
        try
        {
            SaveCheckboxValues();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment;filename=StudentDetail's.pdf"));
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.Charset = "";
            Response.ContentType = "application/pdf";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            grdstudent.AllowPaging = false;
            Bindgrid();
            grdstudent.HeaderRow.Style.Add("background-color", "#FFFFFF");
            grdstudent.HeaderRow.Cells[0].Visible = false;
            foreach (TableCell cell in grdstudent.HeaderRow.Cells)
            {
                cell.BackColor = grdstudent.HeaderStyle.BackColor;
            }
            if (ViewState["CHECKED_ITEMS"] != null)
            {
                ArrayList CheckBoxArray = (ArrayList)ViewState["CHECKED_ITEMS"];
                int rowIdx = 0;
                for (int i = 0; i < grdstudent.Rows.Count; i++)
                {
                    GridViewRow row = grdstudent.Rows[i];
                    row.Visible = false;
                    int index = (int)grdstudent.DataKeys[row.RowIndex].Value;
                    if (CheckBoxArray.Contains(index))
                    {
                        row.Visible = true;
                        row.Cells[0].Visible = false;
                    }
                    rowIdx++;
                }
            }
            foreach (GridViewRow row in grdstudent.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = grdstudent.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = grdstudent.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                    List<Control> controls = new List<Control>();
                    foreach (Control control in cell.Controls)
                    {
                        controls.Add(control);
                    }
                    foreach (Control control in controls)
                    {
                        switch (control.GetType().Name)
                        {
                            case "CheckBox":
                                cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
                                cell.Visible = false;
                                break;
                        }
                        cell.Controls.Remove(control);
                    }
                }
            }

            grdstudent.RenderControl(hw);
            string style = @"<style> .textmode { } </style>";
            Response.Write(style);
            StringReader sr = new StringReader(sw.ToString());
            Document PDF = new Document(PageSize.A4, 10f, 10f, 100f, 10f);
            HTMLWorker htmlparser = new HTMLWorker(PDF);
            PdfWriter.GetInstance(PDF, Response.OutputStream);
            PDF.Open();
            htmlparser.Parse(sr);
            PDF.Close();
            Response.Output.Write(sw.ToString());
            Response.End();
        }
        catch (Exception ex)
        {
        }
    }

In VB (.aspx.vb)

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Collections
Imports System.Text
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Bindgrid()
        End If
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered
    End Sub
    Private Sub Bindgrid()
        Dim adp As New SqlDataAdapter("Select * from STUDENT_DETAIL", con)
        Dim dt As New DataTable()
        adp.Fill(dt)
        If dt.Rows.Count > 0 Then
            grdstudent.DataSource = dt
            grdstudent.DataBind()
        Else
            dt.Rows.Add(dt.NewRow())
            grdstudent.DataSource = dt
            grdstudent.DataBind()
            Dim columnCount As Integer = grdstudent.Rows(0).Cells.Count
            grdstudent.Rows(0).Cells.Clear()
            grdstudent.Rows(0).Cells.Add(New TableCell())
            grdstudent.Rows(0).Cells(0).ColumnSpan = columnCount
            grdstudent.Rows(0).Cells(0).Text = "<font color=Red><b><center>No Data Found !</center></b></font>"
        End If
    End Sub

    Protected Sub btnword_Click(sender As Object, e As System.EventArgs) Handles btnword.Click
        Try
            ExportGridview("attachment; filename=StudentDetail's.doc", "application/vnd.ms-word")
        Catch ex As Exception
        End Try
End Sub

    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        Try
            ExportGridview("attachment; filename=StudentDetail.xls", "application/vnd.ms-excel")
        Catch ex As Exception
        End Try
End Sub

    Protected Sub btnpdf_Click(sender As Object, e As System.EventArgs) Handles btnpdf.Click
        Try
            SaveCheckboxValues()
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", String.Format("attachment;filename=StudentDetail's.pdf"))
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.Charset = ""
            Response.ContentType = "application/pdf"
            Dim sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)
            grdstudent.AllowPaging = False
            Bindgrid()
            grdstudent.HeaderRow.Style.Add("background-color", "#FFFFFF")
            grdstudent.HeaderRow.Cells(0).Visible = False
            For Each cell As TableCell In grdstudent.HeaderRow.Cells
                cell.BackColor = grdstudent.HeaderStyle.BackColor
            Next
            If ViewState("CHECKED_ITEMS") IsNot Nothing Then
                Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CHECKED_ITEMS"), ArrayList)
                Dim rowIdx As Integer = 0
                For i As Integer = 0 To grdstudent.Rows.Count - 1
                    Dim row As GridViewRow = grdstudent.Rows(i)
                    row.Visible = False
                    Dim index As Integer = CInt(grdstudent.DataKeys(row.RowIndex).Value)
                    If CheckBoxArray.Contains(index) Then
                        row.Visible = True
                        row.Cells(0).Visible = False
                    End If
                    rowIdx += 1
                Next
            End If
            For Each row As GridViewRow In grdstudent.Rows
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = grdstudent.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = grdstudent.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                    Dim controls As New List(Of Control)()
                    For Each control As Control In cell.Controls
                        controls.Add(control)
                    Next
                    For Each control As Control In controls
                        Select Case control.[GetType]().Name
                            Case "CheckBox"
                                cell.Controls.Add(New Literal() With { _
                                 .Text = TryCast(control, CheckBox).Text _
                                })
                                cell.Visible = False
                                Exit Select
                        End Select
                        cell.Controls.Remove(control)
                    Next
                Next
            Next

            grdstudent.RenderControl(hw)
            Dim style As String = "<style> .textmode { } </style>"
            Response.Write(style)
            Dim sr As New StringReader(sw.ToString())
            Dim PDF As New Document(PageSize.A4, 10.0F, 10.0F, 100.0F, 10.0F)
            Dim htmlparser As New HTMLWorker(PDF)
            PdfWriter.GetInstance(PDF, Response.OutputStream)
            PDF.Open()
            htmlparser.Parse(sr)
            PDF.Close()
            Response.Output.Write(sw.ToString())
            Response.[End]()
        Catch ex As Exception
        End Try
    End Sub

    'Function to Export Gridview Data to Excel and Word
    Private Sub ExportGridview(header As String, contentType As String)
        Try
            SaveCheckboxValues()
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", header)
            Response.Charset = ""
            Response.ContentType = contentType
            Dim sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)
            grdstudent.AllowPaging = False
            Me.Bindgrid()
            grdstudent.HeaderRow.Cells(0).Visible = False
            For Each cell As TableCell In grdstudent.HeaderRow.Cells
                cell.BackColor = grdstudent.HeaderStyle.BackColor
            Next
            If ViewState("CHECKED_ITEMS") IsNot Nothing Then
                Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CHECKED_ITEMS"), ArrayList)
                Dim rowIdx As Integer = 0
                For i As Integer = 0 To grdstudent.Rows.Count - 1
                    Dim row As GridViewRow = grdstudent.Rows(i)
                    row.Visible = False
                    Dim index As Integer = CInt(grdstudent.DataKeys(row.RowIndex).Value)
                    If CheckBoxArray.Contains(index) Then
                        row.Visible = True
                        row.Cells(0).Visible = False
                    End If
                    rowIdx += 1
                Next
            End If
            For Each row As GridViewRow In grdstudent.Rows
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = grdstudent.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = grdstudent.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                    Dim controls As New List(Of Control)()
                    For Each control As Control In cell.Controls
                        controls.Add(control)
                    Next
                    For Each control As Control In controls
                        Select Case control.[GetType]().Name
                            Case "CheckBox"
                                cell.Controls.Add(New Literal() With { _
                                 .Text = TryCast(control, CheckBox).Text _
                                })
                                cell.Visible = False
                                Exit Select
                        End Select
                        cell.Controls.Remove(control)
                    Next
                Next
            Next

            grdstudent.RenderControl(hw)
            Dim style As String = "<style> .textmode { } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub grdstudent_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdstudent.PageIndexChanging
        SaveCheckboxValues()
        grdstudent.PageIndex = e.NewPageIndex
        Bindgrid()
        MaintainCheckedValues()
    End Sub

    Private Sub SaveCheckboxValues()
        Dim studentdetail As New ArrayList()
        Dim index As Integer = -1
        For Each gvrow As GridViewRow In grdstudent.Rows
            index = CInt(grdstudent.DataKeys(gvrow.RowIndex).Value)
            Dim result As Boolean = DirectCast(gvrow.FindControl("chkSelect"), CheckBox).Checked
            ' Check in the Session
            If ViewState("CHECKED_ITEMS") IsNot Nothing Then
                studentdetail = DirectCast(ViewState("CHECKED_ITEMS"), ArrayList)
            End If
            If result Then
                If Not studentdetail.Contains(index) Then
                    studentdetail.Add(index)
                End If
            Else
                studentdetail.Remove(index)
            End If
        Next
        If studentdetail IsNot Nothing AndAlso studentdetail.Count > 0 Then
            ViewState("CHECKED_ITEMS") = studentdetail
        End If
    End Sub

    Private Sub MaintainCheckedValues()
        Dim userdetails As ArrayList = DirectCast(ViewState("CHECKED_ITEMS"), ArrayList)
        If userdetails IsNot Nothing AndAlso userdetails.Count > 0 Then
            For Each gvrow As GridViewRow In grdstudent.Rows
                Dim index As Integer = CInt(grdstudent.DataKeys(gvrow.RowIndex).Value)
                If userdetails.Contains(index) Then
                    Dim myCheckBox As CheckBox = DirectCast(gvrow.FindControl("chkSelect"), CheckBox)
                    myCheckBox.Checked = True
                End If
            Next
        End If
    End Sub

If you get any error "Control 'grdstudent' of type 'GridView' must be placed inside a form tag with runat=server" read this article:


http://articlemirror.blogspot.in/2013/07/control-grdstudent-of-type-gridview.html

http://articlemirror.blogspot.in/2013/07/a-page-can-have-only-one-server-side.html

Now run the project and check out the result.

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget