EPPlus is a powerful and easy-to-use .NET library for reading, creating, and modifying Excel .xlsx files (Office Open XML format).
It provides an API similar to working with Excel itself — with support for formulas, charts, tables, styles, data validation, pivot tables, and more — all without needing Excel installed.
It’s ideal for:
- Generating Excel reports dynamically
- Importing or exporting data from databases
- Creating dashboards or analytics reports
- Building REST APIs that return Excel files
License Information
EPPlus (version 5 and newer) is released under the Polyform NonCommercial License 1.0.0.
That means:
- Free for non-commercial or educational projects
- Requires a paid license for commercial or business use
In non-commercial projects, you must set the license context before using it:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
Installation
Install via NuGet:
dotnet add package EPPlus
Basic Usage Example
Example: Creating a Simple Excel File
using OfficeOpenXml;
using System.IO;
class Program
{
static void Main()
{
// Required for non-commercial use
OfficeOpenXml.ExcelPackage.License.SetNonCommercialPersonal("vahid arya");
//for older version
//ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
// Create a new Excel package (in memory)
using (var package = new ExcelPackage())
{
// Add a new worksheet
var worksheet = package.Workbook.Worksheets.Add("Report");
// Write some data
worksheet.Cells["A1"].Value = "Name";
worksheet.Cells["B1"].Value = "Age";
worksheet.Cells["A2"].Value = "John";
worksheet.Cells["B2"].Value = 30;
worksheet.Cells["A3"].Value = "Mary";
worksheet.Cells["B3"].Value = 25;
// Apply simple formatting
worksheet.Cells["A1:B1"].Style.Font.Bold = true;
worksheet.Cells["A:B"].AutoFitColumns();
// Save file to disk
var file = new FileInfo("PeopleReport.xlsx");
package.SaveAs(file);
}
Console.WriteLine("Excel file generated successfully!");
}
}This creates a file called PeopleReport.xlsx in your project folder.
1-Working with Worksheets
EPPlus lets you easily manage worksheets in a workbook.
// Add a new sheet
var ws = package.Workbook.Worksheets.Add("Summary");
// Rename
ws.Name = "Data Summary";
// Remove
package.Workbook.Worksheets.Delete("Sheet1");
// Access by name or index
var firstSheet = package.Workbook.Worksheets[0];
var namedSheet = package.Workbook.Worksheets["Data Summary"];
2-Reading and Writing Cells
You can access cells either by address ("A1") or row/column index.
// Write data
ws.Cells["A1"].Value = "Title";
ws.Cells[2, 1].Value = "Hello";
ws.Cells[2, 2].Value = "World";
// Read data
string value = ws.Cells["A1"].Text;
double number = ws.Cells[2, 2].GetValue<double>();
You can also merge cells:
ws.Cells["A1:D1"].Merge = true;
ws.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
3-Styling and Formatting
EPPlus provides fine control over cell appearance.
var header = ws.Cells["A1:B1"];
header.Style.Font.Bold = true;
header.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
header.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
//Common formatting examples:
// Set number format
ws.Cells["B2:B10"].Style.Numberformat.Format = "#,##0.00";
// Date format
ws.Cells["C2:C10"].Style.Numberformat.Format = "yyyy-mm-dd";
// Align text
ws.Cells["A:A"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
4-Adding Tables and Charts
EPPlus supports Excel tables and charts for data visualization.
Example: Creating a Table
var dataRange = ws.Cells["A1:B3"];
var table = ws.Tables.Add(dataRange, "PeopleTable");
table.TableStyle = OfficeOpenXml.Table.TableStyles.Medium9;
Example: Adding a Chart
var chart = ws.Drawings.AddChart("chart1", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
chart.Title.Text = "Age Chart";
chart.SetPosition(5, 0, 2, 0);
chart.SetSize(400, 300);
chart.Series.Add("B2:B3", "A2:A3");
5-Formulas
EPPlus supports Excel-compatible formulas:
ws.Cells["C1"].Value = "Total";
ws.Cells["C2"].Formula = "B2 * 2";
ws.Cells["C3"].Formula = "B3 * 2";
ws.Calculate(); // Optional: force calculation in .NET
6-Data Validation (Dropdowns, Ranges, Dates)
// Dropdown list
var validation = ws.DataValidations.AddListValidation("A2:A10");
validation.Formula.Values.Add("Option 1");
validation.Formula.Values.Add("Option 2");
validation.ShowErrorMessage = true;
validation.ErrorTitle = "Invalid selection";
validation.Error = "Please choose one of the listed options.";
7-Loading and Reading Existing Files
using (var package = new ExcelPackage(new FileInfo("PeopleReport.xlsx")))
{
var ws = package.Workbook.Worksheets["Report"];
string name = ws.Cells["A2"].Text;
int age = ws.Cells["B2"].GetValue<int>();
Console.WriteLine($"{name} is {age} years old");
}
You can also load from a stream (useful in web APIs):
using var stream = new MemoryStream(fileBytes);
using var package = new ExcelPackage(stream);
8-Saving to Memory (Useful for APIs)
Example: returning an Excel file from an ASP.NET Core controller:
[HttpGet("download")]
public IActionResult DownloadExcel()
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Data");
ws.Cells["A1"].Value = "Hello EPPlus";
var bytes = package.GetAsByteArray();
return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report.xlsx");
}
9-AutoFit and Page Setup
ws.Cells.AutoFitColumns();
// Set print area and orientation
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.PaperSize = ePaperSize.A4;
ws.PrinterSettings.FitToPage = true;
10-Performance Tips
- Reuse worksheet objects instead of re-accessing cells repeatedly.
- Write large datasets using loops and avoid style changes per cell.
- For big exports, write data in bulk using LoadFromCollection() or LoadFromDataTable().
Example:
ws.Cells["A1"].LoadFromCollection(peopleList, true);
