Spread sheet formula doesn't calculate the results with colon(:)

Spread sheet formula doesn't calculate the results with colon(:)

Posted by: ashok.purty on 7 April 2020, 11:47 pm EST

• 

  ashok.purty

  • Post Options:
  • Link
    Copy

  Posted 7 April 2020, 11:47 pm EST

  Spread sheet formula doesn’t calculate the results with colon( if user inputs the formula manual using keyboard, but works fine in the case of copy paste.

  We are using some custom culture setting like :

  // Sample code
  
  const myCulture = new GC.Spread.Common.CultureInfo();
  
  myCulture.NumberFormat.numberDecimalSeparator = decimalSeparator;
  
  myCulture.NumberFormat.numberGroupSeparator = thousandSeparator;
  
  myCulture.NumberFormat.currencyDecimalSeparator = decimalSeparator;
  
  myCulture.NumberFormat.listSeparator = ‘;’;
  
  myCulture.NumberFormat.arrayListSeparator = ‘:’;
  
  //

  so the format =sum(k5:l5) throws InvalidOperation error if user inputs value using keyword(cell edit mode), but the same formula when 1st written in notepad and paste directly in the cell work fine and execute the formula and renden in the value in spread sheet cell.

  If it is working fine in copy paste operation then same behaviour should be in the case of cell edit mode as both should use the custom culture.

  Could you please let us know how ‘:’ is working fine in case of copy paste so that we can config same for cell edit mode as well.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 12 April 2020, 9:17 pm EST

  Hi Ashok,

  The issue is arising because a different symbol is used for list separator and arrayListSeparator. Using the same symbol should the fix. Further, I would recommend you not to use “:” as the listSeparator symbol because it is a reserved symbol in excel for range references.

  Regards
  
  Sharad

• 

  ashok.purty

  • Post Options:
  • Link
    Copy

  Posted 13 April 2020, 10:40 pm EST

  Hi Sharad,
  
  I am getting mentioned error after implementing your suggested fix.
  
  “The issue is arising because a different symbol is used for list separator and arrayListSeparator. Using the same symbol should the fix.”

  Error :
  
  numberDecimalSeparator, listSeparator and arrayListSeparator should be different in cluture info.

  Also could you please let me know, how it’s working fine in case of copy paste ?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 14 April 2020, 11:53 pm EST

  Hi Ashok,

  In our investigation we found that the error is thrown only if listSeparator = arrayListSeparator = “;” other symbols are working fine. So we have forwarded it to the concerned team for further investigation of the issue(Internal tracking ID: SJS-4135). We will let you know about any updates regarding the same.
  
  Till then if it is possible please use the custom symbols such that listSeparator = arrayListSeparator = “some symbol other than ;” != numberDecimalSeparator.

• 

  ashok.purty

  • Post Options:
  • Link
    Copy

  Posted 16 April 2020, 4:37 pm EST

  Thanks for update
  
  I will try if your suggestion e.g “some symbol other than ;” will work with my current implementation.
  
  Please keep me posting from your team on investigation of this issue.

  Thanks,
  
  Ashok Purty

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 20 April 2020, 8:26 pm EST

  Hi Ashok,

  Dev team has informed us that “:” including some other characters are reserved for calcEngine which is causing the issue.
  
  Further, for a better understanding, the team has asked for additional information regarding the use case that why do you want to set a culture using “:” and “;”, if you are trying to custom culture for some language, could you please inform us about the language too?

  Regards

• 

  ashok.purty

  • Post Options:
  • Link
    Copy

  Posted 21 April 2020, 11:33 pm EST

  Hi Sharad,
  
  But one question is still open
  
  why this is working fine with copy paste operation without edit mode of the cell ??
  
  Could please update me on this ??

  More information about setting culture:
  
  We are setting custom culture for finish language. And as in finish language ‘,’ is considered as decimal separator, that is
  
  why we configured ‘,’ as decimal separator and ‘;’ as list separator.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 22 April 2020, 7:34 pm EST

  Hi Ashok,

  Thank you for providing the additional details. We have shared it with the dev team.

  why this is working fine with copy paste operation without edit mode of the cell ??
  
  It seems that the issue is arising because, formula text box performs some additional operations to support reference highlighting, formula autocomplete etc which is modifying the actual formula. Check the formula after committing the edit value and notice that “:” is removed from the formula. Further, we have also asked the dev team for the actual cause of this issue and will let you know about their response.