Getting Started

... Work in progress ...

So how do I start?

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's something 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 too 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.
Example worksheet.Cells["C11"].Formula="SUBTOTAL(9,\"C1:C10\")";.

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

To see some of these metods in action, have a look at Sample 9 and Sample 13

• 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"].Value = "This is cell B1"; // Sets the value of Cell B1
ws.Cells[1, 2].Value = "This is cell B1"; // Also sets the value of Cell B1

worksheet.Cells["A1:B3"].Style.NumberFormat.Format = "#,##0"; //Sets the numberformat for a range
worksheet.Cells[1,1,3,2].Style.NumberFormat.Format = "#,##0"; //Same as above,A1:B3

worksheet.Cells["A1:B3,D1:E57"].Style.NumberFormat.Format = "#,##0"; //Sets the numberformat for a range containing two addresses.
worksheet.Cells["A:B"].Style.Font.Bold = true; //Sets font-bold to true for column A & B
worksheet.Cells["1:1,A:A,C3"].Style.Font.Bold = true; //Sets font-bold to true for row 1,column A and cell C3
worksheet.Cells["A:XFD"].Style.Font.Name = "Arial"; //Sets font to Arial for all cells in a worksheet.
worksheet.Cells.Style.Font.Name = "Arial"; //This is equal to the above.

Formatting and styling

Cell styling is accessed by the Style property. You can easily style your spreadsheets by using the indexer of the Cells property decribed above. Lets say you want to set the numberformat of a range...

worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";

...or you want to set the header row to bold and dark background and white font...

using (var range = worksheet.Cells[1, 1, 1, 5])  //Address "A1:A5"
{
    range.Style.Font.Bold = true;
    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
    range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
    range.Style.Font.Color.SetColor(Color.White);
}

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

Shapes, Pictures and Charts are all added via the Drawings collection of the ExcelWorksheet class. For example

            //Add the textbox
            var shape = ws.Drawings.AddShape("myShape", eShapeStyle.Rect);
            shape.SetPosition(1, 5, 6, 5);       //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
            shape.SetSize(400, 200);             //Size in pixels
            shape.Text = "This is a rectangular shape.";

Adding an image to the workbook will work very similar way.

            var img = Image.FromFile("image.jpg");
            var pic = ws.Drawings.AddPicture(myPicture, img);
            shape.SetPosition(3, 0, 3, 0)       //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels

To add a chart use the AddChart method...

            //Add the piechart
            var pieChart = ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D) as ExcelPieChart;
            //Set top left corner to row 1 column 2
            pieChart.SetPosition(1, 0, 2, 0);
            pieChart.SetSize(400, 400);
            pieChart.Series.Add(ExcelRange.GetAddress(3, 2, row-1, 2), ExcelRange.GetAddress(3, 1, row-1, 1));

            pieChart.Title.Text = "Extension Size";
            //Set datalabels and remove the legend
            pieChart.DataLabel.ShowCategory = true;
            pieChart.DataLabel.ShowPercent = true;
            pieChart.DataLabel.ShowLeaderLines = true;
            pieChart.Legend.Remove();

For more details have a look at Sample 6

Tables

Pivot Tables

Encrypting your workbook

The Sample Project

VBA

... Work in progress ...