CellFormula Class
Formula.When the object is serialized out as xml, its qualified name is x:f.
Inheritance Hierarchy
System.Object
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlLeafElement
DocumentFormat.OpenXml.OpenXmlLeafTextElement
DocumentFormat.OpenXml.Spreadsheet.CellFormula
Namespace: DocumentFormat.OpenXml.Spreadsheet
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
Syntax
'Declaration
Public Class CellFormula _
Inherits OpenXmlLeafTextElement
'Usage
Dim instance As CellFormula
public class CellFormula : OpenXmlLeafTextElement
Remarks
[ISO/IEC 29500-1 1st Edition]
18.3.1.40 f (Formula)
Formula for the cell. The formula expression is contained in the character node of this element.
[Example:
<f>SUM(C4:E4)</f>
end example]
The possible values for the t attribute are defined by the simple type ST_CellFormulaType, and are as follows:
Value
Description
array (Array formula)
Array formula. An array formula is a single formula, applied across a range of one or more cells. An array formula can return multiple results from one single calculation, the results spanning the cells in which it is contained (§18.17.2.7).
dataTable (Table formula)
Data table. A data table is a range of cells that shows how changing certain values in one or more formulas affects the results of those formulas. A data table provides a shortcut for calculating multiple versions in one operation, and a way to include the results of all of the different variations in a sheet.
Both one- and two-input variable data tables can be created (see attribute dt2D).[Example: A one-input variable data table might be used to calculate how different interest rates affect a monthly mortgage payment, while a two-input variable data table might be used to calculate how different interest rates and loan terms will affect the mortgage payment. end example]
In a one-input variable data table, values are listed either down a column (column-oriented) or across a row (row-oriented) (see attribute dtr).
Formulas that are used in a one-input variable data table shall refer to an input cell (see attribute r1), the cell in which each input value from a data table is substituted. Any cell on a worksheet can be the input cell. Although the input cell does not need to be part of the data table, the formulas in data tables shall refer to that input cell.
Two-input variable data tables use only one formula with two lists of input values. The formula shall refer to two input cells (see attributes r1 and r2).
The top-left cell in the data table is called the master cell.
[Guidance: It is recommended that Spreadsheet applications recalculate data tables whenever a worksheet is recalculated. end guidance]
normal (Normal formula)
Normal cell formula (§18.17).
shared (Shared formula)
Shared formula. If a cell contains the same formula as another cell, the “shared” value can be used for the t attribute and the si attribute can be used to refer to the cell containing the formula. Two formulas are considered to be the same when their respective representations in R1C1-reference notation, are the same.
Parent Elements |
|---|
c (§18.3.1.4); nc (§18.11.1.3); oc (§18.11.1.5) |
Attributes |
Description |
|---|---|
aca (Always Calculate Array) |
Only applies to array formulas. true indicates that the entire array shall be calculated in full. If false the individual cells of the array shall be calculated as needed. The aca value shall be ignored unless the value of the corresponding t attribute is array. [Note: The primary case where an array formula must be calculated in part instead of in full is when some cells in the array depend on other cells that are semi-calculated, e.g., contains the function =RAND(). end note] The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
bx (Assigns Value to Name) |
Specifies that this formula assigns a value to a name. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
ca (Calculate Cell) |
Indicates that this formula needs to be recalculated the next time calculation is performed. [Example: This is always set on volatile functions, like =RAND(), and circular references. end example] The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
del1 (Input 1 Deleted) |
Whether the first input cell for data table has been deleted. Applies to data table formula only. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
del2 (Input 2 Deleted) |
Whether the second input cell for data table has been deleted. Applies to data table formula only. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
dt2D (Data Table 2-D) |
Data table is two-dimentional. Only applies to the data tables function. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
dtr (Data Table Row) |
true if one-dimentional data table is a row, otherwise it's a column. Only applies to the data tables function. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
r1 (Data Table Cell 1) |
First input cell for data table. Only applies to the data tables array function "TABLE()". Written on master cell of data table formula only. The possible values for this attribute are defined by the ST_CellRef simple type (§18.18.7). |
r2 (Input Cell 2) |
Second input cell for data table when dt2D is '1'. Only applies to the data tables array function "TABLE()".Written on master cell of data table formula only. The possible values for this attribute are defined by the ST_CellRef simple type (§18.18.7). |
ref (Range of Cells) |
Range of cells which the formula applies to. Only required for shared formula, array formula or data table. Only written on the master formula, not subsequent formulas belonging to the same shared group, array, or data table. The possible values for this attribute are defined by the ST_Ref simple type (§18.18.62). |
si (Shared Group Index) |
Optional attribute to optimize load performance by sharing formulas. When a formula is a shared formula (t value is shared) then this value indicates the group to which this particular cell's formula belongs. The first formula in a group of shared formulas is saved in the f element. This is considered the 'master' formula cell. Subsequent cells sharing this formula need not have the formula written in their f element. Instead, the attribute si value for a particular cell is used to figure what the formula expression should be based on the cell's relative location to the master formula cell. A cell is shared only when si is used and t is shared. The formula expression for a cell that is specified to be part of a shared formula (and is not the master) shall be ignored, and the master formula shall override. If a master cell of a shared formula range specifies that a particular cell is part of the shared formula range, and that particular cell does not use the si and t attributes to indicate that it is shared, then the particular cell's formula shall override the shared master formula. If this cell occurs in the middle of a range of shared formula cells, the earlier and later formulas shall continue sharing the master formula, and the cell in question shall not share the formula of the master cell formula. Loading and handling of a cell and formula using an si attribute and whose t value is shared, located outside the range specified in the master cell associated with the si group, is implementation defined. Master cell references on the same sheet shall not overlap with each other. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype. |
t (Formula Type) |
Type of formula. The possible values for this attribute are defined by the ST_CellFormulaType simple type (§18.18.6). |
[Note: The W3C XML Schema definition of this element’s content model (CT_CellFormula) is located in §A.2. end note]
© ISO/IEC29500: 2008.
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.