How to Export DataTable Data to an Excel (.xlsx) File with Proper MIME Type and Formatting?

How do I export table data to Excel using proper methods and set the correct MIME type for an Excel (xlsx) file?

The provided guide does not demonstrate how to create an Excel file; instead, it employs a workaround that generates an HTML file containing a table. Excel may encounter formatting errors when attempting to import CSV or HTML files containing tables that do not adhere to its default settings.

Creating a genuine Excel file utilizzando EPPlus is significantly simpler. LoadFromDataTable and LoadFromCollection from EPPlus can be used to populate an Excel range with data from a DataTable or a generic collection. Headers are generated using the column or property names, respectively.

public ActionResult ExportData()
{
    // Load data into 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 and LoadFromCollection return an Excel cell range that can be used to apply formatting to the table:

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

Additionally, you can apply a table style to the range:

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

Formatting, formulas, PivotTables, and table handling can all be found in the library's GitHub wiki.

Saving to a MemoryStream may not be ideal for larger sheets due to duplicate data writing (to memory and output stream). However, returning FileResult directly from an MVC action is discouraged. The solution is to create a FileResult tailored 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)
    {
        Package.SaveAs(response.OutputStream);
    }
}

This allows for the following in your 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 Export DataTable Data to an Excel (.xlsx) File with Proper MIME Type and Formatting?. For more information, please follow other related articles on the PHP Chinese website!