Export Data Into A Predefined Excel Template In .NET Using EPPlus

This article demonstrates how easily we can export data into a predefined template in a .NET application. Here we will use the EPPlus NuGet package to achieve this. Most of the excel properties including cell ranges, cell styling of the border, color, fill, font, number, alignments, etc well supported and it’s kind of plug and play.

For demonstration purposes, we will do a simple .net project with dummy data and we will create an XLSX template where data will be exported.

 Let’s follow step by step approach,

Step 1 – Create a Simple .Net application

Create a simple MVC web application in .NET 5 and a model class with relevant properties. To make it simple, we will manually add some dummy data into a list and will pass that model object to view. In my case, I created a model called “Certification” and passing that into view.

Models/Certification.cs

public class Certification {
    public string EmployeeName {
        get;
        set;
    }
    public string CertificationName {
        get;
        set;
    }
    public string CertificationCode {
        get;
        set;
    }
    public string CertificateIssuingAuthority {
        get;
        set;
    }
}

Controllers/HomeController.cs

public IActionResult Index() {
    List < Certification > cList = new List < Certification > {
        new Certification {
            EmployeeName = "Anupam Maiti", CertificationName = "AWS Certified Cloud Practitioner", CertificationCode = "CLF-C01", CertificateIssuingAuthority = "AWS"
        },
        new Certification {
            EmployeeName = "Anupam Maiti", CertificationName = "Microsoft Certified: DevOps Engineer Expert", CertificationCode = "AZ-400", CertificateIssuingAuthority = "Microsoft"
        },
        new Certification {
            EmployeeName = "Anupam Maiti", CertificationName = "Microsoft Certified: Azure Developer Associate", CertificationCode = "AZ-204", CertificateIssuingAuthority = "Microsoft"
        }
    };
    HttpContext.Session.SetObjectAsJson("CertificationList", cList);
    return View(cList);
}

Views/Home/Index.cshtml

@model List<Certification>
@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    <h3>Export Data Into A Predefined Excel Template In .NET Using EPPlus</h3>
    <div >
        <a id="downloadXlsxReport" style="cursor:pointer; padding-left:95%; padding-bottom:10px" float:right; asp-controller="Home" asp-action="DownloadXlsxReport"><img src="~/images/Excel_download_icon.png" /></a>
    </div>
    <table class="table table-bordered table-striped">
        <thead style="background-color:aquamarine">
            <tr>
                <th class="">
                    Employee Name
                </th>
                <th class="">
                    Certification Name
                </th>
                <th class="">
                    Certification Code
                </th>
                <th class="">
                    Certificate Issuing Authority
                </th>
            </tr>
        </thead>
        <tbody class="userTbody">
            @foreach (var item in Model)
            {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeName)
                </td>
                <td class="">
                    @Html.DisplayFor(modelItem => item.CertificationName)
                </td>
                <td class="">
                    @Html.DisplayFor(modelItem => item.CertificationCode)
                </td>
                <td class="">
                    @Html.DisplayFor(modelItem => item.CertificateIssuingAuthority)
                </td>
            </tr>
            }
        </tbody>
    </table>
</div>

Overall project folder structure shown below,

Step 2 – Install EPPlus NuGet package

Let’s install EPPlus NuGet Package into the project. Here we will use version 4.5.2.1

Step 3 - Create an XLSX template and Add into Project

Let’s create an excel template with the format, color, etc., and add into the project. Here I created the "Template" folder and added a template file into it. Make sure CopyToOutputDirectory is set to Always in project properties.

Step 4 – Create a Helper class for Export to Excel

Now we are ready for relevant changes for export functionality. Let’s create a Utilities class called “ExportToExcelHelper” and include the below changes.

public static class ExportToExcelHelper {
    public static Stream UpdateDataIntoExcelTemplate(List < Certification > cList, FileInfo path) {
        Stream stream = new MemoryStream();
        if (path.Exists) {
            using(ExcelPackage p = new ExcelPackage(path)) {
                ExcelWorksheet wsEstimate = p.Workbook.Worksheets["Certifications"];
                wsEstimate.Cells["B5:E8"].LoadFromCollection(cList);
                p.SaveAs(stream);
                stream.Position = 0;
            }
        }
        return stream;
    }
}

Let’s create an action method and call helper class.

public IActionResult DownloadXlsxReport() {
    var cList = HttpContext.Session.GetObjectFromJson < List < Certification >> ("CertificationList");
    string timestamp = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture).ToUpper().Replace(':', '_').Replace('.', '_').Replace(' ', '_').Trim();
    var templateFileInfo = new FileInfo(Path.Combine(_hostingEnvironment.ContentRootPath, "Template", "CertificationsExportTemplate.xlsx"));
    var stream = ExportToExcelHelper.UpdateDataIntoExcelTemplate(cList, templateFileInfo);
    return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CertificationsReport-" + timestamp + ".xlsx");
}

Make sure we call this action method from view.

<div >
    <a id="downloadXlsxReport" style="cursor:pointer; padding-left:95%; padding-bottom:10px" float:right; asp-controller="Home" asp-action="DownloadXlsxReport"><img src="~/images/Excel_download_icon.png" /></a>
</div>

Step 5 – Test XLSX Export functionality

Awesome! Now we are ready to test the functionality. Let’s run the application. Once the application is loaded, we will see data is populated into the table with an export excel option on the top right of the table.

Now click on the Export option, we will see an excel file downloaded….

Once we open the file… we will see all the relevant content is updated in a respective cell in excel.

Excellent! we can see that without writing much code, we can easily achieve export to excel functionality in a .net application using EPPlus. Please find the attachment for the project that is demonstrated here.

Please do like, comment, and share if you found this article useful.

Happy Learning!