Using built-in operations and functions:
← Older revision
Revision as of 12:27, 10 April 2014
(9 intermediate revisions by one user not shown)
Line 44:
Line 44:
end;
end;
</syntaxhighlight>
</syntaxhighlight>
−
This requires quite some typing. For simplification a methodology of nested function calls has been added to fpspreadsheet in which every element is specified by a function which links to the next function
by means of the
last argument:
+
This requires quite some typing. For simplification a methodology of nested function calls has been added to fpspreadsheet in which every element is specified by a function which links to the next
element
function
via its
last argument:
<syntaxhighlight>
<syntaxhighlight>
Line 61:
Line 61:
====Using cells in formulas====
====Using cells in formulas====
−
Of course, the formulas can also contain links to cells. For this purpose the <code>ElementType</code> needs to be <code>fekCellValue</code>. This instructs Excel to use the value of the cell in the calculation. There are, however, also functions which require other properties of the cell, like format or address. For this case, use <code>fekCellRef</code> for the <code>ElementKind</code>. Another specialty is the usage of absolute and relative cell addresses (<code>$A$1</code> vs. <code>A1</code>, respectively). Cell row and column addresses specified in the RPNFormula elements are absolute by default. If you want relative rows/columns add <code>rfRelRow</code> or <code>rfRelCol</code> to the element's <code>RelFlags</code> set. Or, if you prefer the nested function notation simply use the function RPNCellValue (or RPNCellRef) with the standard notation of the cell adress using the $ sign.
+
Of course, the formulas can also contain links to cells. For this purpose the <code>ElementType</code> needs to be <code>fekCellValue</code>. This instructs Excel to use the value of the cell in the calculation. There are, however, also functions which require other properties of the cell, like format or address. For this case, use <code>fekCellRef</code> for the <code>ElementKind</code>. Another specialty is the usage of absolute and relative cell addresses (<code>$A$1</code> vs. <code>A1</code>, respectively). Cell row and column addresses specified in the RPNFormula elements are absolute by default. If you want relative rows/columns add <code>rfRelRow</code> or <code>rfRelCol</code> to the element's <code>RelFlags</code> set. Or, if you prefer the nested function notation simply use the function
<code>
RPNCellValue
</code>
(or
<code>
RPNCellRef
</code>
) with the standard notation of the cell adress using the
<code>
$
</code>
sign.
Here, as an example, <code>=A1*$B$1</code> in array notation:
Here, as an example, <code>=A1*$B$1</code> in array notation:
Line 92:
Line 92:
====Using ranges of cells ====
====Using ranges of cells ====
−
The notation <code>
A1C5
</code> refers in Excel to a range of cells, the rectangle between cells <code>A1</code> and <code>C5</code>. This feature is available in fpspreadsheet as well: use the <code>ElementKind</code> <code>fekCellRange</code> and a second set of row/column indices (<code>Row2</code> and <code>Col2</code>, respectively). There are also flags <code>rfRelRow2</code> and <code>rfRelCol2</code> to mark the second corner cell as relative.
+
The notation <code>
A1:C5
</code> refers in Excel to a range of cells, the rectangle between cells <code>A1</code> and <code>C5</code>. This feature is available in fpspreadsheet as well: use the <code>ElementKind</code> <code>fekCellRange</code> and a second set of row/column indices (<code>Row2</code> and <code>Col2</code>, respectively). There are also flags <code>rfRelRow2</code> and <code>rfRelCol2</code> to mark the second corner cell as relative.
====Using built-in operations and functions====
====Using built-in operations and functions====
Line 102:
Line 102:
! scope="col" | Meaning
! scope="col" | Meaning
! scope="col" | Operands
! scope="col" | Operands
+
! scope="col" | Argument types
+
! scope="col" | Argument function
|-
|-
−
| fekAdd || <code>=A1+A2</code> || addition || 2
+
| fekAdd || <code>=A1+A2</code> || addition || 2
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekSub || <code>=A1-A2</code> || subtraction || 2
+
| fekSub || <code>=A1-A2</code> || subtraction || 2
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekMul || <code>=A1*A2</code> || multiplication || 2
+
| fekMul || <code>=A1*A2</code> || multiplication || 2
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekDiv || <code>=A1/A2</code> || division || 2
+
| fekDiv || <code>=A1/A2</code> || division || 2
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekPercent || <code>=A1%</code> || division by 100 || 1
+
| fekPercent || <code>=A1%</code> || division by 100 || 1
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekPower || <code>=A1^2</code> || power || 2
+
| fekPower || <code>=A1^2</code> || power || 2
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekUMinus || <code>=-A1</code> || unary minus || 1
+
| fekUMinus || <code>=-A1</code> || unary minus || 1
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekUPlus || <code>=+A1</code> || unary plus || 1
+
| fekUPlus || <code>=+A1</code> || unary plus || 1
|| fekNum, fekCellValue || RPNNumber(), RPNCellValue()
|-
|-
−
| fekConcat || <code>="Hello "&A1 </code> || string concatenation ||2
+
| fekConcat || <code>="Hello "&A1 </code> || string concatenation || 2
|| fekString, fekCellValue || RPNString(), RPNCellValue()
|}
|}
Line 126:
Line 128:
Beyond that, Excel provides a huge number of functions many of which have been made available for fpspreadsheet via a corresponding <code>ElementKind</code>. Please note that some functions allow a variable count of parameters. In this case, this value has to be specified as <code>ParamsNum</code> in the formula.
Beyond that, Excel provides a huge number of functions many of which have been made available for fpspreadsheet via a corresponding <code>ElementKind</code>. Please note that some functions allow a variable count of parameters. In this case, this value has to be specified as <code>ParamsNum</code> in the formula.
−
The following table is far from being complete. To learn about the functions available look at the declaration of <code>TFEKind</code> in <code>fpspreadsheet.pas</code> which hints to the name of the Excel function. And have a look at the file "rpntests.inc" in the <code>tests</code> folder of the fpspreadsheet installation
,
every function is included
by means of
an example.
+
The following table is far from being complete. To learn about the functions available look at the declaration of <code>TFEKind</code> in <code>fpspreadsheet.pas</code> which hints to the name of the Excel function. And have a look at the file "rpntests.inc" in the <code>tests</code> folder of the fpspreadsheet installation
where
every function is included
with
an example.
{| class="wikitable"
{| class="wikitable"
Line 201:
Line 203:
nil))));
nil))));
</syntaxhighlight>
</syntaxhighlight>
+
+
====Remarks====
+
Currently, RPN formulas are implemented only for the biff file formats (<code>sfBiff2</code>, <code>sfBiff5</code>, <code>sfBiff8</code>) and only for writing. This means that Excel, when opening the file, will calculate the formula and display its result. If, however, the written file is opened by fpspreadsheet the calculation will not be performed and the formula field will be empty.
=== Packages ===
=== Packages ===