Namespace: CalcEngine

Spread.Sheets.CalcEngine

Table of contents

Enumerations

• RangeReferenceRelative

Functions

• evaluateFormula
• expressionToFormula
• formulaToExpression
• formulaToRanges
• goalSeek
• rangeToFormula
• rangesToFormula

Functions

evaluateFormula

▸ 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);

Parameters

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	-

Returns

any

The evaluated formula result.

---

expressionToFormula

▸ 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);
}

Parameters

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	-

Returns

string

The formula string.

---

formulaToExpression

▸ 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);

Parameters

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	-

Returns

Expression

The expression tree.

---

formulaToRanges

▸ formulaToRanges(sheet, formula, baseRow?, baseCol?): Object[]

Converts a formula string to the specified cell ranges.

Parameters

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.

Returns

Object[]

The cell ranges that refers to the formula string.

---

goalSeek

▸ 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%

Parameters

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.

Returns

boolean

Indicate that whether a solution has been found.

---

rangeToFormula

▸ 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);

Parameters

Name	Type	Description
range	Range	The cell range in the sheet.
baseRow?	number	-
baseCol?	number	-
rangeReferenceRelative?	RangeReferenceRelative	-
useR1C1?	boolean	-

Returns

string

The formula string that refers to the specified cell range.

---

rangesToFormula

▸ 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);

Parameters

Name	Type	Description
ranges	Range[]	The cell range in the sheet.
baseRow?	number	-
baseCol?	number	-
rangeReferenceRelative?	RangeReferenceRelative	-
useR1C1?	boolean	-

Returns

string

The formula string that refers to the specified cell range.