[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Calculating Formulas Outside Worksheets

SpreadJS provides a powerful calculation engine that allows you to evaluate formulas and perform calculations outside of worksheet cells. The GC.Spread.Sheets.CalcEngine module contains several utility methods to parse, evaluate, and convert formulas without directly setting them in a worksheet.

Prepare a table containing the sales data.

image

evaluateFormula: Evaluates a specified formula string and returns the result.

Use cases:

  • When you need to dynamically calculate formula results in code (for example, when processing data in the backend or performing batch validation of formula logic), without relying on worksheet rendering or user actions.

  • When you need to retain the multiple outputs of an array formula, use preserveArrayResult: true to obtain the complete array.

  • When generating or parsing formulas in R1C1 format in code.

Method: evaluateFormula(context, formula, baseRow?, baseColumn?, useR1C1?, preserveArrayResult?)

Parameters:

  • ​​context​​ (Object) – The evaluation context (typically the active sheet object).

  • ​​formula​​ (string) – The formula string to evaluate.

  • ​​baseRow​​ (number, optional) – The base row index for relative references.

  • ​​baseColumn​​ (number, optional) – The base column index for relative references.

  • ​​useR1C1​​ (boolean, optional) – Whether to use R1C1 reference style (default: false).

  • ​​preserveArrayResult​​ (boolean, optional) – If true, returns an array result; otherwise, returns a single value.

Example 1: Obtain the calculation result directly.​

var sheet = spread.getActiveSheet();
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=SUM(Table1[[#Data],[Units]])')
//Result: 84878

Example 2: Using Relative References with baseRow and baseColumn​

var sheet = spread.getActiveSheet();
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=Table1[@[Sales Rep]]',2,0);
//Result: Tom
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=Table1[@[Sales Rep]]',5,0);
//Result: Sal
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=Table1[@[Sales Rep]]',12,0);
//Result: Amy

Example 3: Using R1C1 style with baseRow and baseColumn.

var sheet = spread.getActiveSheet();
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=R[3]C[3]',0,0,true);
//Result: Grape
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=R[3]C[3]',2,1,true);
//Result: 5323

Example 4: Retrieving Array Results Using preserveArrayResult​

var sheet = spread.getActiveSheet();
spread.options.allowDynamicArray = true; 
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=UNIQUE(Table1[[#Data],[Product]])')
//Result: Apple
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=UNIQUE(Table1[[#Data],[Product]])',null,null,null,true)
//Result: [['Apple'],['Grape'],['Pear'],['Banana']]

expressionToFormula: Converts an expression back to a formula string.

Use cases:

  • Dynamically build complex formulas (to avoid the risks of string concatenation), Often used together with formulaToExpression.

  • Parse expressions for custom names and generate the corresponding formulas.

Method: expressionToFormula(context, expression, baseRow?, baseColumn?, useR1C1?)

Parameters:

  • ​​context​​ (Object) – The context (typically the active sheet object).

  • ​​expression​​ (GC.Spread.CalcEngine.Expression) – The expression tree to convert.

  • baseRow​​ (number, optional) – The base row index for relative references.

  • ​​baseColumn​​ (number, optional) – The base column index for relative references.

  • ​​useR1C1​​ (boolean, optional) – Whether to use R1C1 reference style (default: false).

Example: Converting a Named Expression to a Formula String​

var sheet = spread.getActiveSheet();
sheet.addCustomName('averageUnits', '=AVERAGE(Table1[[#Data],[Units]])');
var cname = sheet.getCustomName("averageUnits");
var expr = cname.getExpression();
GC.Spread.Sheets.CalcEngine.expressionToFormula(sheet,expr);
//Result: AVERAGE(Table1[[#Data],[Units]])

formulaToExpression: Parses a formula string into an expression.

Use cases：

  • You need to programmatically parse existing formulas, modify certain parameters or logic, and then regenerate the formulas.

  • Check the cells, functions, or dependencies referenced in formulas for data flow analysis or auditing purposes.

  • When converting between R1C1 and A1 style, you need to first parse the formula into an expression tree and then reconstruct it.

  • Avoid using strings for formula validation; using Expressions allows for better analysis.

Method: formulaToExpression(context, formula, baseRow?, baseColumn?, useR1C1?)

Parameters:

  • ​​context​​ (Object) – The evaluation context (typically the active sheet object).

  • ​​formula​​ (string) – The formula string to evaluate.

  • ​​baseRow​​ (number, optional) – The base row index for relative references.

  • ​​baseColumn​​ (number, optional) – The base column index for relative references.

  • ​​useR1C1​​ (boolean, optional) – Whether to use R1C1 reference style (default: false).

Example: Converting a formula to an expression object.

var sheet = spread.getActiveSheet();
var expr = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, '=CONCAT(Table1[@Region],Table1[@[Sales Rep]],Table1[@Product])',4,0)
//Result:
//expr.functionName is CONCAT
//expr.arguments.length is 3

formulaToRanges: Converts a formula string into an array of referenced cell ranges.

Use cases:

  • Quickly obtain all cell ranges referenced by a formula to build data dependency graphs or perform impact chain analysis.

  • Highlight all cells referenced by a formula in the UI to help users intuitively understand the formula logic.

  • When you need to work with the physical position information of a formula (row and column indexes) rather than its logical structure, this method is more direct and efficient than formulaToExpression.

Method: formulaToRanges(sheet, formula, baseRow?, baseCol?)

Parameters:

  • ​​sheet​​ (GC.Spread.Sheets.Worksheet) – The base worksheet.

  • ​​formula​​ (string) – The formula to analyze.

  • ​​baseRow​​ (number, optional) – The base row index for relative references.

  • ​​baseColumn​​ (number, optional) – The base column index for relative references.

Example: Get all referenced ranges in a formula

var sheet = spread.getActiveSheet();
var result = GC.Spread.Sheets.CalcEngine.formulaToRanges(sheet, '=SUM(A1,A2,A5:C8)');
//Result:
//result.sheetName is Sheet1
//result.ranges:[
//  Range {row: 0, rowCount: 1, col: 0, colCount: 1}
//  Range {row: 1, rowCount: 1, col: 0, colCount: 1}
//  Range {row: 4, rowCount: 4, col: 0, colCount: 3}
//  Range {row: 2, rowCount: 16, col: 4, colCount: 1}
//]

rangesToFormula: Converts an array of ranges into a formula string.

Use cases:

  • When you need to dynamically construct range reference formulas based on runtime data (such as automatically adjusting the range according to user selections or data volume).

  • When handling worksheet references in code, avoid manually concatenating complex reference strings.

  • You need to flexibly switch between relative references and absolute references.

Method: rangesToFormula(ranges, baseRow?, baseCol?, rangeReferenceRelative?, useR1C1?)

Parameters:

  • ​​ranges​​ (GC.Spread.Sheets.Range[]) – The ranges to convert.

  • ​​baseRow​​ (number, optional) – The base row index for relative references.

  • ​​baseColumn​​ (number, optional) – The base column index for relative references.

  • ​​rangeReferenceRelative​​ (RangeReferenceRelative, optional) – Specifies reference relativity (absolute/relative).

  • ​​useR1C1​​ (boolean, optional) – Whether to use R1C1 style.

Example: Convert an array of ranges into the corresponding formula.

var ranges = [
    new GC.Spread.Sheets.Range(0,0,1,1),
    new GC.Spread.Sheets.Range(3,3,5,5),
];
GC.Spread.Sheets.CalcEngine.rangesToFormula(ranges,0,0,GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative);
//Result: 'A1, D4:H8'
GC.Spread.Sheets.CalcEngine.rangesToFormula(ranges,0,0,GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative);
//Result: '$A$1, $D$4:$H$8'
GC.Spread.Sheets.CalcEngine.rangesToFormula(ranges,0,0,GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.colRelative);
//Result: 'A$1, D$4:H$8'
GC.Spread.Sheets.CalcEngine.rangesToFormula(ranges,0,0,GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative, true);
//Result: 'RC, R[3]C[3]:R[7]C[7]'