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

Introduction

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

Prototype

void notifySetFormula(Cell cell);

Source Link

Document

Should be called to tell the cell value cache that the specified (value or formula) cell has changed.

Usage

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

License:Open Source License

/**
* Konvertiert unterschiedliche Formate in Strings.
* 
* @param cell Excel Zelle//from   w w w  . j a va 2  s . c  o m
* @param evaluator FormulaEvaluator
* @return Wert der Zelle als String
*/
public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) {

    boolean isFormulaPatched = false;
    String initialFormula = null;

    int chkTyp = cell.getCellType();
    if (chkTyp == Cell.CELL_TYPE_FORMULA) {

        initialFormula = cell.getCellFormula();
        // Some formula have to be changed before they can be evaluated in POI
        String formula = FormulaPatch.patch(initialFormula);
        if (!formula.equals(initialFormula)) {
            isFormulaPatched = true;
            cell.setCellFormula(formula);
            evaluator.notifySetFormula(cell);
        }
    }

    try {
        int typ = evaluateFormulaCell(cell, evaluator);
        if (typ == -1)
            typ = cell.getCellType();
        switch (typ) {
        case Cell.CELL_TYPE_NUMERIC:
            /* Datum und Zeit (sind auch Zahlen) */
            if (DateUtil.isCellDateFormatted(cell)) {
                Date dat = cell.getDateCellValue();
                GregorianCalendar cal = new GregorianCalendar();
                cal.setTime(dat);
                /*
                 * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als
                 * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum
                 * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00).
                 * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck-
                 * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine
                 * Zeit.
                 */
                if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat
                    SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss");
                    return STD_TIM.format(dat);
                }

                SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy");
                return STD_DAT.format(dat); // Datumsformat
            } else {
                /* int, long, double Formate */
                double dbl = cell.getNumericCellValue();
                int tryInt = (int) dbl;
                long tryLong = (long) dbl;
                if (tryInt == dbl) {
                    return new Integer(tryInt).toString(); // int-Format
                } else if (tryLong == dbl) {
                    return new Long(tryLong).toString(); // long-Format
                }

                // return new Double(dbl).toString(); // double-Format
                String numberValueString = new Double(dbl).toString(); // double-Format

                // always use decimal format
                try {
                    // scale 14 to solve problem like value 0.22 --> 0.219999999999997
                    BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14,
                            RoundingMode.HALF_UP); // use constructor BigDecimal(String)!

                    String customValueString = getCustomDecimalFormat().format(roundedBigDecimal);
                    if (!customValueString.equals(numberValueString)) {
                        logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'",
                                numberValueString, customValueString);
                        numberValueString = customValueString; // bigdecimal-format

                    }
                } catch (Exception e) {
                    logger.error("An error occurred trying to convert the cell value number to decimal format "
                            + numberValueString, e);
                }

                return numberValueString;
            }

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());

        case Cell.CELL_TYPE_FORMULA:
            /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die
             * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat.
             */
            return cell.getCellFormula();

        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();

        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_ERROR:
            switch (cell.getErrorCellValue()) {
            case 1:
                return "#NULL!";
            case 2:
                return "#DIV/0!";
            case 3:
                return "#VALUE!";
            case 4:
                return "#REF!";
            case 5:
                return "#NAME?";
            case 6:
                return "#NUM!";
            case 7:
                return "#N/A";
            default:
                return "#ERR!";
            }

        default:
            return "ERROR: unknown Format";
        }
    } finally {
        if (isFormulaPatched) {
            cell.setCellFormula(initialFormula);
            evaluator.notifySetFormula(cell);
        }
    }

}

---