Exporting to Excel from GridView (Adding & Removing columns with all rows)

Tweet

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

Previous post of Exporting to Excel from GridView (with visible columns and all rows) shows an example for a normal way of exporting to excel with only visible columns and all rows. But in sometimes, business required showing only some important columns and exporting all possible columns in the Excel.

As I already explained in first post of this series, all those examples are following same way of exporting to Excel. But there will be some tricks to achieve our goals.

Considering our requirement, we can achieve exporting invisible and visible columns in many ways. One of an easy way is to have two GridView in a same page, one is to show to the user with required columns on the screen and another is to export with all possible columns to the excel (this Grid will always be invisible and will not seen by the user). So when an Export button clicked, the code will bind the records to the invisible GridView (which has all possible columns) and export the records to the Excel.

Another way to do the same is to have a class which creates GridView (or a table) on the fly using code behind and populate the data to export it. This example will be explained in the following post of this series.

Note: I am using Northwind database in this example for binding records. So make sure you setup the same database and change the connection string in the Web.Config before testing the working example.

Let us implement the first way “place two Gridview in same page; one is to show in the screen and another one is to export”.

The GridView used to show some particular columns in the screen.

<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" />
    </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>

The GridView used to export all possible columns to the Excel sheet.

<asp:GridView ID="grdViewExport" runat="server"
    AllowPaging="false" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%" ShowFooter="False"
    CellPadding="4" ForeColor="#333333" GridLines="Both" >
    <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="Unit Price" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" />
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="Units On Order" />
        <asp:BoundField DataField="ReorderLevel" HeaderText="Reorder Level" />
        <asp:BoundField DataField="Discontinued" HeaderText="Discontinued" />
    </Columns>
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

<asp:Button ID="btnExport" runat="server" Text="Export" onclick="btnExport_Click" />

Note: The second (used for exporting) GridView AllowPaging property set to False. So it will export all the records and by default the export GridView will not have any records. The data for the export GridView will be bound on demand and export it.

The code behind for binding the records to the GridView.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGrid(grdViewProducts);
    }
}
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid(GridView gridControl)
{
    using (SqlConnection connection =
        new SqlConnection(ConfigurationManager.ConnectionStrings
                            ["SQLConnection"].ConnectionString))
    {

        SqlDataAdapter adaptor = new SqlDataAdapter(
               "SELECT ProductID, ProductName, CompanyName, CategoryName, " +
               "QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued " +
               "FROM Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
               "JOIN Categories ON Products.CategoryID = Categories.CategoryID ", connection);

        DataSet ds = new DataSet();

        adaptor.Fill(ds);
        gridControl.DataSource = ds;
        gridControl.DataBind();
    }
}
/// <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(grdViewExport);

    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);
    grdViewExport.RenderControl(htmlwriter);
    Context.Response.Write(stringwriter.ToString());
    Context.Response.End();
}
/// <summary>
/// This event is used to remove the error occuring while exporting to export
/// The Error is : Control 'ControlID' 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;
}

Second Implementation for the same requirement

Let us take an example of another scenario; the GridView contains some columns with Edit, Delete columns. The Edit, Delete column is used for doing some operation on the particular record but those columns are not required when exporting to the Excel.

The implementation follows.

The GridView scripts.

<asp:GridView ID="grdViewProducts" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%"
    CellPadding="4" ForeColor="#333333" >
    <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" />
        <asp:CommandField HeaderText="Select" ShowSelectButton="True">
            <ItemStyle HorizontalAlign="Center" />
        </asp:CommandField>
        <asp:CommandField HeaderText="Delete" ShowDeleteButton="True">
            <ItemStyle HorizontalAlign="Center" />
        </asp:CommandField>
    </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:GridView ID="grdViewExport" runat="server"
    AllowPaging="false" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%" ShowFooter="False"
    CellPadding="4" ForeColor="#333333" GridLines="Both" >
    <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" />
    </Columns>
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

There are two GridView defined here, one is to show on the screen with Edit and Delete column and another one is to export to Excel.

The code behind will be almost same as above implementation.

Below is the screenshot of the output of this example.

GridView shows only five columns

Exported sheet from GridView has nine columns (ref - Previous image)

GridView with Select and Delete columns

Exported sheet by removing Select and Delete columns

Download the working code of this example
First example (ExportGridViewAdditionalCol) - C# here and VB here.
Second example (ExportExcelRemoveCols) - C# here and VB here.

The other links on Export to Excel from GridView:

1. Exporting to Excel from GridView - Getting Started
2. Exporting to Excel from GridView (All columns and rows - Normal Method)
3. Exporting to Excel from GridView (Adding & Removing columns with all rows)
4. Exporting to Excel from GridView (using a custom class without GridView)
5. Exporting to Excel using Excel Application object
6. Exporting the data to a Templated Excel sheet using Excel Application object
7. Exporting the data to Excel sheet with image embedded using Excel Application object