How to Efficiently Export Data to an Excel File Using EPPlus?

How do I export to Excel?

The guide doesn't show how to create an Excel file, it shows a hack that creates an HTML file with a Table. Excel will try to import csv or HTML files with tables using default settings which can often fail.

It's far easier to create a proper Excel file using a library like EPPlus.
I'm sure this has been answered before but I can't find an answer that shows both how to create an Excel file from data and set the proper content type for an xlsx file.

EPPlus provides the LoadFromDataTable and LoadFromCollection methods to fill an Excel range with data pulled from a DataTable or generic collection. The column or property names are used as headers respectively.

public ActionResult ExportData()
{

    //Somehow, load data to a DataTable

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

LoadFromDataTable or LoadFromCollection return an Excel cell range which can be used to format the table:

var range=ws.Cells["A1"].LoadFromDataTable(table);
range.Style.Numberformat.Format = "#,##0.00";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

You can also apply a table style to the range, or pass the desired style to LoadFromDataTable or LoadFromCollection, eg:

ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

The library's Githup wiki shows how to format the result, generate formulas, PivotTables, handle tables etc.

Saving to a MemoryStream may be an issue if you want to create a relatively large sheet. Writing the same data twice, once to the MemoryStream and then copying it to output, wastes cycles. Writing directly to the output stream from an MVC action is a bad practice though. The trick is to create a custom FileResult that handles EPPlus packages and return that instead of the FileResult returned by File, eg:

public class EpplusResult:FileResult
{
    public EpplusResult(ExcelPackage package)
        : base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        if (package == null)
        {
            throw new ArgumentNullException("package");
        }

        Package = package;
    }

    public ExcelPackage Package { get; private set; }

    protected override void WriteFile(HttpResponseBase response)
    {
        // grab chunks of data and write to the output stream
        Stream outputStream = response.OutputStream;
        using (Package)
        {
            Package.SaveAs(outputStream);
        }
    }
}

Which allows you to write the following action:

public FileResult ExportData()
{

    ExcelPackage package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("My Sheet");       
    ...
    ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

    return new EpplusResult(package){FileDownloadName = "SomeFile.xlsx"};
}

The above is the detailed content of How to Efficiently Export Data to an Excel File Using EPPlus?. For more information, please follow other related articles on the PHP Chinese website!