How to Export data to Excel file with ASP.NET MVC

By FoxLearn 2/18/2024 2:11:53 AM   266
This post shows you how to export data to excel file using C# in ASP.NET MVC

The export to Excel functionality is implemented using the ClosedXML.Excel NuGet Package or download it directly from https://github.com/ClosedXML/ClosedXML, as shown in this example.

We will install ClosedXML.Excel library from the Nuget Packages Manager

What is the NuGet package manager?

It's a tool that allows you to let developers to create, share, and consume useful . NET libraries. NuGet client tools provide the ability to produce and consume these libraries as "packages"

The ClosedXML library makes it easier for developers to create Excel (.xlsx, .xlsm, etc) files. It provides a nice object orientation for manipulating files without having to handle the complexity of XML documents. It can be used by any .NET language such as C# and VB.NET.

public FileResult Export()
{
    if (TempData["Invoices"] != null)
    {
        DataTable dt = new DataTable("Invoices");
        dt.Columns.AddRange(new DataColumn[7]
        {
                    new DataColumn("RowNumber"),
                    new DataColumn("InvoiceNumber"),
                    new DataColumn("BuyerName"),
                    new DataColumn("BuyerAddress"),
                    new DataColumn("BuyerEmail"),
                    new DataColumn("Total"),
                    new DataColumn("InvoiceDate")
        });
        foreach (var item in TempData["Invoices"] as List<InvoiceInfo> )
            dt.Rows.Add(item.RowNumber, item.InvoiceNumber, item.BuyerName, item.BuyerAddress, item.BuyerEmail, item.Total, item.InvoiceDate);
        TempData.Keep("Invoices");
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt);
            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Invoices.xlsx");
            }
        }
    }
    throw new HttpException(404, "File not found !");
}

You can get data from TempData or get directly from your database, in this tutorial i'm using TempData

ClosedXML allows you to create Excel 2007 version (.xlsx, .xlsm, etc) files without the Excel application. The typical example is creating Excel reports on a web server.