public ActionResult ExportEmployeesToExcel()
{
IList<Employee> employees = new List<Employee>()
{
new Employee() { FirstName = "John", MiddleName = "Winston", LastName = "Lennon", Savings = 12345.67, SongsPercent = 0.301234},
new Employee() { FirstName = "Paul", MiddleName = "Phoenix", LastName = "McCartney", Savings = 67891234.56, SongsPercent = 0.205678},
new Employee() { FirstName = "George", MiddleName = "Stoic", LastName = "Harisson", Savings = 3456.78 },
new Employee() { FirstName = "Ringo", MiddleName = "Drum", LastName = "Starr", Savings = 345.67 }
};
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.Savings).Format("#,#00.00");
e.Include(x => x.SongsPercent / 100).Label("Song%").Format("0.000%");
});
}
We would want to be able to pass a complex lambda expression on Include so it would be possible to divide a number (or any expression for that matter), in order to do that, we need to implement the API like this:
public ExportPart<T> Include(Func<T, object> exp)
{
var emd = new ExportMetaData<T>();
ExportList.Add(emd);
var input = new ExportPart<T>(emd, exp);
return input;
}
But since I'm a convention-over-configuration-loving .NETizen, I would love to make it a convention on the code that if I pass a simple lambda expression(e.g. x.LastName, x.FirstName) and I didn't specify an explicit Label, the code shall automatically use the name of the property as the default label for the column on the exported Excel file. With that being said, we shall need to tap the power of Expression:
public ExportPart<T> Include(Expression<Func<T, object>> exp)
{
var emd = new ExportMetaData<T>();
ExportList.Add(emd);
var input = new ExportPart<T>(emd, exp);
return input;
}
Then to get the Expression's string representation, just use this awesome PropertyPathVisitor from: http://www.thomaslevesque.com/2010/10/03/entity-framework-using-include-with-lambda-expressions/
To use:
string label = new PropertyPathVisitor().GetPropertyPath(exp);
We can make things more nicer by automatically splitting the PascalCase label, i.e. BusinessAddress label will be formatted as Business Address:
// http://stackoverflow.com/questions/3103730/is-there-a-elegant-way-to-parse-a-word-and-add-spaces-before-capital-letters
var r = new System.Text.RegularExpressions.Regex(
@" (?<=[A-Z])(?=[A-Z][a-z]) # UC before me, UC lc after me
| (?<=[^A-Z])(?=[A-Z]) # Not UC before me, UC after me
| (?<=[A-Za-z])(?=[^A-Za-z]) # Letter before me, non letter after me
", RegexOptions.IgnorePatternWhitespace);
string label = new PropertyPathVisitor().GetPropertyPath(exp);
label = string.Join(" ", r.Split(label));
Now for the fun part, how can we run the lambda expression if it's from the genericize Expression? To do that, just use the Expression's Compile method to receive a Func delegate of the Expression's lambda expression:
Func<T, object> func = exp.Compile();
var rowValues = _list.Select(x => new { Value = func(x) });
To see how the Expression's Compile method works:
using System;
using System.Linq;
using System.Linq.Expressions;
public class Test
{
public static void Main()
{
Func<int, object> hexConverter = x => "Value is " + x.ToString("X");
Console.WriteLine("{0}", hexConverter(36));
Expression<Func<int, object>> hexConverterExpression = x => "Good number is " + x.ToString("X");
Func<int, object> hexConverterToo = hexConverterExpression.Compile();
Console.WriteLine("{0}", hexConverterToo(36));
}
}
Output:
Value is 24 Good number is 24
Live code: http://ideone.com/clh3mk
Expand to see a comprehensive example of Expression. Download the dependent EPPlus component to use code below:
namespace ExcelExporter
{
using System.Linq.Expressions;
using System.Web.Mvc;
using ExpressionGetter;
// Should I call this ExcelResultFromListFactory?
public static class ExcelResultFromList
{
public static ExcelResultFromList<T> Create<T>(IEnumerable<T> list, string filename, 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> : TalentReward.ESS.Code.ActionResults.ExcelResult
{
public ExcelMapping<T> ExcelMapping { get; set; }
public IEnumerable<T> _list;
public ExcelResultFromList(IEnumerable<T> list)
{
_list = list;
}
public override void ExecuteResult(ControllerContext context)
{
ExcelPackage = new ExcelPackage();
ExcelWorksheet sheet = ExcelPackage.Workbook.Worksheets.Add("Export");
if (_list.Count() > 0)
{
int ordinal = 0;
foreach (ExportMetaData<T> eachEmd in ExcelMapping.ExportList)
{
// Pre-C# 5 necessitates closing the enumerating variable in this loop scope. rationale:
// http://blogs.msdn.com/b/ericlippert/archive/2009/11/12/closing-over-the-loop-variable-considered-harmful.aspx
// This can be removed in C# 5:
var emd = eachEmd;
++ordinal;
if (emd.Label != null)
sheet.Cells[1, ordinal].Value = emd.Label;
else
{
// http://stackoverflow.com/questions/3103730/is-there-a-elegant-way-to-parse-a-word-and-add-spaces-before-capital-letters
var r = new System.Text.RegularExpressions.Regex(
@" (?<=[A-Z])(?=[A-Z][a-z]) # UC before me, UC lc after me
| (?<=[^A-Z])(?=[A-Z]) # Not UC before me, UC after me
| (?<=[A-Za-z])(?=[^A-Za-z]) # Letter before me, non letter after me
", RegexOptions.IgnorePatternWhitespace);
string label = new PropertyPathVisitor().GetPropertyPath(emd.Expression);
sheet.Cells[1, ordinal].Value = string.Join(" ", r.Split(label));
}
ExcelColumn ec = sheet.Column(ordinal);
if (!string.IsNullOrEmpty(emd.AllRowsFormat))
ec.Style.Numberformat.Format = emd.AllRowsFormat;
Func<T, object> func = emd.Expression.Compile();
sheet.Cells[2, ordinal].LoadFromCollection(_list.Select(y => new { Value = func(y) }));
if (emd.EachRowFormat != null)
{
var listCellMapping = _list.Zip(sheet.Cells[2, ordinal, 2 + _list.Count() - 1, ordinal],
(list, cell) => new { Cell = cell, EachRowFormat = emd.EachRowFormat(list) });
foreach (var item in listCellMapping)
item.Cell.Style.Numberformat.Format = item.EachRowFormat;
}
if (emd.WithSum)
{
string startingRowAddress = sheet.Cells[2, ordinal].Address;
string endingRowAddress = sheet.Cells[2 + _list.Count() - 1, ordinal].Address;
string formulaAddress = sheet.Cells[2 + _list.Count(), ordinal].Address;
sheet.Cells[formulaAddress].Formula = string.Format("SUM({0}:{1})", startingRowAddress, endingRowAddress);
}
}
}
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 class ExcelMapping<T>
{
private readonly IList<ExportMetaData<T>> _exportList = new List<ExportMetaData<T>>();
public IList<ExportMetaData<T>> ExportList
{
get { return _exportList; }
}
public ExportPart<T> Include(Expression<Func<T, object>> exp)
{
// string name = new PropertyPathVisitor().GetPropertyPath(p);
var emd = new ExportMetaData<T>();
ExportList.Add(emd);
var input = new ExportPart<T>(emd, exp);
return input;
}
}
public class ExportPart<T>
{
readonly ExportMetaData<T> _emd;
public ExportPart(ExportMetaData<T> exportMetaData, Expression<Func<T, object>> exp)
{
_emd = exportMetaData;
_emd.Expression = exp;
}
public ExportPart<T> Format(string format)
{
_emd.AllRowsFormat = format;
return this;
}
public ExportPart<T> Format(Func<T, string> format)
{
_emd.EachRowFormat = format;
return this;
}
public ExportPart<T> Label(string label)
{
_emd.Label = label;
return this;
}
public ExportPart<T> WithSum()
{
_emd.WithSum = true;
return this;
}
// // just ready this in case EPPlus can compute column width in the future
//public ExportPart<T> AutoFit(bool auto)
//{
// _emd.AutoFit = auto;
// return this;
//}
}
public class ExportMetaData<T>
{
public Expression<Func<T, object>> Expression { get; set; }
public Func<T, string> EachRowFormat { get; set; }
public string Label { get; set; }
public string AllRowsFormat { get; set; }
public bool WithSum { get; set; }
// just ready this in case EPPlus can compute column width in the future
// public bool AutoFit { get; set; }
}
}
namespace ExpressionGetter
{
using System.Linq.Expressions;
using System.Text;
// 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/
class PropertyPathVisitor : ExpressionVisitor
{
private Stack<string> _stack;
public string GetPropertyPath(Expression expression)
{
_stack = new Stack<string>();
Visit(expression);
return _stack
.Aggregate(
new StringBuilder(),
(sb, name) =>
(sb.Length > 0 ? sb.Append(".") : sb).Append(name))
.ToString();
}
protected override Expression VisitMember(MemberExpression expression)
{
if (_stack != null)
_stack.Push(expression.Member.Name);
return base.VisitMember(expression);
}
}
}
Happy Coding! ツ
No comments:
Post a Comment