Skip to content
This repository has been archived by the owner on Mar 9, 2020. It is now read-only.

FAQ

Jump to bottom
Jan Källman edited this page Sep 15, 2017 · 5 revisions

What are the requirements for this library?

.Net Framwork 3.5 or higher. EPPlus 4.5 will support .Net Core 2.0 as well.

What features are supported in version 4.1

  • Cell access using ranges (values, formulas, Formula R1C1 format...)
  • Cell styling (Numberformat, Font, Fill, Border, Alignments and more)
  • Merging cells
  • Names ranges
  • In-cell Richtext
  • Comments
  • Pictures
  • Shapes
  • Charts (Except Radar-, Stock- and Surface 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

What is NOT supported by the library (these are the most obvious features)?

  • Drawing objects
  • Lines, smart art
  • Charts Radar-, Stock- and Surface charts.
  • Embedded Objects
  • Handling of External links (you can reference an external workbook using formulas)
  • Slice / Timeline
  • Win/Loss
  • Themes
  • Chart & Macro worksheets
  • Form & ActiveX controls

How do I install this library

Easiest way is to use Nuget, eather from Visual studio or from the console. Optionally you can download it from here or any older version from http://epplus.codeplex.com/releases.Unzip the package and copy the EPPlus.dll to a directory of your choice, then simply add a reference to it in your project.

How do I get started?

Download the sample project and step through the different samples in the console application. The samples show most things you can use the library for. There is also a simple web sample that shows different ways to send your generated workbooks to the client. The sample project is created in Visual Studio 2015.

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

If you add numeric data as strings (like the original Excelpackage do), Excel will treat the data as a string and it will not be formated. Do not use the ToString method when setting numeric values.

string s="1000"
int i=1000;
worksheet.Cells["A1"].Value=s; //Will not be formated
worksheet.Cells["A2"].Value=i; //Will be formated
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

I have a lot of data I want to load. What should I think of to get the best performance?

If you have more than, let's say 5 000 cells in the sheet and experience performance problems, have this in mind... Always load cells from top to bottom left to right. That means that if you add a formula on the data you load, it's faster to add it for every row than to add it for a range after you have loaded the data (for ex. this will take a while,Worksheet.Cells"C1:C100000".Formula="A1+B1"). If you want to use a shared formula, like the sample, make sure you create the cells in sequence (use something like worksheet.Cellsrow,forumlaColumn.Value=null to create the cell. Then you can add the formula after you have finished loading your data. Avoid using the InsertRow and DeleteRow methods.

How do I add a chart to my worksheet?

Use the drawings collection...

var chart = sheet.Drawings.AddChart("chart1", eChartType.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.AddChart("chtLine", eChartType.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.Add(eChartType.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.AddChart("chtLine", eChartType.LineMarkers);        
var serie1= chart.Series.Add(Worksheet.Cells["B1:B4"],Worksheet.Cells["A1:A4"]);
var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.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;

Can I use this library with Mono?

Yes. I have not build it, but for all i know it should work. From version 4.5 Encryption and VBA should work as well.

Home

Tutorial

Roadmap

FAQ

Clone this wiki locally