Java中Easyexcel 实现批量插入图片功能

<!--hutool工具包-->

        <dependency>

            <groupId>cn.hutool</groupId>

            <artifactId>hutool-all</artifactId>

            <version> 5.5 . 1 </version>

        </dependency>

        <!--easyexcel文档处理工具-->

        <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>

package com.xudongbase.easyexcel.model;

import com.xudongbase.easyexcel.model.builder.PictureModelBuilder;

import com.xudongbase.easyexcel.model测试数据mon.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 pictureType      图片类型

             , byte [] pictureBytes, Integer pictureType) {

         return new PictureModelBuilder(sheetName, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, pictureBytes)

                 //图片类型

                 .pictureType(pictureType)

                 .build();

}

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测试数据mon.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

     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

         Sheet sheet = writeSheetHolder.getSheet();

         //不需要添加图片，或者当前sheet页不需要添加图片

         if (CollUtil.isEmpty(pictureList) || sheetNameList.contains(sheet.getSheetName()) == false ) {

         //获取当前sheet的图片

         List<PictureModel> sheetPictureList = pictureList.stream().filter(x ->

                 StrUtil.equals(x.getSheetName(), sheet.getSheetName())

         ).collect(Collectors.toList());

         //当前sheet页不需要图片

         if (CollUtil.isEmpty(sheetPictureList)) {

         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());

}

/**

      * 测试设置图片

      */

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

         }

     }

from openpyxl import   load_workbook

from openpyxl.drawing.image import Image

import os

import re

def insert_img_to_excel(filname, by_col,to_col,img_folder):

     '''

     filename : 表格文件路径

     by_col : 依靠哪一列

     to_col : 插入到哪一列

     img_folder : 图片路径

     wb = load_workbook(filname)

     ws = wb.active

     # 获取图片名称

     img_fnn = os.listdir( str (img_folder))

     index = 1

      # 将图片名称写入到 excel 中 A 列

     for img_fp in img_fnn:

         s = img_fp.replace( ".jpg" ,'')

         index + = 1

         i = 'A' + str (index)

         ws[i].value = s

         wb.save(filname)

     wb.close()

     for ind , c in enumerate (ws[by_col],start = 1 ):

         # 图片文件的绝对路径

         img_lujin = os.path.join(img_folder,c.value + '.jpg' )

         try :

             # 设置图片大小

             img_size = Image(img_lujin)

             newsize = ( 150 , 200 )

             img_size.width, img_size.height = newsize

             # 将图片写入 excel

             ws.add_image(

                 img_size,

                 anchor = to_col + str (ind)

             )

         except :

             print (c.value, '匹配不到图片' )

     wb.save(filname)

if __name__ = = '__main__' :

     insert_img_to_excel(

         filname = r 'C:\Users\Administrator\Desktop\act_test\mod.xlsx' ,

         by_col = 'A' ,

         to_col = 'E' ,

         img_folder = r "C:\Users\Administrator\Desktop\act_test\Act_img"

     )