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

【poi|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 listRange = sheet.getMergedRegions(); List removeRangeNums = new ArrayList(); CellRangeAddress r = null; for (int i=0; i=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 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= startNumUp && row1 <= endNumUp) { int row2 = anchor.getRow2(); anchor.setRow1(row1 - moveNumUp); anchor.setRow2(row2 - moveNumUp); } } }return sheet; }}


    推荐阅读