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 implicit intersection with EPPlus.

Read more about implicit intersection here (at Microsoft, external link).

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, 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:

sheet.Cells[2, 2].Formula = "RANDARRAY(5,5)";
sheet.Calculate(); //To get the size of the formula you must calculate it.
// Alternatively, to calculate this cell only:
//sheet.Cells[2, 2].Calculate()";

var chart = sheet.Drawings.AddBarChart("Dynamic Chart", eBarChartType.ColumnClustered);

var range = sheet.Cells[2, 2].FormulaRange;
for (var c = range.Start.Column; c <= range.End.Column; c++)
{
   chart.Series.Add(range.TakeSingleColumn(c));
}
chart.StyleManager.SetChartStyle(ePresetChartStyle.ColumnChartStyle9);

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

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

The new Take- and Skip functions are useful when adding a chart to the output of a dynamic array formula.

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
• Formula calculation
• Legacy array formulas