通用的导入excel模板样式

1.controller

    /**
     *导入Excel表
     */
    @RequestMapping(value="/importEmp",method = RequestMethod.POST)
    public ResponseEntity<?> importEmp(@RequestParam("file") MultipartFile file)throws Exception {
        //判断文件是否为空
        if (file == null) {
            return new ResponseEntity("文件为空", HttpStatus.OK);
        }
        //获取文件名
        String name = file.getOriginalFilename();
        //进一步判断文件是否为空（即判断其大小是否为0或其名称是否为null）
        long size = file.getSize();
        if (name == null || ("").equals(name) && size == 0) {
            return new ResponseEntity("文件为空", HttpStatus.OK);
        }
        //  获取当前导入的储存对象
        Expert  expert =new Expert();
        List  list =new ArrayList<Expert>();
        //通过这个方法 ，将excel里的数据 以list列表方式返回
        List list1 = excelService.batchImport(name, file, expert, list);
        for (int i=0;i<list1.size();i++) {
            expertService.insert((Expert) list1.get(i));
        }
        return  new ResponseEntity("成功", HttpStatus.OK);
    }

2.service.impl

public List batchImport(String name, MultipartFile file, Object object,List list) throws NoSuchFieldException, IllegalAccessException {

        Workbook workbook = null;
        try {
             // 判断是哪种后缀
            boolean isExcel2003 = name.endsWith("xls")?true:false;
            if(isExcel2003){
                workbook = new HSSFWorkbook(file.getInputStream());
            }else{
                workbook = new XSSFWorkbook(file.getInputStream());
            }
      
        } catch (Exception e) {
            e.printStackTrace();
        }
        Sheet hssfSheet = workbook.getSheetAt(0);  //可能多个execl表格
        Row hssfRow = hssfSheet.getRow(0);      //第一行数据
         //通过第一行的名称，通过数据库表查询对应的字段名
        for (int i = 0; i < hssfRow.getPhysicalNumberOfCells(); i++) {
            String cell = hssfRow.getCell(i).getStringCellValue();
            Excel excel = selectById(cell);
            if (excel == null) {
                new IllegalAccessException("没找到对应的key");
            }
            hssfRow.getCell(i).setCellValue(excel.getKey());
        }
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            for (int i = 0; i < hssfRow.getPhysicalNumberOfCells(); i++) {
                String stringCellValue = hssfRow.getCell(i).getStringCellValue();
                Cell cell = hssfSheet.getRow(rowNum).getCell(i);
                Class<?> aClass = object.getClass();
                Field field = aClass.getDeclaredField(stringCellValue);
                field.setAccessible(true);
                if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    field.set(object,String.valueOf(cell.getNumericCellValue()));
                }
                if (cell.getCellTypeEnum() ==CellType.STRING) {
                    field.set(object,cell.getStringCellValue());
                }
            }
            list.add(object);
        }
        return list;
    }

3.数据库定义

通过查询value 找到对应的key  数据库字段

4.pom.xml

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
       <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
        </dependency>

总结：

通过sql ,key value的储存，查找想要的key 的字段名 ，在通过对应的字段名来储存到list里
后续可以修改对应的sql里面的value数据。来控制所有的导入