Generic Method to Export Data to Excel File

Introduction

This article describes how to use generic and enum reflection in the presentation layer to map data in business logic layer to export a report to Excel file.

Background

It is common to export Excel report to client side. The easy way is to get the export data from the gridview then convert to Excel and send back to client. But when you disable the gridview viewstate or use Ajax without viewstate to improve application performance, you need to query the data from database again and generate an Excel report.
There is no method like gridview DataSource and DataBind to generate an Excel file. This article will introduce an easy way to allow you to export any query result to an Excel file by using generic, reflection, enum and attribute.

Step by Step

Step 1

Set the page that you want to export Excel report to inherit from ExportExcelPage<T> page with an enum type. I will discuss this base page and the generic type below. For example, you have a page call OrderHistory or SalesInformation, they will be declared like:

public class OrderHistory : ExportExcelPage<OrderHistory.ExportFields>
public class SalesInformation: ExportExcelPage< SalesInformation.ExportFields>

ExportFields is an enum that is used to map the properties in your business logic layer for binding to the export fields.

#region Enums
//All enum members name are copied from BLL class properties name 
//to do mapping except those with [Description(CUSTOM_COL)].
//HearderTxt attribute excel columns title.
//Description attribute for customizing result format in excel.
public enum ExportFields
{
     [HearderTxt("lbl_OrderID")]OrderID,
     [HearderTxt("lbl_CreateDate")]OrderCreateDate,
     [HearderTxt("lbl_TotalPrice")][Description(PRICE_COL)]Price,
     [HearderTxt("lbl_LineNum")]LineNumber,
     [HearderTxt("lbl_Manufacturer")]Manufacturer,
     [HearderTxt("lbl_MfgPartNo")]MfgPartNo,
     [HearderTxt("lbl_OrderQty")][Description(QTY_COL)]OrderQty,
     [HearderTxt("lbl_UnitPrice")][Description(PRICE_COL)]UnitPrice,
     [HearderTxt("lbl_Custom")][Description(CUSTOM_COL)]CustomFieldName,
     ......
 }
#endregion

HearderTxt is an attribute class that is used to localize your Excel columns title.
Description is a built-in attribute class to allow you to custom the result format.
It is easy to copy and paste all the properties from BLL class that you want to export to Excel, for some special column, just make your own name to handle in the code with [Description(CUSTOM_COL)] attribute.

Step 2

Build a HearderTxtAttribute inherited from Attribute class. It is simple, just gets the localization column title from the resource file or directly returns what you want to set in the title.

public class HearderTxtAttribute : Attribute
{
    public string HeaderTxt { get; protected set; }
    public HearderTxtAttribute(string header) {
        string headerStr = string.Empty;
   //localize header 
        headerStr = Global.YourGetResourceMethod(header);
   //if not found in resource file, use the value that pass in  
        this.HeaderTxt = (string.Empty == headerStr) ? header : headerStr;
    }
}

Step 3

Build the generic ExportExcelPage class that inherits from System.Web.UI.Page with constraints and constructor, and the class has generic ExcelExport method to bind your exportField enum with target object property and generate the Excel style sheet.
Before declaration, these are what you need in the class for reflection:

using System;
using System.Text;
using System.Collections;
using System.Reflection;
using System.ComponentModel;

Declaration:

public class ExportExcelPage<T> : System.Web.UI.Page where T:struct

Constructor:

#region Constructor
public ExportExcelPage() { 
if (!typeof(T).IsEnum) 
throw new InvalidOperationException(typeof(T).Name+"is not an enum");
}
#endregion

The constraint enforces the inherited class has a struct declaration and checks whether this struct is an enum in the constructor. Why not set constraint as an enum type directly? The reason is that Microsoft didn't provide such kind of feature in .NET 2.0, 3.0, 4.0 yet. Hope we can do it in .NET 5.0.

Next, declare the constants for different kind of columns that have special format:

#region Constants
protected const string QTY_COL = "QtyCol";
protected const string PRICE_COL = "PriceCol";
//your can create more like:
protected const string BOOL_COL = "BoolCol";
......
protected const string  CUSTOM_COL = "CustomCol";
#endregion

Then, create overload generic methods for exporting the data.

#region Helper Methods
protected void ExcelExport<E, M>(string exportFileName, 
	E dataColloection, M data, ArrayList notExportFields)
    where E : CollectionBase
    where M : YourNameSpace.Logic.BusinessObject
{
    ExcelExport(exportFileName, dataColloection, null, 
	null, null, data, notExportFields);
}
protected void ExcelExport<E, M>(string exportFileName, 
	E dataColloection, int? totalRow, int? firstRow, int? 
	lastRow, M data, ArrayList notExportFields)
    where E : CollectionBase
    where M : YourNameSpace.Logic.BusinessObject
{
    int idx = (firstRow.HasValue)?firstRow.Value:1;
    string exportStr = string.Empty;
    string attributeInfo = string.Empty;
    StringBuilder sb = new StringBuilder();
    //create header
    sb.Append("\t");
    foreach (string s in Enum.GetNames(typeof(T)))
    {
        if (!notExportFields.Contains(s))
        {
            FieldInfo fi = typeof(T).GetField(s);
            HearderTxtAttribute[] attributes = 
		(HearderTxtAttribute[])fi.GetCustomAttributes
		(typeof(HearderTxtAttribute), false);
            attributeInfo = (attributes.Length > 0) ? 
		attributes[0].HeaderTxt : string.Empty;
            sb.Append(attributeInfo);
            sb.Append("\t");
        }
    }
    sb.Append("\r\n");
    //load data
    foreach (M dataItem in dataColloection)
    {
        //add index
        sb.Append(idx.ToString());
        sb.Append("\t");
        //add export fields
        Type objType = dataItem.GetType();
        PropertyInfo[] properties = objType.GetProperties();
        foreach (string s in Enum.GetNames(typeof(T)))
        {
            //get enum attribute
            FieldInfo fi = typeof(T).GetField(s);
            DescriptionAttribute[] attributes = 
		(DescriptionAttribute[])fi.GetCustomAttributes
		(typeof(DescriptionAttribute), false);
            attributeInfo = (attributes.Length > 0) ? 
		attributes[0].Description : string.Empty;
            exportStr = string.Empty;
            //mapping with dataItem property
            PropertyInfo p = typeof(M).GetProperty(s);
            if (!notExportFields.Contains(s))
            {
                switch (attributeInfo)
                {
                    case QTY_COL:
                        exportStr = Global.YourConvertPrecisionNumberMethod
			((decimal)p.GetValue(dataItem, null), QtyPrecision);
                        break;
                    case PRICE_COL:
                        exportStr = Global. YourConvertPrecisionNumberMethod 
			((decimal)p.GetValue(dataItem, null), MoneyPrecision);
                        break;
                    case CUSTOM_COL:
                        exportStr = buildCustomCol(s, dataItem);
                        break;
                    default:
                        exportStr = Convert.ToString(p.GetValue(dataItem, null));
                        break;
                }
                sb.Append(exportStr);
                sb.Append("\t");
            }
        }
        sb.Append("\r\n");
        ++idx;
    }
    //create footer
    if (firstRow.HasValue && lastRow.HasValue && totalRow.HasValue)
    {
        lastRow = (lastRow > totalRow) ? totalRow : lastRow;
        sb.Append(string.Format(Global.YourGetResourceStringMethod("PagesExcel"), 
			firstRow.ToString(), lastRow.ToString(), totalRow));
        sb.Append("\r\n");
    }
    string sReport = sb.ToString();
    byte[] ByteArray = System.Text.Encoding.UTF8.GetBytes(sReport);
    Page.Response.ClearContent();
    Page.Response.ClearHeaders();
    Page.Response.ContentType = "application/vnd.ms-excel";
    Page.Response.AddHeader("Content-disposition", "attachment; 
				filename=" + exportFileName);
    Page.Response.BinaryWrite(ByteArray);
    Page.Response.Flush();
    Page.Response.End();
    return;
}
public virtual string buildCustomCol(string s, 
	YourNameSpace.Logic.BusinessObject dataItem) { return string.Empty; }
#endregion

Let Me Discuss the Methods

protected void ExcelExport<E, M>(string exportFileName, 
	E dataColloection, int? totalRow, int? firstRow, int? 
	lastRow, M data, ArrayList notExportFields)
    where E : CollectionBase
    where M : YourNameSpace.Logic.BusinessObject

Generic ExcelExport<E,M>: has 2 generic data types with constraints.

E must be a CollectionBase object, it is a collection of the data object (YourNameSpace.Logic.BusinessObject) that you query from the database. It is general for most of the projects that have a similar structure.

M must be a YourNameSpace.Logic.BusinessObject object that is the base class in your BLL layer and contains query data.

exportFileName: is the Excel file name that you plan to export.

dataCollection: is the collection of the Logic.BusinessObject, must inherit from CollectionBase.

totalRow, firstRow, lastRow are the Nullable arguments to represent result paging information. firstRow and lastRow stand for current result page first row and last row number. totalRow stands for the total number of the query result. They are optional if you do paging and report the index and the footer in your report.

data: an empty business object in your business logic layer. The purpose of this empty object is to get the BLL object type for the reflection. Why not just pass a DataType instead the empty object? Because you can set the constraint only by passing an object, not a type.

notExportFields: The ArrayList contains the properties that won't be exported depending on certain conditions. It maps with the enum members as ExportFields.

How Does the Method Work?

We have data collection from query, all the exported properties name of business object and a empty business object when we call the export method. We need to know the Excel columns' title and data value to build the Excel file by using reflection.

To Build the Title Row

Loop through the ExportFields and get member's name by reflection and check not in notExportFields and get title in HearderTxtAttribute attribute.

foreach (string s in Enum.GetNames(typeof(T)))
{
    if (!notExportFields.Contains(s))
    {
        FieldInfo fi = typeof(T).GetField(s);
        HearderTxtAttribute[] attributes = 
		(HearderTxtAttribute[])fi.GetCustomAttributes
		(typeof(HearderTxtAttribute), false);
        attributeInfo = (attributes.Length > 0) ? 
		attributes[0].HeaderTxt : string.Empty;
        sb.Append(attributeInfo);
        sb.Append("\t");
    }
}

To Build the Data Row

Loop through the data collection from the result:

foreach (M dataItem in dataColloection)

Then get the object data type and its properties:

Type objType = dataItem.GetType();
PropertyInfo[] properties = objType.GetProperties();

Then loop through all the members in ExportFields by reflection:

foreach (string s in Enum.GetNames(typeof(T)))

Get every enum member description attribute:

//get enum attribute
FieldInfo fi = typeof(T).GetField(s);
DescriptionAttribute[] attributes = 
    (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
attributeInfo = (attributes.Length > 0) ? attributes[0].Description : string.Empty;

Using reflection to get the object property information collection:

//mapping with dataItem property
PropertyInfo p = typeof(M).GetProperty(s);

Check whether this field is in notExportFields and whether it needs to be formatted:

if (!notExportFields.Contains(s))
switch (attributeInfo)

Get the property value from property information collection by reflection:

p.GetValue(dataItem, null)

To customize result format by calling override method in your page.

exportStr = buildCustomCol(s, dataItem);

buildCustomCol methods must be overridden in inherited page to handle what exactly the result be formatted by passing properties name and object data.
Using the same reflection way, get the value like the code upon.
Finally, build the optional footer and write the Excel file and send back to client.

string sReport = sb.ToString();
byte[] ByteArray = System.Text.Encoding.UTF8.GetBytes(sReport);
Page.Response.ClearContent();
Page.Response.ClearHeaders();
Page.Response.ContentType = "application/vnd.ms-excel";
Page.Response.AddHeader("Content-disposition", 
		"attachment; filename=" + exportFileName);
Page.Response.BinaryWrite(ByteArray);
Page.Response.Flush();
Page.Response.End();

How to Call the Export Method in your Page?

First, your page must inherit from ExportExcelPage and has ExportFields enum for mapping. Next, query the data from database, get the return business object collection. Finally, pass the export file name, data collection to ExcelExport method and the notExportFields arrayList. That is it.

//get the result collection
Logic.OrderHistory.DataCollection data = 
	Logic.OrderHistory.YourLookupOrderHistoryMethod(spParams);
int totalRow = data.totalRow;
int lastRow = CodeTakeCareByYourSelf();
int firstRow = CodeTakeCareByYourSelf();
ArrayList notExportFields = new ArrayList();
//if your don't want some field to export, 
//add to notExportFields from exportFields enum 
//notExportFields.add(ExportFields. OrderCreateDate.ToString());
ExcelExport("OrderHistory.xls", data, totalRow, firstRow, 
	lastRow, new Logic.OrderHistory.DataItem(), notExportFields);

Enjoy it.

History

• 5^th November, 2009: Initial post