Formula string validation

Formula string validation

Posted by: ichioka.yuji on 26 February 2026, 12:16 am EST

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 26 February 2026, 12:16 am EST

  Using SpreadJS, I am validating formula strings entered by users in a form by using the formulaToExpression method.

  https://developer.mescius.com/spreadjs/api/modules/GC.Spread.Sheets.CalcEngine#formulatoexpression

  // Parse formulas
  const formulas = [
    '=1+2',
    '=1+2+',
    '=1+2-',
    '=1+2++',
    '=1+2*',
  ];
  
  for (const formula of formulas) {
    try {
      const expression = GC.Spread.Sheets.CalcEngine.formulaToExpression(null, formula);
      const normalized = GC.Spread.Sheets.CalcEngine.expressionToFormula(null, expression);
      console.log(`${formula}: OK  (${normalized})`);
    }
    catch (error) {
      console.log(`${formula}: ${error}`);
    }
  }

  The results of testing several formulas are as follows:

  =1+2: OK  (1+2)
  =1+2+: OK  (1+2)
  =1+2-: OK  (1+2)
  =1+2++: Invalid Formula
  =1+2*: Invalid Formula

  =1+2+ and =1+2- are invalid formulas (they are not accepted by Microsoft Excel), but SpreadJS returns OK.
  
  Is this the correct behavior?

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 26 February 2026, 5:06 am EST

  Hi,

  We are able to replicate the behavior on our end with the available information. However, we are not yet certain whether this is expected behavior, so we have escalated it to the development team for further review. The internal tracking ID is SJS-33913.

  We will update you as soon as we receive any further information.

  Regards,
  
  Priyam

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 6 March 2026, 6:17 am EST

  Hi,
  
  We discussed this behavior internally with the development team. Currently, SpreadJS follows a fault-tolerant approach when handling formulas. In cases like the example below:

  sheet.setFormula(0, 0, ‘=1+2+’)

  SpreadJS internally corrects the formula to =1+2. instead of throwing an error. This behavior is intentional to avoid failures in different scenarios such as when formulas are loaded from persisted files (e.g., .sjs or .ssjson), entered directly by users, or set programmatically through APIs.

  Changing this behavior to strictly follow Excel validation and throw an exception could introduce significant risks and breaking changes. For example, existing workbooks containing such formulas might fail to load or behave differently, which could impact multiple scenarios including importing files, user input, or API usage.

  Therefore, at this time we recommend keeping the current behavior.

  Regards,
  
  Priyam

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 6 March 2026, 8:36 am EST

  I understand your explanation, and it sounds reasonable. I appreciate the sincerity of your response. My team will explore alternative options to address the issue. Thank you for your support!