Java 类org.apache.poi.ss.usermodel.CellValue 实例源码

public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) {
    final CellValue cellValue = evaluator.evaluate(cell);
    if (cellValue == null) {
        return "";
    }
    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP);
            return bd.stripTrailingZeros();
        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case Cell.CELL_TYPE_ERROR:
            return "ERRO";
        default:
            return "";
    }
}

@Test
public void testExternRef() {
    File file = getResourceFile("Excel1.xlsx");

    WorkbookSet set = new WorkbookSet(file, null, new LinkedWorkbookFileResolver() {
        @Override
        public File resolve(String linkedFilename) {
            return getResourceFile(linkedFilename.substring(linkedFilename.lastIndexOf("/")+1));
        }
    }, false, false);

    Sheet s = set.getMainWorkbook().getSheetAt(0);

    Cell c = s.getRow(1).getCell(0);

    CellValue v = set.getMainFormulaEvaluator().evaluate(c);

    Assert.assertEquals("Value", v.getStringValue());   
}

public Object getCellValue(Cell cell) {
    CellValue cellValue = evaluator.evaluate(cell);
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return cellValue.getBooleanValue();
    case Cell.CELL_TYPE_NUMERIC:
        return cellValue.getNumberValue();
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return cellValue.getError(cell.getErrorCellValue()).getStringValue();
        // CELL_TYPE_FORMULA will never happen
    case Cell.CELL_TYPE_FORMULA:
        throw new PaxmlRuntimeException("Internal error: invalid case");
    default:
        return null;
    }
}

@Override
public Long doConvert(Cell cell) {
    int type = cell.getCellType();
    Long value = null;
    if(Cell.CELL_TYPE_STRING==type){
        value = ((Number)Double.parseDouble(getStringValue(cell))).longValue();
    }else if(Cell.CELL_TYPE_NUMERIC==type){
        Double dvalue = cell.getNumericCellValue();
        value = dvalue.longValue();
    }else if(Cell.CELL_TYPE_FORMULA==type){
        CellValue cv = ExcelUtils.getFormulaCellValue(cell);
        value = cv==null?defaultValue:(long)cv.getNumberValue();
    }else{
        String strValue = getAsString(cell);
        if(StringUtils.isBlank(strValue))
            return defaultValue;
        value = Long.parseLong(strValue);
    }
    return value;
}

@Override
public Double doConvert(Cell cell) {
    int type = cell.getCellType();
    Double value = null;
    if(Cell.CELL_TYPE_STRING==type){
        value = Double.parseDouble(getStringValue(cell));
    }else if(Cell.CELL_TYPE_NUMERIC==type){
        value = cell.getNumericCellValue();
    }else if(Cell.CELL_TYPE_FORMULA==type){
        CellValue cv = ExcelUtils.getFormulaCellValue(cell);
        value = cv==null?defaultValue:cv.getNumberValue();
    }else{
        String strValue = getAsString(cell);
        if(StringUtils.isBlank(strValue))
            return defaultValue;
        value = Double.parseDouble(strValue);
    }
    return value;
}

@Override
public String doConvert(Cell cell) {
    int type = cell.getCellType();
    String value = null;
    if(Cell.CELL_TYPE_STRING==type){
        value = getStringValue(cell);
    }else if(Cell.CELL_TYPE_NUMERIC==type){
        Double dvalue = cell.getNumericCellValue();
        if(dvalue!=null){
            value = String.valueOf(dvalue.longValue());
        }
    }else if(Cell.CELL_TYPE_FORMULA==type){
        CellValue cv = ExcelUtils.getFormulaCellValue(cell);
        value = cv==null?defaultValue:cv.getStringValue();
    }else if(Cell.CELL_TYPE_BOOLEAN==type){
        boolean bvalue = cell.getBooleanCellValue();
        value = String.valueOf(bvalue);
    }else if(Cell.CELL_TYPE_BLANK==type){
        value = "";
    }
    return value;
}

@Override
public Date doConvert(Cell cell) {
    int type = cell.getCellType();
    Date value = null;
    if(Cell.CELL_TYPE_STRING==type){
        value = TheFunction.getInstance().parseDateTime(getStringValue(cell));
    }else if(Cell.CELL_TYPE_NUMERIC==type){
        value = cell.getDateCellValue();
    }else if(Cell.CELL_TYPE_FORMULA==type){
        CellValue cv = ExcelUtils.getFormulaCellValue(cell);
        value = cv==null?defaultValue:TheFunction.getInstance().parseDateTime(cv.getStringValue());//Types.convertValue(cv.getStringValue(), Date.class);
    }else {
        String strValue = getAsString(cell);
        if(StringUtils.isBlank(strValue))
            return defaultValue;
        value = TheFunction.getInstance().parseDateTime(strValue);
    }
    return value;
}

/**
 *
 * @param cell
 * @param cellValue
 * @return internal method which switch on the formula result value type then return a String value
 */
private static String getCellValueAsString(Cell cell, CellValue cellValue) {
    if (cellValue == null) {
        return StringUtils.EMPTY;
    }
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return cellValue.getBooleanValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case Cell.CELL_TYPE_ERROR:
        return "Cell Error type";
    case Cell.CELL_TYPE_NUMERIC:
        return getNumericValue(cell, cellValue, cellValue != null);
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}

/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}

private String getData(Cell cell) {
    if (cell == null) {
        return null;
    }

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue value = wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);

        switch (value.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(value.getBooleanValue());
        case Cell.CELL_TYPE_NUMERIC:
            return String.valueOf(value.getNumberValue());
        case Cell.CELL_TYPE_STRING:
            return Strings.emptyToNull(Strings.nullToEmpty(value.getStringValue()).trim());
        default:
            return null;
        }
    } else {
        return Strings.emptyToNull(cell.toString().trim());
    }
}

private boolean updatePrecalculatedCellValue(Cell destination, CellValue val) {
  if(val != null) {
    switch(val.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
      return updatePrecalculatedBoolean(destination, val.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
      return updatePrecalculatedNumeric(destination, val.getNumberValue());
    case Cell.CELL_TYPE_STRING:
      return updatePrecalculatedString(destination, val.getStringValue());
    case Cell.CELL_TYPE_BLANK:
      return updatePrecalculatedBlank(destination);
    case Cell.CELL_TYPE_ERROR:
      return updatePrecalculatedError(destination,
          FormulaError.forInt(val.getErrorValue()));
    default: return false;
    }
  } else {
    return updatePrecalculatedError(destination, FormulaError.REF);
  }
}

/**
 * Evaluate the formula of the given cell.
 * 
 * @param workbook
 *            workbook (excel) for evaluating the cell formula
 * @param cell
 *            cell (excel)
 * 
 * @return the value of the excel-call as string (the formula will be executed)
 */
static String evaluateCellFormula(XSSFWorkbook workbook, XSSFCell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cellValue.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
        return String.valueOf(cellValue.getNumberValue());
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    default:
        return null;
    }
}

private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper()
            .createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    Object result = null;
    if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        result = cellValue.getBooleanValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        result = cellValue.getNumberValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
        result = cellValue.getStringValue();
    }
    return result;
}

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellTypeEnum()) {
        case NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return new JsonPrimitive(DateFormatterHolder.formatter.format(cell.getDateCellValue().toInstant()));
            } else {
                return new JsonPrimitive(cell.getNumericCellValue());
            }
        case STRING:
            return new JsonPrimitive(cell.getStringCellValue());
        case FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellTypeEnum()) {
                case NUMERIC:
                    return new JsonPrimitive(cellValue.getNumberValue());
                case STRING:
                    return new JsonPrimitive(cellValue.getStringValue());
                case BLANK:
                    return new JsonPrimitive("");
                case BOOLEAN:
                    return new JsonPrimitive(cellValue.getBooleanValue());
                case ERROR:
                default:
                    return null;
            }
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cell.getBooleanCellValue());
        case ERROR:
        default:
            return null;
    }
}

