• Blog
  • Creating Excel Reports in .NET Core with EPPlus: A Comprehensive Guide

Creating Excel Reports in .NET Core with EPPlus: A Comprehensive Guide

Explore how to create Excel reports in .NET Core with EPPlus

Publish date:
Discover more of what matters to you

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”.

Need to Update Your .NET Software?
Explore our .NET migration services.
Learn more

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.

Ready to Elevate Your Business?
At Softacom, we have over 15 years of experience in software development and modernization. Let’s work together!
Contact us

Subscribe to our newsletter and get amazing content right in your inbox.

This field is required
This field is required Invalid email address
By submitting data, I agree to the Privacy Policy

Thank you for subscribing!
See you soon... in your inbox!

confirm your subscription, make sure to check your promotions/spam folder

Tags

Subscribe to our newsletter and get amazing content right in your inbox.

You can unsubscribe from the newsletter at any time

This field is required
This field is required Invalid email address

You're almost there...

A confirmation was sent to your email

confirm your subscription, make sure to check
your promotions/spam folder