Click here to Skip to main content
15,867,968 members
Articles / Web Development / ASP.NET

Embrace Reflection and Export Any List Collection to Excel

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
30 Oct 2012CPOL2 min read 13K   5   1
The purpose of this blog post is to provide a generic solution using .NET Reflation and Generics so the same export function can be reused through the entire application regardless of the object type and save us a lot of effort.

Introduction

Exporting to Microsoft Excel is a common feature for data driven business apps. And it is very common that an individual app contains several such export features that take different data with respect to different needs and export to Excel. The purpose of this blog post is to provide a generic solution using .NET Reflection and Generics so the same export function can be reused through the entire application regardless of the object type and save us a lot of effort.

Problem Statement

Engineers spend several hours to write redundant function containing such export features to satisfy different needs. With a little twist, codes can be reused. But the question is why is we implement a different function for the same purpose. The answer is simple - each object has a different property so Excel will contain a different column name for each object. So programmers write different export functions that address a specific object type and hard code the Excel column name.

How Reflection Can Help

To address this issue, we can use .NET Generics and Reflection. Let's not stretch the post by introducing them, you will find lots of good posts about them, if you just Google. Our interest is on a particular feature reflection can do, you will see PropertyInfo.

Use PropertyInfo to discover information such as the name, data type, declaring type, reflected type, and read-only or writeable status of a property, and to get or set property values.

So, the idea is we will use a Generic collection to export and use Reflection to iterate all properties via PropertyInfo, thus we can get all property name/type whatever the object collection is provided. Once we have property name/ type, we can do whatever data formatting or processing we need according to our needs. Say for a particular app specification is whatever date time is exported to Excel, it should contain only time expressed in terms of milliseconds. As we have property type now, we can do that easily right?

Solution

Here is the method that takes Generic List<t> as parameter and uses reflection to iterate the type and export accordingly.

C#
/// <summary>
/// Take object List as input and export 
///to xls which will be prompt save as dialog
/// </summary>
/// <typeparam name="T">Type of object</typeparam>
/// <param name="listToExport">Object list to export</param>
/// <param name="xlsName">Excel file name to export</param>
public static void ExportListToExcel<T>(List<T> listToExport, string xlsName)
{
 HttpContext.Current.Response.Clear();
 HttpContext.Current.Response.ClearContent();
 HttpContext.Current.Response.ClearHeaders();
 HttpContext.Current.Response.AppendHeader
 ("Content-Type", "application/vnd.ms-excel");
 HttpContext.Current.Response.AppendHeader("Content-disposition", 
    "attachment; filename=" + xlsName + ".xls");
 Int32 success = 0;
 string sep = "";
 try
 {
 PropertyInfo[] fieldInfo = listToExport[0].GetType().GetProperties();
 foreach (PropertyInfo col in fieldInfo)
 {
 if (col.PropertyType != typeof(EntityKey) && col.PropertyType != typeof(EntityState))
 {
 HttpContext.Current.Response.Write(sep + col.Name);
 sep = "\t";
 }
 }
 HttpContext.Current.Response.Write("\n");
foreach (T dataItem in listToExport)
 {
 PropertyInfo[] allProperties = dataItem.GetType().GetProperties();
 sep = "";
 foreach (PropertyInfo thisProperty in allProperties)
 {
 if (thisProperty.PropertyType != typeof(EntityKey) 
&& thisProperty.PropertyType != typeof(EntityKey))
 {
 object value = thisProperty.GetValue(dataItem, null);
 String propetyValue = (value == null ? String.Empty : value.ToString());
 HttpContext.Current.Response.Write(sep + propetyValue.ToString());
 sep = "\t";
 }
 }
 ++success;
 HttpContext.Current.Response.Write("\n");
 }
 HttpContext.Current.Response.End();
 }
 catch (Exception ex)
 {
 throw ex;
 }
}

One thing that you might notice above is as follows:

SQL
if (thisProperty.PropertyType != typeof(EntityKey) && 
thisProperty.PropertyType != typeof(EntityKey))

Why we need this checking? You see if you are using Entity Framework in your app, all entities have these two additional property types predefined and we don’t want them to be exported to CSV and confuse the end user, do we? So additional checking is required to remove them. If you do not use Entity Framework, you don’t need to worry about this checking.

So you can see that a simple trick can save lots of development effort, enjoy the free time.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

Comments and Discussions

 
QuestionHow to get rid of the "the file you are trying to open is in a different format" message? Pin
Luis Rubio7-Feb-14 6:07
Luis Rubio7-Feb-14 6:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.