Click here to Skip to main content
15,867,141 members
Articles / Productivity Apps and Services / Microsoft Office
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.77/5 (20 votes)
26 Aug 2016CPOL3 min read 75.7K   29   23
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:

  1. Right-click the Project and select “Manage NuGetPackages…”
  2. 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:

C#
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

C#
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:

C#
// 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

C#
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:

C#
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

C#
int rowCount = locationWorksheet.Dimension.End.Row; 
int colCount = locationWorksheet.Dimension.End.Column; 

Add Filters to Rows

C#
locationWorksheet.Cells["A6:D6"].AutoFilter = true;

Auto or Manually Fit Columns

C#
// 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

C#
deliveryPerformanceWorksheet.Row(curDelPerfRow + 1).Height = HEIGHT_FOR_DELIVERY_PERFORMANCE_TOTALS_ROW;

Add Borders to a Range

C#
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

C#
locationWorksheet.View.FreezePanes(FIRST_DATA_ROW, SHORTNAME_COL);

Assign Background Color to Cells

C#
rowRngprogramParamsRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
rowRngprogramParamsRange.Style.Fill.BackgroundColor.SetColor(Color.DarkRed);

Set Font Color

C#
rowRngprogramParamsRange.Style.Font.Color.SetColor(Color.Red);

Set Horizontal and Vertical Alignment

C#
columnHeaderRowRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
columnHeaderRowRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

Wrap Text

C#
columnHeaderRowRange.Style.WrapText = true;

Set a Number Format to a Cell

C#
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

C#
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:

C#
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

C#
yourWorksheet.Row(_lastRowAdded).Hidden = true;

Hide Gridlines on a Sheet

C#
priceComplianceWorksheet.View.ShowGridLines = false;

Specify a Repeating Row For Printing Above Subsequent Pages

C#
prodUsageWorksheet.PrinterSettings.RepeatRows = new ExcelAddress(String.Format("${0}:${0}", COLUMN_HEADING_ROW));

Save Sheet to Disk

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder Across Time & Space
United States United States
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).

Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.

I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven

Comments and Discussions

 
QuestionCombine EPPlus and Interop Pin
Ignatius_Reilly21-Oct-22 7:14
Ignatius_Reilly21-Oct-22 7:14 
QuestionCtrl Shift Down Arrow Pin
Ignatius_Reilly21-Oct-22 6:09
Ignatius_Reilly21-Oct-22 6:09 
QuestionSorting Pin
kiquenet.com23-Jul-18 23:17
professionalkiquenet.com23-Jul-18 23:17 
Questionadditions planned? ;) Pin
SiL3NC3-SWX11-Oct-17 8:41
SiL3NC3-SWX11-Oct-17 8:41 
Questionhow can i count number worksheets using EPPlus Pin
amitangel1-Mar-17 1:12
amitangel1-Mar-17 1:12 
QuestionIs it possible to embed pdf files Pin
UR-IT23-Aug-16 0:33
UR-IT23-Aug-16 0:33 
GeneralMy vote of 1 Pin
BillWoodruff21-Aug-16 22:50
professionalBillWoodruff21-Aug-16 22:50 
GeneralRe: My vote of 1 Pin
B. Clay Shannon22-Aug-16 3:20
professionalB. Clay Shannon22-Aug-16 3:20 
GeneralRe: My vote of 1 Pin
#realJSOP25-Aug-16 12:19
mve#realJSOP25-Aug-16 12:19 
GeneralRe: My vote of 1 Pin
B. Clay Shannon25-Aug-16 12:23
professionalB. Clay Shannon25-Aug-16 12:23 
QuestionGood Tool to be use Pin
Bhuvanesh Mohankumar19-Aug-16 8:09
Bhuvanesh Mohankumar19-Aug-16 8:09 
QuestionCaveats and Emptors Pin
#realJSOP19-Aug-16 7:44
mve#realJSOP19-Aug-16 7:44 
AnswerRe: Caveats and Emptors Pin
B. Clay Shannon19-Aug-16 7:48
professionalB. Clay Shannon19-Aug-16 7:48 
AnswerRe: Caveats and Emptors Pin
Richard Deeming23-Aug-16 8:28
mveRichard Deeming23-Aug-16 8:28 
GeneralRe: Caveats and Emptors Pin
B. Clay Shannon23-Aug-16 8:31
professionalB. Clay Shannon23-Aug-16 8:31 
QuestionMy vote 5 Pin
Gerstone19-Aug-16 0:53
Gerstone19-Aug-16 0:53 
GeneralMy vote of 5 Pin
Dr. Jones DK17-Aug-16 2:54
professionalDr. Jones DK17-Aug-16 2:54 
Thank you for this fine article. I can't wait to try it out.
GeneralMy vote of 5 Pin
Member 1236439017-Aug-16 1:02
Member 1236439017-Aug-16 1:02 
PraiseEPPplus: Highly recommended Pin
peteSJ12-Aug-16 18:46
peteSJ12-Aug-16 18:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.