解决easyexcel填充图片后合并单元格没有占满的问题

最新推荐文章于 2024-05-16 16:00:13 发布

Luke~

最新推荐文章于 2024-05-16 16:00:13 发布

阅读量1.5k

点赞数 5

分类专栏：总结 easyexcel 填充图片合并单元格文章标签： java 开发语言 excel

本文链接：https://blog.csdn.net/weixin_36340729/article/details/128705280

版权

总结同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

easyexcel 填充图片合并单元格

1 篇文章 0 订阅

订阅专栏

解决easyexcel合并单元格图片没有拉伸的问题
没有处理之前
处理之后

package com.demo.server.excel.handle;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.ImageData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.xmlbeans.XmlCursor;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Objects;

/**
 * 图像细胞编写处理程序
 *
 * @author admin
 * @date 2023/01/13
 */
public class ImageModifyHandler implements CellWriteHandler {

    @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) {
        if (CollectionUtils.isEmpty(cellDataList) || Objects.equals(Boolean.TRUE, isHead)) {
            return;
        }
        // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入只有一个
        WriteCellData<?> writeCellData = cellDataList.get(0);
        //  在afterCellDataConverted方法里面已经将CellDataType设置为EMPTY 此处不能用cellData.getType()来判断是否图片类型
        if (Objects.isNull(writeCellData) || Objects.isNull(writeCellData.getImageDataList()) || writeCellData.getImageDataList().get(0) == null) {
            return;
        }
        ImageData imageData = writeCellData.getImageDataList().get(0);
        setImageValue(imageData, cell);

    }

    private void setImageValue(ImageData imageData, Cell cell) {
        //获取当前单元格所在的sheet
        Sheet sheet = cell.getRow().getSheet();
        //获取当前sheet页中的所有合并单元格信息
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        //获取当前单元格的开始列号
        int firstColumn = (short) cell.getColumnIndex();
        //获取当前单元格的开始行号
        int firstRow = cell.getRow().getRowNum();
        //获取当前单元格的结束列号
        int lastColumn = (short) (cell.getColumnIndex());
        //获取当前单元格的结束行号
        int lastRow = cell.getRow().getRowNum();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            //判断当前单元格是否包含合并行或和并列 当前单元格的所有行号和列号都包含在合并域内 则认为当前单元格存在合并行或和并列
            if (cell.getColumnIndex() >= mergedRegion.getFirstColumn() && cell.getColumnIndex() <= mergedRegion.getLastColumn() && cell.getRow().getRowNum() >= mergedRegion.getFirstRow() && cell.getRow().getRowNum() <= mergedRegion.getLastRow()) {
                //获取合并域的开始行号
                firstRow = mergedRegion.getFirstRow();
                //获取合并域的结束行号
                lastRow = mergedRegion.getLastRow();
                //获取合并域的开始列号
                firstColumn = mergedRegion.getFirstColumn();
                //获取合并域的结束列号
                lastColumn = mergedRegion.getLastColumn();
                break;
            }
        }
        imageData.setRelativeLastColumnIndex(lastColumn - 1);
        imageData.setRelativeLastRowIndex(lastRow - 1);

        int index = sheet.getWorkbook().addPicture(imageData.getImage(), 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(StyleUtil.getCoordinate(2));
        anchor.setDx2(-StyleUtil.getCoordinate(2));
        anchor.setDy1(StyleUtil.getCoordinate(2));
        anchor.setDy2(-StyleUtil.getCoordinate(2));
        // 图片行列
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + (lastColumn - firstColumn + 1));
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex() + (lastRow - firstRow + 1));
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor, index);
    }
}

使用方式

 excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet()
                   .registerWriteHandler(new ImageModifyHandler()).build();

不过这种方式存在一点小缺陷就是大图后还隐藏一张小图
第二种方式暂时没有出现问题

package com.demo.server.excel.handle;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.ImageData;
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 org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Objects;

/**
 * 修改图像处理程序
 *
 * @author admin
 * @date 2023/01/16
 */
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;
        }
        Sheet sheet = cell.getSheet();
        int mergeColumNum = getMergeColumNum(cell, sheet);
        int mergeRowNum = getMergeRowNum(cell, sheet);
        ImageData imageData = cellData.getImageDataList().get(0);
        imageData.setRelativeLastRowIndex(mergeRowNum - 1);
        imageData.setRelativeLastColumnIndex(mergeColumNum - 1);
        CellWriteHandler.super.afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead);
    }


    /**
     * 得到合并行num
     *
     * @param cell  细胞
     * @param sheet 表
     * @return int
     */
    public static int getMergeRowNum(Cell cell, Sheet sheet) {
        int mergeSize = 1;
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : mergedRegions) {
            if (cellRangeAddress.isInRange(cell)) {
                //获取合并的行数
                mergeSize = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1;
                break;
            }
        }
        return mergeSize;
    }

    /**
     * 得到合并列num
     *
     * @param cell  细胞
     * @param sheet 表
     * @return int
     */
    public static int getMergeColumNum(Cell cell, Sheet sheet) {
        int mergeSize = 1;
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : mergedRegions) {
            if (cellRangeAddress.isInRange(cell)) {
                //获取合并的列数
                mergeSize = cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1;
                break;
            }
        }
        return mergeSize;
    }

}