easyexcle处理导出合并单元格图片填充

@GetMapping("getStyleSummaryExport")
@ApiOperation("款式汇总报表--导出")
@ApiImplicitParams({
        @ApiImplicitParam(name = Constant.PAGE, value = "当前页码，从1开始", paramType = "query", dataType = "int"),
        @ApiImplicitParam(name = Constant.LIMIT, value = "每页显示记录数", paramType = "query", dataType = "int"),
        @ApiImplicitParam(name = Constant.ORDER_FIELD, value = "排序字段", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = Constant.ORDER, value = "排序方式，可选值(asc、desc)", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "season", value = "季节", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "batch", value = "批次", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "series", value = "系列", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "styleNo", value = "款号", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "brand", value = "品牌", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "stage", value = "阶段", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "materialNo", value = "主身面料编号", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "supplier", value = "供应商", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "shipmentDate", value = "上货日期", paramType = "query", dataType = "Date"),
        @ApiImplicitParam(name = "ids", value = "勾选列表id,多个逗号隔开", paramType = "query", dataType = "String")
})
public void getStyleSummaryExport(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
    if(!ParamUtil.paramsIsNotNull(params,"limit") || !ParamUtil.paramsIsNotNull(params,"page")){
        params.put("limit", "1000");
        params.put("page", "1");
    }
    if(ParamUtil.paramsIsNotNull(params, "ids")){
        params.put("ids", Arrays.asList(params.get("ids").toString().split(",")));
    }else {
        params.remove("ids");
    }
    List<StyleSummaryDTO> list = styleSummaryService.getStyleSummaryReportPage(params).getList();
    List<List<Object>> resList = new ArrayList<>();
    //合并图片问题
    List<Integer> imgCount = new ArrayList<>();
    //合并的行
    List<Integer> merge = new ArrayList<>();
    AtomicInteger index = new AtomicInteger();
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    if(CollectionUtils.isNotEmpty(list)) {
        list.forEach(e ->{
            if(CollectionUtils.isNotEmpty(e.getItemList())){
                for (int i = 0; i < e.getItemList().size(); i++) {
                    index.addAndGet(1);
                    if(i > 0){
                        merge.add(index.intValue());
                    }
                    List<Object> itemList = new ArrayList<>();
                    if (i == 0 && CollectionUtils.isNotEmpty(e.getStyleUrlList())){
                        try {
                            itemList.add(new URL(e.getStyleUrlList().get(0).getUrl()));
                            imgCount.add(e.getItemList().size());
                        } catch (MalformedURLException malformedURLException) {
                            log.info(malformedURLException.getMessage());
                        }
                    }else {
                        itemList.add("");
                    }
                    itemList.add(e.getBrand());
                    itemList.add(e.getSeason());
                    itemList.add(e.getStage());
                    itemList.add(e.getYbType());
                    itemList.add(e.getBatch());
                    itemList.add(e.getSeries());
                    itemList.add(e.getStyleNo());
                    if(e.getShipmentDate() != null){
                        itemList.add(simpleDateFormat.format(e.getShipmentDate()));
                    }else {
                        itemList.add("");
                    }
                    itemList.add(e.getItemList().get(i).getMaterialNo());
                    itemList.add(e.getItemList().get(i).getSupplier());
                    resList.add(itemList);
                }
            }else {
                index.addAndGet(1);
                List<Object> itemList = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(e.getStyleUrlList())){
                    try {
                        itemList.add(new URL(e.getStyleUrlList().get(0).getUrl()));
                        imgCount.add(1);
                    } catch (MalformedURLException malformedURLException) {
                        log.info(malformedURLException.getMessage());
                    }
                }else {
                    itemList.add("");
                }
                itemList.add(e.getBrand());
                itemList.add(e.getSeason());
                itemList.add(e.getStage());
                itemList.add(e.getYbType());
                itemList.add(e.getBatch());
                itemList.add(e.getSeries());
                itemList.add(e.getStyleNo());
                if(e.getShipmentDate() != null){
                    itemList.add(simpleDateFormat.format(e.getShipmentDate()));
                }else {
                    itemList.add("");
                }
                resList.add(itemList);
            }
        });
    }
    response.setContentType("application/json;charset=utf-8");
    response.setCharacterEncoding("UTF-8");
    String fileName = URLEncoder.encode("开发款式汇总导出-"+  DateUtils.format(new Date()), "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    ExcelWriter excelWriter = null;
    try{
        excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //excelWriter = EasyExcel.write("D:\\2023\\test222111.xlsx").build();
        if(null != excelWriter) {
            //需要合并的列
            int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8};
            // 从那一列开始合并
            int mergeRowIndex = 1;
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            List<List<String>> userHeadList = getHeader();
            WriteSheet writeSheet = EasyExcel.writerSheet( "开发款式汇总").head(userHeadList)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new CustomCellWeightWeightForDevStyleUtil())
                    .registerWriteHandler(new MergeSameRowsStrategy(mergeRowIndex, mergeColumeIndex, merge))
                    .registerWriteHandler(new StyleImageCellWriteHandler(imgCount))
                    .build();
            excelWriter.write(resList, writeSheet);
        }
    }finally {
        //关闭流
        if(excelWriter != null){
            excelWriter.finish();
        }
    }
}

