Excel数据处理:
Excel存储日期、时间均以数值类型进行存储,读取时POI先判断是是否是数值类型,再进行判断转化
日期格式都可以通过getDataFormat()值来判断
yyyy-MM-dd----- 14
yyyy年m月d日--- 31
yyyy年m月------- 57
m月d日 ---------- 58
HH:mm----------- 20
h时mm分 ------- 32
public Object getCellValue(Row row, int column){
Object val = "";
try{
Cell cell = row.getCell(column);
if (cell != null){
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
//val = cell.getNumericCellValue();
short format = row.getCell(column).getCellStyle().getDataFormat();
//判断日期个格式是否是 2017/01/01 这样
/*
* 14 yyyy-MM-dd / 2017/01/01
* 31 yyyy年m月d日
* */
Date date =null;
if(format == 14 || format == 31){
date = HSSFDateUtil.getJavaDate(row.getCell(column).getNumericCellValue());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
String time = sdf.format(date);
val = time;
}else{
//文本
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
}
}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
val = cell.getStringCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
val = cell.getCellFormula();
}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
val = cell.getBooleanCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
val = cell.getErrorCellValue();
}
}
}catch (Exception e) {
return val;
}
return val;
}
下面提供excelpoi的导入导出工具类:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
需要在实体类属性加注解@ExcelField(title="船东", align=2, sort=5)
package net.huadong.common.utils.excel