epplus

#epplus
Table of Contents
About 1

Chapter 1: Getting started with epplus 2

Remarks 2

Versions 2

Examples 3

Installation 3

Getting started 3

Chapter 2: Append data to existing document 5

Introduction 5

Examples 5

Appending data 5

Chapter 3: Columns and Rows 6

Introduction 6

Examples 6

Hide columns and rows 6

Resizing rows and columns 6

Autofit columns 6

Copy columns or rows 7

Chapter 4: Creating charts 8

Introduction 8

Examples 8

Pie Chart 8

Line Chart 8

Chapter 5: Creating formulas and calculate ranges 10

Introduction 10

Examples 10

Add formulas to a cell 10

Formula with multiple sheets 10

Manual calculation 10

Complete example with formulas 11

Chapter 6: Filling the document with data 12

Introduction 12

Examples 12

Fill with a DataTable 12

Fill with a DataTable from an SQL query or Stored Procedure 12

Manually fill cells 13

Fill from collection 13

Chapter 7: Formatting values 15

Introduction 15

Examples 15

Number formatting 15

Date formatting 15

Text Format 15

Chapter 8: Importing data from existing file 17

Introduction 17

Examples 17

Import data from Excel file 17

Import data from CSV file 17

Import data from Excel file with FileUpload Control 18

Create a DataTable from Excel File 18

Chapter 9: Merge Cells 20

Introduction 20

Examples 20

Merging cells 20

Chapter 10: Pivot Table 21

Introduction 21

Examples 21

Creating a Pivot Table 21

Chapter 11: Rich Text in cells 23

Introduction 23

Examples 23

Adding RichText to a cell 23

 Text formatting Properties 24

Inserting RichText in a cell 24

Chapter 12: Saving the Excel document 26

Introduction 26

Examples 26

Save to disk 26

Send to the Browser 27

Save to disk with SaveFileDialog 27

Chapter 13: Styling the Excel document 29

Introduction 29

Examples 29

Background color 29

Border styles 29

Font styles 29

Text alignment and word wrap 30

Complete example with all styles 30

Add an image to a sheet 31

Chapter 14: Tables 32

Introduction 32

Examples 32

Adding and formating a table 32

Chapter 15: User Input Validation 33

Introduction 33

Examples 33

List Validation 33

Integer Validation 33

DateTime Validation 34

Text Length Validation 34

Credits 35
About
You can share this PDF with anyone you feel could benefit from it, downloaded the latest version
from: epplus

It is an unofficial and free epplus ebook created for educational purposes. All the content is
extracted from Stack Overflow Documentation, which is written by many hardworking individuals at
Stack Overflow. It is neither affiliated with Stack Overflow nor official epplus.

The content is released under Creative Commons BY-SA, and the list of contributors to each
chapter are provided in the credits section at the end of this book. Images may be copyright of
their respective owners unless otherwise specified. All trademarks and registered trademarks are
the property of their respective company owners.

Use the content presented in this book at your own risk; it is not guaranteed to be correct nor
accurate, please send your feedback and corrections to info@[Link]

[Link] 1
Chapter 1: Getting started with epplus
Remarks
EPPlus is a .NET library that reads and writes Excel 2007/2010/2013 files using the Open Office
Xml format (xlsx).

EPPlus supports:

• Cell Ranges
• Cell styling (Border, Color, Fill, Font, Number, Alignments)
• Charts
• Pictures
• Shapes
• Comments
• Tables
• Protection
• Encryption
• Pivot tables
• Data validation
• Conditional formatting
• VBA
• Formula calculation

Versions

Version Release Date

First Release 2009-11-30

[Link] 2010-01-25

[Link] 2010-03-23

[Link] 2010-06-17

[Link] 2010-11-15

[Link] 2011-05-31

[Link] 2012-01-31

3.1 2012-04-11

4.0.5 2016-01-08

4.1 2016-07-14

[Link] 2
Examples
Installation

Download the files from CodePlex and add them to the project.

Or install the files with the Package Manager.

PM> Install-Package EPPlus

Getting started

//Create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//Set some properties of the Excel document
[Link] = "VDWWD";
[Link] = "Title of Document";
[Link] = "EPPlus demo export data";
[Link] = [Link];

//Create the WorkSheet

ExcelWorksheet worksheet = [Link]("Sheet 1");

//Add some text to cell A1

[Link]["A1"].Value = "My first EPPlus spreadsheet!";
//You could also use [line, column] notation:
[Link][1,2].Value = "This is cell B1!";

//Save your file

FileInfo fi = new FileInfo(@"Path\To\Your\[Link]");
[Link](fi);
}

//Opening an existing Excel file

FileInfo fi = new FileInfo(@"Path\To\Your\[Link]");
using (ExcelPackage excelPackage = new ExcelPackage(fi))
{
//Get a WorkSheet by index. Note that EPPlus indexes are base 1, not base 0!
ExcelWorksheet firstWorksheet = [Link][1];

//Get a WorkSheet by name. If the worksheet doesn't exist, throw an exeption

ExcelWorksheet namedWorksheet = [Link]["SomeWorksheet"];

//If you don't know if a worksheet exists, you could use LINQ,
//So it doesn't throw an exception, but return null in case it doesn't find it
ExcelWorksheet anotherWorksheet =
[Link](x=>[Link]=="SomeWorksheet");

//Get the content from cells A1 and B1 as string, in two different notations
string valA1 = [Link]["A1"].[Link]();
string valB1 = [Link][1,2].[Link]();

//Save your file

[Link]();
}

[Link] 3
Read Getting started with epplus online: [Link]
started-with-epplus

[Link] 4
Chapter 2: Append data to existing document
Introduction
How to append data to an already existing Excel document.

Examples
Appending data

//the path of the file

string filePath = "C:\\[Link]";

//or if you use [Link], get the relative path

filePath = [Link]("[Link]");

//create a fileinfo object of an excel file on the disk

FileInfo file = new FileInfo(filePath);

//create a new Excel package from the file

using (ExcelPackage excelPackage = new ExcelPackage(file))
{
//create an instance of the the first sheet in the loaded file
ExcelWorksheet worksheet = [Link][1];

//add some data

[Link][4, 1].Value = "Added data in Cell A4";
[Link][4, 2].Value = "Added data in Cell B4";

//save the changes

[Link]();
}

Read Append data to existing document online:

[Link]

[Link] 5
Chapter 3: Columns and Rows
Introduction
This topic contains information about working with columns and rows, like resizing, hiding, autofit

Examples
Hide columns and rows

//Hide column "A"

[Link](1).Hidden = true;

//Hide row 1
[Link](1).Hidden = true;

Resizing rows and columns

//Set the row "A" height to 15

double rowHeight = 15;
[Link](1).Height = rowHeight;

//Set the column 1 width to 50

double columnWidth = 50;
[Link](1).Width = columnWidth;

When Bestfit is set to true, the column will grow wider when a user inputs numbers in a cell

[Link](1).BestFit = true;

Autofit columns

//Make all text fit the cells

[Link][[Link]].AutoFitColumns();

//Autofit with minimum size for the column.

double minimumSize = 10;
[Link][[Link]].AutoFitColumns(minimumSize);

//Autofit with minimum and maximum size for the column.

double maximumSize = 50;
[Link][[Link]].AutoFitColumns(minimumSize, maximumSize);

//optional use this to make all columms just a bit wider, text would sometimes still overflow
after AutoFitColumns().
for (int col = 1; col <= [Link]; col++)
{
[Link](col).Width = [Link](col).Width + 1;
}

[Link] 6
Copy columns or rows

[Link][1,5,100,5].Copy([Link][1,2,100,2]);

Copies column 5 into column 2 Basically [Link](Destination)

This would only copy the first 100 rows.

Cells[RowStart, ColumnStart, RowEnd, ColumnEnd ]

is the format so to copy a row into another row you would just switch the indexes accordingly

Read Columns and Rows online: [Link]

[Link] 7
Chapter 4: Creating charts
Introduction
How to create charts with EPPlus

Examples
Pie Chart

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//fill cell data with a loop, note that row and column indexes start at 1
Random rnd = new Random();
for (int i = 1; i <= 10; i++)
{
[Link][1, i].Value = "Value " + i;
[Link][2, i].Value = [Link](5, 15);
}

//create a new piechart of type Pie3D

ExcelPieChart pieChart = [Link]("pieChart", eChartType.Pie3D) as
ExcelPieChart;

//set the title

[Link] = "PieChart Example";

//select the ranges for the pie. First the values, then the header range
[Link]([Link](2, 1, 2, 10), [Link](1, 1, 1,
10));

//position of the legend

[Link] = [Link];

//show the percentages in the pie

[Link] = true;

//size of the chart

[Link](500, 400);

//add the chart at cell C5

[Link](4, 0, 2, 0);
}

Line Chart

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{

[Link] 8
 //create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//fill cell data with a loop, note that row and column indexes start at 1
Random rnd = new Random();
for (int i = 2; i <= 11; i++)
{
[Link][1, i].Value = "Value " + (i - 1);
[Link][2, i].Value = [Link](5, 25);
[Link][3, i].Value = [Link](5, 25);
}
[Link][2, 1].Value = "Age 1";
[Link][3, 1].Value = "Age 2";

//create a new piechart of type Line

ExcelLineChart lineChart = [Link]("lineChart", [Link]) as
ExcelLineChart;

//set the title

[Link] = "LineChart Example";

//create the ranges for the chart

var rangeLabel = [Link]["B1:K1"];
var range1 = [Link]["B2:K2"];
var range2 = [Link]["B3:K3"];

//add the ranges to the chart

[Link](range1, rangeLabel);
[Link](range2, rangeLabel);

//set the names of the legend

[Link][0].Header = [Link]["A2"].[Link]();
[Link][1].Header = [Link]["A3"].[Link]();

//position of the legend

[Link] = [Link];

//size of the chart

[Link](600, 300);

//add the chart at cell B6

[Link](5, 0, 1, 0);
}

Read Creating charts online: [Link]

[Link] 9
Chapter 5: Creating formulas and calculate
ranges
Introduction
Basic examples of how to create cells with a formula for calculations within the Excel sheet.

Examples
Add formulas to a cell

//set the total value of cells in range A1 - A25 into A27

[Link]["A27"].Formula = "=SUM(A1:A25)";

//set the number of cells with content in range C1 - C25 into C27
[Link]["C27"].Formula = "=COUNT(C1:C25)";

//fill column K with the sum of each row, range A - J

for (int i = 1; i <= 25; i++)
{
var cell = [Link][i, 12];
[Link] = "=SUM(" + [Link][i, 1].Address + ":" + [Link][i,
10].Address + ")";
}

//calculate the quartile of range E1 - E25 into E27

[Link][27, 5].Formula = "=QUARTILE(E1:E25,1)";

Formula with multiple sheets

//set the total value of all cells in Sheet 2 into G27

[Link]["G27"].Formula = "=SUM('" + [Link] + "'!" +
[Link] + ":" + [Link] + ")";

//set the number of cells with content in Sheet 2, range C1 - C25 into I27
[Link]["I27"].Formula = "=COUNT('" + [Link][2].Name + "'!"
+ [Link][2].Cells["A1:B25"] + ")";

Manual calculation

If you use formulas, Excel will ask you to save the file every time, even if there were no changes
made. To prevent this behaviour you can set the calculation mode to manual.

[Link] = [Link];

//fill the sheet with data and set the formulas

[Link]();

[Link] 10
Complete example with formulas

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create 2 WorkSheets
ExcelWorksheet worksheet = [Link]("Sheet 1");
ExcelWorksheet worksheet2 = [Link]("Sheet 2");

//set the calculation mode to manual

[Link] = [Link];

//fill cell data with a loop, note that row and column indexes start at 1
for (int i = 1; i <= 25; i++)
{
for (int j = 1; j <= 10; j++)
{
[Link][i, j].Value = (i + j) - 1;
[Link][i, j].Value = (i + j) - 1;
}
}

//set the total value of cells in range A1 - A25 into A27

[Link]["A27"].Formula = "=SUM(A1:A25)";

//set the number of cells with content in range C1 - C25 into C27
[Link]["C27"].Formula = "=COUNT(C1:C25)";

//fill column K with the sum of each row, range A - J

for (int i = 1; i <= 25; i++)
{
var cell = [Link][i, 12];
[Link] = "=SUM(" + [Link][i, 1].Address + ":" + [Link][i,
10].Address + ")";
}

//calculate the quartile of range E1 - E25 into E27

[Link][27, 5].Formula = "=QUARTILE(E1:E25,1)";

//set the total value of all cells in Sheet 2 into G27

[Link]["G27"].Formula = "=SUM('" + [Link] + "'!" +
[Link] + ":" + [Link] + ")";

//set the number of cells with content in Sheet 2, range C1 - C25 into I27
[Link]["I27"].Formula = "=COUNT('" + [Link][2].Name +
"'!" + [Link][2].Cells["A1:B25"] + ")";

//calculate all the values of the formulas in the Excel file

[Link]();

//Save the file

FileInfo fi = new FileInfo("[Link]");
[Link](fi);
}

Read Creating formulas and calculate ranges online:

[Link]

[Link] 11
Chapter 6: Filling the document with data
Introduction
How to fill your created Excel sheet with data from different sources.

Examples
Fill with a DataTable

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a datatable
DataTable dataTable = new DataTable();

//add three colums to the datatable

[Link]("ID", typeof(int));
[Link]("Type", typeof(string));
[Link]("Name", typeof(string));

//add some rows

[Link](0, "Country", "Netherlands");
[Link](1, "Country", "Japan");
[Link](2, "Country", "America");
[Link](3, "State", "Gelderland");
[Link](4, "State", "Texas");
[Link](5, "State", "Echizen");
[Link](6, "City", "Amsterdam");
[Link](7, "City", "Tokyo");
[Link](8, "City", "New York");

//create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add all the content from the DataTable, starting at cell A1

[Link]["A1"].LoadFromDataTable(dataTable, true);
}

Fill with a DataTable from an SQL query or Stored Procedure

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//the query or stored procedure name for the database
string sqlQuery = "SELECT * FROM myTable";

//create a datatable
DataTable dataTable = loadExternalDataSet(sqlQuery);

//create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

[Link] 12
 //add all the content from the DataTable, starting at cell A1
[Link]["A1"].LoadFromDataTable(dataTable, true);
}

//method for retrieving data from the database and return it as a datatable
public static DataTable loadExternalDataSet(string sqlQuery)
{
DataTable dt = new DataTable();

using (SqlConnection connection = new

SqlConnection([Link]["myConnStr"].ConnectionString))
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection))
{
try
{
[Link](dt);
}
catch
{
}
}

return dt;
}

Manually fill cells

Fill some cells with text.

[Link]["A1"].Value = "Lorem ipsum";

[Link]["B2"].Value = "dolor sit amet";
[Link]["C3"].Value = "consectetur adipiscing";
[Link]["D4"].Value = "elit sed do eiusmod";

[Link]["E5"].Value = 12345;
[Link]["F6"].Value = [Link];

Fill cell data with a loop, note that row and column indexes start at 1

for (int i = 1; i <= 30; i++)

{
for (int j = 1; j <= 15; j++)
{
[Link][i, j].Value = "Row " + i + ", Column " + j;
}
}

Fill from collection

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//create a new list with books

[Link] 13
 List<Book> books = new List<Book>();

//add some books to the list

for (int i = 0; i < 10; i++)
{
Book b = new Book();

[Link] = i;
[Link] = "Name " + i;
[Link] = "Category " + i;
[Link] = [Link](i).AddHours(i);

[Link](b);
}

//add all the content from the List<Book> collection, starting at cell A1
[Link]["A1"].LoadFromCollection(books);
}

Read Filling the document with data online: [Link]

document-with-data

[Link] 14
Chapter 7: Formatting values
Introduction
How to get the desired formatting of DateTime and Numeric values.

Examples
Number formatting

//integer (not really needed unless you need to round numbers, Excel with use default cell
properties)
[Link]["A1:A25"].[Link] = "0";

//integer without displaying the number 0 in the cell

[Link]["A1:A25"].[Link] = "#";

//number with 1 decimal place

[Link]["A1:A25"].[Link] = "0.0";

//number with 2 decimal places

[Link]["A1:A25"].[Link] = "0.00";

//number with 2 decimal places and thousand separator

[Link]["A1:A25"].[Link] = "#,##0.00";

//number with 2 decimal places and thousand separator and money symbol
[Link]["A1:A25"].[Link] = "€#,##0.00";

//percentage (1 = 100%, 0.01 = 1%)

[Link]["A1:A25"].[Link] = "0%";

Date formatting

//default DateTime patterns

[Link]["A1:A25"].[Link] =
[Link];

//custom DateTime patters

[Link]["A1:A25"].[Link] = "dd-MM-yyyy HH:mm";

//or overwrite the patterns in the CurrentThread with your own

[Link] = new CultureInfo("nl-NL")
{
DateTimeFormat = { YearMonthPattern = "MMM yy" }
};
[Link]["A1:A25"].[Link] =
[Link];

Text Format

[Link] 15
 [Link]["A1:A25"].[Link] = "@";

Read Formatting values online: [Link]

[Link] 16
Chapter 8: Importing data from existing file
Introduction
How to import data from an existing Excel or CSV file.

Examples
Import data from Excel file

//create a list to hold all the values

List<string> excelData = new List<string>();

//read the Excel file as byte array

byte[] bin = [Link]("C:\\[Link]");

//or if you use [Link], get the relative path

byte[] bin = [Link]([Link]("[Link]"));

//create a new Excel package in a memorystream

using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
//loop all worksheets
foreach (ExcelWorksheet worksheet in [Link])
{
//loop all rows
for (int i = [Link]; i <= [Link]; i++)
{
//loop all columns in a row
for (int j = [Link]; j <=
[Link]; j++)
{
//add the cell data to the List
if ([Link][i, j].Value != null)
{
[Link]([Link][i, j].[Link]());
}
}
}
}
}

Import data from CSV file

//set the formatting options

ExcelTextFormat format = new ExcelTextFormat();
[Link] = ';';
[Link] = new CultureInfo([Link]());
[Link] = "dd-mm-yyyy";
[Link] = new UTF8Encoding();

//read the CSV file from disk

[Link] 17
 FileInfo file = new FileInfo("C:\\[Link]");

//or if you use [Link], get the relative path

FileInfo file = new FileInfo([Link]("[Link]"));

//create a new Excel package

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//load the CSV data into cell A1

[Link]["A1"].LoadFromText(file, format);
}

Import data from Excel file with FileUpload Control

//check if there is actually a file being uploaded

if ([Link])
{
//load the uploaded file into the memorystream
using (MemoryStream stream = new MemoryStream([Link]))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
//loop all worksheets
foreach (ExcelWorksheet worksheet in [Link])
{
//loop all rows
for (int i = [Link]; i <= [Link]; i++)
{
//loop all columns in a row
for (int j = [Link]; j <=
[Link]; j++)
{
//add the cell data to the List
if ([Link][i, j].Value != null)
{
[Link]([Link][i, j].[Link]());
}
}
}
}
}
}

Create a DataTable from Excel File

public static DataTable ExcelPackageToDataTable(ExcelPackage excelPackage)

{
DataTable dt = new DataTable();
ExcelWorksheet worksheet = [Link][1];

//check if the worksheet is completely empty

if ([Link] == null)
{
return dt;
}

[Link] 18
 //create a list to hold the column names
List<string> columnNames = new List<string>();

//needed to keep track of empty column headers

int currentColumn = 1;

//loop all columns in the sheet and add them to the datatable
foreach (var cell in [Link][1, 1, 1, [Link]])
{
string columnName = [Link]();

//check if the previous header was empty and add it if it was

if ([Link] != currentColumn)
{
[Link]("Header_" + currentColumn);
[Link]("Header_" + currentColumn);
currentColumn++;
}

//add the column name to the list to count the duplicates

[Link](columnName);

//count the duplicate column names and make them unique to avoid the exception
//A column named 'Name' already belongs to this DataTable
int occurrences = [Link](x => [Link](columnName));
if (occurrences > 1)
{
columnName = columnName + "_" + occurrences;
}

//add the column to the datatable

[Link](columnName);

currentColumn++;
}

//start adding the contents of the excel file to the datatable

for (int i = 2; i <= [Link]; i++)
{
var row = [Link][i, 1, i, [Link]];
DataRow newRow = [Link]();

//loop all cells in the row

foreach (var cell in row)
{
newRow[[Link] - 1] = [Link];
}

[Link](newRow);
}

return dt;
}

Read Importing data from existing file online:

[Link]

[Link] 19
Chapter 9: Merge Cells
Introduction
How to merge cells

Examples
Merging cells

//By range address

[Link]["A1:B5"].Merge = true;

//By indexes
[Link][1,1,5,2].Merge = true;

Read Merge Cells online: [Link]

[Link] 20
Chapter 10: Pivot Table
Introduction
Pivot table is one kind of interactive table, which can be used to calculate data, such as get sum or
count data. Also, users can change pivot table layout for analyzing data with different ways or
reassign row/column label. Every time users change layout, data will be recalculated in pivot table.

Examples
Creating a Pivot Table

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create 2 WorkSheets. One for the source data and one for the Pivot table
ExcelWorksheet worksheetPivot = [Link]("Pivot");
ExcelWorksheet worksheetData = [Link]("Data");

//add some source data

[Link]["A1"].Value = "Column A";
[Link]["A2"].Value = "Group A";
[Link]["A3"].Value = "Group B";
[Link]["A4"].Value = "Group C";
[Link]["A5"].Value = "Group A";
[Link]["A6"].Value = "Group B";
[Link]["A7"].Value = "Group C";
[Link]["A8"].Value = "Group A";
[Link]["A9"].Value = "Group B";
[Link]["A10"].Value = "Group C";
[Link]["A11"].Value = "Group D";

[Link]["B1"].Value = "Column B";

[Link]["B2"].Value = "emc";
[Link]["B3"].Value = "fma";
[Link]["B4"].Value = "h2o";
[Link]["B5"].Value = "emc";
[Link]["B6"].Value = "fma";
[Link]["B7"].Value = "h2o";
[Link]["B8"].Value = "emc";
[Link]["B9"].Value = "fma";
[Link]["B10"].Value = "h2o";
[Link]["B11"].Value = "emc";

[Link]["C1"].Value = "Column C";

[Link]["C2"].Value = 299;
[Link]["C3"].Value = 792;
[Link]["C4"].Value = 458;
[Link]["C5"].Value = 299;
[Link]["C6"].Value = 792;
[Link]["C7"].Value = 458;
[Link]["C8"].Value = 299;
[Link]["C9"].Value = 792;
[Link]["C10"].Value = 458;

[Link] 21
 [Link]["C11"].Value = 299;

[Link]["D1"].Value = "Column D";

[Link]["D2"].Value = 40075;
[Link]["D3"].Value = 31415;
[Link]["D4"].Value = 384400;
[Link]["D5"].Value = 40075;
[Link]["D6"].Value = 31415;
[Link]["D7"].Value = 384400;
[Link]["D8"].Value = 40075;
[Link]["D9"].Value = 31415;
[Link]["D10"].Value = 384400;
[Link]["D11"].Value = 40075;

//define the data range on the source sheet

var dataRange = [Link][[Link]];

//create the pivot table

var pivotTable = [Link]([Link]["B2"], dataRange,
"PivotTable");

//label field
[Link]([Link]["Column A"]);
[Link] = false;

//data fields
var field = [Link]([Link]["Column B"]);
[Link] = "Count of Column B";
[Link] = [Link];

field = [Link]([Link]["Column C"]);

[Link] = "Sum of Column C";
[Link] = [Link];
[Link] = "0.00";

