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:
- 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.
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.
- 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.
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 😃.
Top comments (0)