Exporting to Excel from GridView (Using a custom class without GridView)

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

In previous post of this export to Excel from GridView series we had discussed about how to export data which is not present in the Gridview or to remove some of the columns.

But the examples contain two GridView, one to show on the screen and another one is to export to excel. But instead of having one separate GridView for export, better to consider some other way to avoid it.

In this post, I am planning to provide an example which has a custom class that gets the data set as input and export to Excel without having any separate GridView for exporting functionality. The custom class will create a GridView dynamically on the code and export it.

This example also provides a way to export to Excel in ASP.NET applications without having any GridView or any other data controls present on the screen.

Below is the custom class (Exporter.cs) used for exporting the data to excel.

public class Exporter<T> : System.Web.UI.Page
{
    IList<T> ListData;

    IList<ExportColumn> ExportColumns;

    public void AddExportColumn(string ColumnName, string HeaderText)
    {
        if (ExportColumns == null) ExportColumns = new List<ExportColumn>();
        ExportColumns.Add(new ExportColumn(ColumnName, HeaderText));
    }

    public IList<T> BindDataList
    {
        get { return ListData; }
        set { ListData = value; }
    }
    public void Export()
    {
        try
        {
            GridView gvExportExcel = new GridView();
            gvExportExcel.ID = "ExportExcel";

            if (ExportColumns.Count > 0)
            {
                foreach (ExportColumn exportColumn in ExportColumns)
                {
                    BoundField field = new BoundField();
                    if (exportColumn.ColumnName != string.Empty) field.DataField = exportColumn.ColumnName;
                    if (exportColumn.HeaderText != string.Empty) field.HeaderText = exportColumn.HeaderText;
                    gvExportExcel.Columns.Add(field);
                }
                gvExportExcel.AutoGenerateColumns = false;
            }
            else
                gvExportExcel.AutoGenerateColumns = true;

            gvExportExcel.DataSource = ListData;
            
            gvExportExcel.DataBind();
            
            PrepareGridViewForExport(gvExportExcel);

            Context.Response.ClearContent();
            Context.Response.ContentType = "application/ms-excel";
            Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "Sheet1"));
            Context.Response.Charset = "";

            System.IO.StringWriter stringwriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlwriter = new System.Web.UI.HtmlTextWriter(stringwriter);
            gvExportExcel.RenderControl(htmlwriter);
            Context.Response.Write(stringwriter.ToString());
            Context.Response.End();
        }
        catch (Exception ex)
        {
        }
    }

    /// <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++)
        {
            Control currentControl = gridView.Controls[i];
            if (currentControl is CheckBox)
            {
                gridView.Controls.Remove(currentControl);
                gridView.Controls.AddAt(i, new LiteralControl((currentControl as CheckBox).Checked ? "True" : "False"));
            }
            if (currentControl.HasControls())
            {
                PrepareGridViewForExport(currentControl);
            }
        }
    }
}
public class ExportColumn
{
    public string ColumnName { get; set; }
    public string HeaderText { get; set; }

    public ExportColumn(string ColumnName, string HeaderText)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
    }
}

This class inherited from System.Web.UI.Page, so all the functionality we do with aspx page can be done here. This class accepts a List object as input which contains list of entity objects to represent the data on the excel sheet. I also have AddExportColumn for adding the columns to be exported in to the excel.

I have a button used for triggering the export.

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

The code behind

protected void btnExport_Click(object sender, EventArgs e)
{
    Exporter<Product> exportor = new Exporter<Product>();
    exportor.BindDataList = ProductList();
    exportor.AddExportColumn("ProductName", "Product Name");
    exportor.AddExportColumn("SupplierName", "Supplier Name");
    exportor.AddExportColumn("UnitPrice", "Unit Price");
    
    exportor.Export();
}

/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private IList<Product> ProductList()
{
    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<Product> productList = new List<Product>();
        while (dr.Read())
        {
            Product product = new Product();
            product.ProductID = dr["ProductID"].ToString();
            product.ProductName = dr["ProductName"].ToString();
            product.SupplierName = dr["CompanyName"].ToString();
            product.CategoryName = dr["CategoryName"].ToString();
            product.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
            product.UnitPrice = Convert.ToDouble(dr["UnitPrice"]);
            product.UnitsInStock = Convert.ToInt32(dr["UnitPrice"]);
            product.UnitsOnOrder = Convert.ToInt32(dr["UnitPrice"]);
            product.ReorderLevel = Convert.ToInt32(dr["UnitPrice"]);
            product.Discontinued = Convert.ToBoolean(dr["UnitPrice"]);

            productList.Add(product);
        }
        return productList;
    }
}

The entity class - Product.cs

public class Product
{
        public string ProductID { get; set; }
        public string ProductName { get; set; }
        public string SupplierName { get; set; }
        public string CategoryName { get; set; }
        public string QuantityPerUnit { get; set; }
        public double UnitPrice { get; set; }
}

As you seen in this example, I have no GridView placed on the page, but I am getting the data exported.

Below is the output of the screen