Getting Started

So how do I start?

Installation

EPPlus is distributed via Microsofts package manager Nuget. We refer to Microsoft's official documentation for detailed descriptions on how to get started/use Nuget. Here is a link to the EPPlus Nuget package.

For supported .NET frameworks, see this link.

Set the LicenseContext

Before you start to code against EPPlus you need to set the static ExcelPackage.LicenseContext property. This can have two different values: NonCommercial if you use EPPlus for noncommercial purposes (see the Polyform Noncommercial 1.0 license) or Commercial if you have a commercial license. If the LicenseContext is not set EPPlus will throw a LicenseException, this happens only when a debugger is attached.
Commercial licenses can be purchased at our website: https://epplussoftware.com.
For more details on how to configure EPPlus see our Configuration wiki page.

Here are the options for how you can set the license context.

1. Via code

using OfficeOpenXml;
// if you have a commercial license
ExcelPackage.LicenseContext = LicenseContext.Commercial;
// if you are using epplus for noncommercial purposes, see https://polyformproject.org/licenses/noncommercial/1.0.0/
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

2. Via appSettings.json

{
    {
    "EPPlus": {
        "ExcelPackage": {
            "LicenseContext": "Commercial"
            }
        }
    }
}

3. Via app/web.config

For environments where System.Configuration.ConfigurationManager.Appsettings is supported.

<appSettings>
    <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />
</appSettings>

4. Via environment variable EPPlusLicenseContext

Set this variable to either NonCommercial or Commercial. The variable should be set on user- or process-level.

Start writing code

The ExcelPackage class

The first thing you do is to create an instance to the ExcelPackage class. To do that you first need to add a using directive to OfficeOpenXml namespace in the top of your file. This is the top namespace in EPPlus;

using OfficeOpenXml;

You can now reference the ExcelPackage class directly for your class. Remember that the ExcelPackage class implements the System.IDisposable interface, so you should add a using-statement when declaring instances of it (alternatively make sure to call its Dispose() method when you are done with it). Below are some simple examples of common tasks.

Working with files

//Creates a blank workbook. Use the using statment, so the package is disposed when we are done.
using (var p = new ExcelPackage())
{
   // A workbook must have at least one worksheet, so lets add one... 
   var ws=p.Workbook.Worksheets.Add("MySheet");
   // To set values in the spreadsheet use the Cells indexer.
   ws.Cells["A1"].Value = "This is cell A1";
   // Save the new workbook. We haven't specified the filename in the constructor,
   // so use the SaveAs method.
   p.SaveAs(@"c:\workbooks\myworkbook.xlsx");
}

You can also specify a the path of a workbook directly in the constructor.

// Open the workbook (or create it if it doesn't exist)
using (var p = new ExcelPackage(@"c:\workbooks\myworkbook.xlsx"))
{
   // Get the Worksheet created in the previous codesample. 
   var ws=p.Workbook.Worksheets["MySheet"];
   // Set the cell value using row and column.
   ws.Cells[2, 1].Value = "This is cell A2. Its font style is now set to bold";
   // The style object is used to access most cells formatting and styles.
   ws.Cells[2, 1].Style.Font.Bold=true;
   // Save and close the package.
   p.Save();
}

Working with streams

Besides reading/writing files, EPPlus can also handle workbooks as streams. This can be useful when creating workbooks on a web server or whenever you want to create/read workbooks without having a physical file.

// Create a new workbook and save it to a System.IO.MemoryStream
using var ms = new MemoryStream();
using var p = new ExcelPackage(ms);
var sheet = p.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["A1"].Value = "Hello world!";
// write the workbook bytes to the stream
p.Save();

// Open a new ExcelPackage from the MemoryStream...
using var p2 = new ExcelPackage(ms);
var helloWorld = p2.Workbook.Worksheets[0].Cells["A1"].Value;

Besides writing to a System.IO.Stream, you can also get the workbook as an array of bytes.

using var p = new ExcelPackage();
var sheet = p.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["A1"].Value = "Hello world!";
byte[] workbookBytes = p.GetAsByteArray();

Password protection/encryption

You can also pass a password to the constructor, if the workbook is encrypted.

Good to know

In most cases you probably have some data that you want to move to an Excel spreadsheet, do some styling, maybe add a formula or a chart.

But before we get started, here are some things to keep in mind when you work with EPPlus:

• Cell addresses, number formats and formulas are culture-insensitive, meaning that these might look a little bit different when you write your code compared with how they appear in your spreadsheet application. This is the way OOXML is stored and is then translated to your culture when the workbook is opened in Excel (or whatever spreadsheet application you are using).

• Addresses are separated by a comma (,).
  Example worksheet.Cells["A1:C1,C3"].Style.Font.Bold = true.

• Numberformats use dot for decimal (.) and comma (,) for thousand separator.
  Example worksheet.Cells["B2:B3"].Style.NumberFormat.Format = "#,##0.00";.

• Formulas use comma (,) to separate parameters. And you should not add the leading equal sign as you do in spreadsheet applications. Here is an example: worksheet.Cells["C11"].Formula="SUBTOTAL(9,\"C1:C10\")";.

What's next?

This wiki contains many examples and introductions to EPPlus functionality. Another good way to learn more about the library is to clone one of our sample projects and explore the code. You can also have a look at our web sample project to learn more about how you can use EPPlus in web apps.

See also

• The Sample Project
• EPPlus and Docker
• EPPlus and Blazor
• EPPlus and Linux
• Security
• Configuration
• Releases/versioning