快捷導(dǎo)航

Java中Easyexcel?實(shí)現(xiàn)批量插入圖片功能

更新時(shí)間：2022年04月26日 09:22:24 作者：旭東怪

這篇文章主要介紹了Easyexcel?實(shí)現(xiàn)批量插入圖片,本文通過(guò)實(shí)例代碼給大家介紹了easyexcel文檔處理工具、自定義圖片處理器的相關(guān)知識(shí)，需要的朋友可以參考下

1 Maven依賴(lài)

 <!--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 {
    /**
     * 圖片數(shù)據(jù)
     */
    private byte[] pictureBytes;
     * 圖片類(lèi)型
    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頁(yè)名稱(chēng)
     * @param startRowIndex    開(kāi)始行號(hào)
     * @param endRowIndex      結(jié)束行號(hào)
     * @param startColumnIndex 開(kāi)始列號(hào)
     * @param endColumnIndex   結(jié)束列號(hào)
     * @param pictureBytes     圖片數(shù)據(jù)
     * @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      圖片類(lèi)型
            , byte[] pictureBytes, Integer pictureType) {
        return new PictureModelBuilder(sheetName, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, pictureBytes)
                //圖片類(lèi)型
                .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頁(yè)名稱(chēng)列表
     */
    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頁(yè)創(chuàng)建之后調(diào)用
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //不需要添加圖片，或者當(dāng)前sheet頁(yè)不需要添加圖片
        if (CollUtil.isEmpty(pictureList) || sheetNameList.contains(sheet.getSheetName()) == false) {
        //獲取當(dāng)前sheet的圖片
        List<PictureModel> sheetPictureList = pictureList.stream().filter(x ->
                StrUtil.equals(x.getSheetName(), sheet.getSheetName())
        ).collect(Collectors.toList());
        //當(dāng)前sheet頁(yè)不需要圖片
        if (CollUtil.isEmpty(sheetPictureList)) {
        for (PictureModel pictureModel : sheetPictureList) {
            //圖片數(shù)據(jù)
            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 調(diào)試代碼

/**
     * 測(cè)試設(shè)置圖片
     */
    @Test
    public void testPicture() {
        try {
            File file = new File("D:/easyexcel/testPicture.xlsx");
            FileUtil.createNewFile(file);
            //生成表格數(shù)據(jù)
            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"})));
            //導(dǎo)出文件
            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);
            //千萬(wàn)別忘記finish 會(huì)幫忙關(guān)閉流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5 調(diào)試結(jié)果

注：

1、注冊(cè)自定義處理器之前必須調(diào)用inMemory(Boolean.TRUE)方法。

2、覺(jué)得這篇博客寫(xiě)的不錯(cuò)的可以前往Gitee點(diǎn)個(gè)Star，源碼請(qǐng)查看Gitee的xudongbase項(xiàng)目easyexcel分支。

xudongbase: 主要是項(xiàng)目中可以用到的共通方法，現(xiàn)有easyexcel分支在持續(xù)更新中。歡迎大家Star和提交Issues。easyexcel分支：批量設(shè)置樣式，批量添加批注，批量合并單元格，設(shè)置凍結(jié)行和列，設(shè)置行高列寬，隱藏行和列，綁定下拉框數(shù)據(jù)，設(shè)置水印 - Gitee.com

補(bǔ)充：下面給大家分享基于python語(yǔ)言寫(xiě)的日常小工具向excel中批量添加圖片和圖片名稱(chēng)

需求：現(xiàn)有一個(gè)200張圖片的文件夾，向 excel 中 A列，寫(xiě)入圖片的名稱(chēng)，向ecxel 中的 E 列插入對(duì)應(yīng)的圖片。

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
    # 獲取圖片名稱(chēng)
    img_fnn = os.listdir(str(img_folder))
    index = 1
     # 將圖片名稱(chēng)寫(xiě)入到 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):
        # 圖片文件的絕對(duì)路徑
        img_lujin = os.path.join(img_folder,c.value + '.jpg')
        try:
            # 設(shè)置圖片大小
            img_size = Image(img_lujin)
            newsize = (150, 200)
            img_size.width, img_size.height = newsize
            # 將圖片寫(xiě)入 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"
    )

到此這篇關(guān)于Easyexcel 實(shí)現(xiàn)批量插入圖片的文章就介紹到這了,更多相關(guān)Easyexcel插入圖片內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: