SpreadJS 14
Formula Reference / Formula Overview / Cell References in a Formula / Use Cell References
In This Topic
    Use Cell References
    In This Topic

    SpreadJS allows you to perform various operations by using cell references.

    Convert Expression to Formula

    This example converts an expression to formula using the expressionToFormula() method. 

    JavaScript
    Copy Code
    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);
    }                
    

    Convert Formula to Expression

    This example converts a formula to an expression using the formulaToExpression() method.

    JavaScript
    Copy Code
    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); 
    

    Convert Formula to Ranges

    This example converts a formula string to the specified cell range using the formulaToRanges() method.

    JavaScript
    Copy Code
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
    { sheetCount: 1 });
    sheet = spread.getSheet(0);
    
    // Creating cell range using formulaToRanges() method
    cellRanges = GC.Spread.Sheets.CalcEngine.formulaToRanges(sheet, 'B3:D5', 0, 0);
    
    // Getting row/column indexes and rowCount/colCount of range in formula
    console.log("Starting Row Index of cell range 'B3:D5' is " + cellRanges[0].ranges[0].row);
    console.log("Starting Column Index of cell range 'B3:D5' is " + cellRanges[0].ranges[0].col);
    console.log("RowCount of cell range 'B3:D5' is " + cellRanges[0].ranges[0].rowCount);
    console.log("ColCount of cell range 'B3:D5' is " + cellRanges[0].ranges[0].colCount);
    

    Evaluate Formula

    This example evaluates a formula using the evaluateFormula() method. 

    JavaScript
    Copy Code
    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);
    


    Goal Seek Method

    This example uses the goalSeek() method in order to find a value for one cell that produces the desired formula result in another cell.

    JavaScript
    Copy Code
    /* 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%