Poi操作Excel,保留格式的情况下插入行

2023/03/20修改bug，startRow为最后一行时无法插入新行

如果复制最后一行，首先需要创建最后一行的下一行，否则无法插入

例1:

问：如果我想在李四下方插入一个空行该如何操作

我们应该在行标为3的下方插入1行

startRow = 3

row = 1



    public static void main(String[] args) {
        ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\Wsong\\Desktop\\template.xlsx"),"活动信息");
        int startRow= 3;
        int rows = 1;
        XSSFSheet sheet = (XSSFSheet)writer.getSheet();

        ExcelCopyUtils.InsertRow(writer, startRow, rows, sheet,false);
    }

结果

执行后的结果，我们可以看到样式与行标为3的行一致。

例2

问：在例1的结果基础上，我们想要在张三后插入3行，样式拷贝张三，如何操作

我们应该在行标为5的下方插入3行

startRow =5

row = 3



    public static void main(String[] args) {
        ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\Wsong\\Desktop\\template.xlsx"),"活动信息");
        int startRow= 5;
        int rows = 3;
        XSSFSheet sheet = (XSSFSheet)writer.getSheet();

        ExcelCopyUtils.InsertRow(writer, startRow, rows, sheet,false);
    }

结果

代码如下。

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.0.5</version>
        </dependency>

package com.ws.excel;


import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.File;
import java.util.List;

public class ExcelCopyUtils {

    public static void main(String[] args) {
        ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\Wsong\\Desktop\\template.xlsx"),"活动信息");
        // startRow=5 行标为5的行后插入2行,样式拷贝第5行的样式
        int startRow= 5;
        int rows = 2;
        XSSFSheet sheet = (XSSFSheet)writer.getSheet();

        InsertRow(writer, startRow, rows, sheet,false);
    }

    /**
     *
     * @param writer ExcelWriter
     * @param startRow 插入行的行标,即在哪一行下插入
     * @param rows 插入多少行
     * @param sheet XSSFSheet
     * @param copyvalue 新行复制(startRow-1)行的样式,而且在拷贝行的时候可以指定是否需要拷贝值
     * @Author Wsong qzsoft
     */
    private static void InsertRow(ExcelWriter writer, int startRow, int rows, XSSFSheet sheet,Boolean copyvalue) {
        if(sheet.getRow(startRow+1)==null){
            // 如果复制最后一行，首先需要创建最后一行的下一行，否则无法插入，Bug 2023/03/20修复
            sheet.createRow(startRow+1);
        }

        //先获取原始的合并单元格address集合
        List<CellRangeAddress> originMerged = sheet.getMergedRegions();

        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            //判断移动的行数后重新拆分
            if(region.getFirstRow()>startRow){
                sheet.removeMergedRegion(i);
            }
        }

        sheet.shiftRows(startRow,sheet.getLastRowNum(),rows,true,false);
        sheet.createRow(startRow);

        for(CellRangeAddress cellRangeAddress : originMerged) {
            //这里的8是插入行的index，表示这行之后才重新合并
            if(cellRangeAddress.getFirstRow() > startRow) {
                //你插入了几行就加几，我这里插入了一行，加1
                int firstRow = cellRangeAddress.getFirstRow() + rows;
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(firstRow, (firstRow + (cellRangeAddress
                        .getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
                        cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }
        writer.flush();
        CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
        cellCopyPolicy.setCopyCellValue(copyvalue);
        cellCopyPolicy.isCopyCellValue();
        for (int i = 0; i < rows; i++) {
            sheet.copyRows(startRow-1,startRow-1,startRow+i,cellCopyPolicy);
        }
        writer.close();
    }
}