之前项目有个读取excel数据并落库的处理,因为没有处理空行导致失败。
//读取解析文件将其数据存放在List中
public List ReadFromExcel(String fileName){
List result=new ArrayList();
File file=new File(fileName);
try{
InputStream in = new FileInputStream(file);
Workbook wb=WorkbookFactory.create(in);
Sheet sheet=wb.getSheetAt(0);
int len=sheet.getLastRowNum()+1;
for(int i=1;i<len;i++){
Row row= sheet.getRow(i);
if(IsRowEmpty(row)){
continue;
}
Row rowtitle=sheet.getRow(0);
int lenj=row.getLastCellNum();
HashMap rowMap=new HashMap();
HashMap map=new HashMap();
for(int j=0;j<lenj;j++){
Cell cell = row.getCell(j);
Cell cellTitle=rowtitle.getCell(j);
String titleStr=getCellValue(cellTitle);
String str=getCellValue(cell);
map.put(titleStr, str);
}
rowMap.put(i-1, map);
result.add(rowMap);
}
}catch(Exception e){
e.printStackTrace();
}
return result;
}
//获取单元格的值
@SuppressWarnings("unused")
public String getCellValue( Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
//判断单元格数据的类型,不同类型调用不同的方法
switch (cell.getCellType()) {
//数值类型
case Cell.CELL_TYPE_NUMERIC:
//进一步判断 ,单元格格式是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
Date date=cell.getDateCellValue();
cellValue=new SimpleDateFormat("yyyy-MM-dd").format(date);
//cellValue = formatter.formatCellValue(cell);
} else {
//数值
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//判断单元格是公式格式,需要做一种特殊处理来得到相应的值
case Cell.CELL_TYPE_FORMULA:{
try{
cellValue = String.valueOf(cell.getNumericCellValue());
}catch(IllegalStateException e){
cellValue = String.valueOf(cell.getRichStringCellValue());
}
}
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
//判断读取excel是否空行 空行时返回true 非空行直接返回false
public static boolean IsRowEmpty(Row row){
for(int i=row.getFirstCellNum();i<row.getLastCellNum();i++){
Cell cell=row.getCell(i);
if(cell!=null&&cell.getCellType()!=cell.CELL_TYPE_BLANK){
return false;
}
}
return true;
}