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

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.

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 3^rd 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.