Skip to content

Instantly share code, notes, and snippets.

@derans
Created September 23, 2012 22:17
Show Gist options
  • Save derans/3773242 to your computer and use it in GitHub Desktop.
Save derans/3773242 to your computer and use it in GitHub Desktop.
Excel Action Result
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; }
}
}
@leroyvaughan
Copy link

This code would be great if you had a working sample of all of it together in a zip file format. I can't open a gz file on my locked down system and I have a bunch of red squiggly lines in the code.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment