FileDocCategorySizeDatePackage
HSSFFormulaEvaluator.javaAPI DocApache Poi 3.0.128961Sun Mar 11 12:59:28 GMT 2007org.apache.poi.hssf.usermodel

HSSFFormulaEvaluator

public class HSSFFormulaEvaluator extends Object
author
Amol S. Deshmukh < amolweb at ya hoo dot com > Limitations: Unfortunately, cyclic references will cause stackoverflow exception

Fields Summary
private static final Class[]
OPERATION_CONSTRUCTOR_CLASS_ARRAY
private static final Class[]
VALUE_CONTRUCTOR_CLASS_ARRAY
private static final Class[]
AREA3D_CONSTRUCTOR_CLASS_ARRAY
private static final Class[]
REFERENCE_CONSTRUCTOR_CLASS_ARRAY
private static final Class[]
REF3D_CONSTRUCTOR_CLASS_ARRAY
private static final Map
VALUE_EVALS_MAP
private static final Map
OPERATION_EVALS_MAP
protected HSSFRow
row
protected HSSFSheet
sheet
protected HSSFWorkbook
workbook
Constructors Summary
public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook)

        this.sheet = sheet;
        this.workbook = workbook;
Methods Summary
public org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValueevaluate(org.apache.poi.hssf.usermodel.HSSFCell cell)
If cell contains a formula, the formula is evaluated and returned, else the CellValue simply copies the appropriate cell value from the cell and also its cell type. This method should be preferred over evaluateInCell() when the call should not modify the contents of the original cell.

param
cell
return

        CellValue retval = null;
        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_BLANK:
                retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
                retval.setBooleanValue(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
                retval.setErrorValue(cell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                retval = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
                retval.setNumberValue(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
                retval.setRichTextStringValue(cell.getRichStringCellValue());
                break;
            }
        }
        return retval;
public org.apache.poi.hssf.usermodel.HSSFCellevaluateInCell(org.apache.poi.hssf.usermodel.HSSFCell cell)
If cell contains formula, it evaluates the formula, and puts the formula result back into the cell. Else if cell does not contain formula, this method leaves the cell unchanged. Note that the same instance of HSSFCell is returned to allow chained calls like: 
int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();

param
cell

        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                CellValue cv = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
                switch (cv.getCellType()) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cell.setCellValue(cv.getBooleanValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    cell.setCellValue(cv.getErrorValue());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                	cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(cv.getNumberValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    cell.setCellValue(cv.getRichTextStringValue());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we have already evaluated the formula
                    break;
                }
            }
        }
        return cell;
protected static org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValuegetCellValueForEval(org.apache.poi.hssf.record.formula.eval.ValueEval eval)
Returns a CellValue wrapper around the supplied ValueEval instance.

param
eval
return

        CellValue retval = null;
        if (eval != null) {
            if (eval instanceof NumberEval) {
                NumberEval ne = (NumberEval) eval;
                retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
                retval.setNumberValue(ne.getNumberValue());
            }
            else if (eval instanceof BoolEval) {
                BoolEval be = (BoolEval) eval;
                retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
                retval.setBooleanValue(be.getBooleanValue());
            }
            else if (eval instanceof StringEval) {
                StringEval ne = (StringEval) eval;
                retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
                retval.setStringValue(ne.getStringValue());
            }
            else if (eval instanceof BlankEval) {
                retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
            }
            else {
                retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
            }
        }
        return retval;
protected static org.apache.poi.hssf.record.formula.eval.ValueEvalgetEvalForCell(org.apache.poi.hssf.usermodel.HSSFCell cell, org.apache.poi.hssf.usermodel.HSSFRow row, org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook)
Given a cell, find its type and from that create an appropriate ValueEval impl instance and return that. Since the cell could be an external reference, we need the sheet that this belongs to. Non existent cells are treated as empty.

param
cell
param
sheet
param
workbook
return

        ValueEval retval = BlankEval.INSTANCE;
        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                retval = new NumberEval(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                retval = new StringEval(cell.getRichStringCellValue().getString());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                retval = internalEvaluate(cell, row, sheet, workbook);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                retval = cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE;
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                retval = BlankEval.INSTANCE;
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                retval = ErrorEval.UNKNOWN_ERROR; // TODO: think about this...
                break;
            }
        }
        return retval;
protected static org.apache.poi.hssf.record.formula.eval.EvalgetEvalForPtg(org.apache.poi.hssf.record.formula.Ptg ptg)
returns an appropriate Eval impl instance for the Ptg. The Ptg must be one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, StringPtg, BoolPtg
special Note: OperationPtg subtypes cannot be passed here!

