We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

how to distinguish between error and calculated value when calcQuick is used.

Hello,
We use Calculate's calcQuick to parse and calculate formulas. What we found is: when something goes wrong with calculation, syncfusion library returns either 1) undefined value, or 2) some human readable string such as "#name", "#value", or 3) some descriptive text such as "wrong number of arguments".  This, however, causes a problem for us. When something is returned from syncfusion calculate library, e.g #value, we cannot tell if it is normal calculation result or it is an error. Ideally  we would like to get an error code (machine code) from formula calculation, so that we can return the calculated result to upper level function for further handling if calculation is ok. If there is an error, we pop up an error dialog window asking user to check error(typo) in supplied formula. Is it possible to achieve this? Can you show us example for error handling?

        var calculator = new CalcQuick();
        calculator.getEngine().setExcelLikeComputations(true); 
        calculator.setAutoCalc(false);
        calculator.setKeyValue("formula", "=" + expression);
        calculator.refreshAllCalculations();
        var val = calculator.getKeyValue("formula");
        if (there_is_an_error) {
          show_error_dialog_window;
        } else { 
         return val;  }




3 Replies

MG Mohanraj Gunasekaran Syncfusion Team August 29, 2017 02:47 PM UTC

Hi Hui, 

Thanks for using Syncfusion product. 

We can understand your scenario. By default, we have handled the error in our source and return the value like (#Name, #Value, (etc)) like MS-Excel. So, there is no support to get the errors in formula calculation. 

By default, following error strings are available in ejCalculate, 

  • Error Stings (Occurs due to pass the invalid parameters)
  • Formula Error Strings (Occurs to give the invalid formulas like missed parenthesis, invalid formula, (etc))

You can get this error string from CalcEngine using formulaErrorStrings property and getErrorStrings method. Please refer to the below code example, 
 
Code example 
var formulaValue = document.getElementById("txtBoxA").value; 
var index = calculator.getEngine().getErrorStrings().indexOf(formulaValue.toString()); 
var index2 = calculator.getEngine().formulaErrorStrings.indexOf(formulaValue.toString()); 
if (index > -1) 
  window.alert("Enter the valid parameters"); 
else if(index2 > -1) 
   window.alert("Enter the valid formula"); 
 

 
Note: 
There is no support to get the reason of the error strings. This errors strings implemented based on the MS-Excel. So please refer the below link to get the definition of error strings. 

Regards, 
Mohanraj G 
 



HZ hui zheng August 30, 2017 01:49 PM UTC

Hello,

Thank you for the example and sample link. However, it is not fully working. For example, when a formula "SQRT(1 + ad2 )" is used, i got the error message "TypeError: args.split is not a function" and this error is not actually caught (index and index2 is -1).

I wonder if calcQuick(ejCalculate) can be configured to throw exception in case of  any error (instead of checking the calculated value against several error lists as you suggested in your example).

Thanks!

Hui




MG Mohanraj Gunasekaran Syncfusion Team August 31, 2017 02:14 PM UTC

Hi Hui, 

Sorry for the inconvenience caused. 

We can understand your scenario. As we updated earlier, we don’t have support to get the root cause of the thrown errors (#VALUE!,  #NAME?,..). So that we have provided the customization to get the error messages from (ErrorStrings and FormulaStrings) collection for the errors (#NAME?,#VALUE!,..). If you use “1+AD2” string SQRT formula, “AD2” considered as sheet reference. But in provided sample, we do not have any sheet reference so only this issue occurs as this is invalid use case.  In our previous suggested options (Error Stings and FormulaErrorString) for some valid and defined use cases. But in your scenario, you have tried to calculate the SQRT for mismatched value which is not a valid case.  

Regards, 
Mohanraj G 


Loader.
Up arrow icon