Custom function isnt called when from XML

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

  • 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
  • Replied 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.
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Marked as Answer

    Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels