【JAVA】easyexcel 导出excel文件带多个图片

天空~华

已于 2024-05-07 18:18:59 修改

阅读量8.6k

点赞数 6

分类专栏：【JAVA】文章标签： java excel easyexcel 导出图片列图片导出excel

于 2022-11-03 15:44:10 首次发布

本文链接：https://blog.csdn.net/xiaosemei/article/details/127671561

版权

【JAVA】专栏收录该内容

34 篇文章 1 订阅

订阅专栏

最终效果

pom版本

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

实现代码

测试执行类

package org.open.openfile;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.data.ClientAnchorData;
import com.alibaba.excel.metadata.data.ImageData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.FileUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 图片导出
 *
 * @author dqh
 * @date 2022/11/3 15:20
 */
public class ImageExportTest {

    /**
     * 图片导出
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link ImageDemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void imageWrite() {
        //文件最后生成的路径
        String fileName = "E:\\image\\" + "imageWrite.xlsx";
        System.out.println(fileName);
        //测试用的图片路径
        String imagePath = "E:\\image\\img.jpeg";
        String imagePath2 = "E:\\image\\img2.jpeg";

        //数据列表Demo
        List<ImageDemoData> demoDataList = new ArrayList<>();

        //图片路径
        List<String> path1 = new ArrayList<>();
        path1.add(imagePath);

        List<String> path2 = new ArrayList<>();
        path2.add(imagePath);
        path2.add(imagePath2);

        List<String> path3 = new ArrayList<>();
        path3.add(imagePath);
        path3.add(imagePath);
        path3.add(imagePath2);

        List<String> path4 = new ArrayList<>();
        path4.add(imagePath);
        path4.add(imagePath2);
        path4.add(imagePath);
        path4.add(imagePath2);

        demoDataList.add(new ImageDemoData("王离", path1));
        demoDataList.add(new ImageDemoData("杨开", path2));
        demoDataList.add(new ImageDemoData("欧阳微微", path3));
        demoDataList.add(new ImageDemoData("上官婉儿", path4));
        genImageExcel(demoDataList, fileName);
    }

    /**
     * 生成有图片的excel
     *
     * @param demoDataList 数据列表
     * @param fileName     生成文件路径
     */
    private void genImageExcel(List<ImageDemoData> demoDataList, String fileName) {
        if (CollectionUtils.isEmpty(demoDataList)) {
            return;
        }

        AtomicReference<Integer> maxImageSize = getMaxImageSize(demoDataList);

        //设置列长度所用类
        AutoColumnWidthStyleStrategy longWidth = new AutoColumnWidthStyleStrategy();
        demoDataList.forEach(item -> {
            WriteCellData<Void> writeCellData = new WriteCellData<>();
            if (CollectionUtils.isNotEmpty(item.getImagePathList())) {
                //每张图片间距
                Integer splitWidth = 2;
                //每张图片的长度
                Integer imageWidth = 80;
                //图片列的最大长度
                Integer sumWidth = maxImageSize.get() * (imageWidth + splitWidth);

                List<ImageData> imageDataList = new ArrayList<>();
                List<String> imagePathList = item.getImagePathList();
                for (int i = 1; i <= imagePathList.size(); i++) {
                    String path = imagePathList.get(i - 1);
                    Integer left = imageWidth * (i - 1) + i * splitWidth;
                    Integer right = sumWidth - imageWidth - left;
                    ImageData imageData = new ImageData();
                    try {
                        imageData.setImage(FileUtils.readFileToByteArray(new File(path)));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
                    //距离单元格顶部距离
                    imageData.setTop(1);
                    //距离单元格底部距离
                    imageData.setBottom(1);
                    //距离单元格左边距离
                    imageData.setLeft(left);
                    //距离单元格右边距离
                    imageData.setRight(right);
                    imageData.setAnchorType(ClientAnchorData.AnchorType.DONT_MOVE_DO_RESIZE);
                    imageDataList.add(imageData);
                }
                writeCellData.setImageDataList(imageDataList);

                Map<String, Integer> zdyColumnWidth = new HashMap<>();
                //图片列名称，对应导出对象的列名称，图片列长度
                zdyColumnWidth.put("上传图片", sumWidth / 6);
                longWidth.setZdyColumnWidth(zdyColumnWidth);
            }
            item.setWriteCellDataFile(writeCellData);
        });

        //写入数据
        EasyExcel.write(fileName, ImageDemoData.class).registerWriteHandler(longWidth).sheet().doWrite(demoDataList);

    }

    private static AtomicReference<Integer> getMaxImageSize(List<ImageDemoData> demoDataList) {
        //图片列最大图片数
        AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
        demoDataList.forEach(item -> {
            if (CollectionUtils.isNotEmpty(item.getImagePathList()) && item.getImagePathList().size() > maxImageSize.get()) {
                maxImageSize.set(item.getImagePathList().size());
            }
        });
        return maxImageSize;
    }


}

实体类

package org.open.openfile;
 
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
 
import java.util.List;
 
/**
 * 图片导出类
 *
 * @author Jiaju Zhuang
 */
@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(40)
public class ImageDemoData {
    @ExcelProperty(value = "用户名称")
    private String test;
    /**
     * 图片路径
     */
    @ExcelIgnore
    private List<String> imagePathList;
    /**
     * 图片列
     */
    @ExcelProperty(value = "上传图片")
    private WriteCellData<Void> writeCellDataFile;

    public ImageDemoData(String test, List<String> imagePathList) {
        this.test = test;
        this.imagePathList = imagePathList;
    }

}

package org.open.openfile;
 
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
 
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * 自动列宽
 */
public class AutoColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
 
 
    private static final int MAX_COLUMN_WIDTH = 255;
 
    //自定义列的列宽
    private Map<String, Integer> zdyColumnWidth = MapUtils.newHashMapWithExpectedSize(2);
 
 
    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);
 
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
                                  Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }
 
        if (zdyColumnWidth.containsKey(cell.toString())) {
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), zdyColumnWidth.get(cell.toString()) * 256);
            return;
        }
 
        Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap<Integer, Integer>(16);
            cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
 
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        }
        WriteCellData<?> cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes().length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            default:
                return -1;
        }
    }
 
    public void setZdyColumnWidth(Map<String, Integer> zdyColumnWidth) {
        this.zdyColumnWidth = zdyColumnWidth;
    }
}