How to Export Data to Excel File in ASP.NET Core MVC

How to Export Data to an Excel File in ASP.NET Core MVC

In this article, I will discuss How to Export Data to an Excel File in an ASP.NET Core MVC Application with Examples. Please read our previous article discussing How to Upload Multiple Files in ASP.NET Core MVC Application with Examples. Let’s continue with the application we worked on in previous articles.

How do you export data to an Excel file in ASP.NET Core MVC?

Exporting data to an Excel file in an ASP.NET Core MVC application involves several steps. Here’s a general overview of how you can accomplish this:

  • Prepare the Data to be Exported: Typically, this data is in the form of a list of objects. You should have this list ready in your controller. You can also get this data from the database.
  • Install a Library for Excel Creation: You can use libraries like ClosedXML, EPPlus, or NPOI to create Excel files in C#. These libraries simplify the process of creating and formatting Excel files. In this article, we are going to use ClosedXML. You can install ClosedXML via NuGet Package Manager by running Install-Package ClosedXML.
  • Create an Action Method to Handle Export: In your controller, create an action method that will handle the Excel file creation and download.
  • Generate the Excel File: Use ClosedXML to create an Excel workbook and worksheet. Populate the worksheet with your data.
  • Return the Excel File as a Response: Convert the Excel workbook to a memory stream. Return the file as a FileResult in your action method.
Install the Necessary NuGet Package:

To work with Excel files, you can use the ClosedXML library. It’s a .NET library that makes it easier to create, read, and edit Excel files. To install it, use the NuGet Package Manager and search for ClosedXML. Install it in your ASP.NET Core MVC project.

Install the Necessary NuGet Package

Create a Model:

Define a model that represents the data you want to export. So, add a class file named Employee.cs within the Models folder and then copy and paste the following code.

namespace FileUploadInMVC.Models
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Departmet { get; set; }
        public long Salary { get; set; }
        public string Position { get; set; }
        public DateTime DateOfJoining { get; set; }
    }
}

Update the Context Class:

Now, we need to generate a database table for the Employee model. So, modify the EFCoreDbContext class as follows:

using Microsoft.EntityFrameworkCore;

namespace FileUploadInMVC.Models
{
    public class EFCoreDbContext : DbContext
    {
        public EFCoreDbContext() : base()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=FileHandlingDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        //Adding Domain Classes as DbSet Properties
        public DbSet<FileModel> Files { get; set; }
        public DbSet<Employee> Employees { get; set; }
    }
}
Updating the Database:

Open Package Manager Console and Execute the following add-migration and update-database commands. You can give any name to your migration. Here, I am giving Mig3. The name that you are giving it should not be given earlier.

Updating the Database

Now, the table should be created in the database with the following structure:

Export Data to an Excel File in ASP.NET Core MVC

Create a Method to Generate the Excel File:

You can create a method that takes a list of your models (e.g., a list of Employees) and returns a MemoryStream object containing the Excel file. So, create a class file named ExcelFileHandling.cs and then copy and paste the following code. The following example code is self-explained, so please go through the comment lines.

using ClosedXML.Excel;
namespace FileUploadInMVC.Models
{
    public class ExcelFileHandling
    {
        //This Method will Create an Excel Sheet and Store it in the Memory Stream Object
        //And return thar Memory Stream Object
        public MemoryStream CreateExcelFile(List<Employee> employees)
        {
            //Create an Instance of Workbook, i.e., Creates a new Excel workbook
            var workbook = new XLWorkbook();

            //Add a Worksheets with the workbook
            //Worksheets name is Employees
            IXLWorksheet worksheet = workbook.Worksheets.Add("Employees");

            //Create the Cell
            //First Row is going to be Header Row
            worksheet.Cell(1, 1).Value = "ID"; //First Row and First Column
            worksheet.Cell(1, 2).Value = "Name"; //First Row and Second Column
            worksheet.Cell(1, 3).Value = "Departmet"; //First Row and Third Column
            worksheet.Cell(1, 4).Value = "Salary"; //First Row and Fourth Column
            worksheet.Cell(1, 5).Value = "Position"; //First Row and Fifth Column
            worksheet.Cell(1, 6).Value = "Date of Joining"; //First Row and Sixth Column

            //Data is going to stored from Row 2
            int row = 2;

            //Loop Through Each Employees and Populate the worksheet
            //For Each Employee increase row by 1
            foreach (var emp in employees)
            {
                worksheet.Cell(row, 1).Value = emp.Id;
                worksheet.Cell(row, 2).Value = emp.Name;
                worksheet.Cell(row, 3).Value = emp.Departmet;
                worksheet.Cell(row, 4).Value = emp.Salary;
                worksheet.Cell(row, 5).Value = emp.Position;
                worksheet.Cell(row, 6).Value = emp.DateOfJoining;
                row++; //Increasing the Data Row by 1
            }

            //Create an Memory Stream Object
            var stream = new MemoryStream();

            //Saves the current workbook to the Memory Stream Object.
            workbook.SaveAs(stream);

            //The Position property gets or sets the current position within the stream.
            //This is the next position a read, write, or seek operation will occur from.
            stream.Position = 0;

            return stream;
        }
    }
}
Create a Controller Action to Download the File:

