How to get culture specific formula of a cell?

How to get culture specific formula of a cell?

Posted by: viswanath.malepati on 5 June 2018, 5:09 am EST

• 

  viswanath.malepati

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 June 2018, 5:09 am EST

  Hi,

  We are tying to change culture of spread and sheet rendering according to the new culture.

  But now, when we are trying to read cell formula like activeSheet.getCell(1,2).formula() and this returns formula in en-US but not in culture that we have set. for example: -=IF(TRUE; 1; 2) is formula in a specific culture. but when we get formula it returns =IF(TRUE, 1, 2)

  So, is there a way to get formula specific to culture and not in en-US?

  Thanks,
  
  Viswanath

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 6 June 2018, 2:31 am EST

  Hello Viswanath,

  It sounds like you will just need to create a custom culture and set that as the culture for SpreadJS. Here is documentation about that:

  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#culturecustom.html

  Hope it helps.

  Thanks,
  
  Reeva

• 

  viswanath.malepati

  • Post Options:
  • Link
    Copy

  Posted 6 June 2018, 5:15 am EST

  Hi Reeva,

  I tried setting culture as mentioned in the link which you shared, that’s what I have mentioned in 1st line of my post.

  Even after setting culture, when I get cell.formula(), spread is returning English formula.

  Please find the attached html, in which I am setting a different culture as mentioned in the link. Now in any random cell, enter a formula =IF(TRUE;1;2). After this, try to get the cell and read formula like: activeSheet.getCell(row,col).formula() → this will give =IF(TRUE,1,2).

  So, in this case, how to get user entered formula, which is: =IF(TRUE;1;2)

  Note: My observation is that culture is used for representational purposes. But when we read or set values through code its only in en-US. Like after we change culture, even when we try to set activeSheet.getCell(row,col).formula(“IF(TRUE;1;2)”) it doesnt work. Where was manually entering same formula works. Correct me if im wrong.

  Thanks,
  
  Viswanath

• 

  viswanath.malepati

  • Post Options:
  • Link
    Copy

  Posted 6 June 2018, 5:15 am EST

  spread11.zip

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 12 June 2018, 5:37 am EST

  Hello,

  This is a design behavior. When using code to get formula or set formula, our formula was base on the Invariant culture(same as the en-us culture).

  Hence, we use ‘en’ culture formula string to get/set formula by code, such as activeSheet.setFormula(1,1,“IF(TRUE,1,2)”,GC.Spread.Sheets.SheetArea.viewport);

  But when using UI(such as double click a cell to enter the editing state) to watch or input the formula, the formula string was base on the current culture. It’s should be “=IF(TRUE;1;2)”.

  Hope it clarifies.

  Thanks,
  
  Reeva

• 

  viswanath.malepati

  • Post Options:
  • Link
    Copy

  Posted 12 June 2018, 8:10 am EST

  Got it. And you are right that when we double click, it shows culture specific formula.

  So, can you provide any API to get culture specific formula (which is shown when we double click)?

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 14 June 2018, 5:05 am EST

  Hello,

  Unfortunately, I couldn’t find a way to get the culture specific formula using any API.
  
  Hence, we are discussing this issue with our concerned team and we will get back to you as soon as we hear anything from them.

  Tracking id for your issue is #259520

  Thanks,
  
  Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 14 June 2018, 6:24 am EST

  Hello,

  For culture specific formula you could use Worksheet.getFormulaInformation API.

  The return values include:-
  
  hasFormula
  
  isArrayFormula
  
  formula
  
  formulaWithCulture

  function getFormula() {
  
  activeSheet.setFormula(1, 1, “if(true,1,2)”, GC.Spread.Sheets.SheetArea.viewport);
  
  var test = activeSheet.getFormulaInformation(1, 1, GC.Spread.Sheets.SheetArea.viewport);
  
  alert('formula: ’ + test.formula);
  
  alert('formula with culture: ’ + test.formulaWithCulture);
  
  }

  Hope it helps.
  
  Thanks,
  
  Reeva

  P.S. Our online documentation for the same API will be updated soon.
  
  spread11.zip

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 18 June 2018, 6:59 am EST

  Hello,

  We have updated our documentation, you can check the same here:-
  
  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.Worksheet~getFormulaInformation.html

  Thanks,
  
  Reeva

• 

  narendrareddy.bandi

  • Post Options:
  • Link
    Copy

  Posted 17 October 2018, 6:57 am EST

  Thanks for your time. But it will not solve my problem.

  code snippet.

  var sheet = spread.getActiveSheet();
  
  var cell = sheet.getCell(2, 2);

  			if (spread.currentCulture != spread.constants.defaultCulture) {
  				spread._rootNamespace.Common.CultureManager.culture(spread.constants.defaultCulture);
  			}
  			formulaValue = "=IF(C3>0;100;200)" // this formula is entered in text box out side of the spread.
  			//spread.currentCulture = "de-DE"
  			//spread.constants.defaultCulture = "en-US";
  			if (spread.currentCulture != spread.constants.defaultCulture) {
  							spread._rootNamespace.Common.CultureManager.culture(spread.currentCulture);
  			}
  
  			
  			try {					
  				cell.formula(formulaValue); // here i am not able to assign the formula. 
  			}
  			catch (e) { }
  			
  			var serverLocaleFormula = spread.getEnFormula(2, 2, sheet);
  			if (serverLocaleFormula)
  				formulaValue = serverLocaleFormula;
  

  I am trying to get the equllent en-US formula against differen culture.

  Thanks,
  
  Narendra

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 18 October 2018, 3:41 am EST

  Hello Narendra,

  Can you please share a small working sample replicating the issue so that we can investigate this issue further. We need to debug your code at our end.

  Thanks,
  
  Reeva