field = [Link]([Link]["Column D"]);

[Link] = "Sum of Column D";
[Link] = [Link];
[Link] = "€#,##0.00";
}

Read Pivot Table online: [Link]

[Link] 22
Chapter 11: Rich Text in cells
Introduction
Most of the time, when we create spreadsheets, we just use a Cell's Value property to put content
in the cell and the Style property to format it.

Occasionally, however, we may wish to apply multiple styles to a cell - maybe put a bold and
underlined title before the rest of the content, or highlight a particular part of the text in Red - this is
where the cell's RichText property comes into play.

Examples
Adding RichText to a cell

Each element of text you want to use distinct formatting on should be added separately, by adding
to the cell's RichText collection property.

var cell = [Link][1,1];

[Link] = true; // Cell contains RichText rather than basic values
[Link] = true; // Required to honor new lines

var title = [Link]("This is my title");

var text = [Link]("\nAnd this is my text");

Note that each time you Add() a new string, it will inherit the formatting from the previous section.
As such, if you want to change the default formatting you will only need to change it on the first
string added.

This behaviour can, however, cause some confusion when formatting your text. Using the
example above, the following code will make all of the text in the cell Bold and Italic - this is not
the desired behavior:

// Common Mistake
var title = [Link]("This is my title");
[Link] = true;
[Link] = true;

var text = [Link]("\nAnd this is my text"); // Will be Bold and Italic too

The preferred approach is to add all your text sections first, then apply section-specific formatting
afterwards, as shown here:

var title = [Link]("This is my title");

[Link] = "Verdana"; // This will be applied to all subsequent sections as well

var text = [Link]("\nAnd this is my text");

[Link] 23
 // Format JUST the title
[Link] = true;
[Link] = true;

Text formatting Properties

There are a number of properties that can be applied to sections of RichText.

var title = [Link]("This is my title");

// Data Type: bool

// Default Value: false
[Link] = true;

// Data Type: [Link]

// Default Value: [Link]
[Link] = [Link];
[Link] = [Link](255, 0, 0);
[Link] = [Link]("#FF0000");

// Data Type: string

// Default Value: "Calibri"
[Link] = "Verdana";

// Data Type: bool

// Default Value: false
[Link] = true;

// Data Type: bool

// Default Value: true
// If this property is set to false, any whitespace (including new lines)
// is trimmed from the start and end of the Text
[Link] = true;

// Data Type: float

// Default Value: 11
// The font size is specified in Points
[Link] = 16;

// Data Type: bool

// Default Value: false
// Strikethrough
[Link] = false;

// Data Type: string

// Default Value: Whatever was set when the text was added to the RichText collection
[Link] += " (updated)";

// Data Type: bool

// Default Value: false
[Link] = true;

// Data Type: [Link]

// Default Value: [Link]
[Link] = [Link];

Inserting RichText in a cell

[Link] 24
EPPlus also supports the ability to insert text in a cell using the Insert() method. For example:

var file = new FileInfo(filePath);

using (var p = new ExcelPackage(file))
{
var wb = [Link];
var ws = [Link]() ?? [Link]("Sheet1");

var cell = [Link][1, 1];

[Link] = true;
[Link](); // Remove any RichText that may be in the cell already
var s1 = [Link]("Section 1.");
var s2 = [Link]("Section 2.");

var s3 = [Link](1, "Section 3.");

[Link] = true;
[Link]();
}

Note that the Insert() method does NOT insert at a character index, but at a Section index.
Because the sections are zero-indexed, the above code will produce the following text in the cell:

Section [Link] [Link] 2.

Read Rich Text in cells online: [Link]

[Link] 25
Chapter 12: Saving the Excel document
Introduction
Examples on how to save the created Excel sheet to the Disk or send it to the Browser.

Examples
Save to disk

//Using [Link]
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new Worksheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some text to cell A1

[Link]["A1"].Value = "My second EPPlus spreadsheet!";

//convert the excel package to a byte array

byte[] bin = [Link]();

//the path of the file

string filePath = "C:\\[Link]";

//or if you use [Link], get the relative path

filePath = [Link]("[Link]");

//write the file to the disk

[Link](filePath, bin);

//Instead of converting to bytes, you could also use FileInfo

FileInfo fi = new FileInfo(filePath);
[Link](fi);
}

//Using SaveAs
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new Worksheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some text to cell A1

[Link]["A1"].Value = "My second EPPlus spreadsheet!";
//the path of the file
string filePath = "C:\\[Link]";

//or if you use [Link], get the relative path

filePath = [Link]("[Link]");

//Write the file to the disk

FileInfo fi = new FileInfo(filePath);
[Link](fi);
}

[Link] 26
Send to the Browser

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create the WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some text to cell A1

[Link]["A1"].Value = "My second EPPlus spreadsheet!";

//convert the excel package to a byte array

byte[] bin = [Link]();

//clear the buffer stream

[Link]();
[Link]();
[Link] = true;

//set the correct contenttype

[Link] = "application/[Link]-
[Link]";

//set the correct length of the data being send

[Link]("content-length", [Link]());

//set the filename for the excel package

[Link]("content-disposition", "attachment; filename=\"[Link]\"");

//send the byte array to the browser

[Link](bin, 0, [Link]);

//cleanup
[Link]();
[Link]();
}

Save to disk with SaveFileDialog

//Using [Link]
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new Worksheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some text to cell A1

[Link]["A1"].Value = "My fourth EPPlus spreadsheet!";

//convert the excel package to a byte array

byte[] bin = [Link]();

//create a SaveFileDialog instance with some properties

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
[Link] = "Save Excel sheet";
[Link] = "Excel files|*.xlsx|All files|*.*";
[Link] = "ExcelSheet_" + [Link]("dd-MM-yyyy") + ".xlsx";

//check if user clicked the save button

[Link] 27
 if ([Link]() == [Link])
{
//write the file to the disk
[Link]([Link], bin);
}
}

//Using SaveAs
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new Worksheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some text to cell A1

[Link]["A1"].Value = "My fourth EPPlus spreadsheet!";

//create a SaveFileDialog instance with some properties

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
[Link] = "Save Excel sheet";
[Link] = "Excel files|*.xlsx|All files|*.*";
[Link] = "ExcelSheet_" + [Link]("dd-MM-yyyy") + ".xlsx";

//check if user clicked the save button

if ([Link]() == [Link])
{
//Get the FileInfo
FileInfo fi = new FileInfo([Link]);
//write the file to the disk
[Link](fi);
}
}

Read Saving the Excel document online: [Link]

excel-document

[Link] 28
Chapter 13: Styling the Excel document
Introduction
How to style Cells with font types, background color, border styles etc.

Examples
Background color

//fill column A with solid red color from hex

[Link](1).[Link] = [Link];
[Link](1).[Link]([Link]("#FF0000"));

//fill row 4 with striped orange background

[Link](4).[Link] = [Link];
[Link](4).[Link]([Link]);

Border styles

//make the borders of cell F6 thick

[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];

//make the borders of cells A18 - J18 double and with a purple color
[Link]["A18:J18"].[Link] = [Link];
[Link]["A18:J18"].[Link] = [Link];
[Link]["A18:J18"].[Link]([Link]);
[Link]["A18:J18"].[Link]([Link]);

Font styles

//set the font type for cells C1 - C30

[Link]["C1:C30"].[Link] = 13;
[Link]["C1:C30"].[Link] = "Calibri";
[Link]["C1:C30"].[Link] = true;
[Link]["C1:C30"].[Link]([Link]);

//Multiple Fonts in the same cell

ExcelRange rg = [Link]["A1"];
[Link] = true;
//ExcelRichText uses "using [Link];"
ExcelRichText text1 = [Link]("Text with Font1");
[Link] = true;
[Link] = true;
[Link] = [Link];
ExcelRichText text2 = [Link]("Text with Font2");
[Link] = true;
[Link] = false;

[Link] 29
 [Link] = [Link];
ExcelRichText text3 = [Link]("Text with Font3");
[Link] = false;
[Link] = true;

Text alignment and word wrap

//make column H wider and set the text align to the top and right
[Link](8).Width = 25;
[Link](8).[Link] = [Link];
[Link](8).[Link] = [Link];

//wrap text in the cells

[Link](8).[Link] = true;

Complete example with all styles

//create a new ExcelPackage

using (ExcelPackage excelPackage = new ExcelPackage())
{
//create the WorkSheet
ExcelWorksheet worksheet = [Link]("Sheet 1");

//add some dummy data, note that row and column indexes start at 1
for (int i = 1; i <= 30; i++)
{
for (int j = 1; j <= 15; j++)
{
[Link][i, j].Value = "Row " + i + ", Column " + j;
}
}

//fill column A with solid red color

[Link](1).[Link] = [Link];

[Link](1).[Link]([Link]("#FF0000"));

//set the font type for cells C1 - C30

[Link]["C1:C30"].[Link] = 13;
[Link]["C1:C30"].[Link] = "Calibri";
[Link]["C1:C30"].[Link] = true;
[Link]["C1:C30"].[Link]([Link]);

//fill row 4 with striped orange background

[Link](4).[Link] = [Link];
[Link](4).[Link]([Link]);

//make the borders of cell F6 thick

[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];
[Link][6, 6].[Link] = [Link];

//make the borders of cells A18 - J18 double and with a purple color
[Link]["A18:J18"].[Link] = [Link];
[Link]["A18:J18"].[Link] = [Link];
[Link]["A18:J18"].[Link]([Link]);

[Link] 30
 [Link]["A18:J18"].[Link]([Link]);

//make all text fit the cells

[Link][[Link]].AutoFitColumns();

//i use this to make all columms just a bit wider, text would sometimes still overflow
after AutoFitColumns(). Bug?
for (int col = 1; col <= [Link]; col++)
{
[Link](col).Width = [Link](col).Width + 1;
}

//make column H wider and set the text align to the top and right
[Link](8).Width = 25;
[Link](8).[Link] = [Link];
[Link](8).[Link] = [Link];

//get the image from disk

using ([Link] image =
[Link]([Link]("[Link]")))
{
var excelImage = [Link]("My Logo", image);

//add the image to row 20, column E

[Link](20, 0, 5, 0);
}
}

