In this article, we will learn how to Export Data from Database to Excel File in Asp.net Core MVC . In previous article we had discussed How to Upload multiple files using Asp.Net Core MVC C#.
Table of Contents
Create an ASP.NET Core MVC Project
Open visual studio, Go to File menu and click on New and select Project. Then new project window, select ASP.NET Core Web Application (Model-View-Controller) template.
I had to export some data to Excel from an ASP.NET Core website. I have been implementing a lot of exporting in the past with ASP.NET and ASP.NET MVC. Supporting the same in ASP.NET Core was a good excuse to look at different possibilities and write about it 😃
When needing to export data to Excel from ASP.NET Core, you have a couple of different options. If you need simple tabular data, generating a CSV file may be sufficient. If you need to embed colors and formulas, you will need something more advanced. Let's start simple.
For the examples in this post, I'll use an ASP.NET Core controller with some hard-coded data. In a real application, these data would come from user input, a database, or similar:
Export as a CSV
The simplest way to export data is as a comma-separated values (CSV) file. There are NuGet packages available to help to that, but for this post, I'll create the CSV manually:
Export as an XLSX
If you need more complexity in your exported file than simple tabular data, you need to export data as a real Excel file. CSV files cannot contain colors, formulas, etc.
1 from Microsoft, but neither provides an API as simple as
To generate an
Then add a new controller action. For this post, I have formatted the output like in the CSV example for consistency. ClosedXML provides a rich set of features to include colors, calculations, and much more. Check out their documentation for details. Here's the code:
Let's go through the code. To create the Excel document, I'm creating a new
5. You can see the benefits of
7. Excel worksheets aren't available using a CSV file.
Like in the previous example, I add a headline row and put in the values from the
8 list in the following rows. The API doesn't require a lot of introduction since it is pretty easy to understand. You reference rows and cells using the
Finally, I export the workbook to a stream and return it from the controller as a file.
As it turns out, exporting data to Excel from ASP.NET Core is pretty straight-forward. Depending on the requirements of the outputted file, you can choose either CSV or XLSX. Since ClosedXML is my go-to framework, I would love to hear your experience with good Excel packages.
Extending the date for removing the v2 API
elmah.io: Error logging and Uptime Monitoring for your web apps