Downloading GridView Data Into Excel and XML Format in a Web Application With Mater Pages

Introduction

This article shows how to download GridView data into Excel and XML format in a web application using Master Pages. This article starts by giving brief details on populating the grid that has paging and sorting functionalities and then describes the challenges of a master page while downloading data into Excel format. The download in XML format is very clean.

Populate the grid

Create a web application or web site that has implemented master pages. Create a content page and place a grid view like the following code snippet.

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
    <div id="dvBtns"> <!—div for buttons -->
<asp:ImageButton ID="imgExcel" runat="server" ToolTip="Dwonload in Excel format" ImageUrl="~/images/excel-icon.gif"
                        OnClick="imgExcel_Click" Visible="false" />
                    <asp:ImageButton ID="imgXML" runat="server" ToolTip="Dwonload in XML format" ImageUrl="~/images/xml-icon.gif" Width="24px" Height="24"
                        OnClick="imgXML_Click" Visible="false" />
    </div>
    <div id="dvData"> <!—div for grid -->
        <asp:GridView ID="gvEmpData" AutoGenerateColumns="false" ShowHeader="true" HeaderStyle-BackColor="#4b6c9e"
            HeaderStyle-ForeColor="White" AlternatingRowStyle-BackColor="#DEDEDE" runat="server"
            EnableSortingAndPagingCallbacks="true" EnableViewState="true" AllowPaging="true"
            AllowSorting="true" PageSize="15" Height="100%" OnPageIndexChanging="gvEmpData_PageIndexChanging"
            EmptyDataText="No data available." OnSorting="gvEmpData_Sorting" Width="100%">
            <Columns>
                <asp:BoundField HeaderText="Login id" DataField="UserName" SortExpression="UserName" />
                <asp:BoundField HeaderText="Display Name" DataField="DisplayName" SortExpression="DisplayName" />
                <asp:BoundField HeaderText="Country" DataField="Country" SortExpression="Country" />
                <asp:BoundField HeaderText="Role" DataField="Role" SortExpression="Role" />
            </Columns>
        </asp:GridView>
    </div>
</asp:Content>

In the code behind, add a method to populate the grid “gvEmpData” with employee details, such as in the following code snippet:

void loadEmpDataIntoGrid()
{
    try
    {
        DataTable dtEmpData = GetEmpData();
        if (dtEmpData != null && dtEmpData.Rows.Count > 0)
        {
            gvEmpData.DataSource = dtEmpData;
            gvEmpData.DataBind();
        }
        else
        {
            gvEmpData.DataBind();
        }
    }
    catch (Exception ex)
    {
        //Log error in exception handling module;
    }
}
private DataTable GetEmpData()
{
    DataTable dtEmpData = null;
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL_ConnectionString"].ToString()))
    {
        SqlCommand command = new SqlCommand("GetEmpData", conn); //SP to get emp data
        conn.Open();
        command.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds);
        ViewState["Employees"] = ds;
        if (ds.Tables[0].Rows.Count > 0)
            dtEmpData = ds.Tables[0];
        conn.Close();
    } 
    return dtEmpData;
}

Now we are done with populating the employee data into the grid. Next is to provide the implementation for paging and sorting.

Paging and Sorting

protected void gvEmpData_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvEmpData.PageIndex = e.NewPageIndex;
    DataSet ds = ViewState["Employees"] as DataSet;
    DataTable dt = ds.Tables[0];
    if (ViewState["SortExpression"] != null && ViewState["SortDirection"] != null)
        dt.DefaultView.Sort = ViewState["SortExpression"].ToString() + " " + ViewState["SortDirection"].ToString();
    gvEmpData.DataSource = dt;
    gvEmpData.DataBind();
}
#region Sorting
protected void gvEmpData_Sorting(object sender, GridViewSortEventArgs e)
{
    //Retrieve the DataTable from the ViewState object.
    DataSet ds = ViewState["Employees"] as DataSet;
    DataTable dt = ds.Tables[0];
    if (dt != null)
    {
        dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression, "Employees");
        gvEmpData.DataSource = dt;
        gvEmpData.DataBind();
    }
}
private string GetSortDirection(string column, string grid)
{
    string sortDirection = "ASC";
    string sortExpression = "";
    string lastDirection = "";
    if (grid == "Employees")
    {
        sortExpression = ViewState["SortExpression"] as string;
        lastDirection = ViewState["SortDirection"] as string;
    }
    if (sortExpression != null)
    {
        //Otherwise, the default value can be returned.
        if (sortExpression == column)
        {
            if ((lastDirection != null) && (lastDirection == "ASC"))
            {
                sortDirection = "DESC";
            }
        }
    }
    if (grid == "Employees")
    {
        //Save new values in ViewState.
        ViewState["SortDirection"] = sortDirection;
        ViewState["SortExpression"] = column;
    } 
    return sortDirection;
}
#endregion

Download data into Excel format

Once we are done with paging and sorting, we now should implement the click event of the button to download the grid data into the Excel format.

protected void imgExcel_Click(object sender, ImageClickEventArgs e)
{
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Employees.xlsx"));
    //Add content type of the response as Excel.
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    //Remove custom functionality from the grid and re-bind the grid to avoid any discrepancy of the data.
    gvEmpData.AllowPaging = false;
    gvEmpData.AllowSorting = false;
    gvEmpData.DataSource = (DataSet)ViewState["Employees"];
    gvEmpData.DataBind();
    //Formatting the grid which will reflect into downloaded spreadsheet.
    //Change the Header Row to white color so that all the columns of sheet to be white 
    gvEmpData.HeaderRow.Style.Add("background-color", "#FFFFFF");
    //Applying stlye to gridview header cells to reflect into sreadsheet
    for (int i = 0; i < gvEmpData.HeaderRow.Cells.Count; i++)
    {
        gvEmpData.HeaderRow.Cells[i].Style.Add("background-color", "#4b6c9e");
        gvEmpData.HeaderRow.Cells[i].Style.Add("color", "#FFFFFF");
        gvEmpData.HeaderRow.Cells[i].Style.Add("font-weight", "bold");
    }
    int j = 1;
    //This loop is used to apply style to cells based on particular row
    foreach (GridViewRow gvrow in gvEmpData.Rows)
    {
        gvrow.BackColor = Color.White;
        if (j <= gvEmpData.Rows.Count)
        {
            if (j % 2 != 0) //This is to provide alternative row format
            {
                for (int k = 0; k < gvrow.Cells.Count; k++)
                {
                    gvrow.Cells[k].Style.Add("background-color", "#DEDEDE");
                }
            }
        }
        j++;
    } 
    gvEmpData.RenderControl(htw); //Generate HTML for the grid
    Response.Write(sw.ToString()); //Write it to output stream
    Response.End();
}

Compile and run the code, is it going to work…..??

No! We’ll get a run time error as:

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

The application uses gvEmpData.RenderControl(htw) and subsequently the page raises an exception that a Server-Control was rendered outside of a form.

There are the following solutions to solve this problem.

Solution 1

Override the method of the Page class Page.VerifyRenderingInServerForm. Confirm that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

Controls that are required to be inside <form runat=server> tags can call this method before they render so that an error message is shown if they are placed outside the tags.

Override this method after the page load event.

public override void VerifyRenderingInServerForm(Control control)
{
}

Solution 2

In some cases we cannot override the Page class method “VerifyRenderingInServerForm” then remove the grid from the controls collection while you do the render, and then add it back when you are finished before the page loads.

//gvEmpData.RenderControl(htw);
Control parent = gvEmpData.Parent;
int GridIndex = 0;
if (parent != null)
{
    GridIndex = parent.Controls.IndexOf(gvEmpData);
    parent.Controls.Remove(gvEmpData);
}
gvEmpData.RenderControl(htw); //Get HTML code of Grid
if (parent != null)
{
    parent.Controls.AddAt(GridIndex, gvEmpData);
}
Response.Write(sw.ToString()); 
Response.End();

Download data into XML format

Implement the click event of the image button of XML.

protected void imgXML_Click(object sender, ImageClickEventArgs e)
{ 
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Employees.xml"));
    Response.ContentType = "application/vnd.xml"; 
    DataSet ds = (DataSet)ViewState["Employees"]; 
    Response.Write(ds.GetXml());//Gel XML and write it to output steam.
    Response.End();
}

Summary

In this article, I tried to address how to download data into Excel format from a Grid and a common problem caused by the RenderControl method and its probable solutions as well as how to download grid data into XML format. Please share your feedback to improve this. Thanks.