Add an image to a sheet

//get the image from disk

using ([Link] image =
[Link]([Link]("[Link]")))
{
var excelImage = [Link]("My Logo", image);

//add the image to row 20, column E

[Link](20, 0, 5, 0);
}

Read Styling the Excel document online: [Link]

excel-document

[Link] 31
Chapter 14: Tables
Introduction
This topic describe how to add and style tables

Examples
Adding and formating a table

//Using statement for ExcelTable and TableStyles

using [Link];

//Defining the tables parameters

int firstRow =1;
int lastRow = [Link];
int firstColumn = 1;
int lastColumn = [Link];
ExcelRange rg = [Link][firstRow, firstColumn, lastRow, LastColumn];
string tableName = "Table1";

//Ading a table to a Range

ExcelTable tab = [Link](rg, tableName);

//Formating the table style

[Link] = TableStyles.Light8;

Read Tables online: [Link]

[Link] 32
Chapter 15: User Input Validation
Introduction
How to validade user inputs. Validation constrains the values a user can input into a cell, and/or
set a combobox for the user select the value for the cell. Optionally, a message can be displayed
when the user clicks in a cell and a message error, when the validation fails.

Examples
List Validation

//Add a List validation to B column. Values should be in a list

var val = [Link]("B:B");
//Shows error message when the input doesn't match the accepted values
[Link] = true;
//Style of warning. "information" and "warning" allow users to ignore the validation,
//while "stop" and "undefined" doesn't
[Link] = [Link];
//Title of the error mesage box
[Link] = "This is the title";
//Message of the error
[Link] = "This is the message";
//Set to true to show a prompt when user clics on the cell
[Link] = true;
//Set the message for the prompt
[Link] = "This is a input message";
//Set the title for the prompt
[Link] = "This is the title from the input message";
//Define the accepted values
[Link]("This is accepted");
[Link]("This is also accepted");
[Link]("Any other thing is rejected");
//Set to true if blank value is accepted
[Link] = false;

//Add a List validation to the C column

var val2 = [Link]("C:C");
//Define the Cells with the accepted values
[Link] = "=$D$3:$D$5";
//Fill the cells with the accepted values
[Link]["D3"].Value = "Val1";
[Link]["D4"].Value = "Val2";
[Link]["D5"].Value = "Val3";

Integer Validation

//Add a List validation to the C column

var val3 = [Link]("E:E");
//For Integer Validation, you have to set error message to true
[Link] = true;
[Link] = "The value must be an integer between 0 and 10";

[Link] 33
 //Minimum allowed Value
[Link] = 0;
//Maximum allowed Value
[Link] = 10;
//If the cells are not filled, allow blanks or fill with a valid value,
//otherwise it could generate a error when saving
[Link] = true;

DateTime Validation

//Add a DateTime Validation to column F

var val4 = [Link]("F:F");
//For DateTime Validation, you have to set error message to true
[Link] = true;
//Minimum allowed date
[Link] = new DateTime(2017,03,15, 01, 0,0);
//Maximum allowed date
[Link]= new DateTime(2017, 03, 16, 12, 0, 0);
[Link] = true;

Text Length Validation

//Add a TextLength Validation to column G

var val5 = [Link]("G:G");
//For TextLenght Validation, you have to set error message to true
[Link] = true;
//Minimum allowed text lenght
[Link] = 3;
//Maximum allowed text lenght
[Link] = 5;
[Link] = true;

Read User Input Validation online: [Link]

validation

[Link] 34
Credits
S.
Chapters Contributors
No

Getting started with

1 Community, Magnetron, VDWWD
epplus

Append data to
2 VDWWD
existing document

3 Columns and Rows hellyale, Magnetron, VDWWD

4 Creating charts VDWWD

Creating formulas
5 Magnetron, VDWWD
and calculate ranges

Filling the document

6 VDWWD
with data

7 Formatting values Magnetron, VDWWD

Importing data from

8 VDWWD
existing file

9 Merge Cells Magnetron

10 Pivot Table VDWWD

11 Rich Text in cells Pete

Saving the Excel

12 Magnetron, VDWWD
document

Styling the Excel

13 Magnetron, VDWWD
document

14 Tables Magnetron

15 User Input Validation Magnetron

[Link] 35