Working with Excel in C# (.NET): Read and Create XLSX |…

Working with Excel in C# (.NET): A Detailed Guide to EPPlus and ClosedXML

In modern .NET development, there is often a need to programmatically create, read, and edit Excel spreadsheets. This can include generating reports, importing data from databases, exporting statistics, or automating business processes. In this article, we will look at two of the most popular and powerful libraries for working with Excel in C#: EPPlus and ClosedXML. Both libraries work with the Open XML (XLSX) format and do not require Microsoft Office to be installed.

Why Use Libraries for Excel?

Manually creating XML files for the XLSX format is extremely labor-intensive and error-prone. Libraries like EPPlus and ClosedXML provide a high-level API that hides all the complexity of working with Open XML. They allow you to:

Create Excel files from scratch with full control over formatting.
Read data from existing tables with support for various cell types.
Edit cells, rows, columns, and sheets.
Apply styles, fonts, colors, borders, and conditional formatting.
Work with formulas, pivot tables, and charts.
Export data to PDF (available in the paid version of EPPlus).

EPPlus Library

EPPlus is one of the oldest and most feature-rich libraries for working with Excel in .NET. It supports all major features of the XLSX format, including charts, pivot tables, conditional formatting, and VBA macros. Starting from version 5, EPPlus switched to an LGPL licensing model with additional restrictions for commercial use (free for projects with annual revenue under $1 million).

Installing EPPlus

You can install the library via NuGet Package Manager:

Install-Package EPPlus

Or via .NET CLI:

dotnet add package EPPlus

Example: Creating a Simple Excel File with EPPlus

The following code demonstrates creating an Excel file with headers and data, as well as applying basic formatting:

using OfficeOpenXml;using OfficeOpenXml.Style;using System.Drawing;

public void CreateExcelWithEPPlus(){    // Setting the license context (required for EPPlus 5+)    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

    using (var package = new ExcelPackage())    {        // Add a new worksheet        var worksheet = package.Workbook.Worksheets.Add("Employees");

        // Headers        worksheet.Cells[1, 1].Value = "Name";        worksheet.Cells[1, 2].Value = "Position";        worksheet.Cells[1, 3].Value = "Salary";

        // Styling headers        using (var range = worksheet.Cells[1, 1, 1, 3])        {            range.Style.Font.Bold = true;            range.Style.Fill.PatternType = ExcelFillStyle.Solid;            range.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;        }

        // Data        worksheet.Cells[2, 1].Value = "Ivan Ivanov";        worksheet.Cells[2, 2].Value = "Developer";        worksheet.Cells[2, 3].Value = 150000;

        worksheet.Cells[3, 1].Value = "Maria Petrova";        worksheet.Cells[3, 2].Value = "Analyst";        worksheet.Cells[3, 3].Value = 120000;

        // Number formatting        worksheet.Cells[2, 3, 3, 3].Style.Numberformat.Format = "#,##0";

        // Auto-fit columns        worksheet.Cells[1, 1, 3, 3].AutoFitColumns();

        // Save the file        var fileInfo = new FileInfo("employees.xlsx");        package.SaveAs(fileInfo);    }}