List of usage examples for org.apache.poi.ss.formula FormulaParser parse
public static Ptg[] parse(String formula, FormulaParsingWorkbook workbook, FormulaType formulaType, int sheetIndex)
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 * /* ww w. j a v a 2 s . c om*/ * @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.dataart.spreadsheetanalytics.engine.ConverterUtils.java
License:Apache License
/** Returns true if value string is formula */ static boolean isFormula(String value, EvaluationWorkbook workbook) { try {//from w w w . j av a2 s .co m return FormulaParser.parse(value, (FormulaParsingWorkbook) workbook, 0, 0).length > 1; } // TODO: formulaType and sheet index are 0 catch (FormulaParseException e) { return false; } }
From source file:com.dataart.spreadsheetanalytics.engine.graph.PoiDependencyGraphBuilder.java
License:Apache License
protected void collect(ExecutionGraphVertex parent, String formula) { Ptg[] ptgs = FormulaParser.parse(formula, this.poiFormulaBook, FormulaType.CELL, 0 /*TODO: Sheet number = 0*/); Deque<ExecutionGraphVertex> ptgBag = new ArrayDeque<>(); for (Ptg ptg : ptgs) { String name = ptgToString(ptg); ExecutionGraphVertex v = ExecutionGraph.createVertex(name); this.state.addVertex(v); if (ptg instanceof OperandPtg) { //operand (cell) ptgBag.push(v);/*from w w w .ja va 2 s.c o m*/ A1Address address = A1Address.fromA1Address(name); Cell c = this.poiBook.getSheetAt(0).getRow(address.row()).getCell(address.column()); //recursive call to formula cells if (CELL_TYPE_FORMULA == c.getCellType()) { this.collect(v, c.getCellFormula()); } } else if (ptg instanceof OperationPtg) { //operator (function) for (ExecutionGraphVertex s : ptgBag) { this.state.addEdge(s, v); } ptgBag.clear(); ptgBag.push(v); } } this.state.addEdge(ptgBag.poll(), parent); }
From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java
License:Apache License
private Map<Integer, PoiProxyName> makeNames(Workbook wb, EvaluationWorkbook ewb) { Map<Integer, PoiProxyName> names = new HashMap<>(); for (int nIdx = 0; nIdx < wb.getNumberOfNames(); nIdx++) { Name wbName = wb.getNameAt(nIdx); Ptg[] ptgs;/* w w w .jav a2 s . c o m*/ String refersToFormula; if (!wbName.isFunctionName() && wbName.getRefersToFormula() != null) { //NOPMD refersToFormula = wbName.getRefersToFormula(); ptgs = FormulaParser.parse(refersToFormula, (FormulaParsingWorkbook) ewb, FormulaType.NAMEDRANGE, 0 /*TODO: sheet index*/); } else { ptgs = null; refersToFormula = null; } names.put(nIdx, new PoiProxyName(wbName.getNameName(), wbName.isFunctionName(), refersToFormula != null, ptgs, wbName.isFunctionName(), nIdx)); } return names; }
From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java
License:Apache License
private PoiProxySheet makeSheet(Workbook wb, FormulaParsingWorkbook ewb) { Sheet wbSheet = wb.getSheetAt(0);//from ww w.j av a 2 s. c o m PoiProxySheet sheet = new PoiProxySheet(wbSheet.getSheetName()); for (int r = 0; r <= wbSheet.getLastRowNum(); r++) { Row wbSheetRow = wbSheet.getRow(r); if (wbSheetRow == null) { continue; } for (int c = 0; c <= wbSheetRow.getLastCellNum(); c++) { Cell wbSheetRowCell = wbSheetRow.getCell(c); if (wbSheetRowCell == null) { continue; } final Ptg[] tokens = CELL_TYPE_FORMULA == wbSheetRowCell.getCellType() ? FormulaParser.parse(wbSheetRowCell.getCellFormula(), ewb, FormulaType.CELL, 0) : null; sheet.setCell(PoiProxyCell.makeCell(sheet, wbSheetRowCell, tokens)); } } return sheet; }
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Checks if the formula in the given rule evaluates to <code>true</code>. * <p>/*from w w w. jav 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.configuration.FormCommand.java
License:MIT License
/** * Builds the watch list for cell.//ww w . j a v a2s.com * * @param wbWrapper * the wb wrapper * @param sheetIndex * the sheet index * @param cell * the cell * @param watchList * the watch list * @param lastStaticRow * the last static row */ private void buildWatchListForCell(final XSSFEvaluationWorkbook wbWrapper, final int sheetIndex, final Cell cell, final List<Integer> watchList, final int lastStaticRow) { String formula = cell.getCellFormula(); Ptg[] ptgs = FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, sheetIndex); for (int k = 0; k < ptgs.length; k++) { Object ptg = ptgs[k]; // For area formula, only first row is watched. // Reason is the lastRow must shift same rows with // firstRow. // Otherwise it's difficult to calculate. // In case some situation cannot fit, then should make // change to the formula. int areaInt = ShiftFormulaUtility.getFirstSupportedRowNumFromPtg(ptg); if (areaInt >= 0) { addToWatchList(areaInt, lastStaticRow, watchList); } } // when insert row, the formula may changed. so here is the // workaround. // change formula to user formula to preserve the row // changes. cell.setCellType(CellType.STRING); cell.setCellValue(TieConstants.USER_FORMULA_PREFIX + formula + TieConstants.USER_FORMULA_SUFFIX); }
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Builds the cell formula for shifted rows. * * @param sheet/*w w w. j a v a 2 s .com*/ * the sheet * @param wbWrapper * the wb wrapper * @param shiftFormulaRef * the shift formula ref * @param cell * the cell * @param originFormula * the origin formula */ public static void buildCellFormulaForShiftedRows(final Sheet sheet, final XSSFEvaluationWorkbook wbWrapper, final ShiftFormulaRef shiftFormulaRef, final Cell cell, final String originFormula) { // only shift when there's watchlist exist. if ((shiftFormulaRef.getWatchList() != null) && (!shiftFormulaRef.getWatchList().isEmpty())) { Ptg[] ptgs = FormulaParser.parse(originFormula, wbWrapper, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet)); Ptg[] convertedFormulaPtg = ShiftFormulaUtility.convertSharedFormulas(ptgs, shiftFormulaRef); if (shiftFormulaRef.getFormulaChanged() > 0) { // only change formula when indicator is true cell.setCellFormula(FormulaRenderer.toFormulaString(wbWrapper, convertedFormulaPtg)); } } }
From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void parseFormula(Cell cell, FormulaParsingWorkbook fpwb, FormulaRenderingWorkbook frwb) { CellReference c = new CellReference(cell); String cr = c.formatAsString(); Ptg[] pp = FormulaParser.parse(cell.getCellFormula(), fpwb, FormulaType.CELL, currentSheet); for (Ptg p : pp) { if (p instanceof RefPtg) { RefPtg a = (RefPtg) p;// ww w . j a va 2s .c om Cell dest = cell.getSheet().getRow(a.getRow()).getCell(a.getColumn()); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(a.toFormulaString()); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(a.toFormulaString(), ls); } else ls.add(cr); } if (p instanceof AreaPtg) { AreaPtg a = (AreaPtg) p; for (int i = a.getFirstColumn(); i <= a.getLastColumn(); i++) { for (int k = a.getFirstRow(); k <= a.getLastRow(); k++) { String cc = new CellReference(k, i).formatAsString(); Cell dest = cell.getSheet().getRow(k).getCell(i); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(cc); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(cc, ls); } else ls.add(cr); } } } } String cellF = "[." + cr + "]=" + FormulaRenderer.toFormulaString(frwb, pp); System.out.println(cellF); cellToFormulaConverted.put(cr, cellF); }