Export to Excel with MVC

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);
	}

view raw ExportToExcel_Sample_for_EPPlusForMVC.cs hosted with ❤ by GitHub

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};
	}

view raw Specific_columns_and_formatting_export.cs hosted with ❤ by GitHub

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);
	}

view raw column_helper_method.cs hosted with ❤ by GitHub

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};
	}

view raw Specific_columns_and_formatting_with_method.cs hosted with ❤ by GitHub

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;
	}
	}

view raw ExcelColumnDefinition.cs hosted with ❤ by GitHub

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; }
	}
	}

view raw ExcelFileResult.cs hosted with ❤ by GitHub

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.