POI导入Excel

需要的pom依赖

       <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

controller层方法

    @PostMapping("/importAssetsList")
    @ApiOperation(value = "数据导入资产")
    public ResponseEntity importTaskList(@ApiParam(value = "需要导入的excel文件", required = true)
                                         @RequestParam() MultipartFile file, HttpServletRequest request) throws Exception {
        try {
            long start = System.currentTimeMillis();
            if (null == file ) {
                return ResponseEntity.status(HttpStatus.BAD_REQUEST).body("导入格式不正确，请重新导入");
            }
            //判断file不为空，并且导入的是excel格式文件
            String excelName = file.getOriginalFilename();
            if (StringUtils.isEmpty(excelName) || !(excelName.endsWith(".xls") || excelName.endsWith(".xlsx"))) {
                return ResponseEntity.status(HttpStatus.BAD_REQUEST).body("导入格式不正确，请重新导入");
            }
            if (file.getSize() > Const.LIMIT_SIZE) {
                return ResponseEntity.status(HttpStatus.BAD_REQUEST).body("导入Excel文件过大");
            }
            long start1 = System.currentTimeMillis();
            Sheet sheet = ImportUtils.getSheet(file);
            long end1 = System.currentTimeMillis();
            LOGGER.info(String.format("\n\n------读取文件数据耗时：" + (end1 - start1) +
                    "ms;" + sheet.getLastRowNum() + "行"));

            if(!checkTiles(sheet)){
                return ResponseEntity.status(HttpStatus.UNSUPPORTED_MEDIA_TYPE).body("导入错误，请核对模版信息");
            }
            if (sheet.getLastRowNum() > 2000) {
                return ResponseEntity.status(HttpStatus.BAD_REQUEST).body("导入数据不能超过两千条");
            } else if (sheet.getLastRowNum() < 1) {
                return ResponseEntity.status(HttpStatus.PAYMENT_REQUIRED).body("不能导入空excel");
            }
            List<AssetsInventory> assetsList = EasyPoiExcelUtil.importExcel(file, 0, 1, AssetsInventory.class);
           
            //入库批量添加
            if (assetsList != null && assetsList.size() > 0 ) {
                long start2 = System.currentTimeMillis();
                assetsInventoryService.insertList(assetsList);
                long end2 = System.currentTimeMillis();
                LOGGER.info(String.format("\n\n------插入数据耗时：" + (end2 - start2) +
                        "ms;" + assetsList.size() + "条"));
            }

            Map map = new HashMap();
            map.put("asssetsListReturn", asssetsListReturn);
            return ResponseEntity.ok(map);
        } catch (Exception e) {
            exceptionService.handler("导入资产异常", e, request);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(Const.GLOBAL_ERROR_INFO);
        }
     }

    //校验导入excel列表头是否与模版一致, 是返回true, 否则false
     private boolean checkTiles(Sheet sheet){
        String cnt = "\n（请不要删除列）";
        String resultCnt = "\n（请选择“1采购合同”“2电子网购”）\n（请不要删除列）";
        String[] titles = new String[]{"资产名称" + cnt,  "品牌" + cnt, "型号" + cnt, "规格/配置" + cnt,
                "特性值" + cnt, "SN码" + cnt, "启用日期" + cnt, "责任人" + cnt, "使用人" + cnt,  "一级部门" + cnt, "二级部门" + cnt, "三级部门" + cnt,
                "公司主体名称" + cnt,"资产状态名称" + cnt, "地点名称" + cnt,
                "存放地点" + cnt, "具体位置" + cnt, "管理条线名称" + cnt, "资产大类名称" + cnt,  "资产小类名称" + cnt,  "实物分类名称" + cnt, "数量" + cnt,
                "单位" + cnt, "资产原值" + cnt, "资产净值" + cnt, "残值" + cnt, "供应商" + cnt, "PO单号" + cnt, "PO接收编码" + cnt,
                "来源" + resultCnt};// 设置列名

        //存放导入excel的列表头
        List<String> titleList = new ArrayList<>();
        Row rows = sheet.getRow(0);
        for (Cell c: rows) {
            titleList.add(c.getStringCellValue());
        }

        //是否缺少列头
        if(titles.length > titleList.size()){
            return false;
        }

        //检验列表头里是否包含了模版里的全部列
        for (String title: titles) {
            if(!titleList.contains(title)){
                return false;
            }
        }
        return true;
    }

Util工具类中方法(ImportUtils工具类)

   //获取导入sheet
   public static Sheet getSheet(MultipartFile file) throws IOException, InvalidFormatException {
        InputStream input = file.getInputStream();
        Workbook book = WorkbookFactory.create(input);
        return book.getSheetAt(0);
    }

Util工具类中方法(EasyPoiExcelUtil工具类)

//通过文件导入excel
public static <T> List<T> importExcel(
            MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IllegalArgumentException("excel文件不能为空");
        } catch (Exception e) {
            throw new IllegalArgumentException(e.getMessage());
        }
        return list;
    }