Export to Excel in ASP.Net Core MVC

 In this article we will learn about Export to Excel in ASP.Net Core MVC.

When building web applications, we will often need to import or export data from Word, Excel or PDF documents. There are several ways to achieve this, and plenty of NuGet packages to work with Word or Excel. This article discusses how we can work with ClosedXML in ASP.NET Core to export data to Excel.

Create an ASP.NET Core MVC project in Visual Studio

Let’s create an ASP.NET Core project in Visual Studio. I have used Visual Studio 2022 to create 

The data from the database is fetched using Entity Framework and then the data is exported and downloaded as Microsoft Excel file using ClosedXML Excel library which is a wrapper for OpenXml Excel library.

Installing and adding reference of ClosedXML Library

In order to install and add reference of ClosedXML library,

Right Click the Project in Solution Explorer and click Manage NuGet Packages from the Context Menu and add the ClosedXML library like below.

Add the package Entity Framework Core

You will need to install the Microsoft.EntityFrameworkCore.SqlServer package using the following command.

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.1.1

Add the Customer Model class

Add the customer model class like below,

Creating an Entity Data Model Data Context

The very first step is to create an ASP.Net MVC Application and connect it to the Database using Entity Framework. Once the Entity Framework is configured and connected to the database table, the Database Context will look as shown below.

Controller

The Controller consists of two Action methods.

Action method for handling GET operation

Inside this Action method, we load customer records are fetched and returned to the View.

 

Action method for handling the Excel File Export and Download operation

This Action method is executed when the Export Submit button is clicked.

Customer records are fetched from the Customers Table using Entity Framework and are added to a dynamic DataTable. Then the DataTable is added to the Worksheets collection of the ClosedXML’s XLWorkbook object. Then XLWorkbook object is saved to a MemoryStream object which then is converted to Byte Array and exported and downloaded as Excel file using the File function.

In Line no. 23, we used the method that convert List to Data Table as the Workshhet need the said type.

The below are the lines of code that convert List to DataTable.

Add the connection string in Appsettings

Register the connection string in Startup File

View

Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that it will be available as a Collection.

For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.

Finally there’s an HTML Submit button enclosed inside a Form with the Action method specified as Export. When this Button will be clicked, the Grid (Html Table) data will be exported and downloaded as Excel file.

Output

The Final output like below, as we can able to generate excel from the customer data.

</> Find the Source Code in Github

  Summary

In this tutorial we discussed about Export to Excel in ASP.Net Core MVC. If have any question related to this topic then give your feedback.