Embrace reflection and export any List collection to excel

Introduction

Export to MS excel is a common feature for data driven business apps. And its very common that a individual app contains several such export feature that take different data respect to different needs and export to excel. Purpose this blog post is to provide a generic solution using .net Reflation and Generics so same export function can be reusable though entire application disrespect to object type and save our engineers effort.

Problem Statement

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

How Reflection can Help

To address this issue we can use .net Generics and Reflection.  Lets not stretch the post by introducing them, you will find lots of good post 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 writable status of a property, and to get or set property values.

So idea is we will use a Generic collection to export and use Reflection to iterate all property via PropertyInfo, thus we can get all property name/type all that 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 exports to excel it should contains only time expressed in terms if millisecondsAs we have property type now , we can do that easily right? :)

Solution

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

/// <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 might you notice above

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 entity has these two additional property type predefined and we don’t what them to be exported to csv and confuse 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 a simple trick can save lots of development effort, enjoy the free time :)

About these ads

Posted on October 28, 2012, in Asp.NET, Tip/Trick and tagged , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 234 other followers

%d bloggers like this: