POI报表导入

50 阅读 0 评论 33 点赞

我是靠谱客的博主标致帅哥，最近开发中收集的这篇文章主要介绍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;
}