param
ptg
return

        Eval retval = null;

        Class clazz = (Class) VALUE_EVALS_MAP.get(ptg.getClass());
        try {
            if (ptg instanceof Area3DPtg) {
                Constructor constructor = clazz.getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
                retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
            }
            else if (ptg instanceof AreaPtg) {
                Constructor constructor = clazz.getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
                retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
            }
            else if (ptg instanceof ReferencePtg) {
                Constructor constructor = clazz.getConstructor(REFERENCE_CONSTRUCTOR_CLASS_ARRAY);
                retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
            }
            else if (ptg instanceof Ref3DPtg) {
                Constructor constructor = clazz.getConstructor(REF3D_CONSTRUCTOR_CLASS_ARRAY);
                retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
            }
            else {
                if (ptg instanceof IntPtg || ptg instanceof NumberPtg || ptg instanceof StringPtg
                        || ptg instanceof BoolPtg) {
                    Constructor constructor = clazz.getConstructor(VALUE_CONTRUCTOR_CLASS_ARRAY);
                    retval = (ValueEval) constructor.newInstance(new Ptg[] { ptg });
                }
            }
        }
        catch (Exception e) {
            throw new RuntimeException("Fatal Error: ", e);
        }
        return retval;
protected static org.apache.poi.hssf.record.formula.eval.EvalgetOperationEvalForPtg(org.apache.poi.hssf.record.formula.OperationPtg ptg)
returns the OperationEval concrete impl instance corresponding to the suplied operationPtg

param
ptg
return

        Eval retval = null;

        Class clazz = (Class) OPERATION_EVALS_MAP.get(ptg.getClass());
        try {
            Constructor constructor = clazz.getConstructor(OPERATION_CONSTRUCTOR_CLASS_ARRAY);
            retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
        }
        catch (Exception e) {
            throw new RuntimeException("Fatal Error: ", e);
        }
        return retval;
voidinspectPtgs(java.lang.String formula)
debug method

param
formula
param
sheet
param
workbook

        FormulaParser fp = new FormulaParser(formula, workbook.getWorkbook());
        fp.parse();
        Ptg[] ptgs = fp.getRPNPtg();
        System.out.println("<ptg-group>");
        for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
            System.out.println("<ptg>");
            System.out.println(ptgs[i]);
            if (ptgs[i] instanceof OperationPtg) {
                System.out.println("numoperands: " + ((OperationPtg) ptgs[i]).getNumberOfOperands());
            }
            System.out.println("</ptg>");
        }
        System.out.println("</ptg-group>");
protected static org.apache.poi.hssf.record.formula.eval.ValueEvalinternalEvaluate(org.apache.poi.hssf.usermodel.HSSFCell srcCell, org.apache.poi.hssf.usermodel.HSSFRow srcRow, org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook)
Dev. Note: Internal evaluate must be passed only a formula cell else a runtime exception will be thrown somewhere inside the method. (Hence this is a private method.)

