Getting Started

... Work in progress ...

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. So how do I start?

ExcelPackage

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"]="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)
        var fi=new FileInfo(@"c:\workbooks\myworkbook.xlsx")
	using (var p = new ExcelPackage(fi))
        {
           //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 B1. It is set to bolds";
           //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.

Reading and Writing Data

You can read and write data from your spreadsheet in a few different ways. The most obvious is to use the Cells property of the ExcelWorksheet class, shown in the samples above. There are also a few other ways to access the data...

On a from the cell property you can access these methods to read data from various sources:

• LoadFromText- Read a csv test file.
• LoadFromDataReader - Loads data from a DataReader
• LoadFromDataTable - Loads data from a DatatTable
• LoadFromCollection - Loads data from an IEnumerable using reflection.
• LoadFromArrays - Loads data from arrays
• GetValue<T> - Gets a value, with the option to specify a datatype
• Value - Gets or sets the value of the range.

You can also use the GetValue and SetValue methods directly on the worksheet object. (This will give a little bit better performance than reading the range):

• GetValue<T> - Gets a value of a single cell, with the option to specify a datatype
• SetValue - Sets a value of a single cell

Most of these methods are demonstrated in the sample project.

Addressing a worksheet

Cell addressing in EPPlus works pretty much as it works in Excel. Addressing cells is made in the indexer of the ExcelWorksheet.Cells property (the ExcelRange class).

Here´s a few examples of how you can address ranges.

ws.Cells["B1"]="This is cell B1"; // Sets the value of Cell B1

ws.Cells[1, 2]="This is cell B1"; // Also sets the value of Cell B1

Formatting and styling

To see the some real code showing cell access and styling, have a look at sample 1 here

Working with formulas

Adding a comment

Conditional formatting

Some examples (more extensive examples are available in the sample project):

// -------------------------------------------------------------------
// TwoColorScale Conditional Formatting example
// -------------------------------------------------------------------
        ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
        var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);

        // Now, lets change some properties:
        cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
        cfRule1.LowValue.Value = 4;
        cfRule1.LowValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
        cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
        cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
        cfRule1.StopIfTrue = true;
        cfRule1.Style.Font.Bold = true;

Data validation

Some examples: The following code adds a data validation to a range that only allows integer values between 1 and 5.

private static void AddIntegerValidation(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("integer");
    // add a validation and set values
    var validation = sheet.DataValidations.AddIntegerValidation("A1:A2");
    // Alternatively:
    //var validation = sheet.Cells["A1:A2"].DataValidation.AddIntegerDataValidation();
    validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
    validation.PromptTitle = "Enter a integer value here";
    validation.Prompt = "Value should be between 1 and 5";
    validation.ShowInputMessage = true;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Value must be between 1 and 5";
    validation.ShowErrorMessage = true;
    validation.Operator = ExcelDataValidationOperator.between;
    validation.Formula.Value = 1;
    validation.Formula2.Value = 5;
}

The following code adds a dropdown list of valid options based on an Excel range.

private static void AddListValidationFormula(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list formula");
    sheet.Cells["B1"].Style.Font.Bold = true;
    sheet.Cells["B1"].Value = "Source values";
    sheet.Cells["B2"].Value = 1;
    sheet.Cells["B3"].Value = 2;
    sheet.Cells["B4"].Value = 3;
            
    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    // Alternatively:
    // var validation = sheet.Cells["A1"].DataValidation.AddListDataValidation();
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    validation.Formula.ExcelFormula = "B2:B4";
            
}

The following code adds a dropdown list of valid options based on a set of values. Note that this option is only for a small amount of values, since Excel has a built in limitation on the length of the field containing the values. If you want to create a data validation with a larger number of options, reference a formula instead as demonstrated above.

private static void AddListValidationValues(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list values");

    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    for (var i = 1; i <= 5; i++)
    {
        validation.Formula.Values.Add(i.ToString());
    }
    Console.WriteLine("Added sheet for list validation with values");

}

Shapes, Pictures and Charts

Tables

Pivot Tables

The Sample Project

VBA

... Work in progress ...