package com.jack.modules.dsr.common.utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;


import java.util.List;

@Data
public class MergeSameRowsStrategy implements CellWriteHandler{
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
    private List<Integer> merge;

    public MergeSameRowsStrategy() {
    }
    public MergeSameRowsStrategy(int mergeRowIndex, int[] mergeColumnIndex, List<Integer> merge) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.merge = merge;
    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        if (CollectionUtils.isNotEmpty(merge)){
            // 当前行
            int curRowIndex = cell.getRowIndex();
            if (merge.contains(curRowIndex)){
                // 当前列
                int curColIndex = cell.getColumnIndex();
                if (curRowIndex > mergeRowIndex) {
                    for (int i = 0; i < mergeColumnIndex.length; i++) {
                        if (curColIndex == mergeColumnIndex[i]) {
                            mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                            break;
                        }
                    }
                }
            }
        }

    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder sheet保持对象
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        if (cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex) == null){
            return;
        }
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData) || ("0.0".equals(preData.toString()) && "".equals(curData))) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergedRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

}

package com.jack.modules.dsr.common.handler;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.util.ArrayList;
import java.util.List;

/**
 * EasyExcel图片填充拦截器
 *
 * @author piyc
 */
@Data
public class StyleImageCellWriteHandler implements CellWriteHandler {


    private List<Integer> imgCount;

    public StyleImageCellWriteHandler() {
    }
    public StyleImageCellWriteHandler(List<Integer> imgCount) {
        this.imgCount = imgCount;
    }

    int zab = 0;
    List<byte[]> imageList = new ArrayList<>();


    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 在 数据转换成功后 ，修改第一列 当然这里也可以根据其他判断 然后不是头 就把类型设置成空 这样easyexcel 不会去处理该单元格
        if (head.getColumnIndex() != 0 || aBoolean) {
            return;
        }
        if (cellData.getImageValue() != null && cellData.getImageValue().length > 0){
            cellData.setType(CellDataTypeEnum.EMPTY);
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 在 单元格写入完毕后 ，自己填充图片
        if (head.getColumnIndex() != 0 || aBoolean || CollectionUtils.isEmpty(list) || list.get(0) == null) {
            return;
        }
        if (list.get(0).getStringValue() != null && StringUtils.isNotEmpty(list.get(0).getStringValue())){
            list.get(0).setType(CellDataTypeEnum.STRING);
            return;
        }

        Sheet sheet = cell.getSheet();
        // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
        if (list.get(0).getImageValue() != null && list.get(0).getImageValue().length > 0){
            int index = sheet.getWorkbook().addPicture(list.get(0).getImageValue(), HSSFWorkbook.PICTURE_TYPE_PNG);
            Drawing drawing = sheet.getDrawingPatriarch();
            if (drawing == null) {
                drawing = sheet.createDrawingPatriarch();
            }
            CreationHelper helper = sheet.getWorkbook().getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            // 设置图片坐标
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            //设置图片位置
            anchor.setCol1(cell.getColumnIndex());
            anchor.setCol2(cell.getColumnIndex() +1);
            anchor.setRow1(cell.getRowIndex());
            if (head.getColumnIndex() == 0 && CollectionUtils.isNotEmpty(imgCount)){
                anchor.setRow2(cell.getRowIndex() + imgCount.get(zab));
                zab++;
            } else {
                anchor.setRow2(cell.getRowIndex() + 1);
            }
            // 设置图片可以随着单元格移动
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            drawing.createPicture(anchor, index);
        }
    }
}

package com.jack.modules.dsr.common.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.nacos.common.utils.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.List;

public class CustomCellWeightWeightForDevStyleUtil extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Sheet sheet = writeSheetHolder.getSheet();
            int columnIndex = cell.getColumnIndex();
            if (columnIndex == 0 || columnIndex == 6 || columnIndex == 7 || columnIndex == 8 || columnIndex == 9 || columnIndex == 10){
                sheet.setColumnWidth(columnIndex,4576);
            }
        }
    }
}