Creating Excel Reports with EPPlus in ASP.NET Core 8.0

Introduction

In modern web development, generating and managing Excel files directly within your applications can significantly boost data handling and reporting. This guide will show you how to integrate EPPlus with ASP.NET Core 8.0 to create, customize, and export Excel files effortlessly. Learn to automate report generation, manage data exports, and enhance your application's reporting features with practical, step-by-step examples. Whether you're developing financial reports or data dashboards, this tutorial will help you leverage EPPlus to streamline your Excel-related tasks.

Prerequisites

Visual Studio
.NET 8 SDK
EPPlus Library

Installing EPPlus Library for ASP.NET Core 8.0

To install the EPPlus package using the .NET CLI, you can use the following command.

dotnet add package EPPlus

Let's Start Implementation

Create the ReportData Class
Right-click on the Models folder in your project.
Select Add > Class.
Name the class ReportData.cs and click Add.

namespace ReportGenerationInExcel.Models
{
    public class ReportData
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string PhoneNumber { get; set; }
    }
}

Add a New Controller
- Right-click on the Controllers folder in Solution Explorer.
- Select Add > Controller.
- Select API > Controller.
Name Your Controller
- Enter a name for your controller, such as ReportsGenerationController.
- Click Add.
Add Mock Data
- Add mock data to your controller to test the Excel generation. Here's an example snippet to include in your controller action.

var data = new List<ReportData>
{
    new ReportData 
    { 
        Id = 1, 
        Name = "Amit Kumar", 
        Age = 32, 
        Email = "[email protected]", 
        Address = "12/34, Sector 15, Noida, Uttar Pradesh", 
        PhoneNumber = "97123-456780" 
    },
    new ReportData 
    { 
        Id = 2, 
        Name = "Sneha Patel", 
        Age = 29, 
        Email = "[email protected]", 
        Address = "56, Gali No. 7, Delhi", 
        PhoneNumber = "97765-432100" 
    },
    new ReportData 
    { 
        Id = 3, 
        Name = "Rajesh Sharma", 
        Age = 45, 
        Email = "[email protected]", 
        Address = "78, Green Park, Mumbai, Maharashtra", 
        PhoneNumber = "97234-567890" 
    },
    new ReportData 
    { 
        Id = 4, 
        Name = "Mohammed Ali", 
        Age = 40, 
        Email = "[email protected]", 
        Address = "22/45, Jamia Nagar, Delhi", 
        PhoneNumber = "97123-876540" 
    },
    new ReportData 
    { 
        Id = 5, 
        Name = "Tahir Ansari", 
        Age = 35, 
        Email = "[email protected]", 
        Address = "8-9, Park Road, Bangalore, Karnataka", 
        PhoneNumber = "97001-234560" 
    },
    new ReportData 
    { 
        Id = 6, 
        Name = "Imran Sheikh", 
        Age = 28, 
        Email = "[email protected]", 
        Address = "10/12, Saki Naka, Mumbai, Maharashtra", 
        PhoneNumber = "97234-567890" 
    }
};

Note. Ensure to NonCommercial.

ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // LicenseContext.NonCommercial

Initialize ExcelPackage to create a new ExcelPackage instance for managing and manipulating Excel files.

  using var package = new ExcelPackage();
  var worksheet = package.Workbook.Worksheets.Add("Sheet1");

Set Column headers

worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 3].Value = "Age";
worksheet.Cells[1, 4].Value = "Email";
worksheet.Cells[1, 5].Value = "Address";
worksheet.Cells[1, 6].Value = "PhoneNumber";

Code Sample

using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using ReportGenerationInExcel.Models;

namespace ReportGenerationInExcel.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ReportsGenerationController : ControllerBase
    {
        [HttpGet("generate-report")]
        public async Task<IActionResult> GenerateExcel()
        {
            var data = new List<ReportData>
            {
                new ReportData 
                { 
                    Id = 1, 
                    Name = "Amit Kumar", 
                    Age = 32, 
                    Email = "[email protected]", 
                    Address = "12/34, Sector 15, Noida, Uttar Pradesh", 
                    PhoneNumber = "97123-456780" 
                },
                new ReportData 
                { 
                    Id = 2, 
                    Name = "Sneha Patel", 
                    Age = 29, 
                    Email = "[email protected]", 
                    Address = "56, Gali No. 7, Delhi", 
                    PhoneNumber = "97765-432100" 
                },
                new ReportData 
                { 
                    Id = 3, 
                    Name = "Rajesh Sharma", 
                    Age = 45, 
                    Email = "[email protected]", 
                    Address = "78, Green Park, Mumbai, Maharashtra", 
                    PhoneNumber = "97234-567890" 
                },
                new ReportData 
                { 
                    Id = 4, 
                    Name = "Mohammed Ali", 
                    Age = 40, 
                    Email = "[email protected]", 
                    Address = "22/45, Jamia Nagar, Delhi", 
                    PhoneNumber = "97123-876540" 
                },
                new ReportData 
                { 
                    Id = 5, 
                    Name = "Tahir Ansari", 
                    Age = 35, 
                    Email = "[email protected]", 
                    Address = "8-9, Park Road, Bangalore, Karnataka", 
                    PhoneNumber = "97001-234560" 
                },
                new ReportData 
                { 
                    Id = 6, 
                    Name = "Imran Sheikh", 
                    Age = 28, 
                    Email = "[email protected]", 
                    Address = "10/12, Saki Naka, Mumbai, Maharashtra", 
                    PhoneNumber = "97234-567890" 
                }
            };

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");

            worksheet.Cells[1, 1].Value = "ID";
            worksheet.Cells[1, 2].Value = "Name";
            worksheet.Cells[1, 3].Value = "Age";
            worksheet.Cells[1, 4].Value = "Email";
            worksheet.Cells[1, 5].Value = "Address";
            worksheet.Cells[1, 6].Value = "PhoneNumber";

            for (int i = 0; i < data.Count; i++)
            {
                worksheet.Cells[i + 2, 1].Value = data[i].Id;
                worksheet.Cells[i + 2, 2].Value = data[i].Name;
                worksheet.Cells[i + 2, 3].Value = data[i].Age;
                worksheet.Cells[i + 2, 4].Value = data[i].Email;
                worksheet.Cells[i + 2, 5].Value = data[i].Address;
                worksheet.Cells[i + 2, 6].Value = data[i].PhoneNumber;
            }

            var stream = new MemoryStream();
            await package.SaveAsAsync(stream);
            stream.Position = 0;
            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
        }
    }
}

Swagger UI

Conclusion

This article demonstrated how to use EPPlus in an ASP.NET Core 8.0 application to generate Excel files. We covered creating the ReportData model, populating it with data, and implementing a controller to create and serve Excel reports. This setup enables efficient data reporting and integration within your API.

Happy Coding!