Java使用EasyExcel导出图片（原比例大小）到Excel中

EasyExcel导出图片

又开始写Excel导出的需求了，哈哈哈……

目前的需求是将图表分析对的饼图和折线图，也就是一张完整的图片单独导出到Excel中

为了方便客户在业务报告时，可以使用数据分析图片，从而更清晰准确地展示数据趋势

因此关键点是将图片原比例尺寸大小导出，不能进行压缩

原数据是由图表📈+表格数据组成，下图所示：

现在需要将上述数据，分两个Sheet导出到Excel：图表📈Sheet +表格数据Sheet

关于表格数据导出，此处就不做展示了，往期的Excel导出有文章说明，此文只导出图表📈Sheet

步骤1：将图片保存成url

将图片转为url的形式传参

{
  "imagePath": "http://www.echola.com/8pJTiC_1724644117200.png",
   …… //其他参数
}

步骤2：自定义图片导出工具类

@Slf4j
public class CustomImageStrategy implements CellWriteHandler {

    private final HashMap<String, List<ImageData>> imageDataMap = new HashMap<>(16);

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            return;
        }
        // 将单元格图片数据复制出来，清空单元格图片数据
        if (!CollectionUtils.isEmpty(cellData.getImageDataList())) {
            imageDataMap.put(cell.getRowIndex() + "_" + cell.getColumnIndex(), cellData.getImageDataList());
            cellData.setType(CellDataTypeEnum.EMPTY);
            cellData.setImageDataList(new ArrayList<>());
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead || CollectionUtils.isEmpty(cellDataList)) {
            return;
        }
        String key = cell.getRowIndex() + "_" + cell.getColumnIndex();
        List<ImageData> imageDataList = imageDataMap.get(key);
        if (CollectionUtils.isEmpty(imageDataList)) {
            return;
        }

        // 插入图片
        for (int i = 0; i < imageDataList.size(); i++) {
            ImageData imageData = imageDataList.get(i);
            if (ObjectUtils.isEmpty(imageData)) {
                continue;
            }
            byte[] image = imageData.getImage();
            this.insertImage(writeSheetHolder.getSheet(), cell, image, i);
        }
        imageDataMap.remove(key);
    }

    private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) {
        // 读取图片的宽度和高度
        int pictureWidth = 0;
        int pictureHeight = 0;
        try (ByteArrayInputStream bis = new ByteArrayInputStream(pictureData)) {
            BufferedImage bufferedImage = ImageIO.read(bis);
            pictureWidth = bufferedImage.getWidth();
            pictureHeight = bufferedImage.getHeight();
        } catch (Exception e) {
            log.error("Error reading image dimensions", e);
        }

        // 将像素转换为 EMU 单位
        int emuWidth = Units.pixelToEMU(pictureWidth);
        int emuHeight = Units.pixelToEMU(pictureHeight);
        int index = sheet.getWorkbook().addPicture(pictureData, Workbook.PICTURE_TYPE_PNG);
        Drawing<?> drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        CreationHelper helper = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置图片在哪个单元格中
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex() + 1);

        // 设置图片在单元格中的位置
        // 横向偏移量
        anchor.setDx1(0);
        anchor.setDx2(emuWidth);
        // 纵向偏移量
        anchor.setDy1(0);
        anchor.setDy2(emuHeight);

        // 设置图片可以随着单元格移动
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor, index);
        // 设置行高和列宽为图片的实际大小
        Row row = sheet.getRow(cell.getRowIndex());
        if (row == null) {
            row = sheet.createRow(cell.getRowIndex());
        }
        // 设置行高，1像素大约等于0.75点
        float maxRowHeight = 300;
        float imageHeight = (float) (pictureHeight * 0.75);
        row.setHeightInPoints(Math.min(imageHeight, maxRowHeight));

        // 设置列宽，列宽单位是1/256个字符，1个字符大约为7像素
        int maxColumnWidth = 255 * 256;
        int imageWidth = pictureWidth * 37;
        sheet.setColumnWidth(cell.getColumnIndex(), Math.min(imageWidth, maxColumnWidth));
    }

调试过程中，出了一点小问题，出了这个异常：

The maximum column width for an individual cell is 255 characters

由于图片宽度过大，导致超过了Excel单元格的最大宽度255，因此需要兼容最大行高和列宽

原代码段是：

 // 设置行高，1像素大约等于0.75点
row.setHeightInPoints((float) (pictureHeight * 0.75));

// 设置列宽，列宽单位是1/256个字符，1个字符大约为7像素
sheet.setColumnWidth(cell.getColumnIndex(), pictureWidth * 37);

修改后代码段：

// 设置行高，1像素大约等于0.75点
float maxRowHeight = 300;
float imageHeight = (float) (pictureHeight * 0.75);
row.setHeightInPoints(Math.min(imageHeight, maxRowHeight));

// 设置列宽，列宽单位是1/256个字符，1个字符大约为7像素
int maxColumnWidth = 255 * 256;
int imageWidth = pictureWidth * 37;
sheet.setColumnWidth(cell.getColumnIndex(), Math.min(imageWidth, maxColumnWidth));

步骤3：导出图片

public void exportAnalysis(EnergyStatisticDTO param, HttpServletResponse response) {
        String fileName = "数据分析" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT);
        try {
            EasyExcelUtils.setResponse(response, fileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            //各用电用途趋势
            String sheetName = "数据分析图示";
            this.meterImageSheet(sheetName, param.getImagePath(), excelWriter);
            String imagePath = param.getImagePath();
            if (StringUtils.isEmpty(imagePath)) return;
            //支持多图片导出，此处只是单图片
            List<ImageDTO> imageList = new ArrayList<>();
            ImageDTO imageDTO = new ImageDTO();
            imageDTO.setImageUrl(Collections.singletonList(imagePath));
            imageList.add(imageDTO);
            WriteSheet imageSheet = EasyExcel.writerSheet(1, sheetName)
                    .registerWriteHandler(new CustomImageStrategy())
                    .build();
            excelWriter.write(imageList, imageSheet);
            excelWriter.finish();
        } catch (Exception e) {
            log.error("数据分析:{}", e.getMessage());
        }
    }

Excel导出图片效果图

可以看出和Web的图表的大小是基本一致的

撒花完结……