excel导入及注意事项

　　在Excel导入过程中经常遇到单元格数据格式不正确引发的错误，在赋值前需要做下类型转换。

提供一个类型转换工具类:

 1 /**
 2      * 单元格类型转化工具类
 3      * @param cell
 4      * @return
 5      */
 6     private Map<String,Object> cellTypeDefine(Cell cell){
 7         Map<String,Object> map = new HashMap<>();
 8         switch (cell.getCellType()) {
 9         case Cell.CELL_TYPE_BOOLEAN:
10             map.put("COL", cell.getBooleanCellValue());
11             break;
12         case Cell.CELL_TYPE_NUMERIC:
13             map.put("COL", cell.getNumericCellValue());
14             break;
15         case Cell.CELL_TYPE_STRING:
16             map.put("COL", cell.getStringCellValue());
17             break;
18         case Cell.CELL_TYPE_BLANK:
19             map.put("COL","");
20             break;
21         case Cell.CELL_TYPE_FORMULA:
22             map.put("COL", cell.getCellFormula());
23             break;
24         case Cell.CELL_TYPE_ERROR:
25             map.put("COL","");
26             break;
27         default:
28             map.put("COL", "");
29             break;
30         }
31         return map;
32     }

类型转换

Excel导入demo实例：

 1 public class ExcelImport {
 2 
 3     public String excelImport(File file) {
 4         HashMap<String, Sheet> typeSheet = null;
 5         List<CmWiringPo> wiringlist = new ArrayList<>();
 6         try{
 7             HSSFWorkbook workbook = new HSSFWorkbook() ;
 8             typeSheet = Maps.newHashMap();
 9             Cell cell = null;
10             Row row = null;
11             HSSFSheet sheet = workbook.getSheetAt(0);
12             String sheetName = sheet.getSheetName();
13             typeSheet.put(sheetName, sheet);
14             
15             for(int j=1;j < sheet.getLastRowNum()+1;j++){
16                 row = sheet.getRow(j);
17                 CmWiringPo cmWiringPo = new CmWiringPo() ;
18                 //主键生成
19                 cmWiringPo.setId(11L);
20                 for(int k=0;k < row.getLastCellNum();k++){
21                     cell = row.getCell(k);
22                     switch (k) {
23                     case 0:
24                         Long srid = (long)cell.getNumericCellValue();
25                         cmWiringPo.setSrid(srid);
26                         break;
27                     case 1:
28                         cmWiringPo.setSourceCabinetUName(String.valueOf(cellTypeDefine(cell).get("COL")));
29                         break;
30 
31                     default:
32                         break;
33                     }
34                 }
35             }
36             
37         }catch (Exception e) {
38             
39             return null;
40         }
41         return wiringlist.toString();
42     }

View Code