Versions used: .net 8.0, EPPlus 7.3.2 (Polyform noncomercial license 1.0)
In this article, we will explore the process of generating dynamic Excel reports using the EPPlus library in a .NET Core environment. EPPlus is a powerful and popular library that allows developers to create, edit, and manipulate Excel workbooks programmatically. With its extensive features, EPPlus simplifies the task of generating professional-looking reports and provides an efficient way to present data.
General Note about Office automation
As a .NET developer, there are several options for automating actions on an Excel workbook, each with different dependencies and platform compatibility. If you need to use Excel’s full functionality and are on .NET Framework, you can use the Microsoft Office Interop library, which requires Excel to be installed and only works with .NET Framework (not .NET Core). Another option is the Open XML SDK from Microsoft, which works for both .NET Framework and .NET Core (including .NET Core 8) and does not require Excel to be installed; this SDK allows for creating, reading, and modifying Excel files at a lower level by directly manipulating the Open XML file format, though without Excel-specific formulas and macros. ClosedXML is a popular open-source library that wraps the Open XML SDK and provides a more user-friendly API, also compatible with both .NET Framework and .NET Core, and does not require Excel. For more advanced scenarios, like handling formulas and complex formatting, EPPlus – which we cover here – is a versatile, Excel-like API that works with .NET Framework and .NET Core and doesn’t require Excel.
Getting Started with EPPlus
To begin, you’ll need to install the EPPlus library in your .NET Core project (this assumes you have a csproj already open). You can do this by opening your project’s terminal or package manager and running the following command:
This will add the EPPlus package to your project’s dependencies.
Creating an Excel Workbook
Let’s start by creating a simple Excel workbook with a single worksheet. The following code snippet demonstrates how to initialize an Excel package and add a worksheet:
In this example, we create an instance of ExcelPackage and add a worksheet named “Sample Sheet” using the Add method. We then set some basic properties for the worksheet, such as displaying grid lines and headings. Finally, we save the Excel file with the name “SampleReport.xlsx”.
Adding Data to the Worksheet
Now, let’s populate our worksheet with some data. EPPlus provides a straightforward way to add cells, rows, and columns to a worksheet. Here’s an example of adding data to our “Sample Sheet”:
In this code snippet, we set the values of specific cells in the worksheet. The first row represents the column headers, and the second row contains sample data. You can continue adding data to subsequent rows as needed. Note that you can use two different techniques to add data to Excel cells.
Formatting Cells and Worksheets
EPPlus offers a wide range of formatting options to enhance the appearance of your Excel reports. Here are some examples of common formatting tasks:
Setting Cell Styles
Insert the following code anywhere before the call to SaveAs():
In this example, we set the font style of the header row to bold and apply a light gray background color to it. You can explore various style properties to achieve the desired formatting.
Adding Conditional Formatting
Here, we add a conditional formatting rule that highlights cells in the second row with a value greater than or equal to 30 in red font color.
Handling Large Datasets
When working with large datasets, it’s essential to optimize the performance of your Excel report generation. EPPlus provides efficient methods for handling large amounts of data. Here’s an example of using the Cells.Load method to load data from a collection:
By using the LoadFromCollections method, you can efficiently load data from a collection into the worksheet, reducing the need for manual cell assignments.
Full Code + Output
Here’s the full code:
And here is the output:
Conclusion
EPPlus offers a comprehensive set of tools for generating Excel reports in .NET Core applications. With its intuitive API and extensive features, you can create professional-looking reports with ease. This article provided a glimpse into the capabilities of EPPlus, and you can further explore its documentation to unlock more advanced functionalities.
