Easyexcel 实现批量插入图片

ゝ一世哀愁。 2023-09-29 12:00 1阅读 0赞

**目录**

1 Maven依赖

2 PictureModel

3 CustomPictureHandler

4 调试代码

5 调试结果

注：

--------------------

## 1 Maven依赖 ##

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.1</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>

## 2 PictureModel ##

图片信息。

package com.xudongbase.easyexcel.model;
    
    import com.xudongbase.easyexcel.model.builder.PictureModelBuilder;
    import com.xudongbase.easyexcel.model.common.SheetRangeModel;
    import lombok.Getter;
    
    /**
     * 图片信息
     *
     * @author xudongmaster
     */
    @Getter
    public class PictureModel extends SheetRangeModel {
        /**
         * 图片数据
         */
        private byte[] pictureBytes;
        /**
         * 图片类型
         */
        private Integer pictureType;
    
        public PictureModel(PictureModelBuilder builder) {
            this.sheetName = builder.getSheetName();
            this.startRowIndex = builder.getStartRowIndex();
            this.endRowIndex = builder.getEndRowIndex();
            this.startColumnIndex = builder.getStartColumnIndex();
            this.endColumnIndex = builder.getEndColumnIndex();
            this.pictureBytes = builder.getPictureBytes();
            this.pictureType = builder.getPictureType();
        }
    
        /**
         * 生成图片信息
         *
         * @param sheetName        sheet页名称
         * @param startRowIndex    开始行号
         * @param endRowIndex      结束行号
         * @param startColumnIndex 开始列号
         * @param endColumnIndex   结束列号
         * @param pictureBytes     图片数据
         * @return
         */
        public static PictureModel createPictureModel(String sheetName, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex
                , byte[] pictureBytes) {
            return createPictureModel(sheetName, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, pictureBytes, null);
        }
    
    
        /**
         * 生成图片信息
         *
         * @param sheetName        sheet页名称
         * @param startRowIndex    开始行号
         * @param endRowIndex      结束行号
         * @param startColumnIndex 开始列号
         * @param endColumnIndex   结束列号
         * @param pictureBytes     图片数据
         * @param pictureType      图片类型
         * @return
         */
        public static PictureModel createPictureModel(String sheetName, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex
                , byte[] pictureBytes, Integer pictureType) {
            return new PictureModelBuilder(sheetName, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, pictureBytes)
                    //图片类型
                    .pictureType(pictureType)
                    .build();
        }
    }

## 3 CustomPictureHandler ##

自定义图片处理器。

package com.xudongbase.easyexcel.handler;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import com.xudongbase.common.poi.util.POIExcelUtil;
    import com.xudongbase.easyexcel.model.PictureModel;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.stream.Collectors;
    
    /**
     * 自定义图片处理器
     *
     * @author xudongmaster
     */
    public class CustomPictureHandler implements SheetWriteHandler {
    
        /**
         * sheet页名称列表
         */
        private List<String> sheetNameList;
        /**
         * 图片信息
         */
        private List<PictureModel> pictureList = new ArrayList<>();
    
    
        public CustomPictureHandler(List<PictureModel> pictureList) {
            if (CollUtil.isEmpty(pictureList)) {
                return;
            }
            this.pictureList = pictureList.stream().filter(x ->
                    StrUtil.isNotBlank(x.getSheetName()) && x.getPictureBytes() != null && x.getPictureBytes().length > 0)
                    .collect(Collectors.toList());
            sheetNameList = this.pictureList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
        }
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
        }
    
        /**
         * sheet页创建之后调用
         *
         * @param writeWorkbookHolder
         * @param writeSheetHolder
         */
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            //不需要添加图片，或者当前sheet页不需要添加图片
            if (CollUtil.isEmpty(pictureList) || sheetNameList.contains(sheet.getSheetName()) == false) {
                return;
            }
            //获取当前sheet的图片
            List<PictureModel> sheetPictureList = pictureList.stream().filter(x ->
                    StrUtil.equals(x.getSheetName(), sheet.getSheetName())
            ).collect(Collectors.toList());
            //当前sheet页不需要图片
            if (CollUtil.isEmpty(sheetPictureList)) {
                return;
            }
            for (PictureModel pictureModel : sheetPictureList) {
                //图片数据
                byte[] pictureBytes = pictureModel.getPictureBytes();
                //插入图片
                POIExcelUtil.insertImg(writeWorkbookHolder.getWorkbook(), sheet, pictureBytes, pictureModel.getStartRowIndex()
                        , pictureModel.getEndRowIndex(), pictureModel.getStartColumnIndex(), pictureModel.getEndColumnIndex()
                        , (pictureModel.getPictureType() == null ? Workbook.PICTURE_TYPE_JPEG : pictureModel.getPictureType()));
            }
            //删除图片信息
            pictureList.removeAll(sheetPictureList);
            sheetNameList = pictureList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
        }
    }

## 4 调试代码 ##

/**
         * 测试设置图片
         */
        @Test
        public void testPicture() {
            try {
                File file = new File("D:/easyexcel/testPicture.xlsx");
                FileUtil.createNewFile(file);
                //生成表格数据
                List<List<Object>> dataList = new ArrayList<>();
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头17777777777", "表头2", "表头3", "表头4444"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{11.111, 11.111, "11.111", "表头4"})));
                //导出文件
                List<PictureModel> pictureModelList = new ArrayList<>();
                String imgUrl = "https://profile.csdnimg.cn/9/5/B/1_qq_38974638";
                byte[] bytes = HttpUtil.downloadBytes(imgUrl);
                String sheetName="模板";
                pictureModelList.add(PictureModel.createPictureModel(sheetName,0,10,0,4,bytes));
                pictureModelList.add(PictureModel.createPictureModel(sheetName,11,22,0,4,bytes));
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                ExcelWriter excelWriter = EasyExcel.write(fileOutputStream)
                        .inMemory(Boolean.TRUE).registerWriteHandler(new CustomPictureHandler(pictureModelList)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                excelWriter.write(dataList, writeSheet);
                //千万别忘记finish 会帮忙关闭流
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

## 5 调试结果 ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16][]

## 注： ##

1、 注册自定义处理器之前必须调用inMemory(Boolean.TRUE)方法。

2、觉得这篇博客写的不错的可以前往Gitee点个Star，源码请查看Gitee的xudongbase项目easyexcel分支。

[xudongbase: 主要是项目中可以用到的共通方法，现有easyexcel分支在持续更新中。欢迎大家Star和提交Issues。easyexcel分支：批量设置样式，批量添加批注，批量合并单元格，设置冻结行和列，设置行高列宽，隐藏行和列，绑定下拉框数据，设置水印 - Gitee.com][xudongbase_ _easyexcel_Star_Issues_easyexcel_ - Gitee.com]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/a81d590cb6634b4ea925821d299ca239.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pet5Lic5oCq,size_20,color_FFFFFF,t_70,g_se,x_16
[xudongbase_ _easyexcel_Star_Issues_easyexcel_ - Gitee.com]: https://gitee.com/xudong_master/xudongbase/tree/easyexcel/