Problem refering to dynamically loaded sheets

Problem refering to dynamically loaded sheets

Posted by: joni.dewinter on 20 August 2021, 1:16 pm EST

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 20 August 2021, 1:16 pm EST

  Hello

  We’re trying to achieve the following with Spread.NET V14 in ASP.NET MVC:
  
  We have a screen where a user has full control on the spread component, and thus has the ability to create multiple sheets and refer from one sheet to another.
  
  There is second screen where the user can edit each sheet individually (where we only load the sheet content of that specific sheet).
  
  There is a third screen which loads all sheets into a single spread instance.

  Here we face an issue:
  
  Say the user creates two sheets: Sheet1 and Sheet2, and refers from Sheet1 with the following formula: =Sheet2!A1
  
  This works fine in the first and second screen.
  
  On the third screen, we load Sheet1 and Sheet2 sequentially, but this leads to initially showing #REF! in the cell which refers to cell A1 of Sheet2.
  
  The cell value in the back-end also shows #REF! in the formula, text and value property.
  
  Reversing the sheet loading order doesn’t solve the issue.
  
  Neither do the methods Recalculate and RecalculateAll.

  However, calling the javascript Update-method seems to re-evaluate the formula and eventually shows the right value, but this workaround isn’t always possible for us.

  Is there any way to let the formula evaluate correctly using the .NET Spread API?
  
  Thanks in advance!

  Kind regards
  
  Joni

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy

  Posted 20 August 2021, 1:30 pm EST

  Hello

  We just got one step closer using https://www.grapecity.com/spreadnet/docs/v14/online-asp/FarPoint.Web.Spread~FarPoint.Web.Spread.SheetView~LoadFormulas.html: this sets the correct formula in the cell, but doesn’t evaluate to the correct value yet.

  Kind regards
  
  Joni

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 23 August 2021, 7:12 am EST

  Hi Joni,

  As per my understanding,

  Say the user creates two sheets: Sheet1 and Sheet2, and refers from Sheet1 with the following formula: =Sheet2!A1
  
  This works fine on the first and second screens.

  This should not work on the second Screen(spread) since it has only a single sheet opened. Could you please share a sample that demonstrates the issue so that we could investigate it further and assist you accordingly?

  Further for the third screen. Reversing the order of the loading sheet should solve this issue. Please provide a working sample for investigation so that we could have a better understanding of the issue and help you accordingly.

  Regards
  
  Avinash

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy

  Posted 23 August 2021, 9:04 am EST

  Hello Avinash

  Thanks for your reply.
  
  You’re right that the cell reference doesn’t work in the second screen due to the missing sheet, I didn’t pay much attention to describing this screen because it is working as we expect it to work :).
  
  Our real issue was in the third screen, and the key to solving it seems, as you pointed out correctly, the reversed load order, but followed by a call to LoadFormulas.
  
  This gives us the correctly evaluated cell.
  
  Could you confirm that this is the desired approach?
  
  The only scenario in which this approach may not be sufficient is when Sheet1 refers to Sheet2 and Sheet2 refers to Sheet1.
  
  I’m quite sure we don’t have this use case at the moment, but do you have any advice on how to solve that in case we need it in the future?
  
  Thanks in advance!

  Kind regards
  
  Joni

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 24 August 2021, 7:15 am EST

  Hi Joni,

  The only scenario in which this approach may not be sufficient is when Sheet1 refers to Sheet2 and Sheet2 refers to Sheet1.

  Yes, this will cause the issue since both the sheet is dependent to each other for this recommended approach would be to call the Update method after loading all the sheets so that UI should get updated.

  Regards
  
  Avinash

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy

  Posted 24 August 2021, 7:56 am EST

  Hello Avinash

  Thanks for the clarification, I’ll mark the post with the solution as answer.

  Kind regards
  
  Joni