How to Efficiently Export Data to Excel from an ASP.NET MVC Application Using EPPlus?

How to Export Data to Excel

Overview

This question focuses on exporting data to Excel from an ASP.NET MVC application. While the provided guide suggests an HTML-to-Excel conversion approach, this article explores using EPPlus, an Excel package library, for creating proper Excel files.

Using EPPlus for Excel Creation

EPPlus offers methods like LoadFromDataTable and LoadFromCollection to populate an Excel range with data. Here's an example of creating an Excel file:

using (ExcelPackage package = new ExcelPackage())
{
    var ws = package.Workbook.Worksheets.Add("My Sheet");
    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);
}

Formatting and Styles

EPPlus allows for formatting and applying table styles to the exported data. For example:

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

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

Custom File Result for EPPlus

To avoid issues with stream handling, a custom FileResult class can be created to manage EPPlus package responses:

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)
    {
        Stream outputStream = response.OutputStream;
        using (Package)
        {
            Package.SaveAs(outputStream);
        }
    }
}

Implementation in Action Controller

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 Excel from an ASP.NET MVC Application Using EPPlus?. For more information, please follow other related articles on the PHP Chinese website!