使用POI导入Excel数据

首先引入配置

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

@SneakyThrows
@Override
public ActionResult testImportExcel(MultipartFile fileName) {
   ContractDto contractDto = new ContractDto();
   InputStream stream = null;
   try {
        // 创建改文件的输入流
        stream = fileName.getInputStream();
        // 创建工作簿
        Workbook workbook = WorkbookFactory.create(stream);
        // 获取一个工作表，下标从0开始
         Sheet sheet = workbook.getSheetAt(0);
            // 通过循环，逐行取出表中每行数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                // 获取行
                Row row = sheet.getRow(i);
                // 获取行中列的数据
                String[] value = new String[5];
                //判断合同编号不为空
                if (isEmptyCell(row.getCell(0))) {
                    throw new ExcelException("合同编号不能为空");
                }
                //判断租户名称不为空
                if (isEmptyCell(row.getCell(1))) {
                    throw new ExcelException("租户名称不能为空");
                }
                //判断单元面积不为空
                if (isEmptyCell(row.getCell(2))) {
                    throw new ExcelException("摊位面积不能为空");
                }
                //判断租户手机号不为空
                if (isEmptyCell(row.getCell(3))) {
                    throw new ExcelException("租户手机号不能为空");
                }
                //判断身份证号不为空
                if (isEmptyCell(row.getCell(4))) {
                    throw new ExcelException("身份证号不能为空");
                }


                row.getCell(0).setCellType(CellType.STRING);
                row.getCell(1).setCellType(CellType.STRING);
                row.getCell(2).setCellType(CellType.STRING);
                row.getCell(3).setCellType(CellType.STRING);
                row.getCell(4).setCellType(CellType.STRING);

                value[0] = row.getCell(0).getStringCellValue();
                value[1] = row.getCell(1).getStringCellValue();
                value[2] = row.getCell(2).getStringCellValue();
                value[3] = row.getCell(3).getStringCellValue();
                value[4] = row.getCell(4).getStringCellValue();


                if (!isEmptyCell(row.getCell(2))) {
                    String stallExtent = row.getCell(2).getStringCellValue();
                    if (!stallExtent.matches("^[+-]?\\d+(\\.\\d*)?")) {
                        throw new ExcelException("摊位面积请输入正确的数字");
                    }
                }
               //赋值
                contractDto.setContractNumber(row.getCell(0).getStringCellValue());
                contractDto.setTenantName(row.getCell(1).getStringCellValue());
                //面积
                if (StringUtil.isNotEmpty(row.getCell(2).getStringCellValue())) {
                    contractDto.setStallExtent(new BigDecimal(row.getCell(2).getStringCellValue()));
                }
                contractDto.setTenantPhone(row.getCell(3).getStringCellValue());
                contractDto.setIdCard(row.getCell(4).getStringCellValue());

                //打印拿到的数据
                System.out.println(contractDto.toString());
                //插入数据库
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.info("抛出异常日志:{}", e.toString());
            throw new ExcelException(e.getMessage());
        }  finally {
            if (stream != null) {
                try {
                    stream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    log.info("抛出异常日志:{}", e.toString());
                }
            }
        }
        return ActionResult.success();
    }

//判断单个单元格是否为空
public static boolean isEmptyCell(Cell cell) {
if (cell == null || cell.getCellType().equals(CellType.BLANK)) {
      return true;
    }
      return false;
}

测试结果: