Prevent deletion of invalid formulas

Prevent deletion of invalid formulas

Posted by: eulrich on 2 April 2024, 12:22 pm EST

• 

  eulrich

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 2 April 2024, 12:22 pm EST

  In excel, if a user presses enter after typing an invalid formula, an error message appears but the formula is retained and the user is returned to the formula editor.

  In SJS, after the error message appears, the invalid formula is deleted and their work is lost.

  Is there a way that we can simulate the Excel behavior, where we retain the formula and return the user to the formula editor?

  Thanks!

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 3 April 2024, 3:21 am EST - Updated 3 April 2024, 3:26 am EST

  Hi,

  You could set the “allowInvalidFormula” property to true to let the user input invalid formula string. Later, you could handle the EditEnded and show the dialog to the user and use the SpreadJS APIs to continue editing the cell when the dialog is closed.

  Refer to the following code snippet:

  // Allow Users to Input Invalid Formula
  spread.options.allowInvalidFormula = true;
  spread.bind(GC.Spread.Sheets.Events.EditEnded, function (sender, args) {
      let editingText = args.editingText;
      if (editingText) {
          try {
              GC.Spread.Sheets.CalcEngine.evaluateFormula(args.sheet, editingText);
          }
          // Invalid Formula
          catch (e) {
              // Show the Message to the User
              GC.Spread.Sheets.Designer.showMessageBox("Invalid Formula", "SpreadJS Designer", GC.Spread.Sheets.Designer.MessageBoxIcon.warning, () => {
                  // Start Editing the Sheet when the User Clicks the "Ok" button
                  args.sheet.startEdit();
              });
  
              setTimeout(() => {
                  args.sheet.setSelection(args.row, args.col, 1, 1);
                  spread.focus(false);
              })
          }
      }
  })

  Sample: https://jscodemine.mescius.io/share/FWKuNLrAPEqXP-FHlQ1FJg/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  References:

  EditEnded Event: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#editended

  evaluateFormula method: https://developer.mescius.com/spreadjs/api/modules/GC.Spread.Sheets.CalcEngine#evaluateformula

  showMessageBox method: https://developer.mescius.com/spreadjs/api/designer/modules/GC.Spread.Sheets.Designer#showmessagebox

  allowInvalidFormula property: https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorkbookOptions#allowinvalidformula

  Regards,

  Ankit