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

Introduction

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

Prototype

void clearAllCachedResultValues();

Source Link

Document

Should be called whenever there are changes to input cells in the evaluated workbook.

Usage

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private DataFrame readData(Sheet sheet, int startRow, int startCol, int nrows, int ncols, boolean header,
        ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat,
        boolean takeCached, int[] subset) {

    DataFrame data = new DataFrame();
    int[] colset;

    // Formula evaluator - only if we don't want to take cached values
    FormulaEvaluator evaluator = null;
    if (!takeCached) {
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.clearAllCachedResultValues();
    }//from  ww w  . ja  v  a 2  s  .  c  o m

    if (subset == null) {
        colset = new int[ncols];
        for (int i = 0; i < ncols; i++) {
            colset[i] = i;
        }
    } else {
        colset = subset;
    }

    ColumnBuilder cb;
    switch (readStrategy) {
    case DEFAULT:
        cb = new DefaultColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, missingValue,
                dateTimeFormat);
        break;
    case FAST:
        cb = new FastColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, dateTimeFormat);
        break;
    default:
        throw new IllegalArgumentException("Unknown read strategy!");
    }

    // Loop over columns
    for (int col : colset) {
        int colIndex = startCol + col;
        // Determine column header
        String columnHeader = null;
        if (header) {
            Cell cell = getCell(sheet, startRow, colIndex, false);
            // Check if there actually is a cell ...
            if (cell != null) {
                if (!takeCached) {
                    CellValue cv = evaluator.evaluate(cell);
                    if (cv != null)
                        columnHeader = cv.getStringValue();
                } else {
                    columnHeader = cell.getStringCellValue();
                }
            }
        }
        // If it was specified that there is a header but an empty(/non-existing)
        // cell or cell value is found, then use a default column name
        if (columnHeader == null)
            columnHeader = "Col" + (col + 1);

        // Prepare column builder for new set of rows
        cb.clear();

        // Loop over rows
        Row r;
        for (int row = header ? 1 : 0; row < nrows; row++) {
            int rowIndex = startRow + row;

            // Cell cell = getCell(sheet, rowIndex, colIndex, false);
            Cell cell = ((r = sheet.getRow(rowIndex)) == null) ? null : r.getCell(colIndex);
            cb.addCell(cell);
        }

        DataType columnType = ((colTypes != null) && (colTypes.length > 0)) ? colTypes[col % colTypes.length]
                : cb.determineColumnType();
        switch (columnType) {
        case Boolean:
            data.addColumn(columnHeader, cb.buildBooleanColumn());
            break;
        case DateTime:
            data.addColumn(columnHeader, cb.buildDateTimeColumn());
            break;
        case Numeric:
            data.addColumn(columnHeader, cb.buildNumericColumn());
            break;
        case String:
            data.addColumn(columnHeader, cb.buildStringColumn());
            break;
        default:
            throw new IllegalArgumentException("Unknown data type detected!");

        }
        // ArrayList columnValues = cb.build(columnType);
        // data.addColumn(columnHeader, columnType, columnValues);
        // Copy warnings
        for (String w : cb.retrieveWarnings())
            this.addWarning(w);
    }

    return data;
}

---

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

Report(final ReportSource reportSource, UDFFinder customFunctions, final InputStream template) {
    if (template == null)
        this.workbook = new HSSFWorkbook();
    else/*from w  w  w . jav  a 2  s  .  c  o  m*/
        try {
            this.workbook = new HSSFWorkbook(new BufferedInputStream(template));
        } catch (IOException e) {
            throw new RuntimeException("Could not load template for report!");
        }

    if (customFunctions != null)
        this.workbook.addToolPack(customFunctions);

    final Set<String> sheetsToHide = new HashSet<String>();
    final Set<String> sheetsToDelete = new HashSet<String>();

    String previousSheetName = null;
    Sheet sheet = null;
    // Iterator over all celldefinitions
    // this doesn't compile inside Oracle Database VM. You need to import the compiled classes
    // or use reportSource.iterator() directly
    for (CellDefinition cellDefinition : reportSource) {
        Matcher m = null;
        if (HIDE_SHEET_CELL.equals(cellDefinition.sheetname)) {
            sheetsToHide.add(cellDefinition.value);
        } else if (DELETE_SHEET_CELL.equals(cellDefinition.sheetname)) {
            sheetsToDelete.add(cellDefinition.value);
        } else if ((m = CLONE_SHEET_CELL.matcher(cellDefinition.sheetname)).matches()) {
            final String sourceName = m.group(1);
            final String targetName = m.group(2);
            final Sheet target = workbook.cloneSheet(workbook.getSheetIndex(sourceName));
            workbook.setSheetName(workbook.getSheetIndex(target), targetName);
        } else {
            // Create and cache the current sheet.         
            if (previousSheetName == null || !previousSheetName.equals(cellDefinition.sheetname)) {
                previousSheetName = cellDefinition.sheetname;
                sheet = getSheet(workbook, cellDefinition.sheetname);
            }

            // create, fill and add cell
            this.addCell(workbook, sheet, cellDefinition);
        }
    }

    // Evaluate all formulas
    try {
        final FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formulaEvaluator.clearAllCachedResultValues();
        formulaEvaluator.evaluateAll();
    } catch (Exception e) {
    }

    // Hide and delete sheets
    for (String sheetName : sheetsToHide)
        workbook.setSheetHidden(workbook.getSheetIndex(sheetName), true);
    for (String sheetName : sheetsToDelete)
        workbook.removeSheetAt(workbook.getSheetIndex(sheetName));
}

---

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

public List<CellDefinition> evaluateWorkbook() {
    final List<CellDefinition> rv = new ArrayList<CellDefinition>();

    boolean reevaluate = false;
    if (workbook instanceof HSSFWorkbook) {
        try {/*from  w w  w.j a  va 2  s. c  om*/
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        } catch (Exception e) {
            reevaluate = true;
        }
    }

    final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook,
            IStabilityClassifier.TOTALLY_IMMUTABLE);
    formulaEvaluator.clearAllCachedResultValues();

    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        final Sheet sheet = workbook.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    try {
                        formulaEvaluator.evaluateFormulaCell(cell);
                    } catch (Exception e) {
                        ReportEngine.logger.log(Level.WARNING,
                                String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s",
                                        cell.getCellFormula(), CellReferenceHelper
                                                .getCellReference(cell.getColumnIndex(), row.getRowNum()),
                                        sheet.getSheetName(), e.getMessage()));
                    }
                }

                final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey(
                        new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell)
                                : null;
                if (cellDefinition != null)
                    rv.add(cellDefinition);
            }
        }
    }

    return rv;
}

---