Custom function isnt called when from XML

Custom function isnt called when from XML

Posted by: mikael.stridfeldt on 13 September 2020, 6:28 pm EST

• 

  mikael.stridfeldt

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 13 September 2020, 6:28 pm EST

  Hello
  
  I have a custom function called GetEstimationin a xml.
  
  I cant se that the evalute function is called. Function is registred by:
  
  AddCustomFunction

  In other cases i se the evaluate call but not with this xml.

  I attached the project.

  Best regards
  
  Mikael
  
  Spread_Win_doesnt_load_custom_formula.zip

• 

  mikael.stridfeldt

  • Post Options:
  • Link
    Copy

  Posted 14 September 2020, 1:56 am EST

  I can fix this issue by looping through all cells and set them like this:

  
              for (int s = 0; s < this.fpSpread1.Sheets.Count; s++)
              {
                  var sheet = this.fpSpread1.Sheets[s];
                  for (int r = 0; r < sheet.RowCount; r++)
                  {
                      for (int c = 0; c < sheet.ColumnCount; c++)
                      {
                          if (sheet.Cells[r, c].Formula != "")
                          {
                              if ((sheet.Cells[r, c].RealText == "#NAME?") || (sheet.Cells[r, c].RealText == "#NAMN?"))
                              {
                                  sheet.Cells[r, c].Formula += "";
                              }
                          }
                      }
                  }
              }
  

  But why is it even needed.

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 15 September 2020, 1:47 am EST

  Hi,

  You are adding your custom function after loading the XML file. Once the XML file is loaded, all the formulas are parsed. At this point, the “GetEstimation” function is not known (since it’s added later).
  
  Even if the functions are re-evaluated, only the parsed formula is evaluated (and the parsed formula does not know anything about “GetEstimation”).

  When the formula for any cell is changed (like you have done by setting the formula again), the formula is parsed again. Since the definition for “GetEstimation” is available at this point, the formula is evaluated correctly.

  To avoid this, you need to add the custom function before loading the XML file:

  fpSpread1.AddCustomFunction(new GetEstimation());
  this.fpSpread1.Sheets.Add(fpSpreadToOpenFile.Sheets[0]);
  

  Also, note that the new AddCustomFunction on FpSpread takes an object of GrapeCity.CalcEngine.Function.

  Regards,
  
  Jitender

• 

  mikael.stridfeldt

  • Post Options:
  • Link
    Copy

  Posted 15 September 2020, 4:40 am EST

  Hello
  
  Thanks for the answer. Im aware of the new function that looks great where you can add the functions before load to xml.

  But in the old way with the custom function on each sheet how can you add them before you load the xml. ? What is the old way to do in when you have it on the sheets.

  //This is what i come up with.
  
  this.fpSpread1.Open(xml_file);
  
  this.fpSpread1.Sheets[0].AddCustomFunction(new GetEstimation());
  
  Call something to trigger formulas to find the “GetEstimation”. What and how ? (We are currently using Farpoint 8.2 without the new addcustom function on the spread)

  Best Regards
  
  Mikael

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 15 September 2020, 5:13 pm EST

  Hi Mikael,

  This is a limitation in the older versions of Spread. In older versions, it was not possible to add a custom function before loading an XML file.

  Regards,
  
  Jitender

• 

  mikael.stridfeldt

  • Post Options:
  • Link
    Copy

  Posted 15 September 2020, 5:25 pm EST

  Hello Jitender
  
  So for older versions what are you suppose to add after the code:
  
  this.fpSpread1.Open(xml_file);
  
  this.fpSpread1.Sheets[0].AddCustomFunction(new GetEstimation());

  Something needs to be called to trigger evaluate the formulas ?

  best regard
  
  Mikael

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 15 September 2020, 6:47 pm EST

  Hi Mikael,

  Something needs to be called to trigger evaluate the formulas ?
  
  It’s not enough to re-evaluate the formulas. The formulas need to be parsed again.
  
  In older version, the only way to trigger this is to change the formula in some way (like you mentioned in your second post to this thread).

  Regards,
  
  Jitender