Documents for Excel, Java Edition Documentation
Manage Formulas / Precedents and Dependents
In This Topic
    Precedents and Dependents
    In This Topic

    Sometimes, in worksheets containing lots of formulas, it becomes difficult to identify which cell values or ranges are taken into consideration while doing calculations or how the result is calculated. Also, which cells are impacted if a cell value is modified. Hence, comes the need for precedent and dependent cells or ranges. GcExcel library provides getPrecedents and getDependents methods in the IRange interface, which help in identifying the precedent and dependent cells or ranges in excel worksheets.

    For example, the value in cell A1 =10, A2 = 20 and B1 = Sum (A1+A2), then A1 and A2 are the precedent cells of B1 which are used for calculating the value of B1. Also, B1 is the dependent cell for A1 and A2 whose value is calculated based on values of cell A1 and A2.

    Precedents

    Refer to the following example code to get the Precedent ranges in a worksheet.

    Java
    Copy Code
    private static void GetPrecedents() {
        // Initialize workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        // Set Formula in Cell E2
        worksheet.getRange("E2").setFormula("=sum(A1:A2, B4,C1:C3)");
        // Set Value of Cells
        worksheet.getRange("A1").setValue(1);
        worksheet.getRange("A2").setValue(2);
        worksheet.getRange("B4").setValue(3);
        worksheet.getRange("C1").setValue(4);
        worksheet.getRange("C2").setValue(5);
        worksheet.getRange("C3").setValue(6);
    
        // Get Precedent cells of Range E2
        for (IRange item : worksheet.getRange("E2").getPrecedents()) {
            item.getInterior().setColor(Color.GetPink());
        }
    
        // Saving workbook to Xlsx
        workbook.save("36-Precedents.xlsx", SaveFileFormat.Xlsx);

    The below image shows the precedent ranges (highlighted in pink).

     

    Dependents

    Refer to the following example code to get dependent ranges in a worksheet.

    Java
    Copy Code
    private static void GetDependents() {
        // Initialize workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        // Set Value of Cell A1
        worksheet.getRange("A1").setValue(100);
        // Set Formula in Cell C1
        worksheet.getRange("C1").setFormula("=$A$1");
        // Set Formula in Range E1:E5
        worksheet.getRange("E1:E5").setFormula("=$A$1");
    
        // Get Dependent cells of Range A1
        for (IRange item : worksheet.getRange("A1").getDependents()) {
            item.getInterior().setColor(Color.GetLightGreen());
        }
    
        // Saving workbook to Xlsx
        workbook.save("35-Dependents.xlsx", SaveFileFormat.Xlsx);

    The below image shows the dependent ranges (highlighted in green).

    All Precedents

    Often multiple precedent ranges are used to calculate cell formula. Refer to the following example code to get all the precedent ranges in a worksheet.

    Java
    Copy Code
    private static void GetAllPrecedents() {
        // Initialize workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
        // Set Formula in Cell E2
        worksheet.getRange("E2").setFormula("=sum(C1:C2)");
        // Set Formula in Cell C1
        worksheet.getRange("C1").setFormula("=B1");
        // Set Formula in Cell B1
        worksheet.getRange("B1").setFormula("=sum(A1:A2)");
        // Set Value of Cells
        worksheet.getRange("A1").setValue(1);
        worksheet.getRange("A2").setValue(2);
        worksheet.getRange("C2").setValue(3);
    
        // Get Precedent cells of Range E2
        ArrayList<IRange> list = new ArrayList<IRange>();
        for (IRange item : worksheet.getRange("E2").getPrecedents()) {
            list.add(item);
        }
    
        while (list.size() > 0) {
            ArrayList<IRange> temp = list;
            list = new ArrayList<IRange>();
            for (IRange item : temp) {
                for (int i = 0; i < item.getRowCount(); i++) {
                    for (int j = 0; j < item.getColumnCount(); j++) {
                        List<IRange> dependents = item.getCells().get(i, j).getPrecedents();
                        if (dependents.size() == 0) {
                            item.getCells().get(i, j).getInterior().setColor(Color.GetSkyBlue());
                        } else {
                            item.getCells().get(i, j).getInterior().setColor(Color.GetLightGreen());
                            list.addAll(dependents);
                        }
                    }
                }
            }
        }
    
        // Saving workbook to Xlsx
        workbook.save("37-GetAllPrecedents.xlsx", SaveFileFormat.Xlsx);

    The below image shows all the precedent ranges (highlighted in blue and green).