Click here to Skip to main content
14,236,939 members

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

Rate this:
4.77 (19 votes)
Please Sign up or sign in to vote.
4.77 (19 votes)
26 Aug 2016CPOL
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:

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.

License

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

Share

About the Author

B. Clay Shannon
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

 
QuestionSorting Pin
kiquenet.com23-Jul-18 23:17
professionalkiquenet.com23-Jul-18 23:17 
Questionadditions planned? ;) Pin
SiL3NC3-SWX11-Oct-17 8:41
memberSiL3NC3-SWX11-Oct-17 8:41 
Questionhow can i count number worksheets using EPPlus Pin
amitangel1-Mar-17 1:12
memberamitangel1-Mar-17 1:12 
QuestionIs it possible to embed pdf files Pin
UR-IT23-Aug-16 0:33
memberUR-IT23-Aug-16 0:33 
GeneralMy vote of 1 Pin
BillWoodruff21-Aug-16 22:50
mveBillWoodruff21-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
memberBhuvanesh 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
memberGerstone19-Aug-16 0:53 
GeneralMy vote of 5 Pin
Dr. Jones DK17-Aug-16 2:54
professionalDr. Jones DK17-Aug-16 2:54 
GeneralMy vote of 5 Pin
Member 1236439017-Aug-16 1:02
memberMember 1236439017-Aug-16 1:02 
PraiseEPPplus: Highly recommended Pin
peteSJ12-Aug-16 18:46
memberpeteSJ12-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.

Tip/Trick
Posted 12 Aug 2016

Stats

50.2K views
25 bookmarked