Easyexcel插入图片
旭东怪 人气:0各位今天给大家分享Easyexcel 实现批量插入图片的问题,代码如下所示:
1 Maven依赖
<!--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>
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 pictureType 图片类型 , byte[] pictureBytes, Integer pictureType) { return new PictureModelBuilder(sheetName, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, pictureBytes) //图片类型 .pictureType(pictureType) .build(); }
3CustomPictureHandler
自定义图片处理器。
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 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()); }
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 = "http://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 调试结果
注:
1、 注册自定义处理器之前必须调用inMemory(Boolean.TRUE)方法。
2、觉得这篇博客写的不错的可以前往Gitee点个Star,源码请查看Gitee的xudongbase项目easyexcel分支。
补充:下面给大家分享基于python语言写的日常小工具向excel中批量添加图片和图片名称
需求:现有一个200张图片的文件夹,向 excel 中 A列,写入图片的名称,向ecxel 中的 E 列插入对应的图片。
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" )
加载全部内容