GetDependents Performance

Aspose.Cells Product Family
1

I’m using Cell.GetDependents and the runtime is incredibly slow. Is there any more efficient way to get the dependents of a cell? And is there any room on Aspose’s end to further optimize this?

2

@kevin.shaw,

Could you please share your sample code (runnable) and template file to show the performance issue regarding Cell.GetDependents method. We will check your issue soon.

PS. please zip the Excel file or resource file(s) prior attaching here.

3

Hey Amjad, thanks for getting back.

I’m working with a Financial Company called Canalyst, and so the code I’m working with is deeply embedded in one of our tools that works with excel. I therefore unfortunately can’t share the code I’m working with or provide one of the models I’m working with. I’m sorry for the little amount of information I’ve been able to provide-- I’ll ask my team if there is anything I can bring up to narrow down the issue.

I believe it has something to do with either the excel CHOOSE or INDEX function, though (very likely INDEX, when referring to a named reference).

4

@kevin.shaw,
For every invocation of Cell.GetDependents(), we need to traverse and check all formulas in the workbook one by one, so if there are large amount of formulas and the formulas are complicated, the process will be slow. For your situation, we think the best way is to enable the formula calculation chain:
Workbook.Settings.FormulaSettings.EnableCalculationChain = true;

And then make a complete calculation for the Workbook:
Workbook.CalculateFormula(…)

Now you can get all dependents by:
Cell.GetLeafs(false)

If it is not suitable for you to enable the formula calculation chain or perform calculation, then you may try another a bit more complicated solution:

  1. traverse all cells to gather the precedents map by Cell.GetPrecedents()
  2. with the precedents map, you can build the dependents map, or search the dependents for cells one by one.
5

Hi Johnson,

Thanks for this! Some members on my team were considering trying something similar, so they were reassured by your message and will investigate the solutions you’ve proposed. Thank you for the help so far, and I’ll be sure to let you know if there is anything else we might need! :slight_smile: