Example usage for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator evaluate

List of usage examples for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator evaluate

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFFormulaEvaluator
  6. evaluate

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator evaluate.

Prototype

@Override
public CellValue evaluate(Cell cell)

Source Link

Document

If cell contains a formula, the formula is evaluated and returned, else the CellValue simply copies the appropriate cell value from the cell and also its cell type.

Usage

From source file:cn.trymore.core.util.excel.PoiExcelParser.java

License:Open Source License

public Object getCellContent(Object cell) {
    if (cell != null) {
        HSSFCell cel = (HSSFCell) cell;/*w  w  w .j a v  a  2s .  co  m*/
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.book);
        CellValue cellValue = evaluator.evaluate(cel);
        switch (cel.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cel)) {
                return UtilDate.parseTime(cel.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
            } else {
                return Double.valueOf(cellValue.getNumberValue());
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case HSSFCell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(cellValue.getBooleanValue());
        case HSSFCell.CELL_TYPE_ERROR:
            return Byte.valueOf(cellValue.getErrorValue());
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            return cellValue.formatAsString();
        default:
            return null;
        }
    }
    return null;
}

From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

public static String getCellString(Cell cell, HSSFFormulaEvaluator eval, DateFormat dfmt) {
    if (cell == null) {
        return null;
    }//from  w  w w .  jav a  2 s . c  o  m
    String ret = null;
    eval.evaluate(cell);
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
    case HSSFCell.CELL_TYPE_FORMULA: // ?
        if (isCellDateFormatted(cell)) {
            if (dfmt == null) {
                dfmt = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date d = cell.getDateCellValue();
            if (d != null) {
                ret = dfmt.format(d);
            } else {
                ret = "";
            }
        } else {
            try {
                ret = "" + cell.getNumericCellValue();
            } catch (IllegalStateException ise) {
                int errVal = cell.getErrorCellValue();
                String formula = cell.getCellFormula();
                int cacheType = cell.getCachedFormulaResultType();
                throw new RuntimeException(ise.getMessage() + ": errVal=" + errVal + " formula='" + formula
                        + "' cacheType=" + cacheType);
            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        ret = null;
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        ret = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_STRING:
    default:
        ret = cell.getRichStringCellValue().getString();
    }
    return ret;
}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static Boolean getBooleanValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    Boolean rvalue = false;// w w  w  . ja  va  2 s . co  m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        evaluator.evaluate(cell);
        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            rvalue = cellValue.getBooleanValue();
            break;
        case Cell.CELL_TYPE_STRING:
            rvalue = Boolean.parseBoolean(cellValue.getStringValue());
            break;
        }
    } else {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            rvalue = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            rvalue = Boolean.parseBoolean(cell.getRichStringCellValue().getString());
            break;
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static Date getDateValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    Date rvalue = null;/*w ww .  j a va2s .  c  o  m*/

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cell.getDateCellValue();
        }
    } else {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cell.getDateCellValue();
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static Double getNumericValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    Double rvalue = null;//from  w  w  w. j  a v  a  2s.  c o m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cellValue.getNumberValue();
        }
    } else {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cell.getNumericCellValue();
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static String getTextValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    String rvalue = null;//from w  ww  .ja va 2  s.co m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            rvalue = cellValue.getStringValue();
        }
    } else {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            rvalue = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // If the cell type is NUMERIC, cast the value as a long and return it as a String.
            rvalue = (Long.valueOf((long) cell.getNumericCellValue())).toString();
            break;
        case Cell.CELL_TYPE_BLANK:
            rvalue = "";
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static boolean containsWhitespace(HSSFFormulaEvaluator evaluator, Cell cell) {
    boolean containsWhitespace = false;

    String textValue = null;//from  www. j  av a2 s .c o m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            textValue = cellValue.getStringValue();
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        textValue = cell.getRichStringCellValue().getString();
    }

    if (textValue != null) {
        if (textValue.trim().length() == 0) {
            containsWhitespace = true;
        }
    }

    return containsWhitespace;
}

From source file:org.testeditor.core.importer.ExcelFileImporter.java

License:Open Source License

/**
 * Iterates through the cells in a row an creates a {@link TestDataRow}
 * Object.//from   w  w  w.  ja  v  a2 s.  c o  m
 * 
 * @param row
 *            row in excel sheet
 * @return TestDataRow
 */
@SuppressWarnings("rawtypes")
private TestDataRow getTestDataRow(HSSFRow row) {
    int id = 0;
    Iterator cells = row.cellIterator();

    TestDataRow testDataRow = new TestDataRow();

    while (cells.hasNext()) {

        HSSFCell cell = (HSSFCell) cells.next();

        for (int i = id; i < cell.getColumnIndex(); i++) {
            testDataRow.add("");
        }
        id = cell.getColumnIndex() + 1;

        if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            getTestDataNumericCell(testDataRow, cell);
        } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
            testDataRow.add(cell.getStringCellValue());
        } else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
            testDataRow.add(String.valueOf(cell.getBooleanCellValue()));
        } else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) {

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(row.getSheet().getWorkbook());
            CellValue cv = fe.evaluate(cell);
            testDataRow.add(cv.formatAsString());

        } else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) {
            testDataRow.add("");
        } else {
            if (LOGGER.isInfoEnabled()) {
                LOGGER.info("getTestData :: Unknown cell type");
            }
        }
    }
    return testDataRow;
}

From source file:test.ExcelUtil.java

License:Apache License

/**
 * , /*from   w  w w  .  java  2 s .  c o  m*/
 * 
 * @param cell
 * @return
 */
public static String cell2string(HSSFCell cell, HSSFFormulaEvaluator evaluator) {
    if (cell == null) {
        return null;
    }
    String str = null;
    final int cellType = cell.getCellType();

    switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:
        str = "" + cell.getRichStringCellValue().getString().trim();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            str = "" + dateFormat.format(cell.getDateCellValue());
        } else {
            str = String.valueOf(cell.getNumericCellValue());

            //modify by cyyan 2008-09-23 19:17:28 
            //excelE, ;
            //E, 15, (15, )
            // 15, 0, 0
            //            str = "" + new BigDecimal(numberStr).setScale(15, BigDecimal.ROUND_HALF_UP);
            //            
            //            //modify yanchangyou 2008-09-26 18:01:43 
            //            // .0000* , 0, 
            //            if (str.indexOf('.') != -1) {
            //               str = str.replaceAll("(\\.)?0*$", "");
            //            }            

            /*
             * , 
             */
            //            if (str.indexOf('.') != -1) { //
            //               int index = str.length();
            //               for (int i = str.length()-1; i > -1; i--) {
            //                  if (str.charAt(i) == '0') {
            //                     index = i;
            //                  } else if (str.charAt(i) == '.'){
            //                     index = i;
            //                     break;
            //                  } else {
            //                     break;
            //                  }
            //               }
            //               str = str.substring(0, index);
            //            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        str = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        str = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        str = "" + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        if (evaluator == null) {
            str = "" + cell.getRichStringCellValue().getString();
        } else {
            str = "" + evaluator.evaluate(cell).getNumberValue();
        }

        break;
    }

    return (str == null || str.trim().equals("")) ? null : str.trim();
}