The formula cells are the atomic units, which store all the information contained in a formula sheet. The power of the formula sheet is in the fact, that the formula cells values can be specified with formulas, which can reference other formula cells values. This yields great flexibility and dynamic intelligence. Formula cells are represented by the NFormulaCell abstract class, which defines the common features shared by all types of formula cells.
Basically the cell is a container for a single value, which can be specified either constantly or with a formula. The formula of a cell is determined by its Formula property. If this property is set to null the cell is considered constant (i.e. has no formula).
The value of the cell as a variant is get/set by the VariantValue property. If a formula has been specified, the get implementation will return an up to date result of its evaluation. The set implementation automatically resets the cell formula (marks it as a constant cell) and stores the provided value. Strongly typed cells (see below) typically expose a Value property, which performs the same actions, but is strongly typed (for example the NBooleanFormulaCell class has a Value property of type Boolean).
The cell also stores information about the possible error from the last formula evaluation. This error can be obtained from the FormulaError property. If the value of this property is null, then the formula is correct or has not been evaluated yet.
The cell value can be referenced in the formulas of other cells from the sheet by its name. The cell name is obtained from its Name property.
The logic, which must be modeled with a formula sheet often requires that some cells have a value of a concrete type (for example the FlipX cell of the NTransformSection is of Boolean strong type - its value is always true or false).
You can easily check whether the a formula cell is strongly typed with the help of the IsStronglyTyped virtual property. Some strongly typed cells can provide a list of predefined values (options). This list is provided by the GetStrongTypeOptions method. The options (if any) will appear in a combo box inside the formula cell editor.
The following table summarizes the currently provided strongly typed cells:
In contrast to the strongly typed cells the NVariantFormulaCell is used to store a NVariant value.
The following example creates a formula sheet, in which the sum of two cells can be obtained from a third cell:
| C# |
Copy Code
|
float sum;
// create a new sheet
NFormulaSheet sheet = new NFormulaSheet();
// create a section with the specified name and title
NFormulaSection section = new NFormulaSection("MySection", "My First Section");
// add the section to the sheet
sheet.Sections.Add(section);
// create named cells
section.Cells = new NFormulaCellDictionary();
section.Cells.Add("A", new NSingleFormulaCell(0.5f));
section.Cells.Add("B", new NSingleFormulaCell(2));
section.Cells.Add("Sum", new NSingleFormulaCell("A+B"));
// get the SUM value (2.5)
sum = ((NSingleFormulaCell)section.Cells["Sum"]).Value;
// alter the B cell
((NSingleFormulaCell)section.Cells["B"]).Value = 10;
// get the SUM value (10.5)
sum = ((NSingleFormulaCell)section.Cells["Sum"]).Value;
|
| Visual Basic |
Copy Code
|
Dim sum As Single
' create a new sheet
Dim sheet As New NFormulaSheet
' create a section with the specified name and title
Dim section As New NFormulaSection("MySection", "My First Section")
' add the section to the sheet
sheet.Sections.Add(section)
' create named cells
section.Cells = New NFormulaCellDictionary
section.Cells.Add("A", New NSingleFormulaCell(0.5F))
section.Cells.Add("B", New NSingleFormulaCell(2))
section.Cells.Add("Sum", New NSingleFormulaCell("A+B"))
' get the SUM value (2.5)
sum = DirectCast(section.Cells.Item("Sum"), NSingleFormulaCell).Value
' alter the B cell
DirectCast(section.Cells.Item("B"), NSingleFormulaCell).Value = 10
' get the SUM value (10.5)
sum = DirectCast(section.Cells.Item("Sum"), NSingleFormulaCell).Value
|
