How to Efficiently Export Data to Excel Using EPPlus?

How to Export Data to Excel

The provided guide demonstrates a workaround for generating HTML files with tables, but this approach can be unreliable when Excel interprets and imports these files. A more effective method is to create a dedicated Excel file using a library such as EPPlus.

Using EPPlus

EPPlus offers the LoadFromDataTable and LoadFromCollection methods to populate an Excel range with data from a data table or a generic collection, respectively. The column or property names serve as headers during loading.

Example Code:

public ActionResult ExportData()
{
    DataTable dataTable = GetData(); // Method to retrieve data

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

        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 Styling:

Once data is loaded, you can further customize and style the Excel range:

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

Customizing Output:

To alleviate potential issues when creating large files, or to avoid duplicate writes, you can create a custom FileResult handler for EPPlus packages:

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

    public ExcelPackage Package { get; private set; }

    protected override void WriteFile(HttpResponseBase response)
    {
        using (Package)
        {
            Package.SaveAs(response.OutputStream);
        }
    }
}

This custom result can then be used in the controller action:

public FileResult ExportData()
{
    ExcelPackage package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("My Sheet");
    ... // Load and format data

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

This approach ensures direct writing to the output stream, optimizing performance for large files.

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