[alibaba/easyexcel]导出列表,列表的一个字段是多张图片,我设置的图片样式下重叠着图片,重叠的图片填充满了单元格,且会随着单元格变动拉伸

就是第一张图片的样子 拉开图片后不应该有下面红框里面的内容,我补充了一段代码,依然无效 @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { List imageDataList = cellData.getImageDataList(); if (CollectionUtils.isNotEmpty(imageDataList)){ cellData.setType(CellDataTypeEnum.EMPTY); } }

https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E5%9B%BE%E7%89%87%E5%AF%BC%E5%87%BA 你可以根据这个demo直接写 不需要手动处理

这个demo不适用我现在的场景,现在我需要根据模版填充数据,模版大概是这样子 图片包含多个图片 我现在的导出是这样的 图片不能做到上面第一张图的样子 而是所有的图片重叠并填充满了单元格

把你这段代码放到afterdispose试试 可能是生命周期的问题。 如果不生效可以试试从workbookhandler中

public class CustomImageModifyHandler implements CellWriteHandler { private final List repeats = new ArrayList<>(); private Integer maxDataSize = 0; @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) {
    List<ImageData> imageDataList = cellData.getImageDataList();
    if (CollectionUtils.isNotEmpty(imageDataList)){
        cellData.setType(CellDataTypeEnum.EMPTY);
    }
}

@SneakyThrows
@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();
    // 此处为ListUrlConverterUtil的返回值
    List<ImageData> imageDataList = cellDataList.get(0).getImageDataList();
    if (CollectionUtils.isNotEmpty(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() > maxDataSize) {
        maxDataSize = imageDataList.size();
    }
    // 默认要导出的图片大小为60*60px,60px的行高大约是900,60px列宽大概是248*8
    sheet.getRow(cell.getRowIndex()).setHeight((short)900);
    sheet.setColumnWidth(cell.getColumnIndex(),listFlag?240*8*imageDataList.size():240*8);
    if (imageDataList.size() > maxDataSize) {
        maxDataSize = imageDataList.size();
    }
    //图片列最大图片数
    AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
    maxImageSize.set(imageDataList.size());
    //每张图片间距
    int splitWidth = 2;
    //每张图片的长度
    int imageWidth = 80;
    //图片列的最大长度
    int sumWidth = maxImageSize.get() * (imageWidth + splitWidth);
    if (listFlag){
        for (int i = 0; i < imageDataList.size(); i++) {
            int left = imageWidth * (i - 1) + i * splitWidth;
            int right = sumWidth - imageWidth - left;
            ImageData imageData = imageDataList.get(i);
            if(imageData ==null){
                continue;
            }
            imageData.setTop(1);
            //距离单元格底部距离
            imageData.setBottom(1);
            //距离单元格左边距离
            imageData.setLeft(left);
            //距离单元格右边距离
            imageData.setRight(right);
            byte[] image = imageData.getImage();
            this.insertImage(sheet,cell, image,i);
        }
    }else {
        this.insertImage(sheet,cell, imageDataList.get(0).getImage(),0);
    }
}

private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i){
    int picWidth = Units.pixelToEMU(60);
    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();
    // 设置图片坐标
    anchor.setDx1(picWidth*i);
    anchor.setDx2(picWidth+picWidth*i);
    anchor.setDy1(0);
    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);
}

}

这是我的处理器,麻烦看下是否能看出原因

你这个代码我看已经有图片数据了啊 是不是已经插入了 不用你手动插入了

是插入了 但是我需要我自定义的样式 在afterCellDataConverted中置空单元格框架生成的图片无效

图片和单元格是没有关系的其实是附着在sheet里面的， 只是位置放在了单元格的位置而已

你可以使用workbook.getAllPictures() 查看

进afterCellDispose使用workbook.getAllPictures() 可以看到照片,但是那个照片的样式不是我自定义的 ,我需要把自定义的图片放到那个位置,在drawing.createPicture(anchor, index);的时候就又会把相同的照片按照我自定义的样式放到那个单元格的位置,我现在就是想进来的时候把之前的图片去掉,只保留的自定义样式的图片

new WorkbookWriteHandler() {
                @Override
                public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
                    Workbook workbook = writeWorkbookHolder.getWorkbook();
                    Sheet sheetAt = workbook.getSheetAt(0);
                    XSSFDrawing drawingPatriarch = (XSSFDrawing)sheetAt.getDrawingPatriarch();
                    List<XSSFShape> shapes = drawingPatriarch.getShapes();
                    for (XSSFShape shape : shapes) {
                        if(shape instanceof XSSFPicture){
                            XSSFPicture picture = (XSSFPicture) shape;
                            XSSFDrawing drawing = picture.getDrawing();
                            String pid = picture.getCTPicture().getBlipFill().getBlip().getEmbed();
                            drawing.getPackagePart().removeRelationship(pid);
                            drawing.getPackagePart().getPackage().deletePartRecursive(drawing.getRelationById(pid).getPackagePart().getPartName());
                        }
                    }

                }
            }

