Tweet

I’m sure you’ve all had to export to Excel at one point or another. I created a small wrapper around the EPPlus library for MVC. Basically my wrapper contains a few helpers for common formatting, an ActionResult, and a column definition. Here’s what the end result looks like in the simplest form:

        public ActionResult ExportToExcel()
        {
            var records = _getSampleInfoQuery.Execute();
            return new ExcelFileResult<SampleInfo>(records);
        }

Basically this exports all the columns and records in the SampleInfo collection. So if you want to specify the columns you want, you can do this:

public ActionResult ExportToExcel()
{
    var records = _getSampleInfoQuery.Execute();
    var columns = new[]
    {
        ExcelColumnDefinition.Create<SampleInfo>(x => x.College),
        ExcelColumnDefinition.Create<SampleInfo>(x => x.Amount, ExcelFormat.Money),
        ExcelColumnDefinition.Create<SampleInfo>(x => x.CreatedDate, ExcelFormat.Date, "Date"),
        ExcelColumnDefinition.Create<SampleInfo>(x => x.CreatedBy),
        ExcelColumnDefinition.Create<SampleInfo>(x => x.CreatedDate, ExcelFormat.Time, "Time"),
        ExcelColumnDefinition.Create<SampleInfo>(x => x.PercentageExample, ExcelFormat.Percent),
    };

    return new ExcelFileResult<SampleInfo>(records) {ColumnDefinitions = columns};
}

The ExcelColumnDefinition.Create<> is kinda ugly, so you could create a small helper method for readability like this:

private static ExcelColumnDefinition Column(Expression<Func<SampleInfo, object>> member, string format = null, string header = null)
{
    return ExcelColumnDefinition.Create(member, format, header);
}

Now the above example looks like this:

public ActionResult ExportToExcel()
{
    var records = _getSampleInfoQuery.Execute();
    var columns = new[]
    {
        Column(x => x.College),
        Column(x => x.Amount, ExcelFormat.Money),
        Column(x => x.CreatedDate, ExcelFormat.Date, "Date"),
        Column(x => x.CreatedBy),
        Column(x => x.CreatedDate, ExcelFormat.Time, "Time"),
        Column(x => x.PercentageExample, ExcelFormat.Percent),
    };

    return new ExcelFileResult<SampleInfo>(records) {ColumnDefinitions = columns};
}

So this is what the ExcelColumnDefinition looks like:

public class ExcelColumnDefinition
{
public MemberInfo MemberInfo { get; set; }
public string Format { get; set; }
public string Header { get; set; }

public static ExcelColumnDefinition Create<T>(Expression<Func<T, object>> member, string format = null, string header = null)
{
    return new ExcelColumnDefinition { MemberInfo = GetMemberInfo(member), Format = format, Header = header };
}

private static MemberInfo GetMemberInfo<T>(Expression<Func<T, object>> expression)
{
    if (expression.Body is MemberExpression)
        return ((MemberExpression)expression.Body).Member;

    return ((MemberExpression) ((UnaryExpression) expression.Body).Operand).Member;
}
}

Here’s the actual ActionResult:

public class ExcelFileResult<T> : ActionResult
{
    private readonly IEnumerable<T> _records;

    public ExcelFileResult(IEnumerable<T> records)
    {
        _records = records;
    }

    public override void ExecuteResult(ControllerContext context)
    {
        using (var pck = new ExcelPackage())
        {
            var ws = pck.Workbook.Worksheets.Add(WorksheetName);

            if (ColumnDefinitions == null)
            {
                ws.Cells["A1"].LoadFromCollection(_records, true, TableStyles.None);
            }
            else
            {
                ws.Cells["A1"].LoadFromCollection(_records, true, TableStyles.None, BindingFlags.Default, ColumnDefinitions.Select(x => x.MemberInfo).ToArray());
                Format(ws, ColumnDefinitions);
            }

            const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var fileContentResult = new FileContentResult(pck.GetAsByteArray(), contentType) { FileDownloadName = FileDownloadName };
            fileContentResult.ExecuteResult(context);
        }
    }

    private static void Format(ExcelWorksheet worksheet, IList<ExcelColumnDefinition> columnDefinitions)
    {
        worksheet.Row(1).Style.Font.Bold = true;
        for (var columnIndex = 1; columnIndex <= columnDefinitions.Count; columnIndex++)
        {
            var columnDefinitionIndex = columnIndex - 1;
            if (columnDefinitions[columnDefinitionIndex].Format != null)
            {
                worksheet.Column(columnIndex).Style.Numberformat.Format = columnDefinitions[columnDefinitionIndex].Format;
            }
            if (columnDefinitions[columnDefinitionIndex].Header != null)
            {
                worksheet.Cells[1, columnIndex].Value = columnDefinitions[columnDefinitionIndex].Header;
            }
            worksheet.Column(columnIndex).AutoFit();
        }
    }

    public ExcelColumnDefinition[] ColumnDefinitions { get; set; }

    private string _downloadName;
    public string FileDownloadName
    {
        get { return _downloadName ?? "ExcelFile"; }
        set { _downloadName = value; }
    }

    private string _worksheetName;
    public string WorksheetName
    {
        get { return _worksheetName ?? "Sheet1"; }
        set { _worksheetName = value; }
    }
}

So that’s it. If you have any questions, please feel free to comment below.

You can browse or download the source on GitHub. The sample project has examples.