记录下用easyexcel怎么读取表格中的图片资源 Published on Jul 24, 2025 in 随笔 with 0 comment ``` @ApiOperation(value = "导入合伙人数据", notes = "从Excel导入合伙人数据") @PostMapping("/import") public ResultResponse> 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 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 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 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 templateList = new ArrayList<>(); List bankSupportVos = getBankSupportList(); int areaIndex = 5; List distributionLevelList = getDistributionLevelList(); int distributionLeveIndex = 8; // 如果传递了字典名,则依据字典建立下拉 Collection values = Optional.ofNullable(bankSupportVos) .orElseThrow(() -> new ServiceException(String.format("开户行不存在"))) .stream().map(BankSupportVo::getBankName).collect(Collectors.toList()); List optionsTemp = new ArrayList<>(values); DropDownOptions cityToArea = new DropDownOptions(areaIndex, optionsTemp); // 如果传递了字典名,则依据字典建立下拉 Collection 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 optionsTemp2 = new ArrayList<>(values2); DropDownOptions cityToArea2 = new DropDownOptions(distributionLeveIndex, optionsTemp2); // 把所有的下拉框存储 List options = new ArrayList<>(); options.add(cityToArea); options.add(cityToArea2); // 接下来需要将Excel中的展示数据转换为对应的下拉选 List 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()))); } } ``` 本文由 admin 创作,采用 知识共享署名4.0 国际许可协议进行许可。本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名。