Allow Users to Enter Formulas

Users can enter a formula in a cell by starting with an equals sign (=) or a plus sign (+). Type the preferred sign and then the formula such as =sum(A1+B1) or +sum(A1+B1). You can allow or prevent the user from entering formulas with the options.allowUserEditFormula property.

The plus sign (+) functions the same as the equal sign (=) and Excel users who are accustomed to using the plus sign don't have to change their behavior when using SpreadJS. The equal sign is still automatically added before so that formulas are converted to work as expected. For example, if you enter "+SUM(1,2)" when editing, it will be "=+SUM(1,2)" when displayed again.

Users should be aware of the following behavior when using the plus sign (+) for entering formulas:

• If the value entered is like "+1", then this is a number and not a formula and is displayed as "1".

• If the value is "+", then this is a string and not a formula.

• If the value entered is like "=++1+++1", the formula is "=++1+++1".

Note: The minus (-) sign has the same behavior as the plus (+) sign.

The following code sample allows users to enter formulas.

window.onload = function()
{
  var spread =
  new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
  var activeSheet = spread.getActiveSheet(); 

  // Allow users to enter mathematical formula.
  spread.options.allowUserEditFormula = true;
  activeSheet.setValue(0, 0, 1000);
  activeSheet.setValue(1, 0, 200);
}

SpreadJS also gives you the option to apply preset formatting of reference cells on the target cell to further expedite the formatting process. However, you can avoid using preset formatting by setting the formulaFormatHint option to false as shown in the following code.

spread.options.formulaFormatHint = false; 

The following GIF illustrates the behavior of formulaFormatHint option: