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.
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.
The code developed for this project can be found here.
- Asp.Net Core 2.1 MVC (Can work with later versions).
- NPOI Package.
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.
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.
Don't forget to add project references.
- Web references all others. Service, Entity, and Utility.
- Service references both Utility and Entity.
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.
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.
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.
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:
- 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.
- Instantiate the workbook and sheet objects (from NPOI).
- Create the header row with properties created in 1.2.
- Add each new row as a row containing data from the data table.
- Done. Return the Workbook.
We will have two endpoints in the home controller. one for the home page itself and the other for exporting to excel.
First we will retrieve the list of users on a web page, then we will click on a button to export them to Excel.
Following is how the controller returns a file to the client.
- Get the Workbook object containing the user list from service.
- Write the workbook content to a memory stream.
- convert the stream content to a byte array.
- Since the first stream is closed, create a new stream with the byte array content.
- Set the file content type.
- Return a File object containing the data and the content type.
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 😃.