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.

Description:

In the last article i have explained How to Export Gridview Data to Excel in Asp.net, Nested Gridview Example in Asp.net OR Gridview inside Gridview in asp.net and How to Export Gridview Data to PDF in Asp.net.

I have a Table STUDENT_DETAIL:

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 3^rd 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:

<tr><td> </td><td> <asp:GridView ID="grdstudent" runat="server"

AutoGenerateColumns="false" DataKeyNames="STUDENT_ID" PageSize="10" AllowPaging="True"

onpageindexchanging="grdstudent_PageIndexChanging">

<asp:TemplateField>

<asp:CheckBox ID="chkSelectAll" runat="server" onclick="javascript:SelectheaderCheckboxes(this)"/>

</HeaderTemplate>

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

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

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>

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

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

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)

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)

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

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

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)

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)

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

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

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.

Thursday, August 29, 2013

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

Is it helpful?

No comments:

Post a Comment

Popular Posts

Labels