FAQ

What are the requirements for this library?

EPPlus supports .NET Framwork 3.5 or higher and .NET Core 2.0 and higher.
To use EPPlus with .NET Core on non-Windows systems, libgdiplus is required.

Homebrew on MacOS:
brew install mono-libgdiplus

apt-get:
apt-get install libgdiplus

What features are supported in version 5.0

• Cell access using ranges (values, formulas, Formula R1C1 format...)
• Cell styling (Numberformat, Font, Fill, Border, Alignments and more)
• Merging cells
• Filters (Value filters, Date Time Filters, Top 10 filters, Custom filter and Dynamic filters)
• Names ranges
• In-cell Richtext
• Comments
• Pictures
• Shapes
• Charts (Except Stock charts)
• Hyperlinks
• Workbook properties
• Header & Footers
• Printer settings (like margins, orientation, papersize, printarea, titels)
• Freeze panes
• Grouping and ungrouping
• Encryption (AES128, 192 & 256) both Standard and Agile.
• Workbook protection
• Worksheet protection
• Tables (build in styles)
• Enumeration of the cells collection. This makes it possible to use Linq-queries.
• Formula calculation (No support for calculating Array formulas). See supported functions here...
• Create Array formulas
• Pivot tables
• Data validation
• Conditional formatting
• VBA
• Themes

How do I install this library

Use Nuget, either from Visual studio or from the console.

How do I get started?

Clone or Download the sample project and explore the different samples in the console application. The samples show most things you can use the library for. The sample project is created in Visual Studio 2019.

How do I address a range?

This works pretty much as it works in Excel. Here are a few samples...

worksheet.Cells["A1"].Value = 1;	//Set the value of cell A1 to 1
worksheet.Cells[1,1].Value = 1;	//Set the value of cell A1 to 1

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.

//Sets the background color for the selected range (default is A1). 
//A range is selected using the by using the worksheet.Select method
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGreen);	

My number formats does not work

Make sure you add your numeric data using numeric datatypes.

string s="1000"
int i=1000;
worksheet.Cells["A1"].Value=s; //Will not be formatted
worksheet.Cells["A2"].Value=i; //Will be formatted
worksheet.Cells["A1:A2"].Style.NumberFormat.Format="#,##0";

I get an error that Excel has found unreadable content when I open my generated document?

Check your Formulas and Numberformats. Formulas and numberformats are not validated by the library so if you enter anything wrong the package will be corrupt. Use the English formula names. Formula parameters are separated by commas (,), string parameters use double quotes (").

worksheet.Cells["A1"].Formula="CONCATENATE(\"string1_\",\"test\")";

** Numberformats: ** Use culture independent number formats: dot (.) for decimal, comma (thousand).

The easiest way to check a format or a formula is to create an Excel file --> Do the formatting / Add the formulas --> Save it as XLSX ---> Rename the file *.zip. Extract the file and have a look at the number formats in the \xl\styles.xml file and formulas in \xl\worksheets\sheet#.xml.

I don't know what number format code to use

Do the same procedure as above and look at the formats in the styles.xml file

How do I add a chart to my worksheet?

Use the drawings collection...

var chart = sheet.Drawings.AddAreaChart("chart1", eAreaChartType.AreaStacked);
//Set position and size
chart.SetPosition(0, 630);
chart.SetSize(800, 600);
//Add one serie. 
var serie = chart.Series.Add(Worksheet.Cells["A1:A4"],Worksheet.Cells["B1:B4"]);

How can I add a series with a different chart type to my chart?

Here's how you do...

ExcelChart chart = worksheet.Drawings.AddLineChart("chtLine", eLineChartType.LineMarkers);        
var serie1= chart.Series.Add(Worksheet.Cells["B1:B4"],Worksheet.Cells["A1:A4"]);
//Now for the second chart type we use the chart.PlotArea.ChartTypes collection...
var chartType2 = chart.PlotArea.ChartTypes.AddBarChart(eBarChartType.ColumnClustered);
var serie2 = chartType2.Series.Add(Worksheet.Cells["C1:C4"],Worksheet.Cells["A1:A4"]);

How do I add a secondary axis to my chart?

If you want to have a secondary axis on your chart you have to add a new charttype to the chart. Something like this...

ExcelChart chart = worksheet.Drawings.AddLineChart("chtLine", eLineChartType.LineMarkers);        
var serie1= chart.Series.Add(Worksheet.Cells["B1:B4"],Worksheet.Cells["A1:A4"]);
var chartType2 = chart.PlotArea.ChartTypes.AddLineChart(eLineChartType.LineMarkers);
var serie2 = chartSerie2.Series.Add(Worksheet.Cells["C1:C4"],Worksheet.Cells["A1:A4"]);
chartType2.UseSecondaryAxis = true;

//By default the secondary X axis is hidden. If you what to show it, try this...
chartType2.XAxis.Deleted = false;
chartType2.XAxis.TickLabelPosition = eTickLabelPosition.High;