TExcelFile.RecalcExpression Method
Overloads
TExcelFile.RecalcExpression(string)
Calculates the value of any formula and returns the result. The expression must be a valid Excel formula, it must start with "=", and cell references that don't specify a sheet (like for example "=A2") will refer to the active sheet. Cells used by the formula will be recalculated as needed too.
You can use this method as a simple calculator, or to calculate things like the sum of a range of cells in the spreadsheet. Look at the example for more information on how to use it.
Note that we will consider the expression to be located in the cell A1 of the Active sheet. So for example "=ROW()" will return 1, and "=A2" will return the value of A2 in the active sheet.
Syntax
Unit: FlexCel.Core
function TExcelFile.RecalcExpression(const expression: string): TCellValue; overload;Parameters
| <-> | Parameter | Type | Description |
|---|---|---|---|
| const | expression | string | Formula to evaluate. It must start with "=" and be a valid Excel formula. |
Returns
The value of the calculated formula.
Examples
To calculate the sum of all the cells in column A of the sheet "Data", you can use the following code:
xls := TXlsFile.Create('myfile.xls', true);
try
xls.ActiveSheetByName := 'Data';
Result := xls.RecalcExpression('=Sum(A:A)').ToNumberInvariant;
To calculate a simple expression, you can use:
Result := xls.RecalcExpression('=1 + 2 * 3').ToNumberInvariant;
See also
TExcelFile.RecalcExpression(string, Boolean)
Calculates the value of any formula and returns the result. The expression must be a valid Excel formula, it must start with "=", and cell references that don't specify a sheet (like for example "=A2") will refer to the active sheet. Cells used by the formula will be recalculated as needed too.
You can use this method as a simple calculator, or to calculate things like the sum of a range of cells in the spreadsheet. Look at the example for more information on how to use it.
Note that we will consider the expression to be located in the cell A1 of the Active sheet. So for example "=ROW()" will return 1, and "=A2" will return the value of A2 in the active sheet.
Syntax
Unit: FlexCel.Core
function TExcelFile.RecalcExpression(const expression: string; const forced: Boolean): TCellValue; overload; virtual; abstract;Parameters
| <-> | Parameter | Type | Description |
|---|---|---|---|
| const | expression | string | Formula to evaluate. It must start with "=" and be a valid Excel formula. |
| const | forced | Boolean | When true this method will always perform a recalc. When false, only if there has been a change on the spreadsheet. While for performance reasons you will normally want to keep this false, you might need to set it to true if the formulas refer to functions like "=NOW()" or "=RANDOM()" that change every time you recalculate. |
Returns
The value of the calculated formula.
Examples
To calculate the sum of all the cells in column A of the sheet "Data", you can use the following code:
xls := TXlsFile.Create('myfile.xls', true);
try
xls.ActiveSheetByName := 'Data';
Result := xls.RecalcExpression('=Sum(A:A)').ToNumberInvariant;
To calculate a simple expression, you can use:
Result := xls.RecalcExpression('=1 + 2 * 3').ToNumberInvariant;