Java Code Examples for org.apache.poi.ss.usermodel.Cell#getDateCellValue()

/**
 * Get the value of the cell
 *
 * @param cell       cell
 * @param excelField Excel field of current field
 * @param field      Current field
 * @param gson       Gson
 * @param r          Current row generated row
 * @return value
 */
private Object getValue(R r, Cell cell, Field field, ExcelField excelField, Gson gson) {
    switch (cell.getCellType()) {
        case _NONE:
        case BLANK:
        case ERROR:
            this.allowEmpty(r, field, excelField, cell.getRowIndex(), cell.getColumnIndex());
            break;
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            return gson.fromJson(gson.toJson(cell.getNumericCellValue()), field.getType());
        case FORMULA:
            return gson.fromJson(gson.toJson(cell.getStringCellValue()), field.getType());
        default:
            return cell.getStringCellValue();
    }
    return null;
}
 

private Object setCellValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        default:
            System.out.println();
            return "";
    }
}
 

private Boolean isBlank(Cell cell) {
  switch (cell.getCellType()) {
    case STRING:
      if (cell.getRichStringCellValue().length() > 0) {
        return false;
      }
      break;
    case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
          ? cell.getDateCellValue() != null
          : cell.getNumericCellValue() != 0) {
        return false;
      }
      break;
    case BOOLEAN:
      if (cell.getBooleanCellValue()) {
        return false;
      }
      break;
    case BLANK:
      return true;
    default:
      break;
  }
  return null;
}
 

private Boolean isBlank(Cell cell) {
  switch (cell.getCellType()) {
    case STRING:
      if (cell.getRichStringCellValue().length() > 0) {
        return false;
      }
      break;
    case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
          ? cell.getDateCellValue() != null
          : cell.getNumericCellValue() != 0) {
        return false;
      }
      break;
    case BOOLEAN:
      if (cell.getBooleanCellValue()) {
        return false;
      }
      break;
    case BLANK:
      return true;
    default:
      break;
  }
  return null;
}
 

private Object getStringOrDateValue(Cell cell) {
	if (cell == null) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
		return String.valueOf(cell.getBooleanCellValue());
	}

	if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		if (DateUtil.isCellDateFormatted(cell)) {
			return cell.getDateCellValue();
		} else {
			double v = cell.getNumericCellValue();
			return String.valueOf(v);
		}
	}

	if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
		String s = cell.getStringCellValue();
		return StringUtils.trimToNull(s);
	}
	return null;
}
 

/**
 * Reads the giving cell, and provides the best object possible. Is able to read
 * the value of fields that are formulas
 * 
 * @param cell
 * @return the following objects
 *         <ul>
 *         <li>a string in case the cell is text</li>
 *         <li>a date in case the cell is a date</li>
 *         <li>A double in case it is a number</li>
 *         <li>null in case it is another type</li>
 *         </ul>
 */
public Object cellToObject(Cell cell) {
	switch (cell.getCellType()) {
	case BOOLEAN:
		return null;
	case STRING:
		return cell.getStringCellValue();

	case NUMERIC:
		if (DateUtil.isCellDateFormatted(cell)) {

			return cell.getDateCellValue();
		} else {
			return cell.getNumericCellValue();
		}

	case FORMULA:
		switch (cell.getCachedFormulaResultType()) {
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {

				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		case STRING:
			return cell.getRichStringCellValue().getString();
		default:
			return null;
		}
	case BLANK:
		return null;
	default:
		return null;
	}

}
 

/**
 * read the cell. it only supports: boolean, numeric, date(numeric cell type
 * + date cell format) and string.
 *
 * @param cell
 *            the cell to read
 * @return the date if it is a date cell, or else the string value (will be
 *         trimmed to null) . <br/>
 *
 *
 */
static Object readCellAsStringOrDate(Cell cell) {
	if (cell == null) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
		return String.valueOf(cell.getBooleanCellValue());
	}

	if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		if (DateUtil.isCellDateFormatted(cell)) {
			return cell.getDateCellValue();
		} else {
			double v = cell.getNumericCellValue();
			return String.valueOf(v);
		}
	}

	if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
		String s = cell.getStringCellValue();
		return StringUtils.trimToNull(s);
	}
	return null;

}
 

private String getNumericContents(Cell cell) {
  String contents = null;
  if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    // FIXME format string...in JExcel API standard.
    SimpleDateFormat formatter = new SimpleDateFormat("yy/MM/dd");
    contents = formatter.format(date);
  } else {
    contents = String.valueOf(convertDoubleValue(cell
        .getNumericCellValue()));
  }
  return contents;
}
 

@Override
public Date get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return cell.getDateCellValue();
    } else {
        return null;
    }
}
 

static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
  CellType cellType = cell.getCellTypeEnum();
  // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
  boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
  if (isFormula) {
    cellType = cell.getCachedFormulaResultTypeEnum();
  }

  switch (cellType) {
    case STRING:
      return Field.create(cell.getStringCellValue());
    case NUMERIC:
      Double rawValue = cell.getNumericCellValue();  // resolves formulas automatically and gets value without cell formatting
      String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell);
      boolean numericallyEquivalent = false;
      try {
        numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
      } catch (NumberFormatException e) { }

      if (DateUtil.isCellDateFormatted(cell)) {
        // It's a date, not a number
        java.util.Date dt = cell.getDateCellValue();
        // if raw number is < 1 then it's a time component only, otherwise date.
        return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
      }

      // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
      return Field.create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
      return Field.create(cell.getBooleanCellValue());
    case BLANK:
      return Field.create("");
    default:
      throw new ExcelUnsupportedCellTypeException(cell, cellType);
  }
}
 

public Date getReviewerTimestamp(int rowNum) {
	Row row = sheet.getRow(rowNum);
	if (row == null) {
		return null;
	}
	Cell tsCell = row.getCell(TIMESTAMP_COL);
	if (tsCell == null) {
		return null;
	}
	return tsCell.getDateCellValue();
}
 

protected Date getDataCellDateValue(int colNum) {
	Cell cell = getDataRow().getCell(colNum);
	if (cell == null) {
		return null;
	} else {
		return cell.getDateCellValue();
	}
}
 

private Date getDataCellDateValue(int colNum) {
	Cell cell = getDataRow().getCell(colNum);
	if (cell == null) {
		return null;
	} else {
		return cell.getDateCellValue();
	}
}
 

public Date getReviewerTimestamp(int rowNum) {
	Row row = sheet.getRow(rowNum);
	if (row == null) {
		return null;
	}
	Cell tsCell = row.getCell(TIMESTAMP_COL);
	if (tsCell == null) {
		return null;
	}
	return tsCell.getDateCellValue();
}
 

private String getNumericContents(Cell cell) {
  String contents = null;
  if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    // FIXME format string...in JExcel API standard.
    SimpleDateFormat formatter = new SimpleDateFormat("yy/MM/dd");
    contents = formatter.format(date);
  } else {
    contents = String.valueOf(convertDoubleValue(cell
        .getNumericCellValue()));
  }
  return contents;
}
 

protected String getCellFormatValue(Cell cell) {
    String cellValue;
    if (cell != null) {
        switch (cell.getCellType()) {
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue());
                break;
            case FORMULA: {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            }
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case ERROR:
            case BLANK:
            default:
                cellValue = BLANK;
        }
    } else {
        cellValue = BLANK;
    }
    return cellValue;
}
 

public static String getCellVal(Cell cell, int i, int j) {
	if(cell == null) return "";
	
	try {
        switch(cell.getCellTypeEnum()) { // 判断cell类型
	        case NUMERIC:
	            // 判断cell是否为日期格式
	            if( org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ) {
	                Date dateCellVal = cell.getDateCellValue();
					return DateUtil.formatCare2Second( dateCellVal );
	            } 
	            else { // 数字,常规类型的数字会自动多出 .0（因转换后是double类型），需要格式化掉
	            	double cellVal = cell.getNumericCellValue();
	            	
	            	NumberFormat f = NumberFormat.getInstance();
	        		f.setMaximumFractionDigits(8); // 最多保留8位小数
					String val = f.format( cellVal );
					
	            	return val.replace(",", "");
	            }
	        case FORMULA:
	        	return ( (XSSFCell)cell ).getCTCell().getV();
	        case STRING:
	        	return cell.getStringCellValue();
	        default:
	        	return cell.toString();
        }
	} 
	catch( Exception e ) {
		throw new BusinessException( "Excel.getCellVal error, location = [" + i + "," + j + "], cell = " + cell, e);
	}
}
 

public Date getDateValue(Cell cell) {
    if (cell == null) {
        return null;
    } else {
        return cell.getDateCellValue();
    }
}
 

