Getting Started - donkma93/EPPlus GitHub Wiki

So how do I start?

Installation

EPPlus is distributed via Microsofts package manager Nuget. We refer to this page for detailed descriptions on how to get started/use Nuget. Here is a link to the EPPlus Nuget package.

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.

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. The ExcelPackage class has few different constructors depending on what you want to do...

        //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 on cell, 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 so use the Save as method.
           p.SaveAs(new FileInfo(@"c:\workbooks\myworkbook.xlsx"));
        }

You can also specify 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();
        }

EPPlus can also work with workbooks as streams. This can be useful when reading files from a web server or you want to pass workbooks without having a physical file. 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 things might look a little bit different when you write your code. This is the way OOXML is stored and is then translated to your culture when the workbook is opened in Excel.

  • 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 webapps.