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

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

目录1 Maven依赖2 PictureModel3CustomPictureHandler4 调试代码5 调试结果注：

各位今天给大家分享Easyexcel 实现批量插入图片的问题，代码如下所示：

1 Maven依赖

cn.hutool

hutool-all

5.5.1

com.alibaba

easyexcel

2.2.8

org.projectlombok

lombok

true

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

* 图片信息

private List pictureList = new ArrayList<>();

public CustomPictureHandler(List pictureList) {

if (CollUtil.isEmpty(pictureList)) {

http:// return;

}

this.pictureList = pictureList.stream().filter(x ->

StrUtil.isNotBlank(x.getSheetName()http://) && 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 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, pizVdADyZhctureModel.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> 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 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 调试结果

注：

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

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

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

补充：下面给大家分享基于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"

)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。