SpreadJS 14
Features / Formulas / Set Formulas
In This Topic
    Set Formulas
    In This Topic

    In SpreadJS, you can set formula in cells by using setFormula method of Worksheet class. The below image displays resultant values in column B after different formulas are being applied on it. 

    Cell formulas

    Set Formula in Cells

    This example sets formulas in cells.

    JavaScript
    Copy Code
    window.onload = function()
    {
       var spread =
       new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
       {sheetCount:3});
       var activeSheet = spread.getActiveSheet();
       activeSheet.setRowCount(5);
       activeSheet.setColumnCount(2);
       activeSheet.getRange(-1, 1, -1, 1)
       .backColor("lightYellow")
       .width(120);
       activeSheet.setValue(0, 0, 10);
       activeSheet.setValue(1, 0, 20);
       activeSheet.setValue(2, 0, 30);
       activeSheet.setValue(3, 0, 40);
       activeSheet.setValue(4, 0, 50);
    
       // Set SUM function (Sum of all parameter values).
       activeSheet.setFormula(0, 1, "SUM(A1:A5)");
     
       // Set PRODUCT function (Product of all parameter values).
       activeSheet.setFormula(1, 1, "PRODUCT(A1:A5)");
    
       // Set AVERAGE function (Average of all parameter values).
       activeSheet.setFormula(2, 1, "AVERAGE(A1:A5)");
    
      // Set the sum of cell(0,0) and cell(4,0).
      activeSheet.setFormula(3, 1, "A1 + A5");
    
      /* Multiply cell(0,0) by 2 if the value in this cell is greater than 10,
        otherwise multiply it by 3. */
       activeSheet.setFormula(4, 1, "IF(A1>10, A1*2, A1*3)");
    }
    

    Set Formula with Cross Sheet References

    You can reference other sheets in a formula. Taking an example, the following image displays the first sheet.

    Cross references - first sheet

    The following image displays the second sheet.

    Cross references - second sheet

    This example uses cross-sheet references.

    JavaScript
    Copy Code
    window.onload = function()
    {
       var spread =
       new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
       var activeSheet = spread.getActiveSheet();
       var sheet1 = spread.getSheet(0),
       sheet2 = spread.getSheet(1);
       sheet1.name("Sheet1");
       sheet2.name("Sheet2");
           
       sheet1.setValue(0, 0, 1000);
       sheet1.setValue(1, 0, 2000);
       sheet1.setValue(2, 0, 3000);
       sheet1.setValue(3, 0, 4000);
       sheet1.getRange(0, 0, 4, 1).backColor("lightPink");
       sheet2.setValue(0, 0, 5000);
       sheet2.getRange(0, 0, 1, 1).backColor("lightCyan");
    
       // Set a sum formula of "Sheet1: total of cell(0,0) to cell(3,0)" and "Sheet2: cell(0,0)".
       sheet2.setFormula(1, 1, "SUM(Sheet1!A1:A4) + Sheet2!A1");
       sheet2.getRange(1, 1, 1, 1).backColor("lemonChiffon");
    }
    

    Set Subtotal Formula

    You can set SUBTOTAL formula as displayed in the below image.

    Subtotals

    This example uses a subtotal formula.

    JavaScript
    Copy Code
    $(document).ready(function ()
    {
        var spread =
        new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
        {sheetCount:3});
       
        var activeSheet = spread.getActiveSheet();
        activeSheet.setColumnCount(2);
        activeSheet.options.colHeaderAutoText = GC.Spread.Sheets.HeaderAutoText.blank;
        activeSheet.setText(0, 1, "Value", GC.Spread.Sheets.SheetArea.colHeader);
        activeSheet.options.gridline = {showHorizontalGridline: false};
        activeSheet.setText(3, 0, "Sub-Total1");
        activeSheet.setText(7, 0, "Sub-Total2");
        activeSheet.setText(8, 0, "Aggregate");
        activeSheet.getRange(3, -1, 1, -1).backColor("lemonChiffon");
        activeSheet.getRange(7, -1, 1, -1).backColor("lemonChiffon");
        activeSheet.getRange(8, -1, 1, -1).backColor("lightPink");
        activeSheet.setValue(0, 1, 100);
        activeSheet.setValue(1, 1, 200);
        activeSheet.setValue(2, 1, 300);
        activeSheet.setValue(4, 1, 400);
        activeSheet.setValue(5, 1, 500);
        activeSheet.setValue(6, 1, 600);
      
       // Set the sub-total and the aggregate by using SUBTOTAL function.
       activeSheet.setFormula(3, 1, "SUBTOTAL(9,B1:B3)");
       activeSheet.setFormula(7, 1, "SUBTOTAL(9,B5:B7)");
       activeSheet.setFormula(8, 1, "SUBTOTAL(9,B1:B7)");
    });