How to Export Data to Excel in ASP.NET Core

This article explains how to export data to excel using the EPPlus package with examples in simple steps.

Table of Contents

Getting Started
Project structure
Installing NuGet Package Microsoft.EntityFrameworkCore
Installing NuGet Package Microsoft.EntityFrameworkCore.SqlServer
Installing NuGet Package EPPlus
Setting License Property of EPPlus Via code
Configuring Connection string in appsettings.json
appsettings.json file
Adding DbContext
Added UserMasterModel
Adding Dbset to DbContext
Registering AddDbContext in ConfigureServices Method
Adding Interface and Concrete Class
Adding IReporting Interface
Adding ReportingConcrete Class
Registering Service with DI Container
Adding Controller
Adding Method Private Method ExporttoExcel
Adding ExporttoExcel Feature in Reporting Controller
Adding View DownloadReport
Run and Test
Output

Getting Started
We are going to create a new application, as shown below.

Next, we are going to set Project Name DemoExportApp and location. In the last part, we will choose the .Net Core framework and ASP.NET Core Version 5.0 as the framework for application and a few advanced settings for such as configuring HTTPS and enabling docker; we are not going to enable docker settings for this project.

Now finally, click on create button to create a project.

Project structure

The project structure is generated according to the configuration.

After creating the project next, we are going to install NuGet Packages.

Installing NuGet Package

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
EPPlus

Installing Microsoft.EntityFrameworkCore

Installing Microsoft.EntityFrameworkCore.SqlServer

Installing EPPlus Package from NuGet Package

Next after installing the EPPlus package next, we need to set the license parameter.

There are 2 types of licenses

Commercial
Non-Commercial

For this demo, we are going to use a Non-Commercial License if you want a commercial License then click on https://www.epplussoftware.com/en/LicenseOverview/.

We are going to add this Property Via appSettings.json

"EPPlus": {
    "ExcelPackage": {
      "LicenseContext": "NonCommercial" //The license context used
    }
  }

Setting License Property Via code

// If you are a commercial business and have
// purchased commercial licenses use the static property
// LicenseContext of the ExcelPackage class :
ExcelPackage.LicenseContext = LicenseContext.Commercial;

// If you use EPPlus in a noncommercial context
// according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
After setting license Property next we need to get data from the database to export for that we are going to configure the connection string in the appsettings.json file.

Configuring Connection string in appsettings.json

Here in ASP.NET core, we don’t have a web.config file which was there in the older version of ASP.NET and ASP.NET MVC here we have an appsettings.json file where we are going to keep application-level settings, and this file is Json based.

appsettings.json file

In this file, we have added the ConnectionStrings section inside that we have added DatabaseConnection key and other is value.

Notice: – here I am using SQL based authentication for database connection that why I have added the User Id and password of the SQL server.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "EPPlus": {
    "ExcelPackage": {
      "LicenseContext": "NonCommercial" //The license context used
    }
  },
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=DESKTOP-DUM6D15\\MSSQLSERVERSTD; initial catalog=OneFitnessVueDB; user id=sa; password=Pass$123"
  },
  "AllowedHosts": "*"
}

Adding DbContext

using Microsoft.EntityFrameworkCore;

namespace DemoExportApp.EFContext
{
    public class DatabaseContext: DbContext
    {
        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
        {

        }
    }
}

Added UserMasterModel

In this part, we are going to add Model UserMaster.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DemoExportApp.Models
{
    [Table("Usermaster")]
    public class UserMasterModel
    {
        [Key]
        public int UserId { get; set; }
        public string UserName { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailId { get; set; }
        public string MobileNo { get; set; }
        public string Gender { get; set; }
        public bool Status { get; set; }
        public string PasswordHash { get; set; }
        public bool IsFirstLogin { get; set; } = false;
        public DateTime IsFirstLoginDate { get; set; }
        public DateTime? CreatedOn { get; set; } = DateTime.Now;
        public DateTime? ModifiedOn { get; set; }
        public int? CreatedBy { get; set; }
        public int? ModifiedBy { get; set; }
        public int DesignationId { get; set; }
    }
}

Adding Dbset to DbContext

DbSet corresponds to a table or view in your database.

using DemoExportApp.Models;
using Microsoft.EntityFrameworkCore;

namespace DemoExportApp.EFContext
{
    public class DatabaseContext : DbContext
    {
        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
        {
        }
        public DbSet<UserMasterModel> UserMasters { get; set; }
    }
}

Registering AddDbContext in ConfigureServices Method

public void ConfigureServices(IServiceCollection services)
  {
      var connection = Configuration.GetConnectionString("DatabaseConnection");
      services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection));
      services.AddControllersWithViews();
  }

After Registering next, we are going to add a Repository Folder.

Adding Interface and Concrete Class

In this part, we are adding Interface with Name IReporting and Concrete Class with Name ReportingConcrete.

ReportingConcrete class will inherit IReporting Interface. In ReportingConcrete class we are going to write a method for getting data from a database which we are going to pass to the excel library to export as Excel.

Same time we need to Add ViewModel with Name UserViewModel where we are going to Add only that property which we are going to export.

using System;

namespace DemoExportApp.Models
{
    public class UserMasterViewModel
    {
        public string UserName { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailId { get; set; }
        public string MobileNo { get; set; }
        public string Gender { get; set; }
        public DateTime? CreatedOn { get; set; }
        public string Status { get; set; }
    }
}

Adding IReporting Interface

we have added the IReporting interface and declared GetUserwiseReport method init.

using DemoExportApp.Models;
using System.Collections.Generic;

namespace DemoExportApp.Repository
{
    public interface IReporting
    {
        List<UserMasterViewModel> GetUserwiseReport();
    }
}

Adding ReportingConcrete Class

Here we are going to Add Class ReportingConcrete Class which will inherit the IReporting interface and implement GetUserwiseReport Methods in it.

using DemoExportApp.EFContext;
using DemoExportApp.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace DemoExportApp.Repository
{
    public class ReportingConcrete : IReporting
    {
        private readonly DatabaseContext _databaseContext;
        private readonly IConfiguration _configuration;
        public ReportingConcrete(DatabaseContext databaseContext, IConfiguration configuration)
        {
            _databaseContext = databaseContext;
            _configuration = configuration;
        }
        public List<UserMasterViewModel> GetUserwiseReport()
        {
            try
            {
                var listofusers = (from usermaster in _databaseContext.UserMasters.AsNoTracking()
                                   select new UserMasterViewModel()
                                   {
                                       UserName = usermaster.UserName,
                                       FirstName = usermaster.FirstName,
                                       LastName = usermaster.LastName,
                                       CreatedOn = usermaster.CreatedOn,
                                       EmailId = usermaster.EmailId,
                                       Gender = usermaster.Gender == "M" ? "Male" : "Female",
                                       MobileNo = usermaster.MobileNo,
                                       Status = usermaster.Status == true ? "Active" : "InActive",

                                   }).ToList();

                return listofusers;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

After Adding the Interface and Concrete class next we are going to register them in ConfigureServices Method.

Registering Service with DI Container

public void ConfigureServices(IServiceCollection services)
{
    var connection = Configuration.GetConnectionString("DatabaseConnection");
    services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection));
    services.AddTransient<IReporting, ReportingConcrete>();
    services.AddControllersWithViews();
}

Adding Controller

We are going to add a controller with the name Report. In this controller, we are going to add the Action method which will be used to export Excel.

After adding the controller next, we are going to add 2 Action methods with the name “DownloadReport”.

using DemoExportApp.Repository;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using System.Collections.Generic;

namespace DemoExportApp.Controllers
{
    public class ReportController : Controller
    {
        readonly IReporting IReporting;

        public ReportController(IReporting iReporting)
        {
            IReporting = iReporting;
        }


        [HttpGet]
        public IActionResult DownloadReport()
        {
            return View();
        }

        [HttpPost]
        public IActionResult DownloadReport(IFormCollection obj)
        {
            return View();
        }

    }
}

Adding Method Private Method ExporttoExcel

ExporttoExcel Method will take 2 input List of Collection (List<UserMasterViewModel>) and filename and it will return Bytes.

 private byte[] ExporttoExcel<T>(List<T> table, string filename)
 {
     using ExcelPackage pack = new ExcelPackage();
     ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
     ws.Cells["A1"].LoadFromCollection(table, true, TableStyles.Light1);
     return pack.GetAsByteArray();
 }

Adding ExporttoExcel Feature in Reporting Controller

In Reporting Controller, we are going to use constructor injection to inject IReporting Interface.

Using IReporting we can access the GetUserwiseReport method which will return the List of Usermaster which we are going to pass to the ExporttoExcel Method along with filename which will return us bytes that bytes we are going to pass to FileContentResult and other 2 parameters such as content type, file download name.

File(exportbytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", reportname);

using DemoExportApp.Models;
using DemoExportApp.Repository;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using System;
using System.Collections.Generic;

namespace DemoExportApp.Controllers
{
    public class ReportController : Controller
    {
        readonly IReporting _IReporting;

        public ReportController(IReporting iReporting)
        {
            _IReporting = iReporting;
        }


        [HttpGet]
        public IActionResult DownloadReport()
        {
            return View();
        }

        [HttpPost]
        public IActionResult DownloadReport(IFormCollection obj)
        {
            string reportname = $"User_Wise_{Guid.NewGuid():N}.xlsx";
            var list = _IReporting.GetUserwiseReport();
            if (list.Count > 0)
            {
                var exportbytes = ExporttoExcel<UserMasterViewModel>(list, reportname);
                return File(exportbytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", reportname);
            }
            else
            {
                TempData["Message"] = "No Data to Export";
                return View();
            }
        }

        private byte[] ExporttoExcel<T>(List<T> table, string filename)
        {
            using ExcelPackage pack = new ExcelPackage();
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
            ws.Cells["A1"].LoadFromCollection(table, true, TableStyles.Light1);
            return pack.GetAsByteArray();
        }
    }
}

Adding View DownloadReport

In this part, we are going to add View and on View, we are going to add a Form tag with submit button in it. On click on Submit button, it will call DownloadReport Post Method which will export data to excel.


@{
    ViewData["Title"] = "Export Excel";
}


<div class="col-md-12">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Download Report</h3>
        </div>
        <form autocomplete="off" asp-controller="Report" asp-antiforgery="true" asp-action="DownloadReport" method="post">
            <div class="card-body register-card-body">
                <div class="form-row">
                    <button type="submit" id="btnsubmit" class="btn btn-success">Export to Excel</button>
                </div>

            </div>
            <div class="card-footer">
            </div>
        </form>
    </div>
</div>

Lets Run and Test

Output

Summary

We have learned how to export data to excel in ASP.NET Core using the EPPlus package in simple steps.