Tracing Precedents and Dependents in Xlsx4j

Aspose.Cells - Tracing Precedents and Dependents

Complex financial worksheets, especially ones developed in collaboration, can hide the most embarrassing errors. Checking formulas for accuracy and finding the source of an error can be difficult when the formula uses precedent cells and dependent cells.

  • Precedent cells are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.
  • Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To make the spreadsheet easy to read, you might want to clearly show which cells on a spreadsheet are used in a formula. Similarly, you may want to extract the dependent cells of other cells.

Aspose.Cells allows you to trace cells and find out which are linked.

Tracing Precedents

Java

  

 //Instantiating a Workbook object  

Workbook workbook = new Workbook(dataDir + "workbook.xls");  

Cells cells = workbook.getWorksheets().get(0).getCells();  

Cell cell = cells.get("A12");  

//Tracing precedents of cell A12.  

//The returned array contains ranges and cells.  

ReferredAreaCollection ret = cell.getPrecedents();  

//Printing all the precedent cells' names.  

if(ret != null)  

{  

  for(int m = 0 ; m < ret.getCount(); m++)  

  {  

    ReferredArea area = ret.get(m);  

    StringBuilder stringBuilder = new StringBuilder();  

    if (area.isExternalLink())  

    {  

        stringBuilder.append("[");  

        stringBuilder.append(area.getExternalFileName());  

        stringBuilder.append("]");  

     }  

     stringBuilder.append(area.getSheetName());  

     stringBuilder.append("!");  

     stringBuilder.append(CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn()));  

     if (area.isArea())  

      {  

          stringBuilder.append(":");  

          stringBuilder.append(CellsHelper.cellIndexToName(area.getEndRow(), area.getEndColumn()));  

      }  

      System.out.println("Tracing Precedents: " + stringBuilder.toString());  

   }  

}

Tracing Dependents

Java

  

 //Get the A5 cell  

Cell c = cells.get("A5");  

//Get all the dependents of cell A5  

Cell[] dependents = c.getDependents(true);  

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

{  

     System.out.println("Tracing Dependents: " + dependents[i].getWorksheet().getName() + " " + dependents[i].getName() + ": " + dependents[i].getIntValue());  

}

Download Running Code

Download Sample Code

Sort Data in xlsx4j