apache poi Excel导入、导出单元格图片

本文将在导入Excel时获取其浮动在单元格内的图片（嵌入单元格获取不到），并下载到本地。导出也是浮动式图片。这里只提供导入图片部分的代码，仅供参考。与导入图片无关的代码部分已进行删除，如报错请忽略，关注导入图片业务即可。

业务层代码

   /**
     * 导出Excel XSSFWorkbook
     *
     * @param workbook  工作簿 XSSFWorkbook格式
     * @param dataList  数据集合
     * @param fieldList 字段集合
     * @param sheetName 工作表名称
     */   
 public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response, String processDefinitionKey) {
        long startTime = System.currentTimeMillis();
        LoginUser user = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        try {
            formFieldList = listFormFields(formType, processDefinition.getFormId(), null);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.error("查询表单字段失败！");
        }
        // 文件处理
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        List<String> errorMessageList = new ArrayList<>();
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();
            try {
                // 读取excel
                ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
                List<Map<String, Object>> formList = reader.readAll();
                // 获取所有的图画对象
                Sheet sheet = reader.getSheet();
                List<Map<String, List<PictureData>>> pictureDataList = ExcelHutoolUtil.listSheetPicture(sheet);
                int num = 1;
                // 为了在子线程中获取token，使用InheritableThreadLocal存储token
                InheritableThreadLocal<String> tokenThreadLocal = new InheritableThreadLocal<>();
                tokenThreadLocal.set(UserTokenContext.getToken());
                // 创建线程池
                ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 10, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<>(), Executors.defaultThreadFactory(), new ThreadPoolExecutor.AbortPolicy());
                CountDownLatch countDownLatch = new CountDownLatch(formList.size());
                for (Map<String, Object> map : formList) {
                    int finalNum = num;
                    executor.submit(() -> {
                        TransactionStatus status = null;
                        try {
                            // 在子线程中获取token
                            UserTokenContext.setToken(tokenThreadLocal.get());
                            // 开始事务
                            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
                            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
                            def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
                            status = transactionManager.getTransaction(def);
                            boolean success = false;
                            // 设置重试次数，避免死锁
                            int retries = 3;
                            while (!success && retries > 0) {
                                try {
                                    // 导入单个流程实例
                                    importSingleProcessInstance(processDefinitionKey, user, processDefinition, formType, formId, formFieldList, pictureDataList, map, finalNum);
                                    success = true;
                                } catch (Exception e) {
                                    retries--;
                                    if (retries == 0) {
                                        errorMessageList.add("第" + finalNum + "行数据导入失败！" + e.getMessage());
                                        throw e;
                                    }
                                }
                            }
                            transactionManager.commit(status);
                        } catch (Exception e) {
                            if (status != null) {
                                transactionManager.rollback(status);
                            }
                            e.printStackTrace();
                        } finally {
                            countDownLatch.countDown();
                            UserTokenContext.remove();
                        }
                    });
                    num++;
                }
                executor.shutdown();
                countDownLatch.await();
                log.info("工作流导入成功！耗时：" + (System.currentTimeMillis() - startTime) + "ms");
                return ImportExcelUtil.importReturnResTxt(errorMessageList.size(), formList.size() - errorMessageList.size(), errorMessageList);
            } catch (Exception e) {
                e.printStackTrace();
                return Result.error("文件导入失败！" + e.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.error("文件导入失败！");
    }




    /**
     * 导出Excel HSSFWorkbook
     *
     * @param workbook  工作簿 HSSFWorkbook格式
     * @param dataList  数据集合
     * @param fieldList 字段集合
     * @param sheetName 工作表名称
     */
    public void exportExcel(HSSFWorkbook workbook, List<Map<String, Object>> dataList, List<Map<String, String>> fieldList, String sheetName) {
        // 开始时间
        long startTime = System.currentTimeMillis();
        Sheet sheet = workbook.createSheet(sheetName);
        Row headerRow = sheet.createRow(0);
        CellStyle headStyle = getHeadStyle(workbook);
        CreationHelper creationHelper = workbook.getCreationHelper();
        for (int i = 0; i < fieldList.size(); i++) {
            Map<String, String> map = fieldList.get(i);
            String columnName = map.get("name");
            // 必填项标识*，放在列名前面，例：*姓名
            if (null != map.get("mustFlag") && "1".equals(map.get("mustFlag"))) {
                String asterisk = "*";
                columnName = asterisk + columnName;
            }
            Cell headerCell = headerRow.createCell(i);
            headerCell.setCellValue(columnName);
            headerCell.setCellStyle(headStyle);
            // 判断当前列是否为importOrgType列，如果是则设置下拉框的数据验证
            if (isImportOrgTypeColumn(map)) {
                Sheet orgTypeSheet = getOrgTypeSheet(workbook, map);
                setDropDownValidation(sheet, i, 1, 9999, orgTypeSheet);
            }
            sheet.setColumnWidth(i, 25 * 256);
        }
        // 输出内容
        if (dataList != null && !dataList.isEmpty()) {
            CellStyle bodyStyle = getBodyStyle(workbook);
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                int rowIndex = i + 1;
                Row row = sheet.createRow(rowIndex);
                for (int j = 0; j < fieldList.size(); j++) {
                    Map<String, String> fieldMap = fieldList.get(j);
                    String columnName = fieldMap.get("code");
                    Object value = map.get(columnName);
                    Cell cell = row.createCell(j);
                    if (value instanceof Date) {
                        cell.setCellValue((Date) value);
                        bodyStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                        cell.setCellStyle(bodyStyle);
                    } else {
                        cell.setCellValue(value != null ? value.toString() : "");
                        // 判断value中结尾是否为.jpeg/.jpg/.png，如果包含则为图片类型，设置单元格格式为图片
                        if (null != value && (String.valueOf(value).endsWith(".jpeg") || String.valueOf(value).endsWith(".jpg") || String.valueOf(value).endsWith(".png"))) {
                            try {
                                // 计算耗时
                                setPictures(sheet, value.toString(), rowIndex, j);
                                cell.setCellValue("");
                            } catch (Exception e) {
                                e.printStackTrace();
                                log.error("导出Excel设置图片失败：{}", e.getMessage());
                            }
                        }
                        cell.setCellStyle(bodyStyle);
                    }
                }
            }
        }
        log.info("导出Excel耗时：{}ms", System.currentTimeMillis() - startTime);
    }

