How to Efficiently Export Data to Excel from ASP.NET MVC using EPPlus?

How to Export Data to Excel Effectively in ASP.NET MVC

The provided guide attempts to export data to Excel using a less efficient HTML-based approach. To create a proper Excel file, it's recommended to utilize a library like EPPlus.

EPPlus Library

EPPlus provides the LoadFromDataTable and LoadFromCollection methods to fill an Excel range with data. Here's how to implement it in your code:

Code:

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

By default, the column or property names will be used as headers. You can also apply formatting, table styles, and more using the EPPlus library.

Saving to a File

To save the Excel file, create a custom FileResult called EpplusResult:

Code:

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

    public ExcelPackage Package { get; private set; }
}

This allows you to write the following action:

Code:

public FileResult ExportData()
{
    ExcelPackage package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("My Sheet");       
    // Load data to the worksheet
    ws.Cells[1, 1].LoadFromDataTable(table, true, TableStyles.Light1);

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

Bonus: Exporting Paged Data

To export paged data with PagedList, you can modify the action:

Code:

public ActionResult ExportData()
{
    var pagedList = db.Customers.ToPagedList(pageNumber, pageSize);

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

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

The above is the detailed content of How to Efficiently Export Data to Excel from ASP.NET MVC using EPPlus?. For more information, please follow other related articles on the PHP Chinese website!