private static Object getCellValueFromFormula(FormulaEvaluator formulaEvaluator, Cell cell) {
    try {
        CellValue cellValue = formulaEvaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Calendar calendar = GregorianCalendar.getInstance();
                calendar.setTime(DateUtil.getJavaDate(cellValue.getNumberValue()));

                return calendar.getTime();
            } else {
                return DECIMAL_FORMAT.format(cellValue.getNumberValue());
            }
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            if (StringUtils.hasText(cellValue.getStringValue())) {
                return cellValue.getStringValue();
            }
        }
    } catch (NotImplementedException e) {
        // If formula use Excel features not implemented in POI (like proper),
        // we can retrieve the cached value (which may no longer be correct, depending of what you do on your file).
        FormulaFeature feature = EnumUtils.getEnum(FormulaFeature.class, e.getCause().getMessage());
        if (ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.contains(feature)) {
            return getCellPrimitiveValue(cell, cell.getCachedFormulaResultType());
        } else {
            throw e;
        }
    }

    return null;
}

public static CellValue getFormulaCellValue(Cell cell){
        try {
            return cell==null?null:cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator().evaluate(cell);
        } catch (Exception e) {
//          throw new BaseException("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage(), e);
            if(ExcelGenerators.isDevModel()){
                logger.warn("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage());
            }
            return null;
        }
    }

/**
 * セルと評価した結果を
 * @param cell タイプが数式のセル。
 * @param value 数式を評価した結果。
 */
public POIEvaluatedCell(final Cell cell, final CellValue value) {
    super(cell);
    this.value = value;

}

public DoublePointWithLabel(CellValue label, double[] point, CellStyle cellStyle) {
    super(point);
    this.label = label;
    this.cellStyle = cellStyle;
}

public DoublePointWithLabel(CellValue label, int[] point, CellStyle cellStyle) {
    super(point);
    this.label = label;
    this.cellStyle = cellStyle;
}

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
    cfSpreadSheetData   spreadsheet = null;
    int rowNo, columnNo;

    /*
     * Collect up the parameters
     */
spreadsheet = (cfSpreadSheetData)parameters.get(2);
rowNo               = parameters.get(1).getInt() - 1;
columnNo        = parameters.get(0).getInt() - 1;

if ( rowNo < 0 )
    throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
    throwException(_session, "column must be 1 or greater (" + columnNo + ")");


/*
 * Find the cell in question 
 */
Sheet   sheet = spreadsheet.getActiveSheet();
Row row = sheet.getRow( rowNo );
if ( row == null )
    row = sheet.createRow( rowNo );

Cell cell   = row.getCell( columnNo );
if ( cell == null )
    cell = row.createCell( columnNo );

FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
    return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
    return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
    return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
    return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_NUMERIC:
            return new cfNumberData(cellValue.getNumberValue());
        case Cell.CELL_TYPE_STRING:
            return new cfStringData(cellValue.getStringValue());
        default:
            return cfStringData.EMPTY_STRING;
    }

}else
    return cfStringData.EMPTY_STRING;
}

private String generateCSVFromCell(Cell cell, FormulaEvaluator evaluator)
{
    try
    {
        if ((cell != null) && (cell.getCellType() != Cell.CELL_TYPE_BLANK))
        {
            CellValue cellValue = evaluator.evaluate(cell);

            if (cellValue.getCellType() == Cell.CELL_TYPE_STRING)
                return cellValue.getStringValue();
            else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC)
            {
                if (DateUtil.isCellDateFormatted(cell))
                {
                    CellStyle cellStyle = cell.getCellStyle();

                    String     excelDateFormat = cellStyle.getDataFormatString();
                    String     javaDateFormat  = excelToJavaDataFormat(excelDateFormat);
                    DateFormat dateFormat      = new SimpleDateFormat(javaDateFormat);
                    return dateFormat.format(cell.getDateCellValue());
                }
                else
                    return Double.toString(cellValue.getNumberValue());
            }
            else if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                return Boolean.toString(cellValue.getBooleanValue());
            else if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                return "";
            else
            {
                logger.log(Level.WARNING, "Problem process cell: Unknown CellValue Type = " + cellValue.getCellType());
                return "";
            }
        }
        else
            return "";
    }
    catch (Throwable throwable)
    {
        logger.log(Level.WARNING, "Problem process cell: Unknown Cell Type", throwable);
        return "";
    }
}

