Tracing Precedents and Dependents for a formula in a given Cell

Aspose.Cells Product Family
1

Hi,

In Excel, using the formula toolbar, I can trace precedents and dependents for a cell. Is there anyway I can achieve this using the Aspose API?

Thanks,
Shashi

2

Hi,

Well, currently there is no such APIs for the task. I think you can manually check the formulated cell to browse those cells to get the prece. and depend.

We may consider this in our future versions.

Thank you.

3

Amjad,

Thanks for the reply. We have a requirement in our project to change the dependent cell formulas based on the modifications done by the client on the current cell. For e.g. if Cell C1 is dependent on C3 and C4 using some formula, then if C1 is filled with a value (formula overriden) then I need to change C3 and C4 to do some balancing based on some business rules.

When you say that I can check manually, how exactly can I achieve the above? Is there any other workaround that you would like to suggest for achiveing the above functionality?

Thanks,
Shashi

4

Hi Shashi,

It's a very complex feature. We can get which cells a cell depends on. It is hard to get which cells depends on a cell.

5

Hi Warren,

From your statement above, can you please confirm whether the following scenario is difficult to implement:

a) I have a Cell C1 which has the formula =(B1*22)/(M2 * N32)

b) Now if I want to find which Cells C1 depends on, then it would be B1, M2 and N32.

You mentioned "We can get which cells a cell depends on". What can we get here? Can you please provide an example?

Thanks,
Shashi

6

Hi Shashi,

We can support your scenario. "We can get which cells a cell depends on" is same as your scenario. It will take us some time to implement it.

But if you want to find which cells depend on B1, we could not support it. We have to search all formula in the workbook to find which cell depend on it.It is not a good solution. We don't want to support it.

7

Warren,

If you can support the sceanrio what I mentioned, then that would solve our problem. We do not require the second scenario and as you mentioned it would not be a good solution. We dont have any requirement for that.

Can you project any approximate timelines by which you can implement this feature.

Thanks,
Shashi

8

Hi Shashi,

We will add the following method to implement this feature.

/**
* Get all cells which this cell's formula depends on.
* if the cell is not a formula ,it will return null.
* if the cell's formula does not other cell ,it will return null.
* @return all cells which this cell's formula depends on.
*/
public Cell[] getPrecedents()

It will be available tomorrow.Thanks for your patience.

9

Hi,

Please try this fix.

10

Thanks a lot Warren. This was really quick. It works perfectly!

Shashi

11

Warren,

In my earlier post, I had talked about requirement for finding the dependent cells. We have a requirement where we also need to find the dependency of a particular cell on other cells. As our customer business rules are embedded inside the formulae, we would want to find out the dependency and execute some rules which cannot be handled in Aspose.

Is there any efficient way of finding the dependents?

Thanks,
Shashi

12

Hi Shashi,

Please try this fix.Please try the following method.

/**
* Get all cells which formula depends on this cell.
* if no formula depends this cell ,it will return null.
* @param isAll If False,only searches all cells in this worksheet.
* If true ,searches all cells in the workbook.
* @return all cells which formulas depends on this cell.
*/
public Cell[] getDependents(boolean isAll)

13

Hi Warren,

Thanks for implementing this. It works as intended!!! One quick question. I see from the javadoc that this method scans through the complete worksheet for getting the dependent cells. Do you want to point us towards DOs and DONTs which my team can take care of before using this method? i.e. Also will it be efficient to scan through all the cells and keep the dependencies in an in-memory cache?

Thanks,
Shashi

14

Hi Shashi,

We will ans your query soon.

Thanks for being patient!

15

Hi Shashi,

If all depedent cells are in the same worksheet, please make isAll = false. It will be efficient to get dependent cells.

16

Hi Warren,

We have the same requirement in our project. Our requirement is when the value of a particular cell is modified, what cells in the worksheet are impacted by that change. But, I found that we are not able to get all the dependent cells through this API.

1. Does not provide cells that are transitively dependent

For eg. I have D9=C9+10; E11=D9+10. Now with this I have D9 directly dependent on C9, and E11 dependent on D9 (and hence transitively on C9 i.e. E11->D9->C9).

2. Does not provide the dependency through ranges.

e.g. I define a range called myrange {C8, C9}. If I have a formula on G17 as G17=SUM(myrange). But the dependent API does not return G17.

The above situation is produced in the attached excel files. (Excel comments pictorically explains the problem)

The code I have used for testing:

package devtest;

import java.io.InputStream;

import com.aspose.cells.Cell;

import com.aspose.cells.Workbook;

import com.aspose.cells.Worksheet;

public class ReportCellDependencyTracker {

public static void main(String[] args) throws Exception {

new ReportCellDependencyTracker().test() ;

}

public void test() throws Exception {

Workbook wb = new Workbook() ;

InputStream is = this.getClass().getResourceAsStream(

"/designer/DependencyTest.xls");

wb.open(is);

Worksheet ws = wb.getWorksheets().getSheet("Sheet2");

Cell cell = ws.getCells().getCell("C9");

Cell[] dependentCells = cell.getDependents(false);

printData(cell, dependentCells) ;

}

public void printData(Cell cell, Cell[] dependentCells) {

System.out.print(cell.getName()+"->");

for (int i=0; i<dependentCells.length; i++) {

System.out.print(dependentCells[i].getName()+",");

}

}

}

The acutual output obtained:

C9->E8,D9,

Expected Output:

C9->E8,D9,E11,G17

17

Hi,

1,In Excel , only return which cells direct depend on the C9.So it only return D9,E8,G17.

2,We will support Named range in tracing Precedents and Dependents method.Thanks for your patience.

18

Hi Malay and Shashi,

Please try this fix. It supported named range in tracing Precedents and Dependents.

To Shashi: I changed the method Cell[] Cell.getPrecedents() to Object[] Cell.getPrecedents().If the cell depends a large range, I have to init too many cells in the memory in the old method.You can process the range by yourself in the new method.

See following codes to iterate all cells.

Object[] ret = cell.getPrecedents();
if(ret != null)
{
for(Object obj : ret)
{
if(obj instanceof Range)
{
Range range = (Range)obj;
Cell[][] cellArray = range.getCells();
for(int i = 0 ; i < cellArray.length ; i++)
{
for(int j = 0 ; j < cellArray[i].length ; j++)
{
System.out.println(cellArray[i][j].getName());
}
}

}else if(obj instanceof Cell)
{
System.out.println(((Cell)obj).getName());
}
}
}

19

Hi,

Verified and the fix works perfectly. Thanks a ton!!

Thanks,

Malay