前言
最近项目中导出Excel需要带图片导出，看了一位大佬的csdn。文末原文地址。
二、问题分析
关于如何实现带图片导出的功能，在官方文档中有一个简单的说明：
官方文档：图片导出
从官方文档中给的代码示例中可以看出，带图片导出有 6 种方式@Getter@Setter@EqualsAndHashCode@ContentRowHeight(100)@ColumnWidth(100 / 8)public class ImageDemoData { private File file; private InputStream inputStream; * 如果string类型 必须指定转换器，string默认转换成string */ @ExcelProperty(converter = StringImageConverter.class) private String string; private byte[] byteArray; * 根据url导出 * * @since 2.1.1 */ private URL url;
* 根据文件导出 并设置导出的位置。 * * @since 3.0.0-beta1 */ private WriteCellData<Void> writeCellDataFile;}
多图片导出
但是如果要实现情景介绍案例中每个单元格需要存放多张图片就不能仅使用官方提供的方案去解决了，通常情况下需要自己写一个拦截器，对单元格中的图片进行处理
转换器：ImageUrlConverter.java
import com.alibaba.excel.converters.Converter;import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.GlobalConfiguration;import com.alibaba.excel.metadata.data.ImageData;import com.alibaba.excel.metadata.data.ReadCellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.metadata.property.ExcelContentProperty;import com.alibaba.excel.util.IoUtils;import com.alibaba.excel.util.ListUtils;import lombok.extern.slf4j.Slf4j;import org.springframework.util.CollectionUtils;import java.io.InputStream;import java.net.URL;import java.util.List;
@Slf4jpublic class ImageUrlConverter implements Converter<List<URL>> {
@Override public Class<?> supportJavaTypeKey() { return List.class; }
@Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.EMPTY; }
@Override public List<URL> convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return null; }
@Override public WriteCellData<?> convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { List<ImageData> data = ListUtils.newArrayList(); ImageData imageData; for (URL url : value) { try (InputStream inputStream = url.openStream();) { byte[] bytes = IoUtils.toByteArray(inputStream); imageData = new ImageData(); imageData.setImage(bytes); data.add(imageData); } catch (Exception e) { log.error("导出临时记录图片异常：", e); } } WriteCellData<?> cellData = new WriteCellData<>(); if (!CollectionUtils.isEmpty(data)) { cellData.setImageDataList(data); cellData.setType(CellDataTypeEnum.EMPTY); } else { cellData.setStringValue("无图"); cellData.setType(CellDataTypeEnum.STRING); } return cellData; }}
单元格拦截器：CustomImageModifyStrategy.java
import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.ImageData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.util.Units;import org.apache.poi.xssf.usermodel.XSSFDrawing;import org.apache.poi.xssf.usermodel.XSSFPicture;import org.apache.poi.xssf.usermodel.XSSFShape;import org.springframework.util.CollectionUtils;import java.util.List;import java.util.concurrent.CopyOnWriteArrayList;import java.util.concurrent.atomic.AtomicReference;
* Excel导出单元格中有图片，图片会进行压缩，缩略，插入单元格 * 注意： * - 该策略是复制原表格中的图片进行缩放，原图片并没有删除掉，而是将尺寸设置为 0 看不到而已，但是依旧占用空间 * - 且在多 sheet 的环境下，除第一个 sheet，其余的 sheet 图片会被置 0 * 目前上述问题并没有得到解决，如果在导出数据较多或者存在多个 sheet 的情况下不建议使用 */public class CustomImageModifyStrategy implements CellWriteHandler {
* 已经处理的Cell */ private final CopyOnWriteArrayList<String> REPEATS = new CopyOnWriteArrayList<>();
* 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置） */ private final AtomicReference<Integer> MAX_IMAGE_SIZE = new AtomicReference<>(0);
* 标记手动添加的图片，用于排除EasyExcel自动添加的图片 */ private final CopyOnWriteArrayList<Integer> CREATE_PIC_INDEX = new CopyOnWriteArrayList<>();
* 默认图片宽度（单位像素）：60 */ private final static int DEFAULT_IMAGE_WIDTH = 60;
* 默认像素转换因子：32 */ private final static int DEFAULT_PIXEL_CONVERSION_FACTOR = 32;
* 图片宽度，单位像素 */ private final int imageWidth;
* 像素转换因子 */ private final int pixelConversionFactor;
public CustomImageModifyStrategy() { this.imageWidth = DEFAULT_IMAGE_WIDTH; this.pixelConversionFactor = DEFAULT_PIXEL_CONVERSION_FACTOR; }
public CustomImageModifyStrategy(int imageWidth, int pixelConversionFactor) { this.imageWidth = imageWidth; this.pixelConversionFactor = pixelConversionFactor; }
@Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@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())) { cellData.setType(CellDataTypeEnum.EMPTY); } }
@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; } boolean listFlag = false; Sheet sheet = cell.getSheet(); List<ImageData> imageDataList = cellDataList.get(0).getImageDataList(); if (!CollectionUtils.isEmpty(imageDataList)) { listFlag = true; } if (!listFlag && imageDataList == null) { return; } String key = cell.getRowIndex() + "_" + cell.getColumnIndex(); if (REPEATS.contains(key)) { return; } REPEATS.add(key); if (imageDataList.size() > MAX_IMAGE_SIZE.get()) { MAX_IMAGE_SIZE.set(imageDataList.size()); }
int widthValue = imageWidth * pixelConversionFactor; sheet.setColumnWidth(cell.getColumnIndex(), listFlag ? widthValue * MAX_IMAGE_SIZE.get() + pixelConversionFactor : widthValue);
if (listFlag) { for (int i = 0; i < imageDataList.size(); i++) { ImageData imageData = imageDataList.get(i); if (imageData == null) { continue; } byte[] image = imageData.getImage(); int index = this.insertImage(sheet, cell, image, i); CREATE_PIC_INDEX.add(index); } } else { this.insertImage(sheet, cell, imageDataList.get(0).getImage(), 0); }
XSSFDrawing drawingPatriarch = (XSSFDrawing) sheet.getDrawingPatriarch(); List<XSSFShape> shapes = drawingPatriarch.getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); if (shape instanceof XSSFPicture && !CREATE_PIC_INDEX.contains(i)) { CREATE_PIC_INDEX.add(i); XSSFPicture picture = (XSSFPicture) shape; picture.resize(0); } } }
* 重新插入一个图片 * * @param sheet Excel页面 * @param cell 表格元素 * @param pictureData 图片数据 * @param i 图片顺序 */ public int insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) { int picWidth = Units.pixelToEMU(imageWidth); int index = sheet.getWorkbook().addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG); Drawing<?> drawing = sheet.getDrawingPatriarch(); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); } CreationHelper helper = sheet.getWorkbook().getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); * 设置图片坐标 * 为了不让图片遮挡单元格的上边框和右边框，故 x1、x2、y1 这几个坐标点均向后移动了一个像素点 */ anchor.setDx1(Units.pixelToEMU(1) + picWidth * i); anchor.setDx2(Units.pixelToEMU(1) + picWidth + picWidth * i); anchor.setDy1(Units.pixelToEMU(1)); anchor.setDy2(0); int columnIndex = cell.getColumnIndex(); anchor.setCol1(columnIndex); anchor.setCol2(columnIndex); int rowIndex = cell.getRowIndex(); anchor.setRow1(rowIndex); anchor.setRow2(rowIndex + 1); anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE); drawing.createPicture(anchor, index); return index; }
}
输出对象类：SceneProcessInfo.java
@Builder@Data@ExcelIgnoreUnannotated@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, horizontalAlignment = HorizontalAlignmentEnum.FILL, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)@HeadRowHeight(20)@ContentRowHeight(40)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)public class SceneProcessInfo implements Serializable {
@Serial private static final long serialVersionUID = 1L;
@Schema(description = "ID") private String id;
...
@Schema(description = "图片") @ExcelProperty(value = "图片",converter = ImageUrlConverter.class) @ColumnWidth(50) private List<URL> dangerimgs;
@Schema(description = "图片") @ExcelProperty(value = "图片",converter = ImageUrlConverter.class) @ColumnWidth(50) private List<URL> correction;
...
}
代码示例：
* 带图片导出：多图片导出 */ @Test public void exportWithPicture03() {
String fileName = "demo02.xlsx";
try { List<URL> imgList1 = new ArrayList<>(); imgList1.add(new URL( "图片网络地址")); imgList1.add(new URL( "图片网络地址"));
List<URL> imgList2 = new ArrayList<>(); imgList2.add(new URL( "图片的网络地址"));
List<SceneProcessInfo> entityList = new ArrayList<>(); entityList.add(SceneProcessInfo.builder() .imgList(imgList1) .build()); entityList.add(SceneProcessInfo.builder() .imgList(imgList2) .build()); entityList.add(SceneProcessInfo.builder() .build());
AtomicReference<Integer> maxImageSize = new AtomicReference<>(0); entityList.forEach(item -> { if (!CollectionUtils.isEmpty(item.getImgList()) && item.getImgList().size() > maxImageSize.get()) { maxImageSize.set(item.getImgList().size()); } }); EasyExcel.write(fileName, StaffInfoEntity.class) .autoCloseStream(true) .registerWriteHandler( new CustomImageModifyStrategy(60, 32)) .sheet("sheet") .doWrite(entityList); } catch (Exception e) { System.out.println("导出异常"); } }
导出结果
原文链接：https://blog.csdn.net/xhmico/article/details/137724425
阅读原文：原文链接
该文章在 2025/7/1 23:36:52 编辑过
400 186 1886
