public class ExcelResult : ActionResult
{
public ExcelPackage ExcelPackage { get; set; }
public string FileName { get; set; }
public override void ExecuteResult(ControllerContext context)
{
context.HttpContext.Response.AppendHeader("Content-type", "application/vnd.ms-excel");
context.HttpContext.Response.AppendHeader("Content-disposition", string.Format("attachment; filename={0}.xls", FileName));
byte[] a = ExcelPackage.GetAsByteArray();
context.HttpContext.Response.OutputStream.Write(a, 0, a.Length);
}
}
And then we want to re-use that component able to export list of employee, we can do this:
public class EmployeeExcelResult : ExcelResult
{
IList<Employee> _employees;
public EmployeeExcelResult(IList<Employee> employees)
{
_employees = employees;
}
public override void ExecuteResult(ControllerContext context)
{
ExcelPackage = new ExcelPackage();
ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
sheet.Cells[1, 1].Value = "Firstname";
sheet.Cells[2, 1].LoadFromCollection(_employees.Select(y => new {Value = y.FirstName}));
sheet.Cells[1, 2].Value = "Lastname";
sheet.Cells[2, 2].LoadFromCollection(_employees.Select(y => new { Value = y.LastName }));
sheet.Cells[1, 2].Value = "Age";
sheet.Cells[2, 2].LoadFromCollection(_employees.Select(y => new { Value = y.Age}));
base.ExecuteResult(context);
}
}
And we will use that like this in the controller's action:
public ActionResult ExportEmployeesToExcel()
{
IList<Employee> employees = new List<Employee>()
{
new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
};
return new EmployeeExcelResult(employees);
}
It should be noted that no ethically-trained software engineer would ever consent to write a DestroyBaghdad procedure. Basic professional ethics would instead require him to write a DestroyCity procedure, to which Baghdad could be given as a parameter -- Nathaniel Borenstein
The approach above(class EmployeeExcelResult), though it adheres to two of the basic OOP tenets(i.e. inheritance and polymorphism), has a problem if we need to use another kind of list to be exported, say list of product. Aside from that approach is not reusable, it's error prone too. If you are a keen observer, you'll notice that the Age property overwrites the Lastname property, resulting to two exported columns only.
We got to make the component very re-usable:
public class ExcelResult<T> : ExcelResult
{
IEnumerable<T> _list;
public ExcelResult(IEnumerable<T> list)
{
_list = list;
}
public override void ExecuteResult(ControllerContext context)
{
ExcelPackage = new ExcelPackage();
ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
int ordinal = 0;
foreach (System.Reflection.PropertyInfo pi in typeof(T).GetProperties())
{
++ordinal;
sheet.Cells[1, ordinal].Value = pi.Name;
sheet.Cells[2, ordinal].LoadFromCollection(_list.Select(x => new {Value = GetPropValue(x, pi.Name)}));
}
base.ExecuteResult(context);
}
// In this age of stackoverflow, who needs MSDN?
// http://stackoverflow.com/questions/1196991/get-property-value-from-string-using-reflection-in-c-sharp
static object GetPropValue(object src, string propName)
{
return src.GetType().GetProperty(propName).GetValue(src, null);
}
}
.
.
.
public ActionResult ExportEmployeeList()
{
IList<Employee> employees = new List<Employee>()
{
new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
};
return new ExcelResult<Employee>(employees);
}
That's flexible now, but we can improve it more. We can make it more flexible by letting us able to specify which columns should be exported to Excel only, and able to specify the column's format. There are three approach we can use when designing an API such as that.
- First we maintain a dictionary of column's metadata, where the metadata indicates the column's label and formatting. But dictionary is a non-starter as it promotes stringly-typed programming.
- Second is we can use attributes, but that would entail decorating the attributes directly to the concerned class, what if the other party(think B2B's provider) won't allow you to add attributes to their classes? The work-around on this second option is to use buddy classes, this would work only if the consuming party's classes are generated with partials(e.g. WCF) on them. But even buddy classes is an option, buddy classes violates DRY principle, and refactoring wouldn't be available also. Attributes-based API won't let us be creative, even a simple API such as passing the method's address to the function is not possible with attribute, hence necessitates passing the method as string, and calling the method through reflection. Stringly-typed API is brittle and non-discoverable.
That leaves us with the third option, let's design an API that avoids violating DRY principle, and avoids stringly-typed programming to boot. We can use Fluent API and C#'s Expression
We can make a better design for an API if we design it from the end, that is we design it how we wanted to use it. So here's how we wanted to use our API, this is similar to Fluent NHibernate: https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started
public ActionResult ExportEmployeesToExcel()
{
IList<Employee> employees = new List<Employee>()
{
new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
};
return new ExcelResultFromList<Employee>(employees)
{
Filename = "Employees",
ExcelMapping = new EmployeeExcelMapping()
};
}
.
.
.
public class EmployeeExcelMapping : ExcelMapping<Employee>
{
public EmployeeExcelMapping()
{
Include(x => x.LastName).Label("Family Name");
Include(x => x.FirstName);
Include(x => x.Age).NumberFormat("#,#00.00");
Include(x => x.SongsPercent).Label("Song%").NumberFormat("0.000%");
// we didn't include the middle name to exported excel
}
}
The above will appeal to you if you are a Fluent NHibernate fan. If you have used NHibernate 3.2, you might already learned there's another style of mapping by code which is built-in in NHibernate, you can inline-ly(if there's such a word) map your class with the latest NHibernate. Meaning, you don't have to create a separate class for your mapping, this is what "loquacious" (such an unfortunate name, the fluent nomenclature was already taken by others) mapping facilitates. The NHibernate's loquacious API has a more one-stop-shop feel into it. The API below is similar to this style: http://fabiomaulo.blogspot.com/2011/04/nhibernate-32-mapping-by-code.html
public ActionResult ExportEmployeesToExcel()
{
IList<Employee> employees = new List<Employee>()
{
new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Age = 12345, SongsPercent = 0.301234},
new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Age = 67891234, SongsPercent = 0.205678},
new Employee() { FirstName = "George", MiddleName = "Tough", LastName = "Harisson", Age = 3456 },
new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Age = 3456 }
};
return ExcelResultFromList.Create<Employee>
(list: employees,
filename: "Employees",
mapAction: e =>
{
e.Include(x => x.LastName).Label("Apelyido");
e.Include(x => x.FirstName);
e.Include(x => x.Age).NumberFormat("#,#00.00");
e.Include(x => x.SongsPercent).Label("Song%").NumberFormat("0.000%");
});
}
You can choose either fluent style or the loquacious style. Whichever mental model suits you
That's it, that's how we want our API to be, a good .NET citizen, very respecting of POCO. Very respecting of DRY principle: http://en.wikipedia.org/wiki/Don't_repeat_yourself
Expand the full code below to see how to implement the API above:
using System.Linq;
public class ExcelMapping<T>
{
readonly System.Collections.Generic.IList<ExportMetaData<T>> _exportList = new System.Collections.Generic.List<ExportMetaData<T>>();
public System.Collections.Generic.IList<ExportMetaData<T>> ExportList { get { return _exportList; } }
public ExportPart<T> Include<TProp>(System.Linq.Expressions.Expression<System.Func<T, TProp>> p)
{
string name = new ExpressionGetter.PropertyPathVisitor().GetPropertyPath(p);
var emd = new ExportMetaData<T>();
ExportList.Add(emd);
var input = new ExportPart<T>(emd, name);
return input;
}
}
public class ExportMetaData<T>
{
public string Name { get; set; }
public string Label { get; set; }
public string NumberFormat { get; set; }
// just ready this in case EPPlus can compute column width in the future
// public bool AutoFit { get; set; }
public System.Func<T, object> ValueReplacer { get; set; }
}
public class ExportPart<T>
{
ExportMetaData<T> _emd;
public ExportPart(ExportMetaData<T> exportMetaData, string name)
{
_emd = exportMetaData;
_emd.Name = name;
}
public ExportPart<T> NumberFormat(string format)
{
_emd.NumberFormat = format;
return this;
}
public ExportPart<T> Label(string label)
{
_emd.Label = label;
return this;
}
public ExportPart<T> ValueReplacer(System.Func<T, object> valueReplacer)
{
_emd.ValueReplacer = valueReplacer;
return this;
}
}
// Should I call this ExcelResultFromListFactory?
public static class ExcelResultFromList
{
public static ExcelResultFromList<T> Create<T>(System.Collections.Generic.IEnumerable<T> list, string filename, System.Action<ExcelMapping<T>> mapAction)
{
var e = new ExcelMapping<T>();
var r = new ExcelResultFromList<T>(list) { FileName = filename, ExcelMapping = e };
mapAction(e);
return r;
}
}
public class ExcelResultFromList<T> : ExcelResult
{
public ExcelMapping<T> ExcelMapping { get; set; }
public System.Collections.Generic.IEnumerable<T> _list;
public ExcelResultFromList(System.Collections.Generic.IEnumerable<T> list)
{
_list = list;
}
public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
{
ExcelPackage = new OfficeOpenXml.ExcelPackage();
OfficeOpenXml.ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
int col = 0;
foreach (ExportMetaData<T> emd in ExcelMapping.ExportList)
{
int ordinal = ++col;
sheet.Cells[1, ordinal].Value = emd.Label ?? emd.Name;
OfficeOpenXml.ExcelColumn ec = sheet.Column(ordinal);
if (!string.IsNullOrEmpty(emd.NumberFormat))
ec.Style.Numberformat.Format = emd.NumberFormat;
sheet.Cells[2, ordinal].LoadFromCollection(_list.Select(y =>
new
{
Value = emd.ValueReplacer == null ? GetPropValue(y, emd.Name) : emd.ValueReplacer(y)
}));
}
base.ExecuteResult(context);
}
// In this age of stackoverflow, who needs MSDN?
// http://stackoverflow.com/questions/1196991/get-property-value-from-string-using-reflection-in-c-sharp
static object GetPropValue(object src, string propName)
{
return src.GetType().GetProperty(propName).GetValue(src, null);
}
}
// We can t make an apple pie from scratch, some ingredients have to come from somewhere:
// PropertyPathVisitor sourced from: http://www.thomaslevesque.com/2010/10/03/entity-framework-using-include-with-lambda-expressions/
namespace ExpressionGetter
{
class PropertyPathVisitor : System.Linq.Expressions.ExpressionVisitor
{
private System.Collections.Generic.Stack<string> _stack;
public string GetPropertyPath(System.Linq.Expressions.Expression expression)
{
_stack = new System.Collections.Generic.Stack<string>();
Visit(expression);
return _stack
.Aggregate(
new System.Text.StringBuilder(),
(sb, name) =>
(sb.Length > 0 ? sb.Append(".") : sb).Append(name))
.ToString();
}
protected override System.Linq.Expressions.Expression VisitMember(System.Linq.Expressions.MemberExpression expression)
{
if (_stack != null)
_stack.Push(expression.Member.Name);
return base.VisitMember(expression);
}
}
}
public class ExcelResult : System.Web.Mvc.ActionResult
{
public OfficeOpenXml.ExcelPackage ExcelPackage { get; set; }
public string FileName { get; set; }
public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
{
context.HttpContext.Response.AppendHeader("Content-type", "application/vnd.ms-excel");
context.HttpContext.Response.AppendHeader("Content-disposition", string.Format("attachment; filename={0}.xls", FileName));
byte[] a = ExcelPackage.GetAsByteArray();
context.HttpContext.Response.OutputStream.Write(a, 0, a.Length);
}
}
Happy Coding! ツ
