Example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell.

Prototype

CellType evaluateFormulaCell(Cell cell);

Source Link

Document

If cell contains formula, it evaluates the formula, and saves the result of the formula.

Usage

From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java

License:Apache License

private void writeDeltaSnapshotStatements() {
    Row templateRow = statementsSheet.getRow(INDEX_ROW_TEMPLATE_DELTA_SQL_STATEMENT);

    int currentRowIndex = INDEX_ROW_START_SQL_STATEMENTS;
    int currentNumber = 1;
    boolean snapshotContainsMissingBindVariables = false;

    if (listener != null) {
        listener.setMessage("Writing the statements...");
        listener.setStartValue(0);/*from  w ww  .  j a  v a 2  s  . co  m*/
        listener.setEndValue(deltaSnapshot.getDeltaSqlStatementSnapshots().size());
    }

    for (DeltaSQLStatementSnapshot currentSnapshot : deltaSnapshot.getDeltaSqlStatementSnapshots()) {
        if (listener != null) {
            listener.setCurrentValue(currentNumber);
        }

        Row currentRow = SSUtilities.copyRow(statementsSheet, templateRow, currentRowIndex);

        currentRow.getCell(INDEX_COLUMN_NO).setCellValue(currentNumber++);

        currentRow.getCell(INDEX_COLUMN_PARSING_SCHEMA)
                .setCellValue(currentSnapshot.getSqlStatement().getParsingSchema());
        currentRow.getCell(INDEX_COLUMN_INSTANCE).setCellValue(currentSnapshot.getInstanceId());
        // Excel is limited to 32.767 chars per cell
        if (currentSnapshot.getSqlStatement() == null
                || currentSnapshot.getSqlStatement().getSqlText() == null) {
            // The snapshot is no longer available in the library cache so there is no way to get it's SQL text
            currentRow.getCell(INDEX_COLUMN_SQL_TEXT)
                    .setCellValue("<Statement was swapped out of the library cache.>");
        } else {
            currentRow.getCell(INDEX_COLUMN_SQL_TEXT)
                    .setCellValue(currentSnapshot.getSqlStatement().getSqlTextTrimmedForExcel());
        }

        if (currentSnapshot.getDeltaNumberStatements() == null) {
            currentRow.getCell(INDEX_COLUMN_NUMBER_IDENTICAL_STATEMENTS).setCellValue(" ");
        } else {
            currentRow.getCell(INDEX_COLUMN_NUMBER_IDENTICAL_STATEMENTS)
                    .setCellValue(currentSnapshot.getDeltaNumberStatements().doubleValue());
            snapshotContainsMissingBindVariables = true;
        }

        currentRow.getCell(INDEX_COLUMN_DELTA_EXECUTIONS)
                .setCellValue(currentSnapshot.getDeltaExecutions().doubleValue());

        currentRow.getCell(INDEX_COLUMN_DELTA_ELAPSED_SECONDS)
                .setCellValue(currentSnapshot.getDeltaElapsedSeconds().doubleValue());
        currentRow.getCell(INDEX_COLUMN_DELTA_CPU_SECONDS)
                .setCellValue(currentSnapshot.getDeltaCpuSeconds().doubleValue());

        currentRow.getCell(INDEX_COLUMN_DELTA_BUFFER_GETS)
                .setCellValue(currentSnapshot.getDeltaBufferGets().doubleValue());
        currentRow.getCell(INDEX_COLUMN_DELTA_DISK_READS)
                .setCellValue(currentSnapshot.getDeltaDiskReads().doubleValue());

        currentRow.getCell(INDEX_COLUMN_DELTA_CONCURRENCY_SECONDS)
                .setCellValue(currentSnapshot.getDeltaConcurrencySeconds().doubleValue());
        currentRow.getCell(INDEX_COLUMN_DELTA_CLUSTER_SECONDS)
                .setCellValue(currentSnapshot.getDeltaClusterSeconds().doubleValue());

        currentRow.getCell(INDEX_COLUMN_DELTA_ROWS_PROCESSED)
                .setCellValue(currentSnapshot.getDeltaRowsProcessed().doubleValue());

        currentRow.getCell(INDEX_COLUMN_SQL_ID).setCellValue(currentSnapshot.getSqlStatement().getSqlId());

        currentRowIndex++;
    }

    if (!snapshotContainsMissingBindVariables) {
        // if there are no statements with missing bind variables this column is not needed.
        statementsSheet.setColumnHidden(INDEX_COLUMN_NUMBER_IDENTICAL_STATEMENTS, true);
    }

    // delete the template row
    SSUtilities.deleteRow(statementsSheet, templateRow);

    // update the formulas in the third row (sum)
    FormulaEvaluator evaluator = statementsSheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    Iterator<Cell> cellIterator = statementsSheet.getRow(INDEX_ROW_SUM_FORMULAS).cellIterator();
    while (cellIterator.hasNext()) {
        Cell currentCell = cellIterator.next();
        if (currentCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            evaluator.evaluateFormulaCell(currentCell);
        }
    }
}

---

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

void recalculateAllWorkbookFormulas() {
    if (data.wb instanceof XSSFWorkbook) {
        // XLSX needs full reevaluation
        FormulaEvaluator evaluator = data.wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = data.wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }/*from   w w  w.  ja  va 2 s.  co  m*/
                }
            }
        }
    } else if (data.wb instanceof HSSFWorkbook) {
        // XLS supports a "dirty" flag to have excel recalculate everything when a sheet is opened
        for (int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++) {
            HSSFSheet sheet = ((HSSFWorkbook) data.wb).getSheetAt(sheetNum);
            sheet.setForceFormulaRecalculation(true);
        }
    } else {
        String forceRecalc = getVariable(STREAMER_FORCE_RECALC_PROP_NAME, "N");
        if ("Y".equals(forceRecalc)) {
            data.wb.setForceFormulaRecalculation(true);
        }
    }

}

---

From source file:output.ExcelM3Upgrad.java

private void recalculate() {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                }//  w  ww.  j  a  v a 2  s .c om
                sheet.autoSizeColumn(c.getColumnIndex());
            }
        }
    }

}

---

From source file:step.datapool.excel.ExcelFunctions.java

License:Open Source License

private static int evaluateFormulaCell(Cell cell, FormulaEvaluator evaluator) {
    int typ = -1;
    try {/*from  ww  w.j av a 2s .com*/
        typ = evaluator.evaluateFormulaCell(cell);
    } catch (RuntimeException e) {
        String cellRef = CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
        String errMsg = "Error while evaluating cell " + cellRef + " from sheet "
                + cell.getSheet().getSheetName() + ": " + e.getMessage();
        throw new RuntimeException(errMsg, e);
    }
    return typ;
}

---

From source file:uk.ac.liverpool.spreadsheet.ToXML.java

License:Apache License

/** 
 * Spread sheet level conversion//from  www  .j a  v a 2s.  com
 * the Output file will be named filename.[sheetNumber].xml
 * @param filename to convert 
 * @throws IOException
 */

public void convert(String filename) throws IOException {
    if (evaluateFormulae) {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    }

    convertSheets(filename);

}

---

• «
• 1
• 2
• 3
• »