CellFormula Class

Definition

Namespace:

DocumentFormat.OpenXml.Spreadsheet

Assembly:

DocumentFormat.OpenXml.dll

Package:

DocumentFormat.OpenXml v3.0.1

Formula.

This class is available in Office 2007 and above.

When the object is serialized out as xml, it's qualified name is x:f.

public class CellFormula : DocumentFormat.OpenXml.OpenXmlLeafTextElement

type CellFormula = class
    inherit OpenXmlLeafTextElement

Public Class CellFormula
Inherits OpenXmlLeafTextElement

Inheritance

Object

OpenXmlElement

OpenXmlLeafElement

OpenXmlLeafTextElement

CellFormula

Remarks

[ISO/IEC 29500-1 1st Edition]

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 =(). 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 =(), 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.

Constructors

CellFormula()	Initializes a new instance of the CellFormula class.
CellFormula(String)	Initializes a new instance of the CellFormula class with the specified text content.

Properties

AlwaysCalculateArray	Always Calculate Array Represents the following attribute in the schema: aca
Bx	Assigns Value to Name Represents the following attribute in the schema: bx
CalculateCell	Calculate Cell Represents the following attribute in the schema: ca
ChildElements	Gets all the child nodes of the current element. (Inherited from OpenXmlElement)
DataTable2D	Data Table 2-D Represents the following attribute in the schema: dt2D
DataTableRow	Data Table Row Represents the following attribute in the schema: dtr
ExtendedAttributes	Gets all extended attributes (attributes not defined in the schema) of the current element. (Inherited from OpenXmlElement)
Features	Gets a IFeatureCollection for the current element. This feature collection will be read-only, but will inherit features from its parent part and package if available. (Inherited from OpenXmlElement)
FirstChild	Gets the first child of the OpenXmlElement element. Returns null (Nothing in Visual Basic) if there is no such OpenXmlElement element. (Inherited from OpenXmlElement)
FormulaType	Formula Type Represents the following attribute in the schema: t
HasAttributes	Gets a value indicating whether the current element has any attributes. (Inherited from OpenXmlElement)
HasChildren	Gets a value that indicates whether the current element has any child elements. (Inherited from OpenXmlLeafTextElement)
InnerText	Gets or sets the concatenated values of the element and all of its child elements. (Inherited from OpenXmlLeafTextElement)
InnerXml	Gets or sets the markup that only represents child elements of the current element. (Inherited from OpenXmlLeafTextElement)
Input1Deleted	Input 1 Deleted Represents the following attribute in the schema: del1
Input2Deleted	Input 2 Deleted Represents the following attribute in the schema: del2
LastChild	Gets the last child of the OpenXmlElement element. Returns null (Nothing in Visual Basic) if there is no such OpenXmlElement element. (Inherited from OpenXmlElement)
LocalName	Gets the local name of the current element. (Inherited from OpenXmlElement)
MCAttributes	Gets or sets the markup compatibility attributes. Returns null if no markup compatibility attributes are defined for the current element. (Inherited from OpenXmlElement)
NamespaceDeclarations	Gets all the namespace declarations defined in the current element. Returns an empty enumerator if there is no namespace declaration. (Inherited from OpenXmlElement)
NamespaceUri	Gets the namespace URI of the current element. (Inherited from OpenXmlElement)
OpenXmlElementContext	Gets the OpenXmlElementContext of the current element. (Inherited from OpenXmlElement)
OuterXml	Gets the markup that represents the current element and all of its child elements. (Inherited from OpenXmlElement)
Parent	Gets the parent element of the current element. (Inherited from OpenXmlElement)
Prefix	Gets the namespace prefix of current element. (Inherited from OpenXmlElement)
R1	Data Table Cell 1 Represents the following attribute in the schema: r1
R2	Input Cell 2 Represents the following attribute in the schema: r2
Reference	Range of Cells Represents the following attribute in the schema: ref
SharedIndex	Shared Group Index Represents the following attribute in the schema: si
Space	Content Contains Significant Whitespace Represents the following attribute in the schema: xml:space
Text	Gets or sets the text of the current element. (Inherited from OpenXmlLeafTextElement)
XmlQualifiedName	Gets the qualified name of the current element. (Inherited from OpenXmlElement)
XName	Gets the qualified name of the current element. (Inherited from OpenXmlElement)

Methods

