This was asked to me by many readers who read the articles of Exporting GridView to Excel. So finally here’s it. Basically I have used my following past three articles on GridView
Using JavaScript with ASP.Net GridView Control
Preserving state of Checkboxes while paging in ASP.Net GridView Control
Export GridView with Images to Word, Excel and PDF Formats in ASP.Net
to achieve this task
Concept
GridView has a checkbox Template field where user can select the records on one or multiple pages in the GridView control and when he clicks export button the only selected rows will be exported to the Excel document or Sheet.
Database
For this example I have used Northwind Sample SQL Server Database which can be downloaded using the link below.
Download Northwind Database
Once downloaded you’ll need to attach the database to you SQL Server Instance
GridView Markup
Below is the GridView Markup as you’ll notice below I have added a header check all checkbox and checkbox template field
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging" DataKeyNames = "CustomerID" >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server" onclick = "checkAll(this)" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"
HeaderText = "CustomerID" />
<asp:BoundField ItemStyle-Width = "150px" DataField = "City"
HeaderText = "City"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "Country"
HeaderText = "Country"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"
HeaderText = "PostalCode"/>
</Columns>
</asp:GridView>
For the check all functionality I have used JavaScript. More information on the same you’ll find it on my article.
Using JavaScript with ASP.Net GridView Control
Binding the GridView
Below is the function that’s used to bind the data to the ASP.Net GridView control, which simply fires a select query on the customers table of the Northwind database.
C#
private void BindGrid()
{
string strQuery = "select CustomerID,City,Country,PostalCode" +
" from customers";
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
VB.Net
Private Sub BindGrid()
Dim strQuery As String = "select CustomerID,City,Country,PostalCode" & _
" from customers"
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration.ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand(strQuery)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception
Throw ex
Finally
con.Close()
sda.Dispose()
con.Dispose()
End Try
End Sub
Preserving the state of checkboxes
The concept of the preserving the state of checkboxes has already been explained in detailed in my article Preserving state of Checkboxes while paging in ASP.Net GridView Control . Here I am using some of its concepts to remember the list of checkboxes that are selected by the user. The idea is to allow the user to select any one or set of records on any page throughout the GridView using checkboxes
There are two functions help to achieve the functionality
C#
private void PopulateCheckBoxArray()
{
ArrayList CheckBoxArray;
if (ViewState["CheckBoxArray"] != null)
{
CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];
}
else
{
CheckBoxArray = new ArrayList();
}
int CheckBoxIndex;
bool CheckAllWasChecked = false;
CheckBox chkAll = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkAll");
string checkAllIndex = "chkAll-" + GridView1.PageIndex;
if (chkAll.Checked)
{
if (CheckBoxArray.IndexOf(checkAllIndex) == -1)
{
CheckBoxArray.Add(checkAllIndex);
}
}
else
{
if (CheckBoxArray.IndexOf(checkAllIndex) != -1)
{
CheckBoxArray.Remove(checkAllIndex);
CheckAllWasChecked = true;
}
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
if (GridView1.Rows[i].RowType == DataControlRowType.DataRow)
{
CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");
CheckBoxIndex = GridView1.PageSize * GridView1.PageIndex + (i + 1);
if (chk.Checked)
{
if (CheckBoxArray.IndexOf(CheckBoxIndex) == -1 && !CheckAllWasChecked)
{
CheckBoxArray.Add(CheckBoxIndex);
}
}
else
{
if (CheckBoxArray.IndexOf(CheckBoxIndex) != -1 || CheckAllWasChecked)
{
CheckBoxArray.Remove(CheckBoxIndex);
}
}
}
}
ViewState["CheckBoxArray"] = CheckBoxArray;
}
VB.Net
Private Sub PopulateCheckBoxArray()
Dim CheckBoxArray As ArrayList
If ViewState("CheckBoxArray") IsNot Nothing Then
CheckBoxArray = DirectCast(ViewState("CheckBoxArray"), ArrayList)
Else
CheckBoxArray = New ArrayList()
End If
Dim CheckBoxIndex As Integer
Dim CheckAllWasChecked As Boolean = False
Dim chkAll As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex
If chkAll.Checked Then
If CheckBoxArray.IndexOf(checkAllIndex) = -1 Then
CheckBoxArray.Add(checkAllIndex)
End If
Else
If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then
CheckBoxArray.Remove(checkAllIndex)
CheckAllWasChecked = True
End If
End If
For i As Integer = 0 To GridView1.Rows.Count - 1
If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then
Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)
CheckBoxIndex = GridView1.PageSize * GridView1.PageIndex + (i + 1)
If chk.Checked Then
If CheckBoxArray.IndexOf(CheckBoxIndex) = -1 AndAlso Not CheckAllWasChecked Then
CheckBoxArray.Add(CheckBoxIndex)
End If
Else
If CheckBoxArray.IndexOf(CheckBoxIndex) <> -1 OrElse CheckAllWasChecked Then
CheckBoxArray.Remove(CheckBoxIndex)
End If
End If
End If
Next
ViewState("CheckBoxArray") = CheckBoxArray
End Sub
The above function basically maintains a list selected records list in ViewState so that while exporting we know what all records are selected by the user. This function is called up each time on page a PostBack occurs in the Page Load event of the page refer below
C#
protected void Page_Load(object sender, EventArgs e)
{
Response.Cache.SetCacheability(HttpCacheability.NoCache);
if (!IsPostBack)
{
PopulateCheckBoxArray();
}
BindGrid();
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Response.Cache.SetCacheability(HttpCacheability.NoCache)
If Not IsPostBack Then
PopulateCheckBoxArray()
End If
BindGrid()
End Sub
Now the next function is given below
C#
private void ResetCheckBoxes()
{
if (ViewState["CheckBoxArray"] != null)
{
ArrayList CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];
string checkAllIndex = "chkAll-" + GridView1.PageIndex;
if (CheckBoxArray.IndexOf(checkAllIndex) != -1)
{
CheckBox chkAll = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkAll");
chkAll.Checked = true;
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
if (GridView1.Rows[i].RowType == DataControlRowType.DataRow)
{
if (CheckBoxArray.IndexOf(checkAllIndex) != -1)
{
CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");
chk.Checked = true;
}
else
{
int CheckBoxIndex = GridView1.PageSize * (GridView1.PageIndex) + (i + 1);
if (CheckBoxArray.IndexOf(CheckBoxIndex) != -1)
{
CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");
chk.Checked = true;
}
}
}
}
}
}
VB.Net
Private Sub ResetCheckBoxes()
If ViewState("CheckBoxArray") IsNot Nothing Then
Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CheckBoxArray"), ArrayList)
Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex
If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then
Dim chkAll As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
chkAll.Checked = True
End If
For i As Integer = 0 To GridView1.Rows.Count - 1
If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then
If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then
Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)
chk.Checked = True
Else
Dim CheckBoxIndex As Integer = GridView1.PageSize * (GridView1.PageIndex) + (i + 1)
If CheckBoxArray.IndexOf(CheckBoxIndex) <> -1 Then
Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)
chk.Checked = True
End If
End If
End If
Next
End If
End Sub
The above function’s job is to reset the selected checkboxes for the current ASP.Net GridView Page. It simply checks whether the ViewState list of checkboxes has any checkbox that belongs to the current page. If it finds one it sets the checked property for that checkbox to true. This function is called up in the ASP.Net GridView Page index changing event given below
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
ResetCheckBoxes();
}
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
ResetCheckBoxes()
End Sub
Exporting the selected records to Excel
Below is the Export Button click event handler that will process the exporting of selected records in the ASP.Net GridView to excel
C#
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.HeaderRow.Cells[0].Visible = false;
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");
if (ViewState["CheckBoxArray"] != null)
{
ArrayList CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];
string checkAllIndex = "chkAll-" + GridView1.PageIndex;
int rowIdx = 0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
row.Visible = false;
if (row.RowType == DataControlRowType.DataRow)
{
if (CheckBoxArray.IndexOf(i + 1) != -1)
{
row.Visible = true;
row.BackColor = System.Drawing.Color.White;
row.Cells[0].Visible = false;
row.Attributes.Add("class", "textmode");
if (rowIdx % 2 != 0)
{
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
}
rowIdx++;
}
}
}
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.End();
}
VB.Net
Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
GridView1.AllowPaging = False
GridView1.DataBind()
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")
GridView1.HeaderRow.Cells(0).Visible = False
GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(4).Style.Add("background-color", "green")
If ViewState("CheckBoxArray") IsNot Nothing Then
Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CheckBoxArray"), ArrayList)
Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex
Dim rowIdx As Integer = 0
For i As Integer = 0 To GridView1.Rows.Count - 1
Dim row As GridViewRow = GridView1.Rows(i)
row.Visible = False
If row.RowType = DataControlRowType.DataRow Then
If CheckBoxArray.IndexOf(i + 1) <> -1 Then
row.Visible = True
row.BackColor = System.Drawing.Color.White
row.Cells(0).Visible = False
row.Attributes.Add("class", "textmode")
If rowIdx Mod 2 <> 0 Then
row.Cells(1).Style.Add("background-color", "#C2D69B")
row.Cells(2).Style.Add("background-color", "#C2D69B")
row.Cells(3).Style.Add("background-color", "#C2D69B")
row.Cells(4).Style.Add("background-color", "#C2D69B")
End If
rowIdx += 1
End If
End If
Next
End If
GridView1.RenderControl(hw)
Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.[End]()
End Sub
As you’ll notice everything is similar to the normal Excel export function except that By default all rows are set Visible = false and I am looping through GridView Rows and verifying the index with that of the index stored in the list of check items. If the item is found in the checked or selected list I simply make that particular row Visible = true.
The screenshots below describe the ASP.Net GridView with the selected records and the same records are exported to the Excel word document
![User selecting records using checkboxes in ASP.Net GridView Control]()
As you’ll notice in the document I have added an extra button called Export All which basically is the normal function to export the ASP.Net GridView to Excel that can be found in my following article Export GridView with Images to Word, Excel and PDF Formats in ASP.Net. But don't forget to set the first cell visible = false in each row in order to hide the checkboxes.
Note: You might get the following errors or exceptions while trying to export. Please click on their respective links to know the resolutions
RegisterForEventValidation can only be called during Render()
Control ’GridView1’ of type ’GridView’ must be placed inside a form tag with runat=server.
To try the Live demo, click here.
That’s it. Hope you liked the article. You can download the source in VB.Net and C# using the link below
Download Code (7.22 kb)
