How to Export Data into Excel in ASP.NET Core

Problem Defination :- We have a requirement in which we need to Export the table data into Excel Sheet with column names as a header in excel sheet in ASP .Net Core application. For example we have a tblStudents master table and we need to export these students data into excel sheet on button export button click from GUI.

Below is the step by step process explain in details so that you can follow it and export the data into excel sheet in your application.

Step 1 :- Create Project using Microsoft Visual Studio

Select Project Type ASP.Net Core Web App(Model-View-Controller)

Write Name of Your Application Project

Choose Suitable framework version and Check the checkbox for Configure Https

Your Solution explorer looks like as in image

Step 2 :- Install the required packages from nuget package manager

Select Microsoft.EntityFrameworkCore.SqlServer with compatible version and install it.

Select Microsoft.EntityFrameworkCore.Tools with compatible version and install it.

Select Microsoft.EntityFrameworkCore with compatible version and install it.

Select ClosedXml with compatible version and install it.

You can check installed packages as shown in below image

Step 3 :- Create a Model Class Student for exporting data to excel from database.

                
 public class Student
    {
        [Key]
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public string Branch { get; set; }
        public int Fees { get; set; }
        public string Address { get; set; }
        public DateTime DateOfBirth { get; set; }
    }  

Step 4 :- Create a AppDbContext class for Dbcontext and use Student model class as a DBSet as shown in below image and code.

                    
 public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions options)
        : base(options)
        {

        }
        public DbSet tblStudents { get; set; }
    }  

Step 5 :- Define a Connection string into Appsettings.Json file as shown in below image.

Step 6 :- Add a Middileware for DBContext in Program.cs file as shown in below image.

Step 7 :- Trigger the Add-Migration "FirstMigration" Command as shown in below image.

Step 8 :- Trigger the Update-Database Command as shown in below image.

After triggering above migration commands you can see a newly generated migration folder in solution and there is a FirstMigration Class is autogenerated from migration as shown in below image.

After triggering above migration commands you can see a newly generated migration tables in databse and there are some other migration history tables are also autogenerated from migration and insert the data into tblStudentsas shown in below image

Step 9 :- Create a Class and Named it FileProcessing and inside FileProcessing class create a public Method named it CreateExcelFile and it returns the MemoryStream and take a list of student as a parameter in method as shown in below code and image.

                
   public class FileProcessing
    {
        public MemoryStream CreateExcelFile(List students)
        {
            var Excelworkbook = new XLWorkbook();
            IXLWorksheet worksheet = Excelworkbook.Worksheets.Add("Students");
            //Create Header of Excel sheet
            worksheet.Cell(1, 1).Value = "Student Id"; 
            worksheet.Cell(1, 2).Value = "Student Name"; 
            worksheet.Cell(1, 3).Value = "Branch"; 
            worksheet.Cell(1, 4).Value = "Fees"; 
            worksheet.Cell(1, 5).Value = "Address"; 
            worksheet.Cell(1, 6).Value = "Date Of Birth"; 
            int row = 2;
            foreach (var st in students)
            {
                worksheet.Cell(row, 1).Value = st.StudentId;
                worksheet.Cell(row, 2).Value = st.StudentName;
                worksheet.Cell(row, 3).Value = st.Branch;
                worksheet.Cell(row, 4).Value = st.Fees;
                worksheet.Cell(row, 5).Value = st.Address;
                worksheet.Cell(row, 6).Value = st.DateOfBirth;
                row++; 
            }
           var Memorystream = new MemoryStream();
            Excelworkbook.SaveAs(Memorystream);
            Memorystream.Position = 0;
           return Memorystream;
        }
    } 

Step 9 :- Create a Controller and Named it TestController and inside controller create two action Method named it ExportToExcel and DownloadExcel and write the code in the action methods as shown in below image and code example.

                
  public class TestController : Controller
    {
        private readonly AppDbContext db;
        public TestController(AppDbContext _db)
        {
            db = _db;
        }

       [HttpPost]
        public IActionResult ExportToExcel()
        {
         var students = db.tblStudents.ToList();
         FileProcessing excelFileProcessing = new FileProcessing();
        var stream = excelFileProcessing.CreateExcelFile(students);
        string excelSheetName = $"Students-{System.DateTime.Now}.xlsx";
        return File(stream, "application/vnd.openxmlformats-officedocument.
        spreadsheetml.sheet", excelSheetName);
        }
        [HttpGet]
        public IActionResult DownloadExcel()
        {
            return View();
        }
    } 

Step 10 :- Create a View and write the below Html code into it as shown in image.

Now run the application you will get the export to excel button as output shown in below image.

Click on the export to excel button as shown in above image it will download a excel sheet in downloads folder as shown in below image.

Open the downloaded excel sheet and you can find the table data into exported excel sheet as shown in below image.