Exporting to Excel from GridView (All columns and rows - Normal Method)

This post is a continuation of a series. Please look at the end of this post for more information.

In this post, I am giving an example of exporting all the rows from GridView with only the columns what it shown on the page. So, the exported Excel sheet will be as like GridView with all the rows. This is a normal exporting functionality all of us do mostly in ASP.NET page.

This example works with Northwind database. So make sure you setup Northwind database in your SQL Server and change the connection string in Web.Config. The GridView shows list of products with multiple pages and an Export button in the page trigger an event to export the GridView data with the same format to Excel.

The ASPX script:

<asp:GridView ID="grdViewProducts" runat="server"
    AllowPaging="true" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%" ShowFooter="False" 
    CellPadding="4" ForeColor="#333333" GridLines="Both" >
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
        <asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
        <asp:BoundField DataField="CategoryName" HeaderText="Category" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit"/>
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" DataFormatString="{0:#0.00}" />
    </Columns>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:Button ID="btnExport" runat="server" Text="Export" onclick="btnExport_Click" />

Code behind
I am binding the data to the GridView on first Page load.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGrid();
    }
}

/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
    using (SqlConnection connection =
        new SqlConnection(ConfigurationManager.ConnectionStrings
                            ["SQLConnection"].ConnectionString))
    {

        SqlCommand command = new SqlCommand(
               "SELECT ProductID, ProductName, CompanyName, CategoryName, " +
               "QuantityPerUnit, UnitPrice FROM Products " +
               "JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
               "JOIN Categories ON Products.CategoryID = Categories.CategoryID ", connection);

        connection.Open();
        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

        IList<ProductView> productViewList = new List<ProductView>();
        while (dr.Read())
        {
            ProductView productView = new ProductView();
            productView.ProductID = dr["ProductID"].ToString();
            productView.ProductName = dr["ProductName"].ToString();
            productView.CompanyName = dr["CompanyName"].ToString();
            productView.CategoryName = dr["CategoryName"].ToString();
            productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
            productView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
            productViewList.Add(productView);
        }
        grdViewProducts.DataSource = productViewList;
        grdViewProducts.DataBind();
    }
}

Code for exporting to Excel. The comments of each method explains what it does.

/// <summary>
/// Event for exporting to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
    grdViewProducts.AllowPaging = false;
    BindGrid();

    PrepareGridViewForExport(grdViewProducts);

    Context.Response.ClearContent();
    Context.Response.ContentType = "application/ms-excel";
    Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "ExcelFileName"));
    Context.Response.Charset = "";
    System.IO.StringWriter stringwriter = new System.IO.StringWriter();
    HtmlTextWriter htmlwriter = new HtmlTextWriter(stringwriter);
    grdViewProducts.RenderControl(htmlwriter);
    Context.Response.Write(stringwriter.ToString());
    Context.Response.End();
}

/// <summary>
/// This event is used to verify the form control is rendered 
/// It is used to remove the error occuring while exporting to export
/// The Error is : Control 'XXX' of type 'GridView' must be placed inside a form tag with runat=server.
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(Control control)
{
    return;
}

/// <summary>
/// Replace any container controls with literals
/// like Hyperlink, ImageButton, LinkButton, DropDown, ListBox to literals
/// </summary>
/// <param name="gridView">GridView</param>
private void PrepareGridViewForExport(Control gridView)
{
    for (int i = 0; i < gridView.Controls.Count; i++)
    {
        //Get the control
        Control currentControl = gridView.Controls[i];
        if (currentControl is LinkButton)
        {
            gridView.Controls.Remove(currentControl);
            gridView.Controls.AddAt(i, new LiteralControl((currentControl as LinkButton).Text));
        }
        else if (currentControl is ImageButton)
        {
            gridView.Controls.Remove(currentControl);
            gridView.Controls.AddAt(i, new LiteralControl((currentControl as ImageButton).AlternateText));
        }
        else if (currentControl is HyperLink)
        {
            gridView.Controls.Remove(currentControl);
            gridView.Controls.AddAt(i, new LiteralControl((currentControl as HyperLink).Text));
        }
        else if (currentControl is DropDownList)
        {
            gridView.Controls.Remove(currentControl);
            gridView.Controls.AddAt(i, new LiteralControl((currentControl as DropDownList).SelectedItem.Text));
        }
        else if (currentControl is CheckBox)
        {
            gridView.Controls.Remove(currentControl);
            gridView.Controls.AddAt(i, new LiteralControl((currentControl as CheckBox).Checked ? "True" : "False"));
        }
        if (currentControl.HasControls())
        {
            // if there is any child controls, call this method to prepare for export
            PrepareGridViewForExport(currentControl);
        }
    }
}

The ProductView entity class for holding the data of each row while binding.

public class ProductView
{
    public string ProductID { get; set; }
    public string ProductName { get; set; }
    public string CompanyName { get; set; }
    public string CategoryName { get; set; }
    public string QuantityPerUnit { get; set; }
    public double UnitPrice { get; set; }
}

In this example, once the Export Button clicked, we are changing the AllowPaging property to false as we required to export all the data in a single page to Excel sheet. So it exports all the rows which bind to the GridView with all the visible columns.

Below image shows the GridView and exported Excel sheet