poi之----删除Row的实现，包括合并单元格以及图片数据

写在前面

最近用到easypoi poi 比较多，于是把遇到的问题整理了一下，就有着这几篇文章。本篇主要是解决poi无法删除row的问题，网上一些移动row的方法，在遇到合并单元格 或者 含有图片的时候会报错，于是自己封了一个工具方法。

内有注释，主要步骤

1.把删除行之下的所有行下移足够大的距离（大于移动的行数），包含图片处理

2.把删除起始行 到 移动后的开始有数据那一行之前的所有行重置

3.再把移动下去的所有行上移（下移行数+删除行数）包含图片处理

下面直接把工具类贴出来了，之前几篇关于poi的文章也可以看一下，可能会更明了。

package com.ruoyi.framework.easypoi.util;


import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @version v1.0
 * @ProjectName: easypoi-test
 * @ClassName: Sd3eUtil
 * @Description:
 * @Author: xbx
 * @Date: 2022/3/7 16:49
 */
public class Sd3eUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(Sd3eUtil.class);

    /**
     * @Author: 徐本锡
     * @Date: 2022/3/7 16:49
     * @param: [sheet, firstRow, lastRow, firstCol, lastCol]
     * @return: void
     * @description: list循环的时候  存在合并单元格的时候  格式错乱问题
     */
    public static void addMergedRegionByListForEach(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        try {
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        } catch (IllegalStateException var6){
            //合并单元格的循环 可能存在 已经合并的再次合并  不管他
            cancelMerged(sheet, firstRow, lastRow, firstCol, lastCol);
            addMergedRegionByListForEach(sheet, firstRow, lastRow, firstCol, lastCol);
        } catch (Exception var6) {
            LOGGER.debug("发生了一次合并单元格错误,{},{},{},{}", new Integer[]{firstRow, lastRow, firstCol, lastCol});
            LOGGER.debug(var6.getMessage(), var6);
        }

    }


    /**
     * @Author: 徐本锡
     * @Date: 2022/3/9 15:44
     * @param: [sheet, firstRow, lastRow, firstCol, lastCol]
     * @return: void
     * @description: 取消某一个范围内的合并单元格
     */
    public static void cancelMerged(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol){
        List<CellRangeAddress> listRange = sheet.getMergedRegions();

        List<Integer> removeRangeNums = new ArrayList<Integer>();
        CellRangeAddress r = null;
        for (int i=0; i<listRange.size(); i++){
            r = listRange.get(i);
            if (r.getFirstRow()>=firstRow && r.getLastRow()<=lastRow && r.getFirstColumn()>=firstCol && r.getLastColumn()<=lastCol){
                removeRangeNums.add(i);
            }
        }
        sheet.removeMergedRegions(removeRangeNums);
    }

    /**
     * @Author: 徐本锡
     * @Date: 2022/3/11 9:40
     * @param: [sheet, startRow, endRow]
     * @return: org.apache.poi.ss.usermodel.Sheet
     * @description: 删除行
     */
    public static Sheet deleteRow(Sheet sheet,int startRow,int endRow) {
        //获取最大的列数
        int maxColNum=0;
        Iterator<Row> rowIt = sheet.rowIterator();
        while(rowIt.hasNext()){
            maxColNum = Math.max(maxColNum,rowIt.next().getLastCellNum());
        }

        //向下移动
        int startNumDown = endRow+1;
        int endNumDown = sheet.getLastRowNum();
        int moveNumDown = endNumDown - startNumDown + 1;
        sheet.shiftRows(startNumDown, endNumDown,moveNumDown,true,true);
        //图片的处理
        XSSFDrawing drawings = (XSSFDrawing) sheet.createDrawingPatriarch();
        for(XSSFShape shape : drawings.getShapes()){
            if(shape instanceof Picture){
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFClientAnchor anchor = picture.getClientAnchor();
                int row1 = anchor.getRow1();
                if (row1 >= startNumDown && row1 <= endNumDown) {
                    int row2 = anchor.getRow2();
                    anchor.setRow1(row1 + moveNumDown);
                    anchor.setRow2(row2 + moveNumDown);
                }
            }
        }

        //空白部分 取消合并处理
        cancelMerged(sheet,startRow,endRow+moveNumDown,0,maxColNum);
        //重置这一部分
        for (int i=startRow; i<endRow+moveNumDown;i++){
            sheet.createRow(i);
            sheet.removeRow(sheet.getRow(i));
        }

        //再向上移动回去
        int startNumUp = endNumDown+1;
        int endNumUp = sheet.getLastRowNum();
        int moveNumUp = moveNumDown + (endRow-startRow)+1;
        sheet.shiftRows(startNumUp,endNumUp,-1*moveNumUp,true,true);
        //图片的处理
        drawings = (XSSFDrawing) sheet.createDrawingPatriarch();
        for(XSSFShape shape : drawings.getShapes()){
            if(shape instanceof Picture){
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFClientAnchor anchor = picture.getClientAnchor();
                int row1 = anchor.getRow1();
                if (row1 >= startNumUp && row1 <= endNumUp) {
                    int row2 = anchor.getRow2();
                    anchor.setRow1(row1 - moveNumUp);
                    anchor.setRow2(row2 - moveNumUp);
                }
            }
        }

        return sheet;
    }

}