Apache POI for excel get dependent cells

Question

I am writing a program that reads excel files using apache POI. I'm getting all the values, but I want to know which cells are dependent on others (using the formula for the cell). I've tried using String formula = cell.getCellFormula(), but this just returns me the cell index (eg. H5). Is there any other way I can do this? Here's my code for reading cells:

private void handleCell(int type,Cell cell) 
{

    switch (type) 
    {
        case Cell.CELL_TYPE_STRING:
            System.out.print(cell.getStringCellValue() + "\t\t");
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.print(cell.getNumericCellValue() + "\t\t");
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.print(cell.getBooleanCellValue() + "\t\t");
            break;
        case Cell.CELL_TYPE_FORMULA:
            String form = cell.getCellFormula();
            handleCell(cell.getCachedFormulaResultType(),cell);
            break;
        default :

    }
}

Udo Klimaschewski Udo Klimaschewski · Accepted Answer · 2014-12-11T10:10:44

have a look at org.apache.poi.ss.formula.FormulaParser. It has a static method

public static Ptg[] parse(
    java.lang.String formula,
    FormulaParsingWorkbook workbook,
    int formulaType,
    int sheetIndex)

according to the documentation, it parses a formula string into a List of tokens in RPN order.

The tokens (Ptg = "parse things") can be checked for their type (REF/VALUE/ARRAY) using public final byte getPtgClass().

I have not tested it, but it may be the way to go. Parse the formula, then check each Ptg entry for the type (REF?) and get the destination cell.

See:

https://poi.apache.org/apidocs/org/apache/poi/ss/formula/FormulaParser.html https://poi.apache.org/apidocs/org/apache/poi/ss/formula/ptg/Ptg.html

Apache POI for excel get dependent cells

1 Answers