Dynamic arrayformulas

EPPlus has support for dynamic arrayformulas/spillover and support for several new functions that depends on this functionality from version 7 and up. We have also improved the support for handling of complex addresses and usage of operators with ranges/addresses.

Note that dynamic arrayformulas always must be calculated with EPPlus to appear correctly when opened in spreadsheet applications like Excel.

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);

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 (see example below) 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 and up

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 and up

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.FormulaRange 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 range = _ws.Cells[2, 2].FormulaRange;
            for (var c = range.Start.Column; c <= range.End.Column; c++)
            {
                chart.Series.Add(range.TakeSingleColumn(c));
            }

            chart.SetPosition(1, 0, 10, 0);

Optionally you can use the ExcelWorksheet.GetFormulaRange(int, int) function

Functions handling arrays

Many functions in EPPlus 7 and up 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();

See also

Sample 6.2-C# or Sample 6.2-VB