Skip to content

EPPlus 7

EPPlus 7 has a new calculation engine with improved performance and many new features/capabilities. The most notable features includes support for arrayformulas/dynamic arrayformulas/spillover and support for several new functions that depends on this functionality. We have also improved the support for handling of complex addresses and usage of operators with ranges/addresses and reduced floating point errors/improved precision in sums, averages, etc.

New functions supported

  • XMATCH
  • XLOOKUP
  • FREQUENCY
  • FILTER
  • TRANSPOSE
  • HSTACK
  • VSTACK
  • TAKE
  • DROP
  • SINGLE
  • SORT
  • SORTBY
  • UNIQUE
  • ANCHORARRAY
  • CHOOSECOLS
  • CHOOSEROWS
  • EXPAND
  • RANDARRAY
  • SEQUENCE
  • TOCOL
  • TOROW
  • CHISQ.TEST
  • GESTEP
  • IMAGINARY
  • IMARGUMENT
  • IMCONJUGATE
  • IMCOS
  • IMCOT
  • IMCOSH
  • IMCSC
  • IMCSCH
  • IMDIV
  • IMLN
  • IMLOG10
  • IMLOG2
  • IMPOWER
  • IMPRODUCT
  • IMREAL
  • IMSEC
  • IMSECH
  • IMSIN
  • IMSINH
  • IMSQRT
  • IMSUB
  • IMSUM
  • IMTAN
  • ODDFPRICE
  • ODDFYIELD
  • ODDLYIELD
  • ODDLPRICE
  • POISSON.DIST
  • POISSON
  • SLOPE
  • STEYX
  • T.DIST
  • T.DIST.2T
  • T.INV
  • T.DIST.RT
  • T.INV.2T
  • T.TEST
  • TRIMMEAN
  • WEIBULL.DIST
  • WEIBULL
  • Z.TEST
  • ARABIC
  • MODE.MULT
  • BINOMDIST
  • BINOM.DIST
  • BINOM.INV
  • BINOM.DIST.RANGE
  • GAMMADIST
  • GAMMA.DIST
  • LOGNORM.INV
  • NEGBINOM.DIST
  • NEGBINOMDIST
  • LOGNORM.DIST
  • PROB
  • CHISQ.DIST
  • FDIST
  • F.DIST
  • FTEST
  • F.TEST
  • F.INV
  • F.INV.RT
  • ZTEST
  • PRICEDISC
  • VDB
  • RECEIVED
  • PRICEMAT
  • ISREF

Improved handling of addresses/ranges

The new calculation engine supports: - The intersect operator (space) - Addresses with multiple colon operators - Operators in combination with addresses/ranges. For example "A1:A3 + B1:B3 - 1" - Improved support for using functions in addresses.

Legacy array formulas

EPPlus now supports calculation of legacy array formulas. With legacy array formulas you must specify the target range of the array, see example below: 

sheet.Cells["A1"].Value = 1;
sheet.Cells["A2"].Value = 2;
sheet.Cells["A3"].Value = 3;

sheet.Cells["A4:C4"].CreateArrayFormula("TRANSPOSE(A1:A3)");
sheet.Calculate();
// A4:C4 will now contain 1, 2, 3

Dynamic array formulas

EPPlus 7 supports calculation of dynamic array formulas. This will just work under the hood using the existing Calculate() function. A dynamic array formula is always set on a single cell and can spill over to neighboring cells when calculated. If the neighboring cells already contains data you will get a #SPILL! error in the cell that contains the formula.

Example 1

sheet.Cells["A1"].Value = 1;
sheet.Cells["A2"].Value = 2;
sheet.Cells["A3"].Value = 3;
sheet.Cells["A5"].Formula = "A1:A3 > 1";
sheet.Calculate();
// A5:A6 will now contain 2 and 3

Example 2

sheet.Cells["A1"].Value = "Bob";
sheet.Cells["B1"].Value = "Street 1";
sheet.Cells["A2"].Value = "Steve";
sheet.Cells["B2"].Value = "Street 2";
sheet.Cells["A3"].Value = "Phil";
sheet.Cells["B3"].Value = "Street 3";
sheet.Cells["C1"].Value = 25;
sheet.Cells["C2"].Value = 23;
sheet.Cells["C3"].Value = 21;
sheet.Cells["A4"].Formula = "SORTBY(A1:B3,C1:C3,1)";
sheet.Calculate();
Assert.AreEqual("Phil", sheet.Cells["A4"].Value);
Assert.AreEqual("Steve", sheet.Cells["A5"].Value);
Assert.AreEqual("Bob", sheet.Cells["A6"].Value);

