Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 6 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class SharedValueManager method getRecordForFirstCell.

/**
	 * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the
	 * formula record contained in the specified {@link FormulaRecordAggregate} agg.  Note - the
	 * shared value record always appears after the first formula record in the group.  For arrays
	 * and tables the first formula is always the in the top left cell.  However, since shared
	 * formula groups can be sparse and/or overlap, the first formula may not actually be in the
	 * top left cell.
	 *
	 * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of
	 * a table or array region. <code>null</code> if the formula cell is not shared/array/table,
	 * or if the specified formula is not the the first in the group.
	 */
public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) {
    CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference();
    // and/or distinguishing between tExp and tTbl.
    if (firstCell == null) {
        // not a shared/array/table formula
        return null;
    }
    int row = firstCell.getRow();
    int column = firstCell.getCol();
    if (agg.getRow() != row || agg.getColumn() != column) {
        // not the first formula cell in the group
        return null;
    }
    if (!_groupsBySharedFormulaRecord.isEmpty()) {
        SharedFormulaGroup sfg = findFormulaGroupForCell(firstCell);
        if (null != sfg) {
            return sfg.getSFR();
        }
    }
    for (TableRecord tr : _tableRecords) {
        if (tr.isFirstCell(row, column)) {
            return tr;
        }
    }
    for (ArrayRecord ar : _arrayRecords) {
        if (ar.isFirstCell(row, column)) {
            return ar;
        }
    }
    return null;
}

Example 7 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class FormulaRecordAggregate method getArrayFormulaRange.

public CellRangeAddress getArrayFormulaRange() {
    if (_sharedFormulaRecord != null) {
        throw new IllegalStateException("not an array formula cell.");
    }
    CellReference expRef = _formulaRecord.getFormula().getExpReference();
    if (expRef == null) {
        throw new IllegalStateException("not an array formula cell.");
    }
    ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
    if (arec == null) {
        throw new IllegalStateException("ArrayRecord was not found for the locator " + expRef.formatAsString());
    }
    CellRangeAddress8Bit a = arec.getRange();
    return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(), a.getLastColumn());
}

Example 8 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class WorkbookEvaluator method evaluateFormula.

