POI报表导入

Controller层

@ApiOperation(value = "经营数据分析导入")
@PostMapping("/manage/analyze/template/import")
public Result<ExcelImportResult> manageAnalyzeTemplate(@RequestParam("file") MultipartFile file) {
    if (file == null) {
        return ResultUtil.error(ExceptionEnum.PARAM_ERROR);
    }

    String fileName = file.getOriginalFilename();

    if (!fileName.matches("^.+\\.(?i)(xlsx)$")) {
        return ResultUtil.error(ExceptionEnum.PARAM_ERROR);
    }

    ExcelImportResult res = repoService.manageAnalyzeTemplate(file);
    return ResultUtil.success(res);
} 

Service层 

 public ExcelImportResult manageAnalyzeTemplate(MultipartFile file) {
        ExcelImportResult res = new ExcelImportResult();
        Long successCount = 0L;
        Long errorCount = 0L;
        String errorIds = "";
        try {
            InputStream is = file.getInputStream();
            Workbook wb = new XSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);

            if (sheet.getLastRowNum() > 3) {
                for (int i = 3; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    int cellIndex = 0;
                    String id = String.valueOf((int) (row.getCell(cellIndex) == null ? 0 : row.getCell(cellIndex).getNumericCellValue()));
                    cellIndex++;
                    String rcode = row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue();
                    cellIndex++;
                    String scode = row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue();
                    cellIndex++;
                    String dateMonth = row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue();
                    cellIndex++;
                    if (StringUtils.isEmpty(rcode) || StringUtils.isEmpty(scode) || StringUtils.isEmpty(dateMonth) || dateMonth.split("-").length != 2) {
                        if (!"0".equals(id)) {
                            errorCount++;
                            errorIds += id + ",";
                        }
                        continue;
                    }

                    Regions regions = new Regions();
                    regions.setCode(rcode);
                    regions.setStatus(10);
                    List<Regions> regionList = regionsMapper.select(regions);
                    if (regionList == null && regionList.size() == 0) {
                        errorCount++;
                        errorIds += id + ",";
                        continue;
                    }

                    Sites sites = new Sites();
                    sites.setCode(scode);
                    sites.setStatus(10);
                    List<Sites> sitesList = sitesMapper.select(sites);
                    if (sitesList == null && sitesList.size() == 0) {
                        errorCount++;
                        errorIds += id + ",";
                        continue;
                    }

                    ManageAnalyzeEntity save = new ManageAnalyzeEntity();
                    save.setRegionId(regionList.get(0).getId());
                    save.setSiteId(sitesList.get(0).getId());
                    save.setDateMonth(dateMonth);
                    save.setZdygrs(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setKczcws(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setYzcw(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setRzl(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setSrhjbhs(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCwfsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setHlfsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setKfsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCysr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setRtfsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setJjfsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setQtsr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCbhj(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCbhjbhzj(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setRlcb(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setGz(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setSj(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setQt1(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setYycb(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCycb(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setSdf(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setYlcl(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setWhjwlzc(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setZgfzc(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setQtcb(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setXcggf(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setYwzdf(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setQt2(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setZjf(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCqdtfytx(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setCzbt(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setSqlr(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    save.setGop(row.getCell(cellIndex) == null ? "" : row.getCell(cellIndex).getStringCellValue());
                    cellIndex++;
                    try {
                        manageAnalyzeMapper.insert(save);
                        successCount++;
                    } catch (Exception e) {
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }

        res.setSuccessCount(successCount);
        res.setErrorCount(errorCount);
        res.setErrorIds(errorIds);

        return res;
    }

 

实体类

public class ExcelImportResult implements Serializable {
    @ApiModelProperty("成功导入的数量")
    private Long successCount;

    @ApiModelProperty("导入失败的数量")
    private Long errorCount;

    @ApiModelProperty("导入失败的数据编号，中间使用逗号分隔")
    private String errorIds;
}