Export selected rows of gridview to Excel or word in 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:
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
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export Selected records from Gridview to Excel/ Word</title>
<style type="text/css">
.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>
<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=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")
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 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. 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