In your controller, create an action method that calls the above method and returns the file to the user. So, add the following action method within the FileUpload controller.

public IActionResult ExportToExcel()
{
    //Get the Employee data from the database
    EFCoreDbContext dbContext = new EFCoreDbContext();
    var employees = dbContext.Employees.ToList();

    //Create an Instance of ExcelFileHandling
    ExcelFileHandling excelFileHandling = new ExcelFileHandling();
    //Call the CreateExcelFile method by passing the list of Employee
    var stream = excelFileHandling.CreateExcelFile(employees);

    //Give a Name to your Excel File
    string excelName = $"Employees-{Guid.NewGuid()}.xlsx";

    // 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' is the MIME type for Excel files
    return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
}
Link to the Export Action from a View:

In your Razor view, you can create a link to trigger the file download. So, add the following action method within the FileUpload Controller.

public IActionResult DownloadExcel()
{
    return View();
}

Next, add a view named DownloadExcel.cshtml within the Views/FileUpload directory and then copy and paste the following code.

@{
    ViewData["Title"] = "DownloadExcel";
}

<h1>DownloadExcel</h1>
<a href="@Url.Action("ExportToExcel", "FileUpload")">Export to Excel</a>
Demo Test Data:

To test the functionality, we need some data. So, please execute the following SQL script to populate the Employees table with some test data.

INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Pranaya','IT',1000,'Manager','2014-05-25');
INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Hina','IT',1000,'Manager','2015-06-21');
INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Priyanka','HR',1000,'Tester','2016-07-22');
INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Anurag','HR',1000,'Developer','2017-08-23');
INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Sambit','HR',1000,'Developer','2018-09-24');
INSERT INTO Employees(Name, Departmet, Salary, Position, DateOfJoining)
     VALUES('Ramesh','IT',1000,'Tester','2019-10-25');

Now, if you verify the Employees table, then you will see the following data:

How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples

Now, we will export the above data to an Excel file. So, run the application and navigate to the FileUpload/DownloadExcel URL, which should display the following. You need to click on the Export to Excel link, as shown in the image below.

How to Import Excel Data to a Database in an ASP.NET Core MVC

Once you click on the link, it should download the Excel file as shown in the image below:

How do you export data to an Excel file in ASP.NET Core MVC?

If you open the Excel file, you should see the following data.

When should you use export data to an Excel file in ASP.NET Core MVC?

Points to Consider:
  • Performance: Exporting large datasets to Excel can be resource-intensive. Consider implementing measures like paging or server-side processing to handle large exports efficiently.
  • Security: Be cautious about sensitive data when exporting to Excel. Ensure that data access permissions in your application are respected in the export functionality.
  • Data Formatting: While exporting, it’s important to maintain the data’s integrity and format it correctly to match Excel’s standards (e.g., dates and numbers).
  • Dependency Management: Using third-party libraries for Excel export comes with the need to manage these dependencies and keep them updated.
When should you use export data to an Excel file in ASP.NET Core MVC?

Exporting data to an Excel file in an ASP.NET Core MVC application is a valuable feature in various scenarios. Here are some typical use cases when you might want to implement such functionality:

  • Data Reporting and Analysis: Excel is a widely used tool for data analysis. Exporting data to Excel allows users to perform further analysis, apply filters, and create charts on the data.
  • Sharing and Presentation: Excel files are easy to share and often used to present data in meetings or reports. They are a common data exchange format between departments or external stakeholders.
  • Offline Data Access: Users may need to access and review data offline or in environments where they cannot access the web application. Exporting data to Excel provides a portable format that can be used without internet connectivity.
  • Backup Purposes: Users might want to create periodic backups of certain data sets. Exporting to Excel can be a simple way to store data snapshots at various points in time.
  • Data Manipulation and Re-import: In some cases, users export data, manipulate or update it in Excel, and then re-import it into the application. This is common in scenarios where bulk updates are easier to perform in Excel.
  • Compliance and Record-Keeping: Certain industries require data to be available in specific formats for compliance and auditing purposes. Excel is a universally accepted format in many regulatory environments.

In the next article, I will discuss How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples. In this article, I try to explain How to Export Data to an Excel File in an ASP.NET Core MVC Application with Examples. I hope you enjoy this How to Export Data to Excel File in ASP.NET Core MVC article.

Leave a Reply

Your email address will not be published. Required fields are marked *