Export selected rows of gridview to Excel or word in ASP.Net

By: Suresh Dasari Dec 12, 2011

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:

In my previous posts I explained clearly how to export gridview data to Excel or word using asp.net and how to export gridview data to PDF using asp.net and explained another post that is Export gridview data to CSV file using asp.net. After explain 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

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

.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}

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

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

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

.Gridview tr{color: Black; background-color: White; text-align:left}

:link,:visited { color: #DF4F13; text-decoration:none }

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

</style>

</head>

<body>

<div>

<table>

<tr>

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

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

AllowSorting="true" Width="540px" PageSize="10" CssClass="Gridview" DataKeyNames="UserId" OnPageIndexChanging="gvdetails_PageIndexChanging" >

<asp:TemplateField>

<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=SureshDasari;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 Object, ByVal 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 Object, ByVal e As System.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 Object, ByVal 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 String, ByVal 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 i 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")

Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CHECKED_ITEMS"), ArrayList)

Dim rowIdx As Integer = 0

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

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

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

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. In previous post I explained clearly how to maintain state of checkboxes in while paging in gridview here I used same 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 to application/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 like

Control 'gvdetails' of type 'GridView' must be placed inside a form tag with runat=server

Check this post to solve this problem

http://www.aspdotnet-suresh.com/2011/03/control-gvdetails-of-type-gridview-must.html

Otherwise if you’re getting any error message like

RegisterForEventValidation can only be called during Render();

Check this post to solve your problem

http://www.aspdotnet-suresh.com/2011/04/registerforeventvalidation-can-only-be.html

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

16 comments :

Anonymous said...: this is a great link but i got a problem when i run it ....
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
pls sort out this; May 28, 2012 at 4:28 AM
sajid zafar said...: i m facing a problem , when i click on the export to excel button then it will produce an error on save checked values method that specified cast in not valid
int index =-1;
foreach (GridViewRow gvrow in GridGenome.Rows)
{
index = (int)GridGenome.DataKeys[gvrow.RowIndex].Value;
Please help me out; June 23, 2012 at 8:41 PM
Anonymous said...: This error is related to your datakeyname datatype.
in his post his datakeyname is an int datatype, so your datakeyname you specified for GridGenome should be int too if it is not int convert it to match the index datatype. or create a Uid int as what he did and it will work for u.; August 5, 2012 at 12:47 AM
Aruna said...: Hi,

I am trying to read the MS Word Document table and trying to display the same inthe gridview. I am able to read the word content but not able to store in Datatable and display the same in gridview?; September 12, 2012 at 1:44 AM
Emmanuel Constant said...: Hello great tutorial.

I had a question, based on your tutorial, lets say I wanted to select a value from the gridview and use that value to export another query from a separate data source using the value from the gridview column, how would you do that?; September 14, 2012 at 10:00 AM
Anonymous said...: How about using session variable instead table in database?; September 30, 2012 at 8:05 PM
Unknown said...: hai ur post is very useful to me..
can u give code for this scenario suppose we have two dropdownlist A(outside the gridview) and B((outside the gridview)) and a gridview. Within that gridview we have two dropdownlist C and D. Comparing this dropdownlist A and B our C(within gridview) and D(within gridview) dropdownlist should populate watever selecting in dropdownlist C should not listed in dropdownlist D; October 5, 2012 at 8:32 AM
Anonymous said...: i need some help for excel importing to sql
once the excel is imported again it should not import the existing data it should start with new row is it possible; October 25, 2012 at 6:14 AM
Anonymous said...: GREAT POST!

I just need a way to print the gridview by pages. I have a large gridview of 4000, paging allowed=20...so I need it to print a page of 20, then the next 20 on the next page with header, etc..; November 6, 2012 at 9:15 AM
Anonymous said...: simply fantastic!

You are my favourite asp.net teacher Suresh. :); December 13, 2012 at 7:22 AM
Anonymous said...: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index; December 27, 2012 at 3:48 AM
Unknown said...: Dear sir ,
I got some error below mention.

int index =-1;
foreach (GridViewRow gvrow in GridGenome.Rows)
{
index = (int)GridGenome.DataKeys[gvrow.RowIndex].Value;
Please help me out
Rgards,
Daya; December 31, 2012 at 9:39 PM
Unknown said...: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Pls Help ME out of this out this
Thanks in Advance...; July 1, 2013 at 3:52 AM
Baiju EP said...: Dear suresh i am getting following error
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index; September 22, 2013 at 12:51 AM
Unknown said...: Hi all i need how to fill pre formated excel spread sheet using c# or jquery(I select one checkbox from gridview that data is fill dynamically preformated excel spreadsheet) please help me; May 26, 2014 at 8:44 PM
Unknown said...: how to get the selected column data using checkbox and export the html table into excel; December 12, 2019 at 9:54 PM