Excell -Cell's Precedents detail executed long time

Aspose.Cells Product Family
1

my WB has 320 MB file ; while find the cell’s precedents details; not complete the find the precedents details. please provide your suggestions.

Note: if small size files able to find to precedent details.

Code:

ReferredAreaCollection ret=cell.getPrecedents();

2

@JK2023
If your sample file is too large, you can try using memory preference mode to process file data. Please refer to the following documents.

Also, would you like to provide sample files? This is very helpful for us to locate the issue. Due to the size limit of uploaded files on the forum, you can first upload the files to the cloud and then share the link with us.

3

@JK2023
The time-consuming execution of Cell.getPrecedents() may be due to the lengthy execution of many complex formula calculations in the file. You can also try the following solutions. Firstly, by setting FormulaSettings.EnableCalculationChain to true, then call Workbook.CalculateFormula() method, and finally obtain the precedents in the formula calculation.

The sample code as follows:

//load your sample file
Workbook book = new Workbook(filePath + "sample.xlsx");
book.Settings.FormulaSettings.EnableCalculationChain = true;
book.CalculateFormula();

//get your cell
Cell currCell = book.Worksheets[0].Cells["A1"];
IEnumerator iter = currCell.GetPrecedentsInCalculation();

Hope helps a bit.

4

tried added the below lines and executed the large file(175MB) , not get the Precedents details but able to get the Dependents details.

wb.getSettings().getFormulaSettings().setEnableCalculationChain(true);

5

@JK2023
Thank you for your feedback. Would you like to provide sample files? This is very helpful for us to locate the issue. Due to the size limit of uploaded files on the forum, you can upload the files to the cloud and share the link with us.

6

1.Still, Around 150MB file fetching precedents details taking more than 20 minutes. Please suggest.
2. can the dependents information be used to bring the precedents details ?
Cell[] depents = cell.getDependents(true);

Thanks.

7

@JK2023

  1. Have you tried the solution with apis(GetDependentsInCalculation(bool), GetPrecedentsInCalculation()) we suggested here to see whether you can get better performance?

For Cell.GetDependents(), when you calling it for one cell, all other cells with formulas in the workbook will be scaned so it is a time-consumed process when there are large amount of formulas.

  1. Sure, you can build the tree of precedents according to the details of dependents. If so, we think you need to manage all dependents of all cells by your own data structure, and then build the precedents from it.