easyExcel模板导出内容(包含一个单元格多张图片)

概述

业务中经常需要按照一个特定的模板导出特定内容，有些单元格还要求特殊的格式。

这里以一个单元格填充多张图片为例。

模板导出时一个单元格填充多张图片

springboot版本

<parent>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-parent</artifactId>
   <version>2.7.12-SNAPSHOT</version>
   <relativePath/> <!-- lookup parent from repository -->
</parent>

easyExcel版本

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

用到了hutool工具类

 <dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.0</version>
</dependency>

实现概述：
通过writeHandler监听对应的单元格渲染（将指定单元格先默认为空，然后在渲染完之后自己填充图片内容），按照自己的需求填充图片

handler

package com.fchan.excelexport.handler;

import cn.hutool.core.io.IoUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.springframework.core.io.ClassPathResource;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Objects;

@Slf4j
public class ImageModifyHandler implements CellWriteHandler {


    /**
     * 数据转换完成后
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellData
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 此处不处理表头，不处理不包含图像的
        boolean noImageValue = Objects.isNull(cellData) ||
                CollectionUtils.isEmpty(cellData.getImageDataList());
        if(Objects.equals(Boolean.TRUE, isHead) || noImageValue){
            return;
        }
        cellData.setType(CellDataTypeEnum.EMPTY);
    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 在 单元格写入完毕后 ，自己填充图片
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        //指定单元格自己渲染图片
        if(rowIndex == 3 && columnIndex == 3){

            log.info("第{}行，第{}列数据转换后", cell.getRowIndex(), cell.getColumnIndex());

            Sheet sheet = cell.getSheet();

            //图片的数据可以从db中获取
            ClassPathResource imgClassPathResource = new ClassPathResource("imgs/my_huckleberry_friends.jpg");
            InputStream imgInputStream = null;
            try {
                imgInputStream = imgClassPathResource.getInputStream();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

            byte[] imgBytes = IoUtil.readBytes(imgInputStream);


            //每张图片的宽度
            int picWidth = Units.pixelToEMU(60);

            //列宽设置为图片的总宽度---不设置宽度，当图片的坐标超过单元格宽度时图片显示失败
            //EMU转字符宽度：characterWidth = emu / 256
            sheet.setColumnWidth(columnIndex, picWidth * 10 / 256);

            //假设图片张数10张
            for (int i = 0 ; i < 10; i ++) {
                int index = sheet.getWorkbook().addPicture(imgBytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
                Drawing drawing = sheet.getDrawingPatriarch();
                if (drawing == null) {
                    drawing = sheet.createDrawingPatriarch();
                }
                CreationHelper helper = sheet.getWorkbook().getCreationHelper();
                ClientAnchor anchor = helper.createClientAnchor();
                // 设置图片坐标
                anchor.setDx1(picWidth * i);
                anchor.setDx2(picWidth * (1 + i));
                anchor.setDy1(0);
                anchor.setDy2(0);
                //设置图片位置
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex());
                anchor.setRow1(cell.getRowIndex());
                anchor.setRow2(cell.getRowIndex() + 1);
                // 设置图片可以随着单元格移动
                anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                drawing.createPicture(anchor, index);
            }
        }

    }


}

在使用模板填充时注册handler

public static void complexFillMutilFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"{","}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        // 填充数据


        byte[] imgBytes = new byte[]{};

        Map<String, Object> valMap = new HashMap<>();

        valMap.put("name", "123");
        valMap.put("address", "333");
        
        //图片单元格需要一个空值，为null时会跳过该单元格，导致无法自定义渲染
        valMap.put("imgs", imgBytes);


        valMap.put("name2", "123");
        valMap.put("address2", "333");

        //图片单元格需要一个空值，为null时会跳过该单元格，导致无法自定义渲染
        valMap.put("imgs2", imgBytes);


        // 处理数据 (此处是业务需要，可以自行更改)
        //获取模板 (我是将模板放到 resource文件下面了)
        ClassPathResource classPathResource = new ClassPathResource("excelTemplate/templateExport.xlsx");
        InputStream inputStream = classPathResource.getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = "templateExport" + File.separator + System.currentTimeMillis() + ".xlsx";
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

        // 生成sheet
        WriteSheet sheet1 = EasyExcel.writerSheet(0, "oppm").build();
        ExcelWriter write = EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new ImageModifyHandler())
                .withTemplate(inputStream)
                .build();
        // 填充数据
        write.fill(valMap, sheet1);
        write.close();
    }

结果