List of usage examples for org.apache.poi.ss.formula.ptg RefPtgBase isColRelative
public final boolean isColRelative()
From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateWriter.java
License:Apache License
/** * Handles copying the formula for the provided sheet and active row * //from w ww.j av a2 s. c o m * @param sheet * the provided sheet * @param targetCell * the target cell to copy the formula * @param activeRow * the active row * @param column * the TemplateColumn to be used for */ private void copyFormula(Sheet sheet, Cell targetCell, Row activeRow, TemplateColumn column) { if (targetCell == null || sheet == null || targetCell.getCellType() != Cell.CELL_TYPE_FORMULA) { return; } String formula = column.getCellFormula(); int shiftRows = activeRow.getRowNum() - 1; int shiftCols = 0; XSSFEvaluationWorkbook workbookWrapper = XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook()); Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet)); for (Ptg ptg : ptgs) { if (ptg instanceof RefPtgBase) { RefPtgBase ref = (RefPtgBase) ptg; if (ref.isColRelative()) { ref.setColumn(ref.getColumn() + shiftCols); } if (ref.isRowRelative()) { ref.setRow(ref.getRow() + shiftRows); } } else if (ptg instanceof AreaPtg) { AreaPtg ref = (AreaPtg) ptg; if (ref.isFirstColRelative()) { ref.setFirstColumn(ref.getFirstColumn() + shiftCols); } if (ref.isLastColRelative()) { ref.setLastColumn(ref.getLastColumn() + shiftCols); } if (ref.isFirstRowRelative()) { ref.setFirstRow(ref.getFirstRow() + shiftRows); } if (ref.isLastRowRelative()) { ref.setLastRow(ref.getLastRow() + shiftRows); } } } formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs); targetCell.setCellFormula(formula); log.debug("Set Formula for row " + activeRow.getRowNum() + " : " + formula); targetCell.setAsActiveCell(); }
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Checks if the formula in the given rule evaluates to <code>true</code>. * <p>/*from www . j a v a 2 s .c o m*/ * * NOTE: Does not support HSSF files currently. * * @param rule * Conditional formatting rule to get the formula from * @return True if the formula in the given rule is of boolean formula type * and evaluates to <code>true</code>, false otherwise */ protected boolean matchesFormula(ConditionalFormattingRule rule, int deltaColumn, int deltaRow) { if (!(rule instanceof XSSFConditionalFormattingRule)) { // TODO Does not support HSSF files for now, since HSSF does not // read cell references in the file correctly.Since HSSF formulas // are read completely wrong, that boolean formula above is useless. return false; } String booleanFormula = rule.getFormula1(); if (booleanFormula == null || booleanFormula.isEmpty()) { return false; } // Parse formula and use deltas to get relative cell references to work // (#18702) Ptg[] ptgs = FormulaParser.parse(booleanFormula, WorkbookEvaluatorUtil.getEvaluationWorkbook(spreadsheet), FormulaType.CELL, spreadsheet.getActiveSheetIndex()); for (Ptg ptg : ptgs) { // base class for cell reference "things" if (ptg instanceof RefPtgBase) { RefPtgBase ref = (RefPtgBase) ptg; // re-calculate cell references if (ref.isColRelative()) { ref.setColumn(ref.getColumn() + deltaColumn); } if (ref.isRowRelative()) { ref.setRow(ref.getRow() + deltaRow); } } } ValueEval eval; try { eval = WorkbookEvaluatorUtil.evaluate(spreadsheet, ptgs); } catch (NotImplementedException e) { LOGGER.log(Level.FINEST, e.getMessage(), e); return false; } if (eval instanceof BoolEval) { return eval == null ? false : ((BoolEval) eval).getBooleanValue(); } else { return false; } }
From source file:org.tiefaces.components.websheet.utility.ShiftFormulaUtility.java
License:MIT License
/** * Fixup ref relative row one to one.//from w ww. j ava 2s . com * * @param ptg * the ptg * @param newRow * the new row * @return the object */ protected static Object fixupRefRelativeRowOneToOne(final Object ptg, final Row newRow) { if (ptg instanceof RefPtgBase) { if (ptg instanceof Ref3DPxg) { Ref3DPxg ref3dPxg = (Ref3DPxg) ptg; Ref3DPxg new3dpxg = new Ref3DPxg(ref3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(ref3dPxg.getSheetName(), false)), new CellReference(newRow.getRowNum(), ref3dPxg.getColumn())); new3dpxg.setClass(ref3dPxg.getPtgClass()); new3dpxg.setColRelative(ref3dPxg.isColRelative()); new3dpxg.setRowRelative(ref3dPxg.isRowRelative()); new3dpxg.setLastSheetName(ref3dPxg.getLastSheetName()); return new3dpxg; } else { RefPtgBase refPtgBase = (RefPtgBase) ptg; return new RefPtg(newRow.getRowNum(), refPtgBase.getColumn(), refPtgBase.isRowRelative(), refPtgBase.isColRelative()); } } else { if (ptg instanceof Area3DPxg) { Area3DPxg area3dPxg = (Area3DPxg) ptg; Area3DPxg new3dpxg = new Area3DPxg(area3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(area3dPxg.getSheetName(), false)), area3dPxg.format2DRefAsString()); new3dpxg.setClass(area3dPxg.getPtgClass()); new3dpxg.setFirstColRelative(area3dPxg.isFirstColRelative()); new3dpxg.setLastColRelative(area3dPxg.isLastColRelative()); int shiftRow = newRow.getRowNum() - area3dPxg.getFirstRow(); new3dpxg.setFirstRow(area3dPxg.getFirstRow() + shiftRow); new3dpxg.setLastRow(area3dPxg.getLastRow() + shiftRow); new3dpxg.setFirstRowRelative(area3dPxg.isFirstRowRelative()); new3dpxg.setLastRowRelative(area3dPxg.isLastRowRelative()); new3dpxg.setLastSheetName(area3dPxg.getLastSheetName()); return new3dpxg; } else { AreaPtgBase areaPtgBase = (AreaPtgBase) ptg; int shiftRow = newRow.getRowNum() - areaPtgBase.getFirstRow(); return new AreaPtg(areaPtgBase.getFirstRow() + shiftRow, areaPtgBase.getLastRow() + shiftRow, areaPtgBase.getFirstColumn(), areaPtgBase.getLastColumn(), areaPtgBase.isFirstRowRelative(), areaPtgBase.isLastRowRelative(), areaPtgBase.isFirstColRelative(), areaPtgBase.isLastColRelative()); } } }
From source file:org.tiefaces.components.websheet.utility.ShiftFormulaUtility.java
License:MIT License
/** * Builds the dynamic row for ref ptg base. * * @param ptg/*from ww w.j a v a2s .c o m*/ * the ptg * @param originalOperandClass * the original operand class * @param rowList * the row list * @param newPtg * the new ptg * @param includeParenthesis * the include parenthesis */ private static void buildDynamicRowForRefPtgBase(final Object ptg, final byte originalOperandClass, final List<SerialRow> rowList, final Ptg[] newPtg, final boolean includeParenthesis) { RefPtgBase refPtg = (RefPtgBase) ptg; int unitSize = 1; if (includeParenthesis) { unitSize = 2; } for (int i = 0; i < rowList.size(); i++) { Row row = rowList.get(i).getRow(); if (refPtg instanceof Ref3DPxg) { Ref3DPxg ref3dPxg = (Ref3DPxg) refPtg; Ref3DPxg new3dpxg = new Ref3DPxg(ref3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(ref3dPxg.getSheetName(), false)), new CellReference(row.getRowNum(), ref3dPxg.getColumn())); new3dpxg.setClass(originalOperandClass); new3dpxg.setColRelative(ref3dPxg.isColRelative()); new3dpxg.setRowRelative(ref3dPxg.isRowRelative()); new3dpxg.setLastSheetName(ref3dPxg.getLastSheetName()); newPtg[i * unitSize] = new3dpxg; } else { RefPtgBase refPtgBase = refPtg; newPtg[i * unitSize] = new RefPtg(row.getRowNum(), refPtgBase.getColumn(), refPtgBase.isRowRelative(), refPtgBase.isColRelative()); } if ((unitSize == 2) && (i < (rowList.size() - 1))) { newPtg[i * unitSize + 1] = ParenthesisPtg.instance; } } }