在Excel导入过程中经常遇到单元格数据格式不正确引发的错误，在赋值前需要做下类型转换。
提供一个类型转换工具类:
/**
* 单元格类型转化工具类
* @param cell
* @return
*/
private Map<String,Object> cellTypeDefine(Cell cell){
Map<String,Object> map = new HashMap<>();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
map.put("COL", cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
map.put("COL", cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
map.put("COL", cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BLANK:
map.put("COL","");
break;
case Cell.CELL_TYPE_FORMULA:
map.put("COL", cell.getCellFormula());
break;
case Cell.CELL_TYPE_ERROR:
map.put("COL","");
break;
default:
map.put("COL", "");
break;
}
return map;
}
类型转换
Excel导入demo实例：
public class ExcelImport {
public String excelImport(File file) {
HashMap<String, Sheet> typeSheet = null;
List<CmWiringPo> wiringlist = new ArrayList<>();
try{
HSSFWorkbook workbook = new HSSFWorkbook() ;
typeSheet = Maps.newHashMap();
Cell cell = null;
Row row = null;
HSSFSheet sheet = workbook.getSheetAt(0);
String sheetName = sheet.getSheetName();
typeSheet.put(sheetName, sheet);
for(int j=1;j < sheet.getLastRowNum()+1;j++){
row = sheet.getRow(j);
CmWiringPo cmWiringPo = new CmWiringPo() ;
//主键生成
cmWiringPo.setId(11L);
for(int k=0;k < row.getLastCellNum();k++){
cell = row.getCell(k);
switch (k) {
case 0:
Long srid = (long)cell.getNumericCellValue();
cmWiringPo.setSrid(srid);
break;
case 1:
cmWiringPo.setSourceCabinetUName(String.valueOf(cellTypeDefine(cell).get("COL")));
break;
default:
break;
}
}
}
}catch (Exception e) {
return null;
}
return wiringlist.toString();
}