Exporting only selected or checked rows and records from grid to Word or Excel sheet using asp.net

Introduction: 
Here I will explain how to export only selected or checked rows or records from gridview to Word or Excel sheet using asp.net.
Description:

After all the concepts one of the user has asked me a question i.e. how to export gridview records to excel/word based on checkbox selection in gridview. To implement this one first design one table in database as shown below and give name as “UserInformation” 

ColumnName

DataType

UserId

Int(set identity property=true)

UserName

varchar(50)

LastName

varchar(50)

Location

varchar(50)

After completion of table creation enter some dummy data and design aspx page like this

<html xmlns=”http://www.w3.org/1999/xhtml”&gt;

<head id=”Head1″ runat=”server”>

<title>Export Selected records from Gridview to Excel/ Word</title>

<style type=”text/css”>

.GridviewDiv {font-size100%font-family‘Lucida Grande’, ‘Lucida Sans Unicode’, Verdana, Arial,Helevetica, sans-serifcolor#303933;}

Table.Gridview{border:solid 1px #df5015;}

.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;padding:0.5em0.5em 0.5em 0.5em;text-align:center}

.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;padding:0.5em 0.5em 0.5em0.5em;}

.Gridview tr{colorBlackbackground-colorWhitetext-align:left}

:link,:visited { color#DF4F13text-decoration:none }

.highlight {text-decorationnone;color:black;background:yellow;}

</style>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<table>

<tr>

<td align=”right”>

<asp:ImageButton ID=”btnExcel” runat=”server” ImageUrl=”~/ExcelImage.jpg”

onclick=”btnExportExcel_Click” />

<asp:ImageButton ID=”btnWord” runat=”server” ImageUrl=”~/WordImage.jpg”

onclick=”btnWord_Click” />

</td>

</tr>

<tr>

<td>

<div class=”GridviewDiv”>

<asp:GridView ID=”gvdetails” runat=”server” AutoGenerateColumns=”False” AllowPaging=”True”

AllowSorting=”true” Width=”540px” PageSize=”10″ CssClass=”Gridview” 

DataKeyNames=”UserId”OnPageIndexChanging=”gvdetails_PageIndexChanging” >

<HeaderStyle BackColor=”#df5015″ />

<Columns>

<asp:TemplateField>

<ItemTemplate>

<asp:CheckBox ID=”chkSelect” runat=”server” />

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField=”UserId” HeaderText=”UserId” />

<asp:BoundField DataField=”UserName” HeaderText=”UserName” />

<asp:BoundField DataField=”LastName” HeaderText=”LastName” />

<asp:BoundField DataField=”Location” HeaderText=”Location” />

</Columns>

</asp:GridView>

</div>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Now in code behind add following namespace references

using System;

using System.Collections;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Web.UI;

using System.Web.UI.WebControls;

After that write the following code in code behind
C# Code


protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridData();

}

}

/// <summary>

/// This Method is used to bind gridview

/// </summary>

private void BindGridData()

{

SqlConnection con = new SqlConnection(“Data Source=SD;Integrated Security=true;Initial Catalog=MySampleDB”);

SqlCommand cmd = new SqlCommand(“select * from UserInformation”, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

gvdetails.DataSource = ds;

gvdetails.DataBind();

}

public override void VerifyRenderingInServerForm(Control control)

{

/*Verifies that the control is rendered */

}

protected void gvdetails_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

SaveCheckedValues();

gvdetails.PageIndex = e.NewPageIndex;

BindGridData();

PopulateCheckedValues();

}

/// <summary>

/// This event is used to export gridview data to Excel document

/// </summary>

/// <param name=”sender”></param>

/// <param name=”e”></param>

protected void btnExportExcel_Click(object sender, EventArgs e)

{

ExportFunction(“attachment;filename=GridViewExport.xls”“application/vnd.ms-excel”);

}

/// <summary>

/// This event is used to export gridview data to word document

/// </summary>

/// <param name=”sender”></param>

/// <param name=”e”></param>

protected void btnWord_Click(object sender, EventArgs e)

{

ExportFunction(“attachment;filename=GridViewExport.doc”“application/vnd.ms-word”);

}

/// <summary>

/// This Function is used to generate Excel or word document with gridview checkbox selected values

/// </summary>

/// <param name=”header”></param>

/// <param name=”contentType”></param>

private void ExportFunction(string header, string contentType)

{

SaveCheckedValues();

Response.Clear();

Response.Buffer = true;

Response.AddHeader(“content-disposition”, header);

Response.Charset = “”;

Response.ContentType = contentType;

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

gvdetails.AllowPaging = false;

BindGridData();

gvdetails.HeaderRow.Style.Add(“background-color”“#FFFFFF”);

gvdetails.HeaderRow.Cells[0].Visible = false;

for (int i = 0; i < gvdetails.HeaderRow.Cells.Count; i++)

{

gvdetails.HeaderRow.Cells[i].Style.Add(“background-color”“#df5015”);

gvdetails.HeaderRow.Cells[i].Style.Add(“color”“#FFFFFF”);

}

if (ViewState[“CHECKED_ITEMS”] != null)

{

ArrayList CheckBoxArray = (ArrayList)ViewState[“CHECKED_ITEMS”];

int rowIdx = 0;

for (int i = 0; i < gvdetails.Rows.Count; i++)

{

GridViewRow row = gvdetails.Rows[i];

row.Visible = false;

int index = (int)gvdetails.DataKeys[row.RowIndex].Value;

if (CheckBoxArray.Contains(index))

{

row.Visible = true;

row.Cells[0].Visible = false;

}

}

}

gvdetails.RenderControl(hw);

Response.Output.Write(sw.ToString());

Response.End();

}

/// <summary>

///This method is used to populate the saved checked status of checkbox values

/// </summary>

private void PopulateCheckedValues()

{

ArrayList userdetails = (ArrayList)ViewState[“CHECKED_ITEMS”];

if (userdetails != null && userdetails.Count > 0)

{

foreach (GridViewRow gvrow in gvdetails.Rows)

{

int index = (int)gvdetails.DataKeys[gvrow.RowIndex].Value;

if (userdetails.Contains(index))

{

CheckBox myCheckBox = (CheckBox)gvrow.FindControl(“chkSelect”);

myCheckBox.Checked = true;

}

}

}

}

/// <summary>

/// This method is used to save the checkedstate of checkbox values

/// </summary>

private void SaveCheckedValues()

{

ArrayList userdetails = new ArrayList();

int index = -1;

foreach (GridViewRow gvrow in gvdetails.Rows)

{

index = (int)gvdetails.DataKeys[gvrow.RowIndex].Value;

bool result = ((CheckBox)gvrow.FindControl(“chkSelect”)).Checked;

// Check in the Session

if (ViewState[“CHECKED_ITEMS”] != null)

userdetails = (ArrayList)ViewState[“CHECKED_ITEMS”];

if (result)

{

if (!userdetails.Contains(index))

userdetails.Add(index);

}

else

userdetails.Remove(index);

}

if (userdetails != null && userdetails.Count > 0)

ViewState[“CHECKED_ITEMS”] = userdetails;

}

VB.NET Code

Imports System.Collections

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Imports System.Web.UI

Imports System.Web.UI.WebControls

Partial Public Class VBExportData

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs)

If Not IsPostBack Then

BindGridData()

End If

End Sub

”’ <summary>

”’ This Method is used to bind gridview

”’ </summary>

Private Sub BindGridData()

Dim con As New SqlConnection(“Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB”)

Dim cmd As New SqlCommand(“select * from UserInformation”, con)

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds)

gvdetails.DataSource = ds

gvdetails.DataBind()

End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

‘Verifies that the control is rendered

End Sub

Protected Sub gvdetails_PageIndexChanging(ByVal sender As Object

ByVal e As GridViewPageEventArgs)

SaveCheckedValues()

gvdetails.PageIndex = e.NewPageIndex

BindGridData()

PopulateCheckedValues()

End Sub

”’ <summary>

”’ This event is used to export gridview data to Excel document

”’ </summary>

”’ <param name=”sender”></param>

”’ <param name=”e”></param>

Protected Sub btnExportExcel_Click(ByVal sender As ObjectByVal e 

AsSystem.Web.UI.ImageClickEventArgs) Handles btnExcel.Click

ExportFunction(“attachment;filename=GridViewExport.xls”“application/vnd.ms-excel”)

End Sub

”’ <summary>

”’ This event is used to export gridview data to word document

”’ </summary>

”’ <param name=”sender”></param>

”’ <param name=”e”></param>

Protected Sub btnWord_Click(ByVal sender As ObjectByVal e As 

System.Web.UI.ImageClickEventArgs)Handles btnWord.Click

ExportFunction(“attachment;filename=GridViewExport.doc”“application/vnd.ms-word”)

End Sub

”’ <summary>

”’ This Function is used to generate Excel or word document with gridview checkbox selected values

”’ </summary>

”’ <param name=”header”></param>

”’ <param name=”contentType”></param>

Private Sub ExportFunction(ByVal header As StringByVal contentType As String)

SaveCheckedValues()

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)

