[]
▸ evaluateFormula(context
, formula
, baseRow?
, baseColumn?
, useR1C1?
): any
Evaluates the specified formula.
example
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(1, 0, 2);
// Using EvaluateFormula() method to evaluate formula without setting formula in sheet's cell
var result = GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, "SUM(A1:A2)", 0, 0);
console.log("SUM(A1:A2) = " + result);
Name | Type | Description |
---|---|---|
context |
Object |
The evaluation context; in general, you should use the active sheet object. |
formula |
string |
The formula string. |
baseRow? |
number |
- |
baseColumn? |
number |
- |
useR1C1? |
boolean |
- |
any
The evaluated formula result.
▸ expressionToFormula(context
, expression
, baseRow?
, baseColumn?
, useR1C1?
): string
Unparse the specified expression tree to formula string.
example
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(0, 1, 2);
sheet.setValue(0, 2, 3);
sheet.addCustomName("customName1", "=12", 0, 0);
sheet.addCustomName("customName2", "Average(20,45)", 0, 0);
sheet.addCustomName("customName3", "=$A$1:$C$1");
sheet.setFormula(1, 0, "customName1");
sheet.setFormula(1, 1, "customName2");
sheet.setFormula(1, 2, "sum(customName3)");
var cname = sheet.getCustomName("customName2");
if (cname instanceof GC.Spread.Sheets.NameInfo) {
// Get CustomName
var name = cname.getName();
// Get Expression
var expression = cname.getExpression();
// Get Expression String
var expStr = GC.Spread.Sheets.CalcEngine.expressionToFormula(sheet, expression, 0, 0);
console.log("Name: " + name + " ; Expression: " + expStr);
}
Name | Type | Description |
---|---|---|
context |
Object |
The context; in general, you should use the active sheet object. |
expression |
Expression |
The expression tree. |
baseRow? |
number |
- |
baseColumn? |
number |
- |
useR1C1? |
boolean |
- |
string
The formula string.
▸ formulaToExpression(context
, formula
, baseRow?
, baseColumn?
, useR1C1?
): Expression
Parse the specified formula to expression tree.
example
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(0, 1, 2);
sheet.setValue(0, 2, 3);
sheet.getCell(4, 4).formula("=SUM(A1:C1)");
var formula = sheet.getFormula(4, 4);
var expression = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, formula, 0, 0);
console.log("Function Name is: " + expression.functionName);
Name | Type | Description |
---|---|---|
context |
Object |
The context; in general, you should use the active sheet object. |
formula |
string |
The formula string. |
baseRow? |
number |
- |
baseColumn? |
number |
- |
useR1C1? |
boolean |
- |
The expression tree.
▸ formulaToRanges(sheet
, formula
, baseRow?
, baseCol?
): Object
[]
Converts a formula string to the specified cell ranges.
Name | Type | Description |
---|---|---|
sheet |
Worksheet |
The base sheet. |
formula |
string |
The formula. |
baseRow? |
number |
The base row index of the formula. |
baseCol? |
number |
The base column index of the formula. |
Object
[]
The cell ranges that refers to the formula string.
▸ goalSeek(changingSheet
, changingRow
, changingColumn
, formulaSheet
, formulaRow
, formulaColumn
, desiredResult
): boolean
Attempts to find a value for one cell that produces the desired formula result in another cell.
example
// This sample shows how to use the goal seek.
// Loan amount is 10000, term is 18 months and pay 600 each month, evaluate what interest rate you will need to secure in order to meet your loan goal.
sheet.setValue(0, 1, 10000); // Loan Amount
sheet.setValue(1, 1, 18); // Term in Months
sheet.setFormatter(2, 1, "0%"); // Interest Rate
sheet.setFormatter(3, 1, "0.00");
sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment
GC.Spread.Sheets.CalcEngine.goalSeek(sheet, 2, 1, sheet, 3, 1, -600); // result in B3 is 10%
Name | Type | Description |
---|---|---|
changingSheet |
Worksheet |
The sheet that contains the cell that you want to adjust. |
changingRow |
number |
The row index of the cell that contains the value that you want to adjust. |
changingColumn |
number |
The column index of the cell that contains the value that you want to adjust. |
formulaSheet |
Worksheet |
The sheet that contains the formula that you want to resolve. |
formulaRow |
number |
The row index of the cell that contains the formula that you want to resolve. |
formulaColumn |
number |
The column index of the cell that contains the formula that you want to resolve. |
desiredResult |
number |
The formula result that you want. |
boolean
Indicate that whether a solution has been found.
▸ rangeToFormula(range
, baseRow?
, baseCol?
, rangeReferenceRelative?
, useR1C1?
): string
Converts the specified cell range to a formula string.
example
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
sheet = spread.getSheet(0);
// setting value
sheet.setValue(0, 0, 1, 3);
sheet.setValue(1, 0, 50, 3);
sheet.setValue(2, 0, 100, 3);
sheet.setValue(3, 0, 2, 3);
sheet.setValue(4, 0, 60, 3);
sheet.setValue(5, 0, 90, 3);
sheet.clearSelection();
// adding selection
sheet.addSelection(2, 0, 3, 1);
var range = sheet.getSelections();
// Getting range string
var rangeStr = GC.Spread.Sheets.CalcEngine.rangeToFormula(range[0]);
// creating formula using selected range
var formula = "Sum(" + rangeStr + ")";
// setting formula in Sheet's cell
sheet.setFormula(5, 5, formula, GC.Spread.Sheets.SheetArea.viewport);
Name | Type | Description |
---|---|---|
range |
Range |
The cell range in the sheet. |
baseRow? |
number |
- |
baseCol? |
number |
- |
rangeReferenceRelative? |
RangeReferenceRelative |
- |
useR1C1? |
boolean |
- |
string
The formula string that refers to the specified cell range.
▸ rangesToFormula(ranges
, baseRow?
, baseCol?
, rangeReferenceRelative?
, useR1C1?
): string
Converts the specified cell range to a formula string.
example
spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
sheet = spread.getSheet(0);
// Setting Value
sheet.setValue(0, 0, 1, 3);
sheet.setValue(1, 0, 50, 3);
sheet.setValue(2, 0, 100, 3);
sheet.setValue(3, 0, 2, 3);
sheet.setValue(4, 0, 60, 3);
sheet.setValue(5, 0, 90, 3);
sheet.setValue(6, 0, 3, 3);
sheet.setValue(7, 0, 40, 3);
sheet.clearSelection();
// Adding selections
sheet.addSelection(0, 0, 3, 1);
sheet.addSelection(5, 0, 2, 1);
var ranges = sheet.getSelections();
// getting range string
var rangesStr = GC.Spread.Sheets.CalcEngine.rangesToFormula(ranges);
// creating formula using selected ranges
var formula = "Sum(" + rangesStr + ")";
// setting formula in Sheet's cell
sheet.setFormula(5, 5, formula, GC.Spread.Sheets.SheetArea.viewport);
Name | Type | Description |
---|---|---|
ranges |
Range [] |
The cell range in the sheet. |
baseRow? |
number |
- |
baseCol? |
number |
- |
rangeReferenceRelative? |
RangeReferenceRelative |
- |
useR1C1? |
boolean |
- |
string
The formula string that refers to the specified cell range.