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; }