如何使用Apache POI删除行和去除合并单元格-20220530

代码 只能清空数据，不能删除行

public class MainTest {

    public static void main(String[] args) throws IOException {

        FileInputStream file  =   new FileInputStream(new  File("test.xlsx") );
        XSSFWorkbook wb = new XSSFWorkbook(file);
        XSSFSheet sheet = wb.getSheetAt(0);
        sheet.removeRow(sheet.getRow(3));
//      sheet.shiftRows(3, 3, -1);

        File outWB = new File("testResult.xlsx");
        OutputStream out = new FileOutputStream(outWB);
        wb.write(out);
        out.flush();
        out.close();


        System.exit(0);
    }

}

删除合并单元格

// 参数1:sheet、参数2:开始行、参数3:开始列
public static void removeMergedRegion(Sheet sheet, int row , int column)
    {
        int sheetMergeCount = sheet.getNumMergedRegions();//获取所有的单元格
        int index = 0;//用于保存要移除的那个单元格序号
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i); //获取第i个单元格
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow)
            {
                if(column >= firstColumn && column <= lastColumn)
                {
                    index = i;
                }
            }
        }
        sheet.removeMergedRegion(index);//移除合并单元格
    }

本地·测试代码

package com.dxy.demo_05_08.study.excelread;


import com.ibm.icu.impl.Row;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

public class ScExcel {
    public static void main(String[] args) throws Exception {
        File file = new File("E:\\other\\0526.xlsx");
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        //删除第一二行
        sheetAt.removeRow(sheetAt.getRow(0));
       // sheetAt.removeRow(sheetAt.getRow(1));
        //去除合并单元格
       /* removeMergedRegion(sheetAt,0,1);
        removeMergedRegion(sheetAt,0,3);
        removeMergedRegion(sheetAt,0,5);
        removeMergedRegion(sheetAt,1,0);*/

        //循环去除 合并单元格
        for (int i = 0; i < sheetAt.getRow(2).getPhysicalNumberOfCells(); i++) {
            removeMergedRegion(sheetAt, 0, i);
            //removeMergedRegion(sheetAt, 1, i);
        }

        //给第二行第一列设置值
        sheetAt.getRow(1).getCell(0).setCellValue("地市");


        File outWB = new File("E:\\other\\052622.xlsx");
        OutputStream out = new FileOutputStream(outWB);
        sheets.write(out);
        out.flush();
        out.close();
    }

    // 参数1:sheet、参数2:开始行、参数3:开始列
    public static void removeMergedRegion(XSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();//获取所有的单元格
        int index = 0;//用于保存要移除的那个单元格序号
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i); //获取第i个单元格
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    index = i;
                }
            }
        }
        sheet.removeMergedRegion(index);//移除合并单元格
    }


}

参考文章 poi操作excel之删除模版的合并行

https://blog.csdn.net/ITarmi/article/details/123775069