// visibility raised for testing
@Internal
/* package */
ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {
    // always init. to non-null just for defensive avoiding NPE
    String dbgIndentStr = "";
    if (dbgEvaluationOutputForNextEval) {
        // first evaluation call when ouput is desired, so iit. this evaluator instance
        dbgEvaluationOutputIndent = 1;
        dbgEvaluationOutputForNextEval = false;
    }
    if (dbgEvaluationOutputIndent > 0) {
        // init. indent string to needed spaces (create as substring vom very long space-only string;
        // limit indendation for deep recursions)
        dbgIndentStr = "                                                                                                    ";
        dbgIndentStr = dbgIndentStr.substring(0, Math.min(dbgIndentStr.length(), dbgEvaluationOutputIndent * 2));
        EVAL_LOG.log(POILogger.WARN, dbgIndentStr + "- evaluateFormula('" + ec.getRefEvaluatorForCurrentSheet().getSheetNameRange() + "'/" + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() + "): " + Arrays.toString(ptgs).replaceAll("\\Qorg.apache.poi.ss.formula.ptg.\\E", ""));
        dbgEvaluationOutputIndent++;
    }
    Stack<ValueEval> stack = new Stack<ValueEval>();
    for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
        // since we don't know how to handle these yet :(
        Ptg ptg = ptgs[i];
        if (dbgEvaluationOutputIndent > 0) {
            EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "  * ptg " + i + ": " + ptg + ", stack: " + stack);
        }
        if (ptg instanceof AttrPtg) {
            AttrPtg attrPtg = (AttrPtg) ptg;
            if (attrPtg.isSum()) {
                // Excel prefers to encode 'SUM()' as a tAttr token, but this evaluator
                // expects the equivalent function token
                ptg = FuncVarPtg.SUM;
            }
            if (attrPtg.isOptimizedChoose()) {
                ValueEval arg0 = stack.pop();
                int[] jumpTable = attrPtg.getJumpTable();
                int dist;
                int nChoices = jumpTable.length;
                try {
                    int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());
                    if (switchIndex < 1 || switchIndex > nChoices) {
                        stack.push(ErrorEval.VALUE_INVALID);
                        // +4 for tFuncFar(CHOOSE)
                        dist = attrPtg.getChooseFuncOffset() + 4;
                    } else {
                        dist = jumpTable[switchIndex - 1];
                    }
                } catch (EvaluationException e) {
                    stack.push(e.getErrorEval());
                    // +4 for tFuncFar(CHOOSE)
                    dist = attrPtg.getChooseFuncOffset() + 4;
                }
                // Encoded dist for tAttrChoose includes size of jump table, but
                // countTokensToBeSkipped() does not (it counts whole tokens).
                // subtract jump table size
                dist -= nChoices * 2 + 2;
                i += countTokensToBeSkipped(ptgs, i, dist);
                continue;
            }
            if (attrPtg.isOptimizedIf()) {
                ValueEval arg0 = stack.pop();
                boolean evaluatedPredicate;
                try {
                    evaluatedPredicate = IfFunc.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());
                } catch (EvaluationException e) {
                    stack.push(e.getErrorEval());
                    int dist = attrPtg.getData();
                    i += countTokensToBeSkipped(ptgs, i, dist);
                    attrPtg = (AttrPtg) ptgs[i];
                    dist = attrPtg.getData() + 1;
                    i += countTokensToBeSkipped(ptgs, i, dist);
                    continue;
                }
                if (evaluatedPredicate) {
                // nothing to skip - true param follows
                } else {
                    int dist = attrPtg.getData();
                    i += countTokensToBeSkipped(ptgs, i, dist);
                    Ptg nextPtg = ptgs[i + 1];
                    if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg && // if we really have the IF next or some other FuncVarPtg as third param, e.g. ROW()/COLUMN()!
                    ((FuncVarPtg) nextPtg).getFunctionIndex() == FunctionMetadataRegistry.FUNCTION_INDEX_IF) {
                        // this is an if statement without a false param (as opposed to MissingArgPtg as the false param)
                        i++;
                        stack.push(BoolEval.FALSE);
                    }
                }
                continue;
            }
            if (attrPtg.isSkip()) {
                int dist = attrPtg.getData() + 1;
                i += countTokensToBeSkipped(ptgs, i, dist);
                if (stack.peek() == MissingArgEval.instance) {
                    stack.pop();
                    stack.push(BlankEval.instance);
                }
                continue;
            }
        }
        if (ptg instanceof ControlPtg) {
            // skip Parentheses, Attr, etc
            continue;
        }
        if (ptg instanceof MemFuncPtg || ptg instanceof MemAreaPtg) {
            // can ignore, rest of tokens for this expression are in OK RPN order
            continue;
        }
        if (ptg instanceof MemErrPtg) {
            continue;
        }
        if (ptg instanceof UnionPtg) {
            ValueEval v2 = stack.pop();
            ValueEval v1 = stack.pop();
            stack.push(new RefListEval(v1, v2));
            continue;
        }
        ValueEval opResult;
        if (ptg instanceof OperationPtg) {
            OperationPtg optg = (OperationPtg) ptg;
            int numops = optg.getNumberOfOperands();
            ValueEval[] ops = new ValueEval[numops];
            // storing the ops in reverse order since they are popping
            for (int j = numops - 1; j >= 0; j--) {
                ValueEval p = stack.pop();
                ops[j] = p;
            }
            //                logDebug("invoke " + operation + " (nAgs=" + numops + ")");
            opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);
        } else {
            opResult = getEvalForPtg(ptg, ec);
        }
        if (opResult == null) {
            throw new RuntimeException("Evaluation result must not be null");
        }
        //            logDebug("push " + opResult);
        stack.push(opResult);
        if (dbgEvaluationOutputIndent > 0) {
            EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "    = " + opResult);
        }
    }
    ValueEval value = stack.pop();
    if (!stack.isEmpty()) {
        throw new IllegalStateException("evaluation stack not empty");
    }
    ValueEval result = dereferenceResult(value, ec.getRowIndex(), ec.getColumnIndex());
    if (dbgEvaluationOutputIndent > 0) {
        EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of " + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString() + ": " + result);
        dbgEvaluationOutputIndent--;
        if (dbgEvaluationOutputIndent == 1) {
            // this evaluation is done, reset indent to stop logging
            dbgEvaluationOutputIndent = -1;
        }
    }
    // if
    return result;
}

Example 9 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class OperationEvaluationContext method getDynamicReference.

/**
     * Resolves a cell or area reference dynamically.
     * @param workbookName the name of the workbook containing the reference.  If <code>null</code>
     * the current workbook is assumed.  Note - to evaluate formulas which use multiple workbooks,
     * a {@link CollaboratingWorkbooksEnvironment} must be set up.
     * @param sheetName the name of the sheet containing the reference.  May be <code>null</code>
     * (when <tt>workbookName</tt> is also null) in which case the current workbook and sheet is
     * assumed.
     * @param refStrPart1 the single cell reference or first part of the area reference.  Must not
     * be <code>null</code>.
     * @param refStrPart2 the second part of the area reference. For single cell references this
     * parameter must be <code>null</code>
     * @param isA1Style specifies the format for <tt>refStrPart1</tt> and <tt>refStrPart2</tt>.
     * Pass <code>true</code> for 'A1' style and <code>false</code> for 'R1C1' style.
     * TODO - currently POI only supports 'A1' reference style
     * @return a {@link RefEval} or {@link AreaEval}
     */
public ValueEval getDynamicReference(String workbookName, String sheetName, String refStrPart1, String refStrPart2, boolean isA1Style) {
    if (!isA1Style) {
        throw new RuntimeException("R1C1 style not supported yet");
    }
    SheetRefEvaluator se = createExternSheetRefEvaluator(workbookName, sheetName);
    if (se == null) {
        return ErrorEval.REF_INVALID;
    }
    SheetRangeEvaluator sre = new SheetRangeEvaluator(_sheetIndex, se);
    // ugly typecast - TODO - make spreadsheet version more easily accessible
    SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook) _workbook).getSpreadsheetVersion();
    NameType part1refType = classifyCellReference(refStrPart1, ssVersion);
    switch(part1refType) {
        case BAD_CELL_OR_NAMED_RANGE:
            return ErrorEval.REF_INVALID;
        case NAMED_RANGE:
            EvaluationName nm = ((FormulaParsingWorkbook) _workbook).getName(refStrPart1, _sheetIndex);
            if (!nm.isRange()) {
                throw new RuntimeException("Specified name '" + refStrPart1 + "' is not a range as expected.");
            }
            return _bookEvaluator.evaluateNameFormula(nm.getNameDefinition(), this);
    }
    if (refStrPart2 == null) {
        // no ':'
        switch(part1refType) {
            case COLUMN:
            case ROW:
                return ErrorEval.REF_INVALID;
            case CELL:
                CellReference cr = new CellReference(refStrPart1);
                return new LazyRefEval(cr.getRow(), cr.getCol(), sre);
        }
        throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
    }
    NameType part2refType = classifyCellReference(refStrPart1, ssVersion);
    switch(part2refType) {
        case BAD_CELL_OR_NAMED_RANGE:
            return ErrorEval.REF_INVALID;
        case NAMED_RANGE:
            throw new RuntimeException("Cannot evaluate '" + refStrPart1 + "'. Indirect evaluation of defined names not supported yet");
    }
    if (part2refType != part1refType) {
        // LHS and RHS of ':' must be compatible
        return ErrorEval.REF_INVALID;
    }
    int firstRow, firstCol, lastRow, lastCol;
    switch(part1refType) {
        case COLUMN:
            firstRow = 0;
            if (part2refType.equals(NameType.COLUMN)) {
                lastRow = ssVersion.getLastRowIndex();
                firstCol = parseRowRef(refStrPart1);
                lastCol = parseRowRef(refStrPart2);
            } else {
                lastRow = ssVersion.getLastRowIndex();
                firstCol = parseColRef(refStrPart1);
                lastCol = parseColRef(refStrPart2);
            }
            break;
        case ROW:
            // support of cell range in the form of integer:integer
            firstCol = 0;
            if (part2refType.equals(NameType.ROW)) {
                firstRow = parseColRef(refStrPart1);
                lastRow = parseColRef(refStrPart2);
                lastCol = ssVersion.getLastColumnIndex();
            } else {
                lastCol = ssVersion.getLastColumnIndex();
                firstRow = parseRowRef(refStrPart1);
                lastRow = parseRowRef(refStrPart2);
            }
            break;
        case CELL:
            CellReference cr;
            cr = new CellReference(refStrPart1);
            firstRow = cr.getRow();
            firstCol = cr.getCol();
            cr = new CellReference(refStrPart2);
            lastRow = cr.getRow();
            lastCol = cr.getCol();
            break;
        default:
            throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
    }
    return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre);
}

Example 10 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFCell method setCellNum.

/**
     * Sets column index of this cell
     *
     * @param num column index of this cell
     */
protected void setCellNum(int num) {
    checkBounds(num);
    _cellNum = num;
    String ref = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
    _cell.setR(ref);
}