gvdetails.AllowPaging = False

BindGridData()

gvdetails.HeaderRow.Style.Add(“background-color”“#FFFFFF”)

gvdetails.HeaderRow.Cells(0).Visible = False

For As Integer = 0 To gvdetails.HeaderRow.Cells.Count – 1

gvdetails.HeaderRow.Cells(i).Style.Add(“background-color”“#df5015”)

gvdetails.HeaderRow.Cells(i).Style.Add(“color”“#FFFFFF”)

Next

If ViewState(“CHECKED_ITEMS”IsNot Nothing Then

Dim CheckBoxArray As ArrayList = DirectCast(ViewState(“CHECKED_ITEMS”), ArrayList)

Dim rowIdx As Integer = 0

For As Integer = 0 To gvdetails.Rows.Count – 1

Dim row As GridViewRow = gvdetails.Rows(i)

row.Visible = False

Dim index As Integer = CInt(gvdetails.DataKeys(row.RowIndex).Value)

If CheckBoxArray.Contains(index) Then

row.Visible = True

row.Cells(0).Visible = False

End If

Next

End If

gvdetails.RenderControl(hw)

Response.Output.Write(sw.ToString())

Response.[End]()

End Sub

”’ <summary>

”’This method is used to populate the saved checked status of checkbox values

”’ </summary>

Private Sub PopulateCheckedValues()

Dim userdetails As ArrayList = DirectCast(ViewState(“CHECKED_ITEMS”), ArrayList)

If userdetails IsNot Nothing AndAlso userdetails.Count > 0 Then

For Each gvrow As GridViewRow In gvdetails.Rows

Dim index As Integer = CInt(gvdetails.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

”’ <summary>

”’ This method is used to save the checkedstate of checkbox values

”’ </summary>

Private Sub SaveCheckedValues()

Dim userdetails As New ArrayList()

Dim index As Integer = -1

For Each gvrow As GridViewRow In gvdetails.Rows

index = CInt(gvdetails.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

userdetails = DirectCast(ViewState(“CHECKED_ITEMS”), ArrayList)

End If

If result Then

If Not userdetails.Contains(index) Then

userdetails.Add(index)

End If

Else

userdetails.Remove(index)

End If

Next

If userdetails IsNot Nothing AndAlso userdetails.Count > 0 Then

ViewState(“CHECKED_ITEMS”) = userdetails

End If

End Sub

End Class

After that run your application output would be like this

If you observe above code I written code to export selected rows of gridview based on checkbox selection.  here I used “maintain state of checkboxes in while paging in gridview” concept to maintain the state of checkboxes while paging in gridview and I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”.

If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly. Here I used simple code to export gridview data to excel document and for word document we can use the same code (Export to Word) to import gridview data just by replacing GridViewExport.xls to GridViewExport.doc and application/ms-excel toapplication/ms-word

Demo

Now select checkboxes and click on Excel button you will get output like this

Demo for Excel document

Demo for Word document

Download : sample code attached

Now if you’re getting any error message please feel free to let me know ?

Advertisements

10 responses to this post.

  1. I’m not that a lot of a internet reader to be honest but your blogs truly nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. Cheers 31317

  2. In the event you are viewing come up with alter in most of the living, starting point usually L . a . Weight reduction cutting down on calories platform are a wide stair as part of your attaining that most agenda. weight loss 186412

  3. But wanna remark which you have a quite decent internet internet site , I really like the style it genuinely stands out. 622666

  4. Im often to blogging and i in actual fact respect your content material. The piece has truly peaks my interest. Im going to bookmark your content material and preserve checking for brand new details. 96904

  5. The posh distributed could be described as distinctive; customers are really yearning for bags is really a Native aspirations. Which strange surroundings is built that is to market diversity furthermore importance with travel and leisure market trends. hotels unique offers 282165

  6. It can be difficult to write about this topic. I feel you did an superb job though! Thanks for this! 231898

  7. Ill create a hyperlink to the web page about my personal weblog. 972903

  8. Nice blog! Only problem is im running Firefox on Debian, and the site is looking a little.. weird! Perhaps you may want to test it to see for yourself. 738953

  9. I like this internet web site because so significantly utile stuff on here : D. 8740

  10. Thank you for utilizing time in order to create “Exporting only
    selected or checked rows and records from grid to Word or Excel sheet using asp.
    net | Denno Secqtinstien Foundation”. Thanks a ton for a second time ,Junior

Comments are closed.

%d bloggers like this: