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 DataReader
• LoadFromDataTable - Loads data from 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 - Get 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
• SetValue

All of these methods are demonstrated in the sample project.

Addressing a worksheet

Formatting and styling

Working with formulas

Adding a comment

Conditional formatting

Data validation

Shapes, Pictures and Charts

Tables

Pivot Tables

The Sample Project

VBA

... Work in progress ...