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, A12, A1*3)"); }

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

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

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

Product Support Forum | Print this page