ASP.NET Generating Excel Exports using Data Annotations

In my ASP.NET MVC application, I have recently had the requirement to generate a few Excel Spreadsheets from Data.  I soon realised I had a lot of boiler plate code that I was starting to copy and paste all over the show.  

So I took the opportunity to sit back and see if there was a better solution. I use Data Annotations a lot (for example see my Tool tip Data Annotation blog),  so it seemed to make sense to configure the export here too.

For excel generation I use  epplus see http://epplus.codeplex.com/, there is a nuget package too.

The Data Annotation.

Data annotations are simple enough, for this one I decided I would like to have an Index for order, and to be able to override the name of the property.

public class ExportToXlsAttribute : DescriptionAttribute
   {
       public ExportToXlsAttribute()
           : base("")
       {
 
       }
 
 
       public int Index { get; set; }
       public ExportToXlsAttribute(string description, int index)
           : base(description)
       {
           Index = index;
       }
   }

The Data Class.

this is how the data class could look, this could be a return object from either a view, stored procedure or a linq query

public partial class GetWidgetsForExport
 {
     public int WidgetId{ get; set; }

     public int? WidgetTypeId { get; set; }
 
     [ExportToXls(Index = 3)]
     [DisplayName("Widget Type")]
     public string WidgetTypeDescription { get; set; }
 
     [ExportToXls("Part Number",2)]
     public string PartNumber { get; set; }
 
     [ExportToXls(Index = 1)]
     public string Comment { get; set; }
 
  
 }

Getting the Properties for Export

Firstly we need to get a list of all of the properties which are going to be in the spreadsheet.

A simple class is used to handle these, 

internal class ItemsForExport
{
    public string PropertyName { get; set; }
    public int Index { get; set; }
    public string Title { get; set; }
}

These are pretty straightforward, PropertyName, the name of the property, Index this the place in the spreadsheet, and Title is the column header.

To populate this class we need to do some reflection. I have simplified the code for the sake of brevity, for example code can be added in the displayName setting based on the DataAnnotation DisplayName.

private List<ItemsForExport> GetPropertiesForExport(Type type)
{
    const BindingFlags allProperties = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic;
    var itemsToExport = new List<ItemsForExport>();
 
    foreach (var property in type.GetProperties(allProperties))
    {
        var exportToXlsAttributes = property.GetCustomAttributes(typeof (ExportToXlsAttribute)).ToList();
 
        if (exportToXlsAttributes.Any())
        {
            var displayName = property.Name;

            var attribute = (ExportToXlsAttribute) exportToXlsAttributes.First();
           
            if (!string.IsNullOrEmpty(attribute.Description))
            {
                displayName = attribute.Description;
            }

            itemsToExport.Add(new ItemsForExport {Order = attribute.Index, PropertyName = property.Name, Title = displayName});
        }
    }
 
    return itemsToExport.OrderBy(a => a.Order).ToList();
}

Again this is relatively straight forward, the code loops through the properties, looks to see if there is a DataAnnotation ExportToXlsAttribute if there is, it populates the class and adds to the list.  Finally it sends back the list in order.

Generating the Excel Spreadsheet.

We now have our properties we want, so simply we loop through our list of objects looking for the right items and add them to the spreadsheet.

               using (var p = new ExcelPackage())
               {
                   p.Workbook.Properties.Title = title;
 
                   //Create a sheet
                   p.Workbook.Worksheets.Add(title);
 
                   var sheet = p.Workbook.Worksheets[1];
 
 
                   var row = 1; // 1 is the header.
                   foreach (var item in listOfObjects)
                   {
                       var col = 1;
                       if (row == 1)
                       {
                           foreach (var itemForExport in itemsForExport)
                           {
                               sheet.Cells[1, col].Value = itemForExport.Title;
                               col++;
                           }
                           row++;
                           col = 1;
                       }
 
                       foreach (var itemForExport in itemsForExport)
                       {
                           var pi = item.GetType().GetProperty(itemForExport.PropertyName);
                           sheet.Cells[row, col].Value = pi.GetValue(item);
                           col++;
                       }
 
                       row++;
                   }
 
                   return p.GetAsByteArray();
               }

The byteArray which is returned can then be used by FileContentResult on the controller.

  [AcceptVerbs(HttpVerbs.Post)]
      public FileContentResult DownloadXls(string saveOption, string browser)
      {
          const string filename = "Export.xlsx";
 
          return File(BuildXlsFromDataAnnotation(), "text/csv", filename);
      }

This should be enough for you to get started, you could for example add more properties on the DataAnnotation to be able format the cells within the spreadsheet.

Update 1

If you have dates (and who doesn't?) in your export it is worth making this amendment. This will format dates to dd-mmm-yyyy format and tell excel that it is a date col.

var pi = item.GetType().GetProperty(itemForExport.PropertyName);
sheet.Cells[row, col].Value = pi.GetValue(item);
if (pi.PropertyType.FullName.Contains("DateTime"))
{
   sheet.Cells[row, col].Style.Numberformat.Format = "dd-MMM-yyyy";
}