添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2007及2007以上,xlsx后缀,使用XSSFWorkbook获取工作簿对象
获取单元格各类型值,返回字符串类型
public String getCellValueByCell(Cell cell) {
//判断是否为null或空串
if (cell==null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
if (cell.getCellType()==CellType.NUMERIC) {
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
} finally{
sdf = null;
}
} else {
BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 用BigDecimal包装再获取plainString,可以防止获取到科学计数值
}
} else if (cell.getCellType()==CellType.STRING) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellType()==CellType.BOOLEAN) {
cellValue = cell.getBooleanCellValue()+"";
} else if (cell.getCellType()==CellType.FORMULA) {
cellValue = cell.getCellFormula();
} else {
cellValue = "";
}
//返回单元格值并去除首尾空格,去除数字最后解析出.0结尾
if (cellValue.trim().endsWith(".0")) {
return cellValue.trim().split("\\.0")[0];
}
return cellValue.trim();
}
判断行是否为空
public boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) {
return false;
}
}
return true;
}
测试
//读取第一个工作表
XSSFSheet sheet0 = xssfWorkbook.getSheetAt(0);
//判断工作表第一行是否为空
isRowEmpty(sheet0.getRow(0))
//读取第一行第一列数据
String data= getCellValueByCell(sheet0.getRow(0).getCell(0));
//转换成指定类型数据
Double.parseDouble(data);//数值类型
new SimpleDateFormat("yyyy-MM-dd").parse(data);//日期类型