[]
        
(Showing Draft Content)

GC.Spread.Sheets.CalcEngine

Namespace: CalcEngine

Spread.Sheets.CalcEngine

Table of contents

Enumerations

Functions

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.