springboot+poi+vue实现excel导入_springboot + vue poi excel 导入

一、准备工作（可参考上一篇excel导出）

二、实现步骤

        1.将文件“上传到后端”

        2.后端对文件进行解析

        3.把解析后的数据持久化到数据库

三、实现

 1.调用elementui的上传控件

<el-upload style="display: inline-flex;margin-right: 8px"
                     class="upload-demo" :show-file-list="false"
                     :disabled="disableFlag"
                     action="/special/basic/import">
            <el-button type="success" :icon="showIcon" :disabled="disableFlag" size="mini">导入数据</el-button>

2.后端接收数据

 @PostMapping("/import")
    public RespBean specialImport(MultipartFile file){
        //1.对上传的进行解析
       List<SpecialZone> list =  POIUtil.excel2SpecialZone(file);
//    解析后的文件内容存入数据库
      specialZoneService.batchAddSpecial(list);
       return RespBean.ok("导入成功");
    }

3.具体解析方法

 public static List<SpecialZone> excel2SpecialZone(MultipartFile file) {
        List<SpecialZone> list =new ArrayList<>();
        SpecialZone specialZone=null;
        try {
            //1.创建HSSFWorkbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2.创建sheets
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i <numberOfSheets ; i++) {
                //遍历每个sheet 解析sheet
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                //遍历每个sheet的行
                int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    // 标题行跳过
                    if (j==0){
                        continue;
                    }
                    HSSFRow row = sheetAt.getRow(j);
                    // 跳过空行
                    if ( row == null){
                        continue;
                    }
                    // 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    specialZone= new SpecialZone (); //每一行即为一个对象
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        //根据列单元格的格式解析 分为字符串和时间格式
                        switch (cell.getCellType()){
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                //再根据每列序号解析每列
                                switch (k){
                                    case 1:
                                        specialZone.setSpecialZoneNo(cellValue);
                                        break;
                                    case 2:
                                        specialZone.setSpecialZoneName(cellValue);
                                        break;
                                }
                                break;
                                default:
                                    //再根据每列序号解析每列
                                    switch (k){
                                        case 3:
                                            specialZone.setSpecialZoneDate(cell.getDateCellValue());
                                            break;
 
                        }
                    }
                    list.add(specialZone);
                }
            }
 
        } catch (IOException e) {
            e.printStackTrace();
        }
 
        return list;
    }