EPPlus Basics and Snippets (Cheatsheet for generating OpenXML Spreadsheet Files using EPPlus)






4.77/5 (19 votes)
In which the esteemed and humble author selflessly - indeed quasi-heroically! - presents a grab bag of snippets to use when utilizing the open-source EPPlus library to create spreadsheet files
Stop Leaping Headlong Through Flaming Hoops and Retire those Nasty Mucking Boots
I have begun refactoring my Excel Interop apps (a method of generating Excel spreadsheets with C# which requires persnickety and pugnacious setup and teardown along with much dotting of Is, crossing of Ts, and gnashing of teeth) to use the open source EPPlus library, which is easier and requires less hoop jumping and stall mucking. Here is a handful of snippets I have compiled (no pun intended) for many of the common operations for generating spreadsheet files using this excellent library.
Why Try EPPlus?
As a side note on why this refactoring is a worthwhile endeavor, I compared a run of four distinct reports, the first four using the legacy Excel Interop code, and the second set (same exact reports and criteria/data) using EPPlus. The Excel Interop set took 17 minutes to complete; the EPPLus code took 7 minutes - 40% of the time! Also, the size of the files differed significantly; they were:
Excel Interop: 46KB; 1,104KB; 40KB; 231KB
EPPlus: 35KB; 736KB; 31KB; 178KB
So Excel Interop total file size == 1,421KB; EPPlus total file size == 980KB - all of the EPPlus-generated files together are smaller than the largest Excel Interop-generated file, and combined are just over 2/3 the size of its estimable rival!
Upshot: EPPlus is faster as regards development time (quicker to learn and less code needs to be written), faster in execution speed, and creates smaller files. What's not to like? Consider me a convert (no pun intended)!
Caveat Deploytor: When deploying your EPPlusified .exe, be sure that EPPlus.dll rides along with it.
Add EPPlus to a Project
To add the open-source 3rd party Excel-generation library “EPPlus
”, follow these simple steps:
- Right-click the Project and select “Manage NuGetPackages…”
- Search for “
EPPlus
” and Install it
Create Minimal EPPlus File
The crux to using EPPlus (after referencing the lib) is to enclose its code within a using
clause like so:
var fileName = "BaseName_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + ".xlsx";
var outputDir = @"C:\misc\";
Directory.CreateDirectory(outputDir); // in case it doesn't already exist; no harm done if it does
var file = new FileInfo(outputDir + fileName);
using (var package = new ExcelPackage(file))
{
. . .
}
All the creation and manipulation of sheets goes within that using
– no need for Initialize and Deintialize blocks, as with Excel Interop.
Create a Sheet
private ExcelWorksheet locationWorksheet;
. . .
locationWorksheet = package.Workbook.Worksheets.Add("Bla");
Create and Configure a Range
To set up a range of cells (encompassing 1..N rows and 1..N columns), do this:
// Cells args are first row, first col, last row, last col
using (var rowRngUnitName = locationWorksheet.Cells[1, 1, 1, 4])
{
rowRngUnitName.Style.Font.Name = fontForSheets;
rowRngUnitName.Merge = true;
rowRngUnitName.Style.Font.Bold = true;
rowRngUnitName.Style.Font.Size = 14;
rowRngUnitName.Value = _unit; // there are also Text, FormatedText (sic), and RichText properties
// Other possible assignments:
//rowRngUnitName.Style.Fill.PatternType = ExcelFillStyle.Solid;
//rowRngUnitName.Style.Fill.BackgroundColor.SetColor(Color.Black);
//rowRngUnitName.Style.Font.Color.SetColor(Color.WhiteSmoke);
//rowRngUnitName.Style.ShrinkToFit = false;
}
Create and Configure a single Cell
using (var shortNameCell = locationWorksheet.Cells[rowToPop, SHORTNAME_BYDCBYLOC_COL])
{
shortNameCell.Value = "Short Name";
shortNameCell.Style.WrapText = false;
shortNameCell.Style.Font.Size = 12;
}
Add an Image to a Sheet
To add an image at a specific cell, do this:
string imgPath = @"C:\misc\yourImage.png";AddImage(locationWorksheet, 1, 5, imgPath);
AddImage(locationWorksheet, 1, 5, imgPath);
. . .
private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath)
{
Bitmap image = new Bitmap(imagePath);
{
var excelImage = oSheet.Drawings.AddPicture("Platypus Logo", image);
excelImage.From.Column = colIndex-1;
excelImage.From.Row = rowIndex-1;
excelImage.SetSize(108, 84);
excelImage.From.ColumnOff = Pixel2MTU(2);
excelImage.From.RowOff = Pixel2MTU(2);
}
}
public int Pixel2MTU(int pixels)
{
int mtus = pixels * 9525;
return mtus;
}
Get the Row and Column Count
int rowCount = locationWorksheet.Dimension.End.Row;
int colCount = locationWorksheet.Dimension.End.Column;
Add Filters to Rows
locationWorksheet.Cells["A6:D6"].AutoFilter = true;
Auto or Manually Fit Columns
// autofit all columns
deliveryPerformanceWorksheet.Cells[deliveryPerformanceWorksheet.Dimension.Address].AutoFitColumns();
customerWorksheet.Cells.AutoFitColumns();
// autofit a specified range of columns
locationWorksheet.Cells["A:C"].AutoFitColumns();
// manually assign widths of specififed columns
locationWorksheet.Column(4).Width = 3.14;
locationWorksheet.Column(5).Width = 14.33;
locationWorksheet.Column(6).Width = 186000000.00;
Set Row Height
deliveryPerformanceWorksheet.Row(curDelPerfRow + 1).Height = HEIGHT_FOR_DELIVERY_PERFORMANCE_TOTALS_ROW;
Add Borders to a Range
using (var entireSheetRange = locationWorksheet.Cells[6, 1, locationWorksheet.Dimension.End.Row, 6])
{
entireSheetRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
entireSheetRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
entireSheetRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
entireSheetRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
entireSheetRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
Set FreezePanes
locationWorksheet.View.FreezePanes(FIRST_DATA_ROW, SHORTNAME_COL);
Assign Background Color to Cells
rowRngprogramParamsRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
rowRngprogramParamsRange.Style.Fill.BackgroundColor.SetColor(Color.DarkRed);
Set Font Color
rowRngprogramParamsRange.Style.Font.Color.SetColor(Color.Red);
Set Horizontal and Vertical Alignment
columnHeaderRowRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
columnHeaderRowRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
Wrap Text
columnHeaderRowRange.Style.WrapText = true;
Set a Number Format to a Cell
monOrdersCell.Style.Numberformat.Format = "0"; // some other possibilities are "#,##0.00";
// "#,##0"; "\"$\"#,##0.00;[Red]\"$\"#,##0.00";
// "_($* #,##0.00_);_($* (#,##0.00);_($* \"\" - \"\"??_);
// _(@_)"; "0.00";
Add a Formula to a Cell
using (var totalTotalOrdersCell = deliveryPerformanceWorksheet.Cells[curDelPerfRow + 1,
TOTAL_ORDERS_COLUMN])
{
totalTotalOrdersCell.Style.Numberformat.Format = "#,##0";
totalTotalOrdersCell.Formula = string.Format("SUM(J{0}:J{1})", FIRST_DATA_ROW, curDelPerfRow - 1);
totalTotalOrdersCell.Calculate();
// Note that EPPlus apparently differs from Excel Interop in that there is no "="
// at the beginning of the formula, e.g. it does not start "=SUM("
// Another way (rather than using a defined range, as above) is:
// deliveryPerformanceWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";
}
Manually Sum a range of Rows within a Column
I had an occasion where the Formula wouldn't work for me, and had to "brute force" it; here's how I did so:
totalOccurrencesCell.Value = SumCellVals(SUMMARY_TOTAL_OCCURRENCES_COL, FIRST_SUMMARY_DATA_ROW, rowToPopulate - 1);
. . .
private string SumCellVals(int colNum, int firstRow, int lastRow)
{
double runningTotal = 0.0;
double currentVal;
for (int i = firstRow; i <= lastRow; i++)
{
using (var sumCell = priceComplianceWorksheet.Cells[i, colNum])
{
currentVal = Convert.ToDouble(sumCell.Value);
runningTotal = runningTotal + currentVal;
}
}
return runningTotal.ToString();
}
To sum ints rather than real numbers, just change it to use ints rather than doubles.
Hide a Row
yourWorksheet.Row(_lastRowAdded).Hidden = true;
Hide Gridlines on a Sheet
priceComplianceWorksheet.View.ShowGridLines = false;
Specify a Repeating Row For Printing Above Subsequent Pages
prodUsageWorksheet.PrinterSettings.RepeatRows = new ExcelAddress(String.Format("${0}:${0}", COLUMN_HEADING_ROW));
Save Sheet to Disk
String uniqueFolder = @"C:\misc";
string fromAsYYYYMMDD = DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss");
filename = String.Format(@"{0}\{1} - Fill Rate - {2}.xlsx", uniqueFolder, _unit, fromAsYYYYMMDD);
if (File.Exists(filename))
{
File.Delete(filename);
}
Stream stream = File.Create(filename);
package.SaveAs(stream);
stream.Close();
package.Save();
An article where many of these snippets are put to use to create a "real, live" spreadsheet can be found here.
Chaw This Up and Swaller It Down After Reading
This is top secret info, so don't tell anybody, but AFAIK "EPPlus" stands for - not Electric Prunes Plus - but rather Electronic Platypi Penetrating Layers Using Subterfuge.