这样可以删除图片

你好 我刚刚试了一下 CellWriteHandler 的执行顺序是比WorkbookWriteHandler早的 哪里可以设置先进WorkbookWriteHandler的afterWorkbookDispose方法再进CellWriteHandler 的afterCellDispose方法吗

哦 可以了 我直接在CellWriteHandler 的afterCellDispose里去设置就好了,,,,,,谢谢大佬 谢谢谢谢

对了 大佬 还有一个问题麻烦问一下 我刚刚那样导出的图片 好像只有最后一个列表对象的图片导出了,前面的图片都不在了 我应该怎么设置呢 箭头 指的地方都有图片才是对的

Workbook workbook = sheet.getWorkbook(); Sheet sheetAt = workbook.getSheetAt(0); XSSFDrawing drawingPatriarch = (XSSFDrawing)sheetAt.getDrawingPatriarch(); List shapes = drawingPatriarch.getShapes(); for (XSSFShape shape : shapes) { if(shape instanceof XSSFPicture){ XSSFPicture picture = (XSSFPicture) shape; picture.resize(0); } }

你这样每次都把所有的图片都删了啊,你这个方法只执行一次就行了

哦 我是直接在CellWriteHandler的afterCellDispose加的这个代码块.我之前是新建的WorkbookWriteHandler重写了afterWorkbookDispose,打断点执行顺序是先执行的CellWriteHandler后执行的WorkbookWriteHandler,这样的话图片还是会被清空

你手动插入的自己做标记 或者直接在数据里就别让easyexcel插入图片了
原理都知道了发挥自己想象力 功能实现有很多方法没必要一步一问

感谢2位大佬的分析，根据157000841大佬的的代码，我做了如下改动 1.图的列宽改为使用最大图片数*240*8作为图所在列的宽度，大佬中的代码使用了最后一行图片的宽度作为了列宽 2.根据gongxunzhang大佬的提示，在创建代码时加一个位置标记，在处理循环shapes时resize(0)不是自己创建的

大佬说的别让easyexcel插入图片没有想好处理方式

版本：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

具体代码如下

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.commons.collections.CollectionUtils;
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 java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.atomic.AtomicReference;

public class CustomImageModifyHandler 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<>();

    @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;
        }
        //将要插入图片的单元格的type设置为空,下面再填充图片
        if (CollectionUtils.isNotEmpty(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();
        // 此处为ExcelUrlConverterUtil的返回值
        List<ImageData> imageDataList = cellDataList.get(0).getImageDataList();
        if (CollectionUtils.isNotEmpty(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());
        }
        // 默认要导出的图片大小为60*60px,60px的行高大约是900,60px列宽大概是248*8
        sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
        sheet.setColumnWidth(cell.getColumnIndex(), listFlag ? 240 * 8 * MAX_IMAGE_SIZE.get() : 240 * 8);

        if (listFlag) {
            for (int i = 0; i < imageDataList.size(); i++) {
                ImageData imageData = imageDataList.get(i);
                if (imageData == null) {
                    continue;
                }
                byte[] image = imageData.getImage();
                this.insertImage(sheet, cell, image, i);
            }
        } else {
            this.insertImage(sheet, cell, imageDataList.get(0).getImage(), 0);
        }

        // 清除EasyExcel自动添加的没有格式的图片
        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           图片顺序
     */
    private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) {
        int picWidth = Units.pixelToEMU(60);
        int index = sheet.getWorkbook().addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG);
        CREATE_PIC_INDEX.add(index);
        Drawing<?> drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        CreationHelper helper = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置图片坐标
        anchor.setDx1(picWidth * i);
        anchor.setDx2(picWidth + picWidth * i);
        anchor.setDy1(0);
        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);
    }
}

转换器代码如下：

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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;

import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelUrlConverterUtil implements Converter<List<URL>> {
    @Override
    public Class supportJavaTypeKey() {
        return List.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.EMPTY;
    }

    @Override
    public List convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public WriteCellData<?> convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 这里进行对数据实体类URL集合处理
        List<ImageData> data = new ArrayList<>();
        ImageData imageData;
        // for 循环一次读取
        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.isNotEmpty(data)) {
            // 图片返回图片列表
            cellData.setImageDataList(data);
            cellData.setType(CellDataTypeEnum.EMPTY);
        } else {
            // 没有图片使用汉字表示，这个没用到因为CustomImageModifyHandler中为空就EMPTY了
            cellData.setStringValue("无图片");
            cellData.setType(CellDataTypeEnum.STRING);
        }
        return cellData;
    }
}

导入实体对象：

@ExcelProperty(value = "图片" ,converter = ExcelUrlConverterUtil.class)
private List<URL> imgUrlList;