@ApiOperation(value = "导入合伙人数据", notes = "从Excel导入合伙人数据")
@PostMapping("/import")
public ResultResponse<Map<String, Object>> importIssue(@RequestParam("file") MultipartFile file) throws IOException {
if (file.isEmpty()) {
return ResultResponse.fail("上传文件不能为空");
}
// 文件类型校验
String originalFilename = file.getOriginalFilename();
if (originalFilename == null || !(originalFilename.endsWith(".xls") || originalFilename.endsWith(".xlsx"))) {
return ResultResponse.fail("只支持.xls和.xlsx格式的文件");
}
// 处理解析结果
ExcelResult<DistributionApplyExcelDTO> excelResult =
ExcelUtil.importExcel(file.getInputStream(), DistributionApplyExcelDTO.class, new ExportDemoListener());
try {
ZipSecureFile.setMinInflateRatio(-1.0d);
OPCPackage opcPackage = OPCPackage.open(file.getInputStream());
XSSFWorkbook book = new XSSFWorkbook(opcPackage);
// 方式2 获取sheet数量，直接遍历读取每个工作表数据
for (Sheet sheet : book) {
XSSFSheet xssSheet = (XSSFSheet) sheet;
// 获取工作表中绘图包
XSSFDrawing drawing = xssSheet.getDrawingPatriarch();
if (drawing == null) {
break;
}
// 获取所有图像形状
List<XSSFShape> shapes = drawing.getShapes();
// 遍历所有形状
for (XSSFShape shape : shapes) {
// 获取形状在工作表中的顶点位置信息（anchor锚点）
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
// 图片形状在工作表中的位置, 所在行列起点和终点位置
short c1 = anchor.getCol1();
int r1 = anchor.getRow1();
String key = r1 + "行," + c1 + "列";
if (shape instanceof XSSFPicture) {
try {
XSSFPicture pic = (XSSFPicture) shape;
// 形状获取对应的图片数据
XSSFPictureData picData = pic.getPictureData();
// 保存图片到本地
byte[] data = picData.getData();
// TODO 这里上传文件至oss并生成链接，这里不做过多描述，有疑问请参照oss服务调用
// 创建MockMultipartFile对象，直接使用图片字节数组
String fileName = "image_" + UUID.randomUUID() + "." + picData.suggestFileExtension();
String contentType = picData.suggestFileExtension().equals("jpg") ? "image/jpeg" : "image/png"; // 根据文件扩展名确定内容类型
// MockMultipartFile multipartFile = new MockMultipartFile("file", "text/plain" + picData.suggestFileExtension(), contentType, data);
MockMultipartFile multipartFile = new MockMultipartFile("file", fileName, contentType, data);
SysFile sysFile = remoteFileService.upload(fileName, multipartFile.getOriginalFilename(),
multipartFile.getContentType(), multipartFile.getBytes());
if (ObjectUtil.isNull(sysFile)) {
return ResultResponse.fail("文件服务异常，请联系管理员");
}
// TODO 放入excel集合，这里行数要减去1，获取图片是从表头开始（表头位置为0），获取excelVos是从数据开始（第一条数据位置为0）他们相差一个表头，所以要减去1才能对应
if (c1 == 6) {
excelResult.getList().get(r1 - 1).setFrontIdCard(sysFile.getUrl());
}
if (c1 == 7) {
excelResult.getList().get(r1 - 1).setBackIdCard(sysFile.getUrl());
}
} catch (Exception e) {
log.error("asyncImportList XSSFClientAnchor key|{} error|{}", key, e.getMessage());
}
}
}
}
} catch (Exception e) {
log.error("asyncImportList XSSFWorkbook error|{}", e.getMessage());
}
// 调用服务层方法进行导入
Map<String, Object> result = distributionService.importDistributionFromExcel(excelResult.getList());
if (StringUtils.isNotBlank(result.get("failMsg").toString())) {
throw new ServiceException(result.get("failMsg").toString());
}
return ResultResponse.success(result);
}
@ApiOperation(value = "下载合伙人导入模板", notes = "下载Excel格式的合伙人导入模板")
@PostMapping("/import/template")
public void downloadImportTemplate(HttpServletResponse response) throws IOException {
try {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("合伙人导入模板", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 创建一个空的模板数据列表
List<DistributionApplyExcelDTO> templateList = new ArrayList<>();
List<BankSupportVo> bankSupportVos = getBankSupportList();
int areaIndex = 5;
List<DistributionLevelConfig> distributionLevelList = getDistributionLevelList();
int distributionLeveIndex = 8;
// 如果传递了字典名，则依据字典建立下拉
Collection<String> values = Optional.ofNullable(bankSupportVos)
.orElseThrow(() -> new ServiceException(String.format("开户行不存在")))
.stream().map(BankSupportVo::getBankName).collect(Collectors.toList());
List<String> optionsTemp = new ArrayList<>(values);
DropDownOptions cityToArea = new DropDownOptions(areaIndex, optionsTemp);
// 如果传递了字典名，则依据字典建立下拉
Collection<String> values2 = Optional.ofNullable(distributionLevelList)
.orElseThrow(() -> new ServiceException(String.format("合伙人等级配置不存在")))
.stream().map(everyOptions -> {
// 通过 DropDownOptions.createOptionValue 来构建 OptionValue
return DropDownOptions.createOptionValue(everyOptions.getLevelName(), everyOptions.getLevel());
}).collect(Collectors.toList());
List<String> optionsTemp2 = new ArrayList<>(values2);
DropDownOptions cityToArea2 = new DropDownOptions(distributionLeveIndex, optionsTemp2);
// 把所有的下拉框存储
List<DropDownOptions> options = new ArrayList<>();
options.add(cityToArea);
options.add(cityToArea2);
// 接下来需要将Excel中的展示数据转换为对应的下拉选
List<DistributionApplyExcelDTO> outList = StreamUtils.toList(templateList, everyRowData -> {
// 只需要处理没有使用@ExcelDictFormat注解的下拉框
// 一般来说，可以直接在数据库查询即查询出省市县信息，这里通过模拟操作赋值
everyRowData.setSettlementBankBranchName(buildOptions(bankSupportVos, everyRowData.getSettlementBankBranchId()));
everyRowData.setBrokerageLevelName(buildOptions2(distributionLevelList, everyRowData.getBrokerageLevel()));
return everyRowData;
});
// 使用EasyExcel写入响应流
ExcelUtil.exportExcel(outList, "合伙人数据", DistributionApplyExcelDTO.class, response, options);
} catch (Exception e) {
e.printStackTrace();
log.error("下载模板异常", e);
// 重置响应
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSON.toJSONString(ResultResponse.fail("下载模板失败：" + e.getMessage())));
}
}
