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

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getDateCellValue() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: ReadExecutor.java From tools with MIT License

6 votes

/**
 * 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;
}

Example 2

Source File: TestController.java From Mario with Apache License 2.0

6 votes

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 "";
    }
}

Example 3

Source File: XlsxReader.java From tablesaw with Apache License 2.0

6 votes

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;
}

Example 4

Source File: XlsxReader.java From tablesaw with Apache License 2.0

6 votes

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;
}

Example 5

Source File: SsioIntegrationTest.java From sep4j with Apache License 2.0

5 votes

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;
}

Example 6

Source File: ExcelReader.java From Open-Lowcode with Eclipse Public License 2.0

5 votes

/**
 * 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;
	}

}

Example 7

Source File: Ssio.java From sep4j with Apache License 2.0

5 votes

/**
 * 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;

}

Example 8

Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0

5 votes

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;
}

Example 9

Source File: PoiDateGetter.java From SimpleFlatMapper with MIT License

5 votes

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

Example 10

Source File: Cells.java From datacollector with Apache License 2.0

5 votes

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);
  }
}

Example 11

Source File: ReviewersSheet.java From tools with Apache License 2.0

5 votes

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();
}

Example 12

Source File: DocumentInfoSheet.java From tools with Apache License 2.0

5 votes

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

Example 13

Source File: OriginsSheet.java From tools with Apache License 2.0

5 votes

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

Example 14

Source File: ReviewersSheet.java From tools with Apache License 2.0

5 votes

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();
}

Example 15

Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0

5 votes

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;
}

Example 16

Source File: Wrapper.java From ExcelReads with Apache License 2.0

5 votes

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;
}

Example 17

Source File: ExcelPOI.java From boubei-tss with Apache License 2.0

5 votes

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);
	}
}

Example 18

Source File: SpreadsheetReader.java From taro with MIT License

5 votes

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

Example 19

Source File: ExcelHelp.java From hy.common.report with Apache License 2.0

4 votes

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;
}

Example 20

Source File: MappingItem.java From o2oa with GNU Affero General Public License v3.0

4 votes

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;
		}
	}
}