Dynamic array formulas
EPPlus supports dynamic array formulas and spill ranges from version 7. A dynamic array formula is a formula that returns multiple values — the results automatically spill into adjacent cells, just as they do in modern Excel.
Note: Dynamic array formulas must be calculated by EPPlus to appear correctly when the file is opened in Excel or other spreadsheet applications.
Basic example¶
A formula that returns an array of values will automatically spill its results into the cells below or to the right of the formula cell.
Functions that return arrays work the same way. The example below uses SORTBY to sort a range by a separate column and spill the sorted result starting at A4.
sheet.Cells["A1"].Value = "Bob"; sheet.Cells["B1"].Value = "Street 1"; sheet.Cells["C1"].Value = 25;
sheet.Cells["A2"].Value = "Steve"; sheet.Cells["B2"].Value = "Street 2"; sheet.Cells["C2"].Value = 23;
sheet.Cells["A3"].Value = "Phil"; sheet.Cells["B3"].Value = "Street 3"; sheet.Cells["C3"].Value = 21;
// Sort A1:B3 by the values in C1:C3 ascending
sheet.Cells["A4"].Formula = "SORTBY(A1:B3, C1:C3, 1)";
sheet.Calculate();
Assert.AreEqual("Phil", sheet.Cells["A4"].Value); // age 21
Assert.AreEqual("Steve", sheet.Cells["A5"].Value); // age 23
Assert.AreEqual("Bob", sheet.Cells["A6"].Value); // age 25
sheet.Cells("A1").Value = "Bob" : sheet.Cells("B1").Value = "Street 1" : sheet.Cells("C1").Value = 25
sheet.Cells("A2").Value = "Steve" : sheet.Cells("B2").Value = "Street 2" : sheet.Cells("C2").Value = 23
sheet.Cells("A3").Value = "Phil" : sheet.Cells("B3").Value = "Street 3" : sheet.Cells("C3").Value = 21
' Sort A1:B3 by the values in C1:C3 ascending
sheet.Cells("A4").Formula = "SORTBY(A1:B3, C1:C3, 1)"
sheet.Calculate()
Assert.AreEqual("Phil", sheet.Cells("A4").Value) ' age 21
Assert.AreEqual("Steve", sheet.Cells("A5").Value) ' age 23
Assert.AreEqual("Bob", sheet.Cells("A6").Value) ' age 25
Referencing the output of a dynamic array formula¶
In the Excel UI you can use the # operator to reference the full spill range of a dynamic array formula — for example SUM(B3#). This operator is not part of the OOXML standard. In EPPlus you use the ANCHORARRAY function instead:
Note that ANCHORARRAY is not an EPPlus-specific function — it is the function Excel itself uses internally when saving # references to the file format.
Using a dynamic array as the source for a chart¶
From EPPlus 7, you can use ExcelRangeBase.FormulaRange to get the spill range of a calculated dynamic array formula and use it as a chart data source.
sheet.Cells[2, 2].Formula = "RANDARRAY(5,5)";
sheet.Calculate(); // Must be calculated first to determine the spill range
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);
sheet.Cells(2, 2).Formula = "RANDARRAY(5,5)"
sheet.Calculate() ' Must be calculated first to determine the spill range
Dim chart = sheet.Drawings.AddBarChart("Dynamic Chart", eBarChartType.ColumnClustered)
Dim range = sheet.Cells(2, 2).FormulaRange
For c As Integer = range.Start.Column To range.End.Column
chart.Series.Add(range.TakeSingleColumn(c))
Next
chart.StyleManager.SetChartStyle(ePresetChartStyle.ColumnChartStyle9)
chart.SetPosition(1, 0, 10, 0)
You can also use ExcelWorksheet.GetFormulaRange(int row, int col) as an alternative. The Take and Skip functions are useful when building chart series from spill ranges.
Implicit intersection¶
Implicit intersection means that when a formula expects a single value but receives a range, it picks the value in the same row or column as the formula cell. In the Excel UI this is expressed with the @ operator. Since @ is not part of the OOXML standard, EPPlus uses different approaches depending on the context.
Read more about implicit intersection on the Microsoft support site (external link).
Inside functions¶
Use the SINGLE function to apply implicit intersection to a range argument inside a function.
Single cell output¶
To apply implicit intersection to the output of a formula in a single cell, set UseImplicitIntersection = true on the cell after assigning the formula.
// Excel UI: @A1:A5 (in both B1 and B2)
sheet.Cells["B1"].Formula = "A1:A5";
sheet.Cells["B2"].Formula = "A1:A5";
// Must be set AFTER the formula has been assigned
sheet.Cells["B1:B2"].UseImplicitIntersection = true;
sheet.Calculate();
// Without UseImplicitIntersection, EPPlus treats the formula
// as a dynamic array formula and spills the results instead.
' Excel UI: @A1:A5 (in both B1 and B2)
sheet.Cells("B1").Formula = "A1:A5"
sheet.Cells("B2").Formula = "A1:A5"
' Must be set AFTER the formula has been assigned
sheet.Cells("B1:B2").UseImplicitIntersection = True
sheet.Calculate()
' Without UseImplicitIntersection, EPPlus treats the formula
' as a dynamic array formula and spills the results instead.
Shared formulas¶
When a formula is assigned to a multi-cell range (a shared formula), implicit intersection is always applied automatically — each cell in the range picks the value from its own row or column.
Functions that accept arrays¶
Many functions that previously only accepted single values can, from EPPlus 7, take an array as input and return an array as output. This works both with legacy array formulas and dynamic array formulas.