param
formula
param
sheet
param
workbook
return

        int srcRowNum = srcRow.getRowNum();
        short srcColNum = srcCell.getCellNum();
        FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), workbook.getWorkbook());
        parser.parse();
        Ptg[] ptgs = parser.getRPNPtg();
        // -- parsing over --
        

        Stack stack = new Stack();
        for (int i = 0, iSize = ptgs.length; i < iSize; i++) {

            // since we dont know how to handle these yet :(
            if (ptgs[i] instanceof ControlPtg) { continue; }
            if (ptgs[i] instanceof MemErrPtg) { continue; }
            if (ptgs[i] instanceof MissingArgPtg) { continue; }
            if (ptgs[i] instanceof NamePtg) { continue; }
            if (ptgs[i] instanceof NameXPtg) { continue; }
            if (ptgs[i] instanceof UnknownPtg) { continue; }

            if (ptgs[i] instanceof OperationPtg) {
                OperationPtg optg = (OperationPtg) ptgs[i];

                // parens can be ignored since we have RPN tokens
                if (optg instanceof ParenthesisPtg) { continue; }
                if (optg instanceof AttrPtg) { continue; }
                if (optg instanceof UnionPtg) { continue; }

                OperationEval operation = (OperationEval) getOperationEvalForPtg(optg);

                int numops = operation.getNumberOfOperands();
                Eval[] ops = new Eval[numops];

                // storing the ops in reverse order since they are popping
                for (int j = numops - 1; j >= 0; j--) {
                    Eval p = (Eval) stack.pop();
                    ops[j] = p;
                }
                Eval opresult = operation.evaluate(ops, srcRowNum, srcColNum);
                stack.push(opresult);
            }
            else if (ptgs[i] instanceof ReferencePtg) {
                ReferencePtg ptg = (ReferencePtg) ptgs[i];
                short colnum = ptg.getColumn();
                short rownum = ptg.getRow();
                HSSFRow row = sheet.getRow(rownum);
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
                pushRef2DEval(ptg, stack, cell, row, sheet, workbook);
            }
            else if (ptgs[i] instanceof Ref3DPtg) {
                Ref3DPtg ptg = (Ref3DPtg) ptgs[i];
                short colnum = ptg.getColumn();
                short rownum = ptg.getRow();
                Workbook wb = workbook.getWorkbook();
                HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(ptg.getExternSheetIndex()));
                HSSFRow row = xsheet.getRow(rownum);
                HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
                pushRef3DEval(ptg, stack, cell, row, xsheet, workbook);
            }
            else if (ptgs[i] instanceof AreaPtg) {
                AreaPtg ap = (AreaPtg) ptgs[i];
                short row0 = ap.getFirstRow();
                short col0 = ap.getFirstColumn();
                short row1 = ap.getLastRow();
                short col1 = ap.getLastColumn();
                ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
                for (short x = row0; sheet != null && x < row1 + 1; x++) {
                    HSSFRow row = sheet.getRow(x);
                    for (short y = col0; row != null && y < col1 + 1; y++) {
                        values[(x - row0) * (col1 - col0 + 1) + (y - col0)] = 
                            getEvalForCell(row.getCell(y), row, sheet, workbook);
                    }
                }
                AreaEval ae = new Area2DEval(ap, values);
                stack.push(ae);
            }
            else if (ptgs[i] instanceof Area3DPtg) {
                Area3DPtg a3dp = (Area3DPtg) ptgs[i];
                short row0 = a3dp.getFirstRow();
                short col0 = a3dp.getFirstColumn();
                short row1 = a3dp.getLastRow();
                short col1 = a3dp.getLastColumn();
                HSSFSheet xsheet = workbook.getSheetAt(a3dp.getExternSheetIndex());
                ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
                for (short x = row0; sheet != null && x < row1 + 1; x++) {
                    HSSFRow row = sheet.getRow(x);
                    for (short y = col0; row != null && y < col1 + 1; y++) {
                        values[(x - row0) * (col1 - col0 + 1) + (y - col0)] = 
                            getEvalForCell(row.getCell(y), row, xsheet, workbook);
                    }
                }
                AreaEval ae = new Area3DEval(a3dp, values);
                stack.push(ae);
            }
            else {
                Eval ptgEval = getEvalForPtg(ptgs[i]);
                stack.push(ptgEval);
            }
        }
        ValueEval value = ((ValueEval) stack.pop());
        if (value instanceof RefEval) {
            RefEval rv = (RefEval) value;
            value = rv.getInnerValueEval();
        }
        else if (value instanceof AreaEval) {
            AreaEval ae = (AreaEval) value;
            if (ae.isRow()) 
                value = ae.getValueAt(ae.getFirstRow(), srcColNum);
            else if (ae.isColumn()) 
                value = ae.getValueAt(srcRowNum, ae.getFirstColumn());
            else
                value = ErrorEval.VALUE_INVALID;
        }
        return value;
protected static voidpushRef2DEval(org.apache.poi.hssf.record.formula.ReferencePtg ptg, java.util.Stack stack, org.apache.poi.hssf.usermodel.HSSFCell cell, org.apache.poi.hssf.usermodel.HSSFRow row, org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook)
create a Ref2DEval for ReferencePtg and push it on the stack. Non existent cells are treated as RefEvals containing BlankEval.

param
ptg
param
stack
param
cell
param
sheet
param
workbook

        if (cell != null)
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                stack.push(new Ref2DEval(ptg, new NumberEval(cell.getNumericCellValue()), false));
                break;
            case HSSFCell.CELL_TYPE_STRING:
                stack.push(new Ref2DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false));
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                stack.push(new Ref2DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true));
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                stack.push(new Ref2DEval(ptg, cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE, false));
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                stack.push(new Ref2DEval(ptg, ErrorEval.UNKNOWN_ERROR, false)); // TODO: think abt this
                break;
            }
        else {
            stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
        }
protected static voidpushRef3DEval(org.apache.poi.hssf.record.formula.Ref3DPtg ptg, java.util.Stack stack, org.apache.poi.hssf.usermodel.HSSFCell cell, org.apache.poi.hssf.usermodel.HSSFRow row, org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook)
create a Ref3DEval for Ref3DPtg and push it on the stack.

param
ptg
param
stack
param
cell
param
sheet
param
workbook

        if (cell != null)
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                stack.push(new Ref3DEval(ptg, new NumberEval(cell.getNumericCellValue()), false));
                break;
            case HSSFCell.CELL_TYPE_STRING:
                stack.push(new Ref3DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false));
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                stack.push(new Ref3DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true));
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                stack.push(new Ref3DEval(ptg, cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE, false));
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                stack.push(new Ref3DEval(ptg, ErrorEval.UNKNOWN_ERROR, false)); // TODO: think abt this
                break;
            }
        else {
            stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
        }
public voidsetCurrentRow(org.apache.poi.hssf.usermodel.HSSFRow row)

        this.row = row;