AddAnnotation(Object)	Adds an object to the current OpenXmlElement element's list of annotations. (Inherited from OpenXmlElement)
AddNamespaceDeclaration(String, String)	Adds a namespace declaration to the current node. (Inherited from OpenXmlElement)
Ancestors()	Enumerates all of the current element's ancestors. (Inherited from OpenXmlElement)
Ancestors<T>()	Enumerates only the current element's ancestors that have the specified type. (Inherited from OpenXmlElement)
Annotation(Type)	Get the first annotation object of the specified type from the current OpenXmlElement element. (Inherited from OpenXmlElement)
Annotation<T>()	Get the first annotation object of the specified type from the current OpenXmlElement element. (Inherited from OpenXmlElement)
Annotations(Type)	Gets a collection of annotations with the specified type for the current OpenXmlElement element. (Inherited from OpenXmlElement)
Annotations<T>()	Gets a collection of annotations with the specified type for the current OpenXmlElement element. (Inherited from OpenXmlElement)
Append(IEnumerable<OpenXmlElement>)	Appends each element from a list of elements to the end of the current element's list of child elements. (Inherited from OpenXmlElement)
Append(OpenXmlElement[])	Appends each element from an array of elements to the end of the current element's list of child elements. (Inherited from OpenXmlElement)
AppendChild<T>(T)	Appends the specified element to the end of the current element's list of child nodes. (Inherited from OpenXmlElement)
ClearAllAttributes()	Clears all of the attributes, including both known attributes and extended attributes. (Inherited from OpenXmlElement)
Clone()	Creates a duplicate of the current node. (Inherited from OpenXmlElement)
CloneNode(Boolean)	Creates a duplicate of this node.
Descendants()	Enumerates all of the current element's descendants. (Inherited from OpenXmlElement)
Descendants<T>()	Enumerate all of the current element's descendants of type T. (Inherited from OpenXmlElement)
Elements()	Enumerates all of the current element's children. (Inherited from OpenXmlElement)
Elements<T>()	Enumerates only the current element's children that have the specified type. (Inherited from OpenXmlElement)
ElementsAfter()	Enumerates all of the sibling elements that follow the current element and have the same parent as the current element. (Inherited from OpenXmlElement)
ElementsBefore()	Enumerates all of the sibling elements that precede the current element and have the same parent as the current element. (Inherited from OpenXmlElement)
GetAttribute(String, String)	Gets an Open XML attribute with the specified tag name and namespace URI. (Inherited from OpenXmlElement)
GetAttributes()	Gets a list that contains a copy of all the attributes. (Inherited from OpenXmlElement)
GetEnumerator()	Returns an enumerator that iterates through the child collection. (Inherited from OpenXmlElement)
GetFirstChild<T>()	Finds the first child element in type T. (Inherited from OpenXmlElement)
InsertAfter<T>(T, OpenXmlElement)	Inserts the specified element immediately after the specified reference element. (Inherited from OpenXmlElement)
InsertAfterSelf<T>(T)	Inserts the specified element immediately after the current element. (Inherited from OpenXmlElement)
InsertAt<T>(T, Int32)	Inserts the specified element at the specified index in the current element's list of child elements. (Inherited from OpenXmlElement)
InsertBefore<T>(T, OpenXmlElement)	Inserts the specified element immediately before the specified reference element. (Inherited from OpenXmlElement)
InsertBeforeSelf<T>(T)	Inserts the specified element immediately before the current element. (Inherited from OpenXmlElement)
IsAfter(OpenXmlElement)	Determines if the current element appears after a specified element in document order. (Inherited from OpenXmlElement)
IsBefore(OpenXmlElement)	Determines if the current element appears before a specified element in document order. (Inherited from OpenXmlElement)
LookupNamespace(String)	Resolves the namespace prefix in the context of the current node. (Inherited from OpenXmlElement)
LookupPrefix(String)	Finds the corresponding prefix for a namespace uri in the current element scope. (Inherited from OpenXmlElement)
NextSibling()	Gets the OpenXmlElement element that immediately follows the current OpenXmlElement element. Returns null (Nothing in Visual Basic) if there is no next OpenXmlElement element. (Inherited from OpenXmlElement)
NextSibling<T>()	Gets the OpenXmlElement element with the specified type that follows the current OpenXmlElement element. Returns null (Nothing in Visual Basic) if there is no next OpenXmlElement. (Inherited from OpenXmlElement)
PrependChild<T>(T)	Inserts the specified element at the beginning of the current element's list of child elements. (Inherited from OpenXmlElement)
PreviousSibling()	Gets the OpenXmlElement element that immediately precedes the current OpenXmlElement element. Returns null (Nothing in Visual Basic ) if there is no preceding OpenXmlElement element. (Inherited from OpenXmlElement)
PreviousSibling<T>()	Gets the OpenXmlElement element with the specified type that precedes the current OpenXmlElement. Returns null (Nothing in Visual Basic) if there is no preceding OpenXmlElement element. (Inherited from OpenXmlElement)
Remove()	Removes the current element from its parent. (Inherited from OpenXmlElement)
RemoveAllChildren()	Removes all child elements of the current element. (Inherited from OpenXmlLeafTextElement)
RemoveAllChildren<T>()	Remove all of the current element's child elements that are of type T. (Inherited from OpenXmlElement)
RemoveAnnotations(Type)	Removes the annotations of the specified type from the current OpenXmlElement element. (Inherited from OpenXmlElement)
RemoveAnnotations<T>()	Removes the annotations with the specified type from the current OpenXmlElement element. (Inherited from OpenXmlElement)
RemoveAttribute(String, String)	Removes the attribute from the current element. (Inherited from OpenXmlElement)
RemoveChild<T>(T)	Removes the specified child element from the current element's list of child elements. (Inherited from OpenXmlElement)
RemoveNamespaceDeclaration(String)	Removes the namespace declaration for the specified prefix. Removes nothing if there is no prefix. (Inherited from OpenXmlElement)
ReplaceChild<T>(OpenXmlElement, T)	Replaces a child element with another child element in the current element's list of child elements. (Inherited from OpenXmlElement)
SetAttribute(OpenXmlAttribute)	Sets an attribute to the specified element. If the attribute is a known attribute, the value of the attribute is set. If the attribute is an extended attribute, the 'openxmlAttribute' is added to the extended attributes list. (Inherited from OpenXmlElement)
SetAttributes(IEnumerable<OpenXmlAttribute>)	Sets a number of attributes to the element. If an attribute is a known attribute, the value of the attribute is set. If an attribute is an extended attribute, the 'openxmlAttribute' is added to the extended attributes list. (Inherited from OpenXmlElement)
WriteTo(XmlWriter)	Saves the current node to the specified XmlWriter. (Inherited from OpenXmlElement)

Explicit Interface Implementations

IEnumerable.GetEnumerator()	(Inherited from OpenXmlElement)
IEnumerable<OpenXmlElement>.GetEnumerator()	Returns an enumerator that iterates through the child collection. (Inherited from OpenXmlElement)