importSingleProcessInstance

    /**
     * 导入单个流程实例
     *
     * @param processDefinitionKey 流程定义key
     * @param user                 登录用户
     * @param processDefinition    流程定义
     * @param formType             表单类型
     * @param formId               表单id
     * @param formFieldList        表单字段
     * @param pictureDataList      图片数据
     * @param map                  excel数据
     * @param num                  行数
     */
    @Transactional(rollbackFor = Exception.class)
    public void importSingleProcessInstance(String processDefinitionKey, LoginUser user, BpmProcessDefinitionExtDO processDefinition, Integer formType, String formId, List<Map<String, String>> formFieldList, List<Map<String, List<PictureData>>> pictureDataList, Map<String, Object> map, int num) throws Exception {
        Map<String, Object> form = new HashMap<>();
        // 列索引
        int columnNum = 0;
        // 定义图片路径列表
        List<String> picPathList = new ArrayList<>();
        try {
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                for (Map<String, String> formField : formFieldList) {
                    // 如果是必填字段，去掉excel表头中的*号并判断数据是否为空，为空则抛出异常
                    if (null != formField.get("mustFlag") && formField.get("mustFlag").equals("1")) {
                        key = key.replace("*", "");
                    }
                    if (key.equals(formField.get("name"))) {
                        // mustFlag为1表示必填字段，如果为空则抛出异常
                        boolean isMustField = null != formField.get("mustFlag") && "1".equals(formField.get("mustFlag"));
                        boolean isValueEmpty = value == null || "".equals(value);
                        if (isMustField && isValueEmpty) {
                            throw new JeecgBootException(formField.get("name") + "不能为空");
                        }
                        String code = formField.get("code");
                        // 图片处理
                        if (CollUtil.isNotEmpty(pictureDataList) && pictureDataList.size() > 0) {
                            String picPath = ExcelHutoolUtil.readPic(pictureDataList, num, columnNum);
                            if (StrUtil.isNotEmpty(picPath)) {
                                value = picPath;
                                // 保存图片路径
                                String[] split = picPath.split(",");
                                for (String s : split) {
                                    picPathList.add(s);
                                }
                            }
                        }
                        form.put(code, value);
                        break;
                    }
                }
                columnNum++;
            }
            if (form.size() == 0) {
                throw new JeecgBootException("第" + num + "行数据不符合规范！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 删除图片，判断picPathList是否为空，不为空则删除图片
            if (CollUtil.isNotEmpty(picPathList)) {
                picPathList.stream().map(picPath -> excelHutoolUtil.getPicObjectName(picPath)).forEach(picObjectName -> MinioUtil.removeObject(MinioUtil.getBucketName(), picObjectName));
            }
            throw new JeecgBootException(e.getMessage());
        }
    }

ExcelUtil

   /**
     * 读取图片
     *
     * @param pictureDataList 图片集合
     * @param rowIndex        图片所在单元格的行
     * @param columnIndex     图片所在单元格的列
     * @return String 图片路径
     * @throws Exception
     */
    public static String readPic(List<Map<String, List<PictureData>>> pictureDataList, int rowIndex, int columnIndex) throws Exception {
        if (pictureDataList == null || pictureDataList.isEmpty()) {
            return null;
        }
        String key = rowIndex + "-" + columnIndex;
        for (Map<String, List<PictureData>> map : pictureDataList) {
            if (map.containsKey(key)) {
                List<PictureData> pictureList = map.get(key);
                String[] filePaths = new String[pictureList.size()];

                for (int i = 0; i < pictureList.size(); i++) {
                    PictureData picture = pictureList.get(i);
                    byte[] imageData = picture.getData();
                    String fileExtension = picture.suggestFileExtension();
                    InputStream inputStream = new ByteArrayInputStream(imageData);
                    String fileName = UUID.randomUUID().toString().replaceAll("-", "");
                    fileName = fileName + "." + fileExtension;
                    MultipartFile multipartFile = new MockMultipartFile(fileName, fileName, ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
                    String filePath = MinioUtil.upload(multipartFile, "temp");
                    filePaths[i] = filePath;
                }
                // 匹配成功，去掉图片数据
                map.remove(key);
                return String.join(",", filePaths);
            }
        }
        return null;
    }


    /**
     * 获取工作表Sheet的所有图片，并设置key为图片所在单元格的行索引+列索引，value为图数据
     *
     * @param sheet 工作表
     * @return List<Map < String, List < PictureData>>> 图片集合
     */
    public static List<Map<String, List<PictureData>>> listSheetPicture(Sheet sheet) {
        List<Map<String, List<PictureData>>> pictureList = new ArrayList<>();
        Workbook workbook = sheet.getWorkbook();
        ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 10, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<>(), Executors.defaultThreadFactory(), new ThreadPoolExecutor.AbortPolicy());
        if (workbook instanceof HSSFWorkbook) {
            HSSFSheet hssfSheet = (HSSFSheet) sheet;
            HSSFPatriarch drawingPatriarch = hssfSheet.getDrawingPatriarch();
            if (null == drawingPatriarch) {
                return pictureList;
            }
            List<HSSFShape> hssfShapeList = drawingPatriarch.getChildren();
            ConcurrentHashMap<String, List<PictureData>> map = new ConcurrentHashMap<>();
            for (HSSFShape shape : hssfShapeList) {
                if (shape instanceof HSSFPicture) {
                    executor.submit(() -> {
                        HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                        HSSFPicture picture = (HSSFPicture) shape;
                        HSSFPictureData pictureData = picture.getPictureData();
                        String key = anchor.getRow1() + "-" + anchor.getCol1();
                        if (map.containsKey(key)) {
                            map.get(key).add(pictureData);
                        } else {
                            List<PictureData> dataList = new ArrayList<>();
                            dataList.add(pictureData);
                            map.put(key, dataList);
                        }
                    });
                }
            }
            executor.shutdown();
            try {
                executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            if (!map.isEmpty()) {
                pictureList.add(map);
            }
        } else if (workbook instanceof XSSFWorkbook) {
            XSSFSheet xssfSheet = (XSSFSheet) sheet;
            XSSFDrawing drawingPatriarch = xssfSheet.getDrawingPatriarch();
            if (null == drawingPatriarch) {
                return pictureList;
            }
            List<XSSFShape> xssfShapeList = drawingPatriarch.getShapes();
            ConcurrentHashMap<String, List<PictureData>> map = new ConcurrentHashMap<>();
            for (XSSFShape shape : xssfShapeList) {
                if (shape instanceof XSSFPicture) {
                    executor.submit(() -> {
                        XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                        XSSFPicture picture = (XSSFPicture) shape;
                        XSSFPictureData pictureData = picture.getPictureData();
                        String key = anchor.getRow1() + "-" + anchor.getCol1();
                        if (map.containsKey(key)) {
                            map.get(key).add(pictureData);
                        } else {
                            List<PictureData> dataList = new ArrayList<>();
                            dataList.add(pictureData);
                            map.put(key, dataList);
                        }
                    });
                }
            }
            executor.shutdown();
            try {
                executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            if (!map.isEmpty()) {
                pictureList.add(map);
            }
        }
        return pictureList;
    }

    /**
     * 下载图片
     *
     * @param pic 图片路径
     * @return 图片数据
     */
    private byte[] downloadAndProcessImage(String pic) {
        // 根据图片路径获取图片对象名称
        String objectName = getPicObjectName(pic);
        return minioService.downloadFile(objectName);
    }

    /**
     * 根据图片路径获取图片对象名称
     * Minio
     *
     * @param pic 图片路径
     * @return 图片对象名称
     */
    public String getPicObjectName(String pic) {
        String minioUrl = minioPrevUrl;
        if (!minioUrl.endsWith("/")) {
            minioUrl = minioUrl + "/";
        }
        int startIndex = pic.indexOf(minioUrl);
        int endIndex = pic.indexOf("/", startIndex + minioUrl.length());
        String url = pic.substring(0, endIndex + 1);
        String objectName = pic.replace(url, "");
        return objectName;
    }

    /**
     * 设置图片
     *
     * @param sheet       工作表
     * @param picPaths    图片路径，多个图片用逗号隔开
     * @param rowIndex    行索引
     * @param columnIndex 列索引
     */
    public void setPictures(Sheet sheet, String picPaths, int rowIndex, int columnIndex) {
        String[] pics = picPaths.split(",");
        sheet.setColumnWidth(columnIndex, 4800);
        sheet.getRow(rowIndex).setHeight((short) (1000 * pics.length));
        HSSFPatriarch drawing = (HSSFPatriarch) sheet.createDrawingPatriarch();
        int mar = 5 + 5 + (pics.length - 1) * 5;
        int ave = (255 - mar) / pics.length;
        for (int i = 0; i < pics.length; i++) {
            String pic = pics[i];
            // 根据图片路径下载图片
            byte[] imageData = downloadAndProcessImage(pic);
            // 处理图片，将图片写入工作簿
            String suffix = pic.substring(pic.lastIndexOf(".") + 1);
            HSSFClientAnchor anchor = new HSSFClientAnchor();
            anchor.setDx1(20);
            anchor.setDy1(5 * (i + 1) + ave * i);
            anchor.setDx2(1003);
            anchor.setDy2((5 + ave) * (i + 1));
            anchor.setCol1(columnIndex);
            anchor.setRow1(rowIndex);
            anchor.setCol2(columnIndex);
            anchor.setRow2(rowIndex);
            if ("jpg".equals(suffix) || "jpeg".equals(suffix)) {
                drawing.createPicture(anchor, sheet.getWorkbook().addPicture(imageData, HSSFWorkbook.PICTURE_TYPE_JPEG));
            } else if ("png".equals(suffix)) {
                drawing.createPicture(anchor, sheet.getWorkbook().addPicture(imageData, HSSFWorkbook.PICTURE_TYPE_PNG));
            }
        }
    }

注意：导出图片时，只有将工作簿设置成HSSFWorkbook 才有效，具体原因未知。