/**
 * セルの値の取得。 セルのタイプに応じた値を返却する。<br>
 * <br>
 * 注：セルタイプが[CELL_TYPE_ERROR]の場合<br>
 * ・xls形式 ：エラーコードを返却（HSSFErrorConstantsに定義）<br>
 * ・xlsx形式 ：Excelのエラー値を返却（ex.#DIV/0!、#N/A、#REF!・・・）
 * 
 * @param cell 対象セル
 * @return 値
 */
public static Object getCellValue( Cell cell) {
    Object value = null;

    if ( cell != null) {
        switch ( cell.getCellTypeEnum()) {
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                value = cell.getErrorCellValue();
                break;
            case NUMERIC:
                // 日付の場合
                if ( isCellDateFormatted( cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                // 式を評価
                CellValue cellValue = evaluator.evaluate( cell);
                CellType cellType = cellValue.getCellTypeEnum();
                // 評価結果の型で分岐
                switch ( cellType) {
                    case BLANK:
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case ERROR:
                        if ( cell instanceof XSSFCell) {
                            // XSSF形式の場合は、文字列を返却
                            XSSFCell xssfCell = ( XSSFCell) cell;
                            CTCell ctCell = xssfCell.getCTCell();
                            value = ctCell.getV();
                        } else if ( cell instanceof HSSFCell) {
                            // HSSF形式の場合は、エラーコードを返却
                            value = cell.getErrorCellValue();
                        }
                        break;
                    case NUMERIC:
                        // 日付の場合
                        if ( isCellDateFormatted( cell)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    default:
                        break;
                }
            default:
                break;
        }
    }
    return value;
}

private String getCellData(final HSSFCell cell) {
if (cell == null) return null;
      int colspan = 1;
      if (colIndex == mergeStart) {
          // First cell in the merging region - set colspan.
          colspan = mergeEnd - mergeStart + 1;
      } else if (colIndex == mergeEnd) {
          // Last cell in the merging region - no more skipped cells.
          mergeStart = -1;
          mergeEnd = -1;
          return null;
      } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart
              && colIndex < mergeEnd) {
          // Within the merging region - skip the cell.
          return null;
      }
      StringBuffer buf = new StringBuffer();
      String val = "";
      try {
          switch (cell.getCellType()) {
          case HSSFCell.CELL_TYPE_STRING:
              val = cell.getStringCellValue();
              break;
          case HSSFCell.CELL_TYPE_NUMERIC:
              // POI does not distinguish between integer and double, thus:
              final double original = cell.getNumericCellValue(),
              rounded = Math.round(original);
              if (Math.abs(rounded - original) < 0.00000000000000001) {
                  val = String.valueOf((int) rounded);
              } else {
                  val = String.valueOf(original);
              }
              break;
          case HSSFCell.CELL_TYPE_FORMULA:
              final CellValue cv = evaluator.evaluate(cell);
              switch (cv.getCellType()) {
              case Cell.CELL_TYPE_BOOLEAN:
                  out.append(cv.getBooleanValue());
                  break;
              case Cell.CELL_TYPE_NUMERIC:
                  out.append(cv.getNumberValue());
                  break;
              case Cell.CELL_TYPE_STRING:
                  out.append(cv.getStringValue());
                  break;
              case Cell.CELL_TYPE_BLANK:
                  break;
              case Cell.CELL_TYPE_ERROR:
                  break;
              default:
                  break;
              }
              break;
          default:
              // Neither string or number? Could be a date.
              try {
                  val = sdf.format(cell.getDateCellValue());
              } catch (final Exception e1) {
              }
          }
      } catch (final Exception e) {
          val = e.getMessage();
      }
      if ("null".equals(val)) {
          val = "";
      }
      return val;
  }

/**
 * 式を評価したセルの値を取得する。
 * @return
 */
public CellValue getCellValue() {
    return value;
}

/**
 * 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. This method should be preferred over evaluateInCell() when
 * the call should not modify the contents of the original cell.
 *
 * @param aCell
 *        The cell to evaluate
 * @return The evaluation result
 */
public CellValue evaluate (@Nonnull final Cell aCell)
{
  return m_aEvaluator.evaluate (aCell);
}