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);@Overridepublic 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<>());}}@Overridepublic 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());}}