Export any list to Excel in ASP.Net Core

A lot of us come across the requirement to export the list of users, orders, or another list of items to Excel. A lot of times, one has to repeat the same steps in a function but customize them to their objects of interest. In this post, we will learn how to export ANY list of objects to Excel.

In this article we will explore exporting any list to an excel spreadsheet in Asp.net Core.

Project

Say you have a list of users in your application and you want to data analysis for them with graphs and pivot table and what not. Excel is a really good tool to do that. In the mock application in this article, we will create a few users and export their data to an Excel file.

Code

The code developed for this project can be found here.

Tools

• Asp.Net Core 2.1 MVC (Can work with later versions).
• NPOI Package.

Application

Open up Visual Studio (Or any other tool for your preference) and create a new Asp.Net Core Project (Model-View-Controller). I chose version 2.1, but choosing the current version (3.1 at this time) should also be fine.

Application Architecture

To ensure our code is reusable, we will organize the application in the following manner.

[Web (Client) --> Controllers --> Services --> Entities].
           |
     [Utilities] [referenced by all]

For more on Clean Architecture, check out this blog.

This is how the project structure currently looks like.

Don't forget to add project references.

• Web references all others. Service, Entity, and Utility.
• Service references both Utility and Entity.

Note on Dependency Injection

Asp.Net Core has dependency injection as a built-in feature. Meaning that each class will be created along with its interface and registered as a service in Web/Startup.cs.
It is basically a way to configure the registration and instantiation of classes at runtime without explicitly specifying that you need using the new keyword, which makes testing and maintainability a lot easier. For more on dependency injection, checkout this video.

Creating a list for the demo

For simplicity, we will create a hard coded a list of users and retrieve them for the client. Each user will have a record that contains their Id, first name, last name, email address, and phone number. Ideally, the list will be stored in a database and retrieved using an Object Relational Mapper such as EF Core.

Creating the User Service to get the list

A simple user service the has one function "GetAllUsers()" returns a list of 50 hard coded users.

Creating the Utility for Exporting Lists

Installing NPOI

The library we will use for exporting the list is NPOI since it has no dependencies. You could install the library NPOI using Nuget package manager in Visual Studio or by typing dotnet add package NPOI. You need to install this library both in Utility and Service projects.
You can find the library website here.

Creating the Utility Class and Functions

Now we will create a class called "ExportUtility" under the utility project. Essentially, the algorithm to convert any list to data on an excel sheet works as follows:

1. Convert the list to a data table. (1.1) Get the properties of each value in the list data type. (1.2) Add the properties to the first row of the data table. (1.3) Add the data in the list each in a new row.
2. Instantiate the workbook and sheet objects (from NPOI).
3. Create the header row with properties created in 1.2.
4. Add each new row as a row containing data from the data table.
5. Done. Return the Workbook.

Controller

We will have two endpoints in the home controller. one for the home page itself and the other for exporting to excel.

Demo

First we will retrieve the list of users on a web page, then we will click on a button to export them to Excel.

Run the application

Run the application either by clicking the arrow button on Visual studio or by typing dotnet run and then opening the link it generates. Then, you will find a table containing the list of users.

Exporting To Excel

Controller Algorithm and Code

Following is how the controller returns a file to the client.

1. Get the Workbook object containing the user list from service.
2. Write the workbook content to a memory stream.
3. convert the stream content to a byte array.
4. Since the first stream is closed, create a new stream with the byte array content.
5. Set the file content type.
6. Return a File object containing the data and the content type.

Demo

We will create an anchor tag to call this function at the endpoint and see the contents of the file.

This is the download popup after the server returns the file.

Excel File Content.

Conclusion

In this tutorial, exporting any list (containing primitive data) in an Excel format was explained in detail. I'll be glad to read your comments and answer your questions 😃.