officefileapi-14942-spreadsheet-document-api-formulas-array-formulas.md
An Array formula executes operations on one or more sets of values, known as array arguments. Each array argument must contain the same number of rows and columns. The outcome of an array formula can be either a single value or multiple values.
Array arguments can be cell ranges or array constants.
Array formulas can return single or multiple values:
The following image uses an array formula to calculate the value length in cells B3, C3, D3, and E3. Although you specify the formula for a single cell, the returned results appear within multiple cells, matching the input array’s layout.
Individual cells or groups of cells within the array formula range remain read-only. Any attempt to change a cell value directly (without modifying the formula) results in an exception. You can, however, change cell styles: colors, fonts, and so on.
The Spreadsheet Document API supports two types of array formulas: static (legacy) and dynamic.
Static (Legacy) Array Formulas
Legacy array formulas are those used in Microsoft Excel 2019 and in earlier versions. These formulas remain static and must be entered into a range that matches the output size. If the number of returned values surpasses the cell count, the formula does not display excess values. If the cell count outnumbers return values, the formula fills extra cells with repeated values.
Refer to the following topic for information on how to organize static array formulas in Spreadsheet Document API: How to: Manage Static (Legacy) Array Formulas
Dynamic Array Formulas
Dynamic array formulas are a newer feature available in Excel 2019 and later versions. These formulas automatically determine output cell range based on the returned result. If data changes, the formula output refreshes automatically.
The output range of a dynamic array formula is known as the spill range , with the first cell containing the formula. The #SPILL! error indicates obstacles within the spill range, such as blocking data.
The CellRange.DynamicArrayFormula property allows you to specify the array formula for a range of cells. Use the CellRange.DynamicArrayFormulaInvariant property to set the array formula in invariant culture. The range with the same array formula acts as a single entity. You may only modify data for the entire range.
The Worksheet.DynamicArrayFormulas property retrieves the collection of dynamic array formulas (DynamicArrayFormula objects) in the worksheet. You can detect the Range of cells containing the array formula, and the formula text (the DynamicArrayFormula.Formula property).
The following code snippet creates a LEN (returns the number of characters in a text string) dynamic array formula:
using DevExpress.Spreadsheet;
Workbook workbook = new Workbook();
workbook.CreateNewDocument();
Worksheet worksheet = workbook.Worksheets.ActiveWorksheet;
// Insert dynamic array formulas
worksheet["A1"].DynamicArrayFormulaInvariant = "={\"Red\",\"Green\",\"Orange\",\"Blue\"}";
worksheet.DynamicArrayFormulas.Add(worksheet["A2"], "=LEN(A1:D1)");
workbook.SaveDocument("result.xlsx");
Imports DevExpress.Spreadsheet
Private workbook As New Workbook()
workbook.CreateNewDocument()
Dim worksheet As Worksheet = workbook.Worksheets.ActiveWorksheet
' Insert dynamic array formulas
worksheet("A1").DynamicArrayFormulaInvariant = "={""Red"",""Green"",""Orange"",""Blue""}"
worksheet.DynamicArrayFormulas.Add(worksheet("A2"), "=LEN(A1:D1)")
workbook.SaveDocument("result.xlsx")
Modify array formulas only for the entire range. The CellRange.HasDynamicArrayFormula allows you to identify whether the cell range contains the dynamic array formula. To change the formula, obtain its range with the use of one of the following members:
Use the CellRange.DynamicArrayFormula property to manage the dynamic array formula for the obtained range.
The following code snippet detects the formula in the specific cell and creates a new range with the obtained formula:
//...
// delete the array formula
// and create a new range with the same formula.
if (worksheet.Cells["C2"].HasDynamicArrayFormula)
{
string af = worksheet.Cells["C2"].DynamicArrayFormula;
worksheet.Cells["C2"].GetDynamicArrayFormulaRange().DynamicArrayFormula = string.Empty;
worksheet.Range["C2:C11"].DynamicArrayFormula = af;
}
'...
' delete the array formula
' and create a new range with the same formula.
If worksheet.Cells("C2").HasDynamicArrayFormula Then
Dim af As String = worksheet.Cells("C1").DynamicArrayFormula
worksheet.Cells("C2").GetDynamicArrayFormulaRange().DynamicArrayFormula = String.Empty
worksheet.Range("C2:C11").DynamicArrayFormula = af
End If
To delete an array formula, call the DynamicArrayFormulaCollection.Remove method. You can remove a specific formula or a formula applied to the specified cell range.
The DynamicArrayFormulaCollection.Clear() method allows you to remove all dynamic array formulas.
Tip
You can also assign an empty string as the CellRange.DynamicArrayFormula value to remove this formula from a cell.
// Clear dynamic array formulas
Cell cell = worksheet.Cells["B2"];
if (cell.HasDynamicArrayFormula) {
CellRange dynamicArrayRange = cell.GetDynamicArrayFormulaRange();
dynamicArrayRange.Clear();
}
// Remove the formula from a specific range
worksheet.DynamicArrayFormulas.Remove(worksheet.Cells["A1"].GetDynamicArrayFormulaRange());
' Clear dynamic array formulas
Dim cell As Cell = worksheet.Cells("B2")
If cell.HasDynamicArrayFormula Then
Dim dynamicArrayRange As CellRange = cell.GetDynamicArrayFormulaRange()
dynamicArrayRange.Clear()
End If
' Remove the formula from a specific range
worksheet.DynamicArrayFormulas.Remove(worksheet.Cells("A1").GetDynamicArrayFormulaRange())
See Also