Function prefixes

Most newer functions must have a prefix such as "_xlfn." to work properly in Excel. In previous versions of EPPlus you had to know when to add these attributes to your formulas. EPPlus 7 will handle this for its built in functions when the workbook is saved, so you don't have to include these prefixes when adding new formulas via EPPlus.

See this wiki page for more info on function prefixes and our supported function list for an overview on which functions/prefixes EPPlus 7 supports.

Implicit intersection

In the Excel user interface you can use the @-operator to specify that implicit intersection should be applied to a range/address/function. This operator is not a part of the OOXML-standard. This section describes how to use explicit intersection with EPPlus.

Inside functions

Inside functions you use the SINGLE function to enforce implicit intersection.

Example: in Excel the formula of cell B1 is set to SUM(@A1:A5)

With EPPlus 7 

sheet.Cells["B1"].Formula = SUM(SINGLE(A1:A5));
sheet.Calculate();

Output from cells

Apply implicit intersection on the output from a single cell.

Example: In Excel the formulas of cells B1 and B2 are both set to @A1:A5

With EPPlus 7 

sheet.Cells["B1"].Formula = "A1:A5";
sheet.Cells["B2"].Formula = "A1:A5";
// NB! This property must be set AFTER the formula/formulas has been set.
sheet.Cells["B1:B2"].UseImplicitIntersection = true;
sheet.Calculate();

// Note that the default behaviour of EPPlus is that the UseImplicitIntersection property is false which means 
// that the formula will be calculated as a dynamic array formula.

Shared formulas

For shared formulas - a formula that references multiple cells - implicit intersection will always be applied. See example below:

sheet.Cells["B1:B5"].Formula = "A1:A5";
sheet.Calculate();

Referencing the output of a dynamic array formula

In the Excel user interface you can use the #-operator to reference the output range of a dynamic array formula. This operator is not a part of the OOXML-standard. When adding formulas via EPPlus you use the ANCHORARRAY function for this purpose. So if you see this in Excel

SUM(B3#)

and want to achieve the same result with EPPlus you add this to your formula:

SUM(ANCHORARRAY(B3))

Note that the ANCHORARRAY function is an Excel function that is used "behind the scenes" in Excel and not an EPPlus-specific function.

Using a dynamic array as source for a chart

From EPPlus 7 Beta 2, if you want use the output of a dynamic array formula as source for a chart you can use the ExcelRangeBase.FormulaAddress on the cell containing the formula: 

            _ws.Cells[2, 2].Formula = "RandArray(5,5)";
            _ws.Calculate(); //To get the size of the formula you must calculate it.

            var chart = _ws.Drawings.AddBarChart("Dynamic Chart", eBarChartType.ColumnClustered);
            chart.StyleManager.SetChartStyle(ePresetChartStyle.ColumnChartStyle9);

            var address = _ws.Cells[2, 2].FormulaAddress;
            for (var c = address.Start.Column; c <= address.End.Column; c++)
            {
                chart.Series.Add(_ws.Cells[address.Start.Row, c, address.End.Row, c]);
            }

            chart.SetPosition(1, 0, 10, 0);
Optionally you can use the ExcelWorksheet.GetFormulaAddress(int, int) function

Functions handling arrays

Many functions in EPPlus 7 that in previous versions only supported single arguments and returned a single value can now take an array as input and will then return an array that can be used in legacy or dynamic array formulas. Here is an example:

// legacy array formula
sheet.Cells["B1:B3"].CreateArrayFormula("YEAR(A1:A3)");
// dynamic array formula
sheet.Cells["D1"].Formula = "YEAR(A1:A3)";
sheet.Calculate();

Conditional Formatting

All Conditional Formattings capable of doing so now support Formulas including references to cells or external worksheets. Extended color options via Theme, Index and Auto instead of just Color. Ensured mutliple Conditionalformattings can be applied to one cell or range of cells with correct priority.

Databars

  • Added Negative Value And Axis options
  • Added enum to denote direction

Iconsets

  • New options: 3Stars, 3triangles, 5Boxes, NoIcons
  • Custom Icons via e.g. myIconSetVariable.Icon1.CustomIcon = eExcelconditionalFormattingCustomIcon.RedFlag

For implementation details see Colors, Custom Iconsets, Databars, Priority wiki pages.