public final static PartitionMap<String ,RCell> readDatas(Sheet i_Sheet ,Integer i_BeginRow ,Integer i_EndRow)
{
    PartitionMap<String ,RCell> v_Ret      = new TablePartition<String ,RCell>();
    Sheet                       v_Sheet    = i_Sheet;
    int                         v_BeginRow = 0;
    int                         v_EndRow   = 0;
    
    if ( i_BeginRow != null )
    {
        v_BeginRow = i_BeginRow.intValue();
        
        if ( v_BeginRow < 0 )
        {
            v_BeginRow = 0;
        }
    }
    
    if ( i_EndRow != null )
    {
        v_EndRow = i_EndRow.intValue();
    }
    else
    {
        v_EndRow = v_Sheet.getLastRowNum();
    }
    
    for (int v_RowNo=v_BeginRow; v_RowNo<=v_EndRow; v_RowNo++)
    {
        Row v_Row = v_Sheet.getRow(v_RowNo);
        if ( v_Row == null )
        {
            continue;
        }
        
        short v_CellCount = v_Row.getLastCellNum();
        
        for (int v_ColumnNo=0; v_ColumnNo<v_CellCount; v_ColumnNo++)
        {
            Cell v_Cell = v_Row.getCell(v_ColumnNo);
            if ( v_Cell == null )
            {
                continue;
            }
            
            if ( v_Cell.getCellTypeEnum() == CellType.STRING )
            {
                String v_Value = v_Cell.getStringCellValue();
                
                if ( !Help.isNull(v_Value) )
                {
                    RCell        v_RCell    = new RCell(v_RowNo ,v_ColumnNo);
                    List<String> v_Decimals = StringHelp.getString(v_Cell.getCellStyle().getDataFormatString() ,$Decimal);
                    if ( !Help.isNull(v_Decimals) )
                    {
                        v_RCell.setDecimal(v_Decimals.get(0).split("\\.")[1].length());
                    }
                    
                    v_Ret.putRow(v_Value ,v_RCell); // 2018-05-07 修复：不再trim()。因为去空格后，原数据格式会被改变，比如说用户就是想导出空格呢？
                                                    //            其二，当为占位符查询时，也是不去空格的查询，这里去空格后，为查询不到匹配的占位符解析信息的。
                }
            }
            else if ( v_Cell.getCellTypeEnum() == CellType.NUMERIC )
            {
                if ( HSSFDateUtil.isCellDateFormatted(v_Cell) ) 
                {
                    if ( v_Cell.getDateCellValue() != null )
                    {
                        v_Ret.putRow((new Date(v_Cell.getDateCellValue())).getFull() ,new RCell(v_RowNo ,v_ColumnNo));
                    }
                } 
                else 
                {
                    v_Ret.putRow(String.valueOf(v_Cell.getNumericCellValue()) ,new RCell(v_RowNo ,v_ColumnNo));
                }
            }
        }
    }
    
    return v_Ret;
}
 

public static void setValue(Row row, JpaObject jpaObject, MappingItem item) throws Exception {
	Cell cell = row.getCell(item.getColumn());
	if (null != cell) {
		switch (item.getType()) {
		case string:
			cell.setCellType(CellType.STRING);
			String stringValue = cell.getStringCellValue();
			if (null != stringValue) {
				PropertyUtils.setProperty(jpaObject, item.getField(), stringValue);
			}
			break;
		case integer:
			cell.setCellType(CellType.NUMERIC);
			Double doubeValue = cell.getNumericCellValue();
			if (null != doubeValue) {
				PropertyUtils.setProperty(jpaObject, item.getField(), doubeValue.intValue());
			}
			break;
		case date:
			Date dateValue = cell.getDateCellValue();
			if (null != dateValue) {
				PropertyUtils.setProperty(jpaObject, item.getField(), dateValue);
			}
			break;
		case genderType:
			String genderTypeStringValue = cell.getStringCellValue();
			if (StringUtils.isNotEmpty(genderTypeStringValue)) {
				if (StringUtils.equalsIgnoreCase(GenderType.f.toString(), genderTypeStringValue)) {
					PropertyUtils.setProperty(jpaObject, item.getField(), GenderType.f);
				} else if (StringUtils.equalsIgnoreCase(GenderType.m.toString(), genderTypeStringValue)) {
					PropertyUtils.setProperty(jpaObject, item.getField(), GenderType.m);
				} else {
					PropertyUtils.setProperty(jpaObject, item.getField(), GenderType.d);
				}
			}
			break;
		case stringList:
			String stringListValue = cell.getStringCellValue();
			if (null != stringListValue) {
				List<String> list = new ArrayList<>(Arrays.asList(StringUtils.split(stringListValue, ",")));
				PropertyUtils.setProperty(jpaObject, item.getField(), list);
			}
			break;
		default:
			break;
		}
	}
}