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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:

Created:

Updated:

Platform:

Replies:

132351 Aug 28,2017 10:13 AM UTC Aug 31,2017 02:14 PM UTC JavaScript 3
loading
Tags: General
hui zheng
Asked On August 28, 2017 10:17 AM UTC

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




Mohanraj Gunasekaran [Syncfusion]
Replied On 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 
 


hui zheng
Replied On 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



Mohanraj Gunasekaran [Syncfusion]
Replied On 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 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;