Java Code Examples for org.apache.poi.ss.usermodel.DateUtil#isCellDateFormatted()
The following examples show how to use
org.apache.poi.ss.usermodel.DateUtil#isCellDateFormatted() .
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: ExcelPOIHelper.java From tutorials with MIT License | 6 votes |
|
private String readCellContent(Cell cell) {
String content;
switch (cell.getCellTypeEnum()) {
case STRING:
content = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
content = cell.getDateCellValue() + "";
} else {
content = cell.getNumericCellValue() + "";
}
break;
case BOOLEAN:
content = cell.getBooleanCellValue() + "";
break;
case FORMULA:
content = cell.getCellFormula() + "";
break;
default:
content = "";
}
return content;
}
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: ExcelServiceImpl.java From cs-actions with Apache License 2.0 | 5 votes |
|
/**
* Get the type of a cell, it can be num, date, time or string
*
* @param cell
* @return
*/
public static String getCellType(final Cell cell) {
final String result;
final double cellValueNumeric;
final CellType cellType = cell.getCellType();
if (cellType == CellType.NUMERIC) {
cellValueNumeric = cell.getNumericCellValue();
//date cell, it can be date, time or datetime
if (DateUtil.isCellDateFormatted(cell)) {
//time cell
if (cellValueNumeric < 1) {
result = "time";
}
//date cell
else {
result = "date";
}
}
//numeric cell
else {
result = "num";
}
}
//String cell
else {
result = "string";
}
return result;
}
Example 6
| Source File: ExcelHelper.java From Excel2Entity with MIT License | 5 votes |
|
/**
* 读取每个单元格中的内容
*
* @param cell
* @return
*/
private String _getCellValue(Cell cell) {
// 如果单元格为空的，则返回空字符串
if (cell == null) {
return "";
}
// 根据单元格类型，以不同的方式读取单元格的值
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(cell.getDateCellValue());
} else {
value = (long) cell.getNumericCellValue() + "";
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
default:
}
return value;
}
Example 7
| Source File: ExcelReader.java From azeroth with Apache License 2.0 | 5 votes |
|
/**
* 获取指定单元格的值
*
* @param rowNumber 行数，从1开始
* @param cellNumber 列数，从1开始
* @return 该单元格的值
*/
public String getCellValue(int rowNumber, int cellNumber) {
String result;
checkRowAndCell(rowNumber, cellNumber);
Sheet sheet = this.workbook.getSheet(this.sheetName);
Row row = sheet.getRow(--rowNumber);
Cell cell = row.getCell(--cellNumber);
switch (cell.getCellTypeEnum()) {
case BLANK:
result = cell.getStringCellValue();
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
result = String.valueOf(cell.getErrorCellValue());
break;
case FORMULA:
result = cell.getCellFormula();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
result = format.format(cell.getDateCellValue());
} else {
result = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
result = cell.getRichStringCellValue().getString();
break;
default:
result = cell.getStringCellValue();
break;
}
return result;
}
Example 8
| Source File: Serialization.java From joinery with GNU General Public License v3.0 | 5 votes |
|
private static final Object readCell(final Cell cell) {
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return DateUtil.getJavaDate(cell.getNumericCellValue());
}
return cell.getNumericCellValue();
case BOOLEAN:
return cell.getBooleanCellValue();
default:
return cell.getStringCellValue();
}
}
Example 9
| Source File: ExcelReader.java From jeesuite-libs with Apache License 2.0 | 5 votes |
|
/**
* 获取指定单元格的值
*
* @param rowNumber 行数，从1开始
* @param cellNumber 列数，从1开始
* @return 该单元格的值
*/
public String getCellValue(int rowNumber, int cellNumber) {
String result;
checkRowAndCell(rowNumber, cellNumber);
Sheet sheet = this.workbook.getSheet(this.sheetName);
Row row = sheet.getRow(--rowNumber);
Cell cell = row.getCell(--cellNumber);
switch (cell.getCellTypeEnum()) {
case BLANK:
result = cell.getStringCellValue();
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
result = String.valueOf(cell.getErrorCellValue());
break;
case FORMULA:
result = cell.getCellFormula();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
result = format.format(cell.getDateCellValue());
} else {
result = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
result = cell.getRichStringCellValue().getString();
break;
default:
result = cell.getStringCellValue();
break;
}
return result;
}
Example 10
| Source File: ExcelDataProvider.java From NoraUi with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* @param cell
* @return
*/
private String dateOrNumberProcessing(Cell cell) {
String txt;
if (DateUtil.isCellDateFormatted(cell)) {
final DateFormat formatter = new SimpleDateFormat(Constants.DEFAULT_DATE_FORMAT);
txt = String.valueOf(formatter.format(cell.getDateCellValue()));
log.debug("CELL_TYPE_NUMERIC (date): {}", txt);
} else {
txt = String.valueOf(cell.getNumericCellValue());
log.debug("CELL_TYPE_NUMERIC: {}", txt);
}
return txt;
}
Example 11
| Source File: XlsxReader.java From tablesaw with Apache License 2.0 | 5 votes |
|
private ColumnType getColumnType(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return ColumnType.STRING;
case NUMERIC:
return DateUtil.isCellDateFormatted(cell) ? ColumnType.LOCAL_DATE_TIME : ColumnType.INTEGER;
case BOOLEAN:
return ColumnType.BOOLEAN;
default:
break;
}
return null;
}
Example 12
| 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 13
| 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 14
| Source File: Utils.java From Excel4J with Apache License 2.0 | 5 votes |
|
/**
* 获取单元格内容
*
* @param c 单元格
* @return 单元格内容
*/
public static String getCellValue(Cell c) {
String o;
switch (c.getCellType()) {
case BLANK:
o = "";
break;
case BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case FORMULA:
o = calculationFormula(c);
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(c)) {
o = DateUtils.date2Str(c.getDateCellValue());
} else {
o = String.valueOf(c.getNumericCellValue());
o = matchDoneBigDecimal(o);
o = RegularUtils.converNumByReg(o);
}
break;
case STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
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: ExcelUtils.java From components with Apache License 2.0 | 5 votes |
|
/**
* 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) {
if (DateUtil.isCellDateFormatted(cell)) {
return sdf.format(cell.getDateCellValue());
}
if (cellValue == null) {
return formatter.formatCellValue(cell);
}
return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
Example 17
| 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 18
| Source File: ExcelReader.java From azeroth with Apache License 2.0 | 4 votes |
|
private void getCellValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
LOG.debug("cell:{}, field:{}, type:{}", cell.getCellTypeEnum(), field.getName(), field.getType().getName());
switch (cell.getCellTypeEnum()) {
case BLANK:
break;
case BOOLEAN:
field.setBoolean(o, cell.getBooleanCellValue());
break;
case ERROR:
field.setByte(o, cell.getErrorCellValue());
break;
case FORMULA:
field.set(o, cell.getCellFormula());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
if (field.getType().getName().equals(Date.class.getName())) {
field.set(o, cell.getDateCellValue());
} else {
field.set(o, format.format(cell.getDateCellValue()));
}
} else {
if (field.getType().isAssignableFrom(Integer.class) || field.getType().getName().equals("int")) {
field.setInt(o, (int) cell.getNumericCellValue());
} else if (field.getType().isAssignableFrom(Short.class) || field.getType().getName().equals("short")) {
field.setShort(o, (short) cell.getNumericCellValue());
} else if (field.getType().isAssignableFrom(Float.class) || field.getType().getName().equals("float")) {
field.setFloat(o, (float) cell.getNumericCellValue());
} else if (field.getType().isAssignableFrom(Byte.class) || field.getType().getName().equals("byte")) {
field.setByte(o, (byte) cell.getNumericCellValue());
} else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
field.setDouble(o, cell.getNumericCellValue());
} else if (field.getType().isAssignableFrom(String.class)) {
String s = String.valueOf(cell.getNumericCellValue());
if (s.contains("E")) {
s = s.trim();
BigDecimal bigDecimal = new BigDecimal(s);
s = bigDecimal.toPlainString();
}
//防止整数判定为浮点数
if (s.endsWith(".0")) { s = s.substring(0, s.indexOf(".0")); }
field.set(o, s);
} else {
field.set(o, cell.getNumericCellValue());
}
}
break;
case STRING:
if (field.getType().getName().equals(Date.class.getName())) {
field.set(o, format.parse(cell.getRichStringCellValue().getString()));
} else {
field.set(o, cell.getRichStringCellValue().getString());
}
break;
default:
field.set(o, cell.getStringCellValue());
break;
}
}
Example 19
| Source File: MyExcelUtils.java From spring-boot with Apache License 2.0 | 4 votes |
|
/**
* 根据 cell 格式，自动转换 cell 内容为 String
*
* @param cell
* @param datePattern 日期格式 yyyy-MM-dd , yyyy-MM-dd HH:mm:ss ...
* @return
*/
private static String getFormatCellValue(Cell cell, String datePattern) {
//如果是日期格式，重新格式化
if (cell.getCellTypeEnum() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
return DateFormatUtils.format(cell.getDateCellValue(), datePattern);
} else //默认格式化
return new DataFormatter().formatCellValue(cell).trim();
/**
* DataFormatter().formatCellValue(cell) 的源码
*/
// if (cell == null) {
// return "";
// }
//
// int cellType = cell.getCellType();
// if (cellType == Cell.CELL_TYPE_FORMULA) {
// if (evaluator == null) {
// return cell.getCellFormula();
// }
// cellType = evaluator.evaluateFormulaCell(cell);
// }
// switch (cellType) {
// case Cell.CELL_TYPE_NUMERIC :
//
// if (DateUtil.isCellDateFormatted(cell)) {
// return getFormattedDateString(cell);
// }
// return getFormattedNumberString(cell);
//
// case Cell.CELL_TYPE_STRING :
// return cell.getRichStringCellValue().getString();
//
// case Cell.CELL_TYPE_BOOLEAN :
// return String.valueOf(cell.getBooleanCellValue());
// case Cell.CELL_TYPE_BLANK :
// return "";
// case Cell.CELL_TYPE_ERROR:
// return FormulaError.forInt(cell.getErrorCellValue()).getString();
// }
// throw new RuntimeException("Unexpected celltype (" + cellType + ")");
}
Example 20
| Source File: ExcelUtil.java From util with Apache License 2.0 | 4 votes |
|
/**
* 读取某个工作簿上的所有单元格的值。
* 作者：朱志杰
* July 30, 2013 3:27:08 PM
* @param sheetOrder 工作簿序号，从0开始。
* @return List<Object[]> 所有单元格的值。
* @throws IOException 加载excel文件IO异常。
* @throws FileNotFoundException excel文件没有找到异常。
* @throws InvalidFormatException
*/
public List<Object[]> read(int sheetOrder) throws FileNotFoundException, IOException, InvalidFormatException{
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if(fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
//用来记录excel值
List<Object[]> valueList=new LinkedList<Object[]>();
//循环遍历每一行、每一列。
for(Row row : sheet){
//每一行
Object[] rowObject=null;
for(Cell cell : row){
//cell.getCellType是获得cell里面保存的值的type
switch(cell.getCellType()){
case Cell.CELL_TYPE_BOOLEAN:
//得到Boolean对象的方法
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
//先看是否是日期格式
if(DateUtil.isCellDateFormatted(cell)){
//读取日期格式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getDateCellValue());
}else{
DecimalFormat df = new DecimalFormat();
//单元格的值,替换掉,
String value=df.format(cell.getNumericCellValue()).replace(",","");
//读取数字
rowObject=CollectionUtil.addObjectToArray(rowObject, value);
}
break;
case Cell.CELL_TYPE_FORMULA:
//读取公式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getCellFormula());
break;
case Cell.CELL_TYPE_STRING:
//读取String
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getRichStringCellValue().toString());
break;
}
}
//将这行添加到list。
valueList.add(rowObject);
}
return valueList;
}
