FAQ

Translations
What are the requirements for this library?
  • .Net Framwork 3.5 or higher. This library utilize the System.IO.Packaging namespace.
What features are supported in version 3.1.2
  • 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 Bubble-, 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).
  • Workbook protection
  • Worksheet protection
  • Tables (build in styles)
  • Enumeration of the cells collection. This makes it possible to use Linq-queries.
  • 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 Bubble-, Radar-, Stock- and Surface charts.
    • Embedded Objects
  • handling of External links (you can reference an external workbook using formulas)
  • Themes
  • Chart & Macro worksheets
  • Form & ActiveX controls
How do I install this library
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 2010.

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?
Good question! I haven't tried,so I have no idea. Please give me some feedback if you have tried.

Last edited Feb 14, 2013 at 6:29 PM by JanKallman, version 9

Comments

davekolb Feb 24 at 4:48 PM 
Saving an Excel file to a database as part of the logic for this tool would pervert the tool. Save the Excel file yourself to the database as a blob after the Excel file is created.

kositzkej Dec 13, 2013 at 5:44 PM 
Have Excel documents generated with EPPlus worked with Office 2013? I don't have that version of office yet, but I want to be sure I won't run into any issues if my company upgrades to that version of MS Office.

Thanks,
Jacob Kositzke

aquamoth Jun 3, 2013 at 3:22 PM 
Great work guys!
I have an "issue" with Numberformat; I write dynamic excel sheets that can take any of a large set of currencies. The number format for each currency is already available in C#.

Is there any way I can create an Excel Numberformat from System.Globalization.CultureInfo
(Like colors are converted from System.Drawing.Color)?

muralitcs May 31, 2013 at 9:12 AM 
Is it possible to read a excel file and update a particular sheet's cell value?
Like the way we do with OLEDB connection, specified here http://csharp.net-informations.com/excel/csharp-excel-oledb-update.htm

tacotortilla Jan 9, 2013 at 9:48 PM 
How do I remove an existing background color? This turns it black:
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.Empty);

mohanprajapati Mar 23, 2012 at 12:51 PM 
I want to add X-Axis Title and Y- Axis Title. How can i do that?

miyonet Mar 8, 2012 at 5:16 AM 
I want to get an uploaded excel file as a stream and enter the data to the SQL server... can i do this with EPPlus.. and how can i do it?

blesuv Feb 28, 2012 at 2:33 PM 
Why do I get "The type initializer for 'MS.Utility.EventTrace' threw an exception." exception when initiating the ExcelPackage object. Please help. Thank you.

BartlomiejSkwira Feb 27, 2012 at 2:17 PM 
Update last question, the license is now LGPL! :)

mchotai Sep 14, 2011 at 12:33 AM 
This is a fantastic library - in total contrast to the convoluted mess OpenXML and other Microsoft approaches take. Thank you! I had to hunt 7 different libraries / techniques before I found [yours] one that could bind from a collection.

cyrilwallace Jun 13, 2011 at 8:04 AM 
I need to save the excel file that I generate to a database. Is there a way that this can be done using the latest version of EPPlus?

thanks,
Cyril