Exporting SQL Data to Excel with ASP.NET Core

In today’s digital age, efficient management of employee information is crucial for any organization. In this blog post, we will walk through the process of creating a simple ASP.NET Core API for managing employee details and exporting them to an Excel file.

Required Packages

Before we dive into the code, let’s take a quick look at the packages we’ll be using for this project:

OfficeOpenXml, Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Tools, Microsoft.EntityFrameworkCore.SqlServer.

Database Configuration

For managing employee data, we’ll use Entity Framework Core. Create an ‘EmployeeContext’ class that inherits from ‘DbContext’ to configure the database connection and define the ‘EmpDetail’ DbSet for the Employee model.

namespace EmpDetails
{
    public class EmployeeContext: DbContext
    {
        public EmployeeContext(DbContextOptions<EmployeeContext> options)
        : base(options)
        {
        }
        public DbSet<Employee> EmpDetail { get; set; } = default!;
    }
}

Employee model

Now, let’s define the Employee model, which represents our employee data structure.

public class Employee
    {
        [Key]
        public int EmpId { get; set; }
        public string? EmpName { get; set; }
        public string? Address { get; set;}
        public string? PhNo { get; set; }
        public int Salary { get; set; }
    }

Creating the API Controller

We can now proceed to create the API controller (EmpDetailController) to manage employee details. This controller will have two main actions:

Inserting Employee Details ([HttpPost(“AddEmpDetail”)]):

This action allows you to insert employee details into the database.

2. Exporting Employee Details to Excel ([HttpGet]):

This action exports employee details to an Excel file and returns it as a downloadable file.

Here’s the complete code for the EmpDetailController:

Ensure that you have the ‘OfficeOpenXml‘ library installed for Excel export.

[Route("api/[controller]")]
    [ApiController]
    public class EmpDetailController : ControllerBase
    {
        private readonly EmployeeContext _context;
        public EmpDetailController(EmployeeContext context)
        {
            _context = context;
        }
        [HttpPost("AddEmpDetail")]
        public IActionResult EnterEmpDetail(Employee emp)
        {
            var user = new Employee
            {
                EmpName = emp.EmpName,
                Address = emp.Address,
                PhNo = emp.PhNo,
                Salary= emp.Salary,
            };
            _context.EmpDetail.Add(user);
            _context.SaveChanges();
            return Ok();
        }
        [HttpGet]
        public IActionResult ExportToExcel()
        {
            var employees = _context.EmpDetail;
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Employee Details");
               
                worksheet.Cells.LoadFromCollection(employees, true);
                var totalSalaryCell = employees.Count() + 3;
                var totalSalaryColumn = 5;
                var totalSalaryFormula = $"SUM(E2:E{totalSalaryCell - 1})";
                worksheet.Cells[totalSalaryCell, 4].Value = "Total";
                worksheet.Cells[totalSalaryCell, totalSalaryColumn].Formula = totalSalaryFormula;
                using (var headerRange = worksheet.Cells[1,1,1,5])
                {
                    headerRange.Style.Font.Bold = true;
                    headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    headerRange.Style.Fill.BackgroundColor.SetColor(Color.YellowGreen);
                }
                var content = package.GetAsByteArray();
                var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                var fileName = "EmployeeDetails.xlsx";
                return File(content, contentType, fileName);
            }
        }
    }
}

[HttpPost(“AddEmpDetail”)]

This is used for inserting employee details into the database.
It accepts an Employee object (emp) as a parameter from the HTTP request body.
It creates a new Employee instance (user) with some of the properties from the incoming emp object.
It adds this new Employee instance to the _context and saves the changes to the database.
It returns an Ok response.

[HttpGet]

This retrieves a list of employee details from the _context.
It then uses the OfficeOpenXml library to create an Excel spreadsheet.
It adds a worksheet named ‘Employee Details’ and loads the employee data into it using the LoadFromCollection method.
It calculates the total salary by adding a formula in the last row of the salary column.
It applies some styling to the header row.

Finally, it converts the Excel package into a byte array, sets the content type to Excel, and returns the Excel file as a response with the filename ‘EmployeeDetails.xlsx’.

To download the file, click the “Download File” button. The Excel file will be downloaded with the data.

Inserting Employee Details:

To add a new employee record, send a POST request to EmpDetail/AddEmpDetail with the employee details in the request body.

Export to Excel:

To export employee details to an Excel file, make a GET request to /api/EmpDetail/ExportToExcel. This will generate an Excel file containing the employee details, along with a total salary calculation.