Export filtered ASP.Net GridView records to Excel using C# and VB.Net

Hi indradeo,

You need to change the procedure.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Procedure

CREATE PROCEDURE spx_GetEmployees
    @Filter VARCHAR(50),
	@From DATETIME NULL,
	@To DATETIME NULL
AS
BEGIN
    SET NOCOUNT ON;
     
    IF @Filter = 'ALL'
        SELECT EmployeeID, FirstName + ' ' + LastName 'Name', City, Country
        FROM Employees 
		WHERE (BirthDate BETWEEN @From AND @To) OR (@From IS NULL OR @To IS NULL)
    ELSE IF @Filter = '5'
        SELECT TOP 5 EmployeeID, FirstName + ' ' + LastName 'Name', City, Country
        FROM Employees 
		WHERE (BirthDate BETWEEN @From AND @To) OR (@From IS NULL OR @To IS NULL)
    ELSE
        SELECT EmployeeID, FirstName + ' ' + LastName 'Name', City, Country
        FROM Employees 
		WHERE Country=@Filter AND (BirthDate BETWEEN @From AND @To) OR (@From IS NULL OR @To IS NULL)
END
GO

HTML

From:<asp:TextBox ID="txtFrom" runat="server"></asp:TextBox>
&nbsp;To:<asp:TextBox ID="txtTo" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" OnPageIndexChanging="OnPaging">
    <Columns>
        <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:TemplateField>
            <HeaderTemplate>
                Country:               
                <asp:DropDownList ID="ddlCountry" runat="server" OnSelectedIndexChanged="CountryChanged" AutoPostBack="true" AppendDataBoundItems="true">
                    <asp:ListItem Text="ALL" Value="ALL"></asp:ListItem>
                    <asp:ListItem Text="Top 5" Value="5"></asp:ListItem>
                </asp:DropDownList>
            </HeaderTemplate>
            <ItemTemplate>
                <%# Eval("Country") %>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#txtFrom, #txtTo').datepicker({
            dateFormat: "yy-mm-dd",
            changeMonth: true,
            changeYear: true,
            yearRange: '1950:2050'
        });
    })
</script>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.IO

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ViewState["Filter"] = "ALL";
        BindGrid();
    }
}

private void BindGrid()
{
    DataTable dt = new DataTable();
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("spx_GetEmployees");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
    cmd.Parameters.AddWithValue("@From", !string.IsNullOrEmpty(txtFrom.Text.Trim()) ? txtFrom.Text.Trim() : (object)DBNull.Value);
    cmd.Parameters.AddWithValue("@To", !string.IsNullOrEmpty(txtTo.Text.Trim()) ? txtTo.Text.Trim() : (object)DBNull.Value);
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    sda.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    DropDownList ddlCountry = (DropDownList)GridView1.HeaderRow.FindControl("ddlCountry");
    this.BindCountryList(ddlCountry);
}

private void BindCountryList(DropDownList ddlCountry)
{
    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("SELECT DISTINCT Country FROM Employees");
    cmd.Connection = con;
    con.Open();
    ddlCountry.DataSource = cmd.ExecuteReader();
    ddlCountry.DataTextField = "Country";
    ddlCountry.DataValueField = "Country";
    ddlCountry.DataBind();
    con.Close();
    ddlCountry.Items.FindByValue(ViewState["Filter"].ToString()).Selected = true;
}

protected void CountryChanged(object sender, EventArgs e)
{
    DropDownList ddlCountry = (DropDownList)sender;
    ViewState["Filter"] = ddlCountry.SelectedValue;
    this.BindGrid();
}

protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

protected void Button1_Click(object sender, System.EventArgs e)
{
    this.BindGrid();
}

protected void ExportToExcel(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";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();

        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }

        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ViewState("Filter") = "ALL"
        BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim dt As DataTable = New DataTable()
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim sda As SqlDataAdapter = New SqlDataAdapter()
    Dim cmd As SqlCommand = New SqlCommand("spx_GetEmployees")
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Filter", ViewState("Filter").ToString())
    cmd.Parameters.AddWithValue("@From", If(Not String.IsNullOrEmpty(txtFrom.Text.Trim()), txtFrom.Text.Trim(), CObj(DBNull.Value)))
    cmd.Parameters.AddWithValue("@To", If(Not String.IsNullOrEmpty(txtTo.Text.Trim()), txtTo.Text.Trim(), CObj(DBNull.Value)))
    cmd.Connection = con
    sda.SelectCommand = cmd
    sda.Fill(dt)
    GridView1.DataSource = dt
    GridView1.DataBind()
    Dim ddlCountry As DropDownList = CType(GridView1.HeaderRow.FindControl("ddlCountry"), DropDownList)
    Me.BindCountryList(ddlCountry)
End Sub

Private Sub BindCountryList(ByVal ddlCountry As DropDownList)
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim sda As SqlDataAdapter = New SqlDataAdapter()
    Dim cmd As SqlCommand = New SqlCommand("SELECT DISTINCT Country FROM Employees")
    cmd.Connection = con
    con.Open()
    ddlCountry.DataSource = cmd.ExecuteReader()
    ddlCountry.DataTextField = "Country"
    ddlCountry.DataValueField = "Country"
    ddlCountry.DataBind()
    con.Close()
    ddlCountry.Items.FindByValue(ViewState("Filter").ToString()).Selected = True
End Sub

Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim ddlCountry As DropDownList = CType(sender, DropDownList)
    ViewState("Filter") = ddlCountry.SelectedValue
    Me.BindGrid()
End Sub

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Me.BindGrid()
End Sub

Protected Sub ExportToExcel(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"

    Using sw As StringWriter = New StringWriter()
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.AllowPaging = False
        Me.BindGrid()
        GridView1.HeaderRow.BackColor = Color.White
        For Each cell As TableCell In GridView1.HeaderRow.Cells
            cell.BackColor = GridView1.HeaderStyle.BackColor
        Next

        For Each row As GridViewRow In GridView1.Rows
            row.BackColor = Color.White
            For Each cell As TableCell In row.Cells
                If row.RowIndex Mod 2 = 0 Then
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor
                Else
                    cell.BackColor = GridView1.RowStyle.BackColor
                End If

                cell.CssClass = "textmode"
            Next
        Next

        GridView1.RenderControl(hw)
        Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
    End Using
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub

Screenshots

Exported Excel