【java】poi操作excel复制、移动覆盖列，删除行，删除列，公式同步--excel工具类

最近接到个需求需要对excel表格的列进行移动，并且要保证数据格式，公式都同步更新到对应的新列。
但是找了好久都没找到能用的方法，看来偷懒不成了，索性自己写了一个。
直接上代码
 

依赖 4.0.0以上都可以，原来5.1.0是有直接可以移动列的方法，但是试过报了索引越界，看了好多人遇到也没说清楚问题就没搞。

其他的移列方法都不能同步更新移动列后的公式，所以自己动手丰衣足食~~~~

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>5.1.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.1.0</version>
            </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

package com.gsr.Excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author Alan
 * @Date 2023/11/7 18:16
 * @Version 0.1
 *
 *  excel 工具类
 *
 */
public class CopyExcelUtil {


    /**
     * 复制单元格
     * 通用方法来根据源单元格的类型设置目标单元格的值
     * @param sourceCell  原单元格 （复制的单元格）
     * @param targetCell  目标单元格 （黏贴的单元格）
     */
    public static void copyCellValue(Cell sourceCell, Cell targetCell) {
        if (sourceCell == null) {
            return;
        }

        //复制值
        CellType cellType = sourceCell.getCellType();
        switch (cellType) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(sourceCell)) {
                    targetCell.setCellValue(sourceCell.getDateCellValue());
                } else {
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellValue(sourceCell.getCellFormula());
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            default:
                targetCell.setCellValue(sourceCell.toString());
        }

        // 复制旧单元格的样式
        CellStyle newCellStyle = targetCell.getSheet().getWorkbook().createCellStyle();
        CellStyle oldCellStyle = sourceCell.getCellStyle(); // 旧单元格样式
        newCellStyle.cloneStyleFrom(oldCellStyle);

        // // 获取旧单元格的边框设置
        newCellStyle.setBorderLeft(oldCellStyle.getBorderLeft());
        newCellStyle.setBorderRight(oldCellStyle.getBorderRight());
        newCellStyle.setBorderTop(oldCellStyle.getBorderTop());
        newCellStyle.setBorderBottom(oldCellStyle.getBorderBottom());

        // 获取旧单元格的边框颜色设置
        newCellStyle.setLeftBorderColor(oldCellStyle.getLeftBorderColor());
        newCellStyle.setRightBorderColor(oldCellStyle.getRightBorderColor());
        newCellStyle.setTopBorderColor(oldCellStyle.getTopBorderColor());
        newCellStyle.setBottomBorderColor(oldCellStyle.getBottomBorderColor());

        targetCell.setCellStyle(newCellStyle);//设置样式

        // 获取源单元格和目标单元格所在的表格
        Sheet sheet = sourceCell.getSheet(); // 旧表格
        Sheet targetsheet = targetCell.getSheet(); // 新表格


        // 检查旧单元格是否为合并单元格
        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
            // 获取当前合并区域
            CellRangeAddress mergedRegion = sheet.getMergedRegion(j);

            if (mergedRegion.isInRange(sourceCell.getRowIndex(), sourceCell.getColumnIndex())) {
                // 计算目标单元格的合并区域
                int newFirstRow = targetCell.getRowIndex();
                int newLastRow = targetCell.getRowIndex() + (mergedRegion.getLastRow() - mergedRegion.getFirstRow());
                int newFirstCol = targetCell.getColumnIndex();
                int newLastCol = targetCell.getColumnIndex() + (mergedRegion.getLastColumn() - mergedRegion.getFirstColumn());

                // 创建一个新的合并区域对象
                CellRangeAddress newMergedRegion = new CellRangeAddress(newFirstRow, newLastRow, newFirstCol, newLastCol);

                // 判断新的合并区域是否与已存在的合并区域重叠
                boolean isOverlapping = false;
                for (int i = 0; i < targetsheet.getNumMergedRegions(); i++) {
                    // 获取目标表格的一个合并区域
                    CellRangeAddress existingRegion = sheet.getMergedRegion(i);
                    // 判断新的合并区域是否与已存在的合并区域重叠
                    if (existingRegion.intersects(newMergedRegion)) {
                        isOverlapping = true;
                        break;
                    }
                }

                // 如果没有重叠的合并区域，则将新合并区域添加到表格中
                if (!isOverlapping) {
                    try{
                        targetsheet.addMergedRegion(new CellRangeAddress(newFirstRow, newLastRow, newFirstCol, newLastCol));
                    }catch (Exception e){
                        System.out.println("合并单元格失败");
                    }
                }
            }
        }

        //设置单元格大小 (宽度和高度)
        targetsheet.setColumnWidth(targetCell.getColumnIndex(), sheet.getColumnWidth(sourceCell.getColumnIndex()));
        targetsheet.setColumnHidden(targetCell.getColumnIndex(), sheet.isColumnHidden(sourceCell.getColumnIndex()));

    }



    /**
     * 删除指定列
     * @param sheet
     * @param column
     */
    private static void removeColumn(Sheet sheet, int column) {
        for (Row row : sheet) {
            Cell cell = row.getCell(column);
            if (cell != null) {
                row.removeCell(cell);
            }
        }
    }



    /**
     * 向左移动带公式的列的方法（注意移动目标的位置必须为空）
     * @param sheet  工作表
     * @param colNum 移动的列数 这里是负数
     * @param firstShiftColumnIndex 移动列的范围 从第几列开始移动
     * @param lastShiftColumnIndex 移动列的范围 从第几列结束移动
     */
    public static void rangeShiftColumnLeft(Sheet sheet, int colNum,int firstShiftColumnIndex, int lastShiftColumnIndex) {

        // 删除合并单元格 （清除移动目标列的单元格格式）
        /**
         * firstShiftColumnIndex + colNum 移动目标列的起始位置 - 移动的位置 = 需要覆盖的列的起始位置
         * firstShiftColumnIndex-1  移动目标列的起始位置 - 1 = 需要覆盖的列的结束位置
         */
        removeMerged(sheet,0,4,firstShiftColumnIndex + colNum ,firstShiftColumnIndex-1);

        // 获取工作表的最后一行的索引
        int lastRowNum = sheet.getLastRowNum();

        // 遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {
            // 获取当前行
            Row row = sheet.getRow(i);

            // 如果当前行为空，创建一个新的行
            if (row == null) {
                row = sheet.createRow(i);
            }


            // 从右向左遍历每一个单元格
            for (int j = firstShiftColumnIndex; j <= lastShiftColumnIndex; j++) {
                // 获取当前单元格
                Cell cell = row.getCell(j);

                // 如果当前单元格为空，创建一个新的单元格
                if (cell == null) {
                    cell = row.createCell(j);
                }

                // 获取当前单元格的类型
                CellType cellType = cell.getCellType();


                // 如果当前单元格是公式类型，更新公式中的引用
                if (cellType == CellType.FORMULA) {
                    // 获取当前单元格的公式3
                    String formula = cell.getCellFormula();

                    // 调用自定义的方法，将公式中的列引用向右移动colNum个位置
                    formula = shiftFormula(formula, colNum);

                    // 设置修改后的公式
                    cell.setCellFormula(formula);
                }

                // 创建一个新的单元格，位置为当前单元格向右移动colNum个位置
                Cell newCell = row.createCell(j + colNum);

                // 将当前单元格的值和样式复制到新的单元格
                copyCellValue(cell,newCell);

                // 清空当前单元格的值和样式
                cell.setCellValue("");
                cell.setCellStyle(null);
                if (cellType == CellType.FORMULA) {
                    cell.setCellFormula(null);
                }
            }
        }

        // 创建一个公式计算器，用来重新计算工作表中的公式
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        // 遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {
            // 获取当前行
            Row row = sheet.getRow(i);

            // 如果当前行为空，跳过
            if (row == null) {
                continue;
            }

            // 获取当前行的最后一个单元格的索引
            int lastCellNum = row.getLastCellNum();

            // 遍历每一个单元格
            for (int j = 0; j <= lastCellNum; j++) {
                // 获取当前单元格
                Cell cell = row.getCell(j);

                // 如果当前单元格为空，跳过
                if (cell == null) {
                    continue;
                }

                // 获取当前单元格的类型
                CellType cellType = cell.getCellType();

                // 如果当前单元格是公式类型，重新计算公式的值
                if ( cellType == CellType.FORMULA) {
                    evaluator.evaluateFormulaCell(cell);
                }
            }
        }

        // 删除合并单元格 （清除移动后原来列残留的单元格格式）
        removeMerged(sheet,0,4,firstShiftColumnIndex,lastShiftColumnIndex);
    }


    /**
     * 向右边移动带公式的列的方法（注意移动目标的位置必须为空）
     * @param sheet  工作表
     * @param colNum 移动的列数 这里是整数
     * @param firstShiftColumnIndex 移动列的范围 从第几列开始移动
     * @param lastShiftColumnIndex 移动列的范围 从第几列结束移动
     */
    public static void rangeShiftColumnRight(Sheet sheet, int colNum,int firstShiftColumnIndex, int lastShiftColumnIndex) {
        // 获取工作表的最后一行的索引
        int lastRowNum = sheet.getLastRowNum();

        // 遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {
            // 获取当前行
            Row row = sheet.getRow(i);

            // 如果当前行为空，创建一个新的行
            if (row == null) {
                row = sheet.createRow(i);
            }


            // 从右向左遍历每一个单元格
            for (int j = lastShiftColumnIndex; j >= firstShiftColumnIndex; j--) {
                // 获取当前单元格
                Cell cell = row.getCell(j);

                // 如果当前单元格为空，创建一个新的单元格
                if (cell == null) {
                    cell = row.createCell(j);
                }

                // 获取当前单元格的类型
                CellType cellType = cell.getCellType();


                // 如果当前单元格是公式类型，更新公式中的引用
                if (cellType == CellType.FORMULA) {
                    // 获取当前单元格的公式3
                    String formula = cell.getCellFormula();

                    // 调用自定义的方法，将公式中的列引用向右移动colNum个位置
                    formula = shiftFormula(formula, colNum);

                    // 设置修改后的公式
                    cell.setCellFormula(formula);
                }

                // 创建一个新的单元格，位置为当前单元格向右移动colNum个位置
                Cell newCell = row.createCell(j + colNum);

                // 将当前单元格的值和样式复制到新的单元格
                // newCell.setCellValue(cell.getStringCellValue());
                // newCell.setCellStyle(cell.getCellStyle());
                copyCellValue(cell,newCell);

                // 清空当前单元格的值和样式
                cell.setCellValue("");
                cell.setCellStyle(null);
                if (cellType == CellType.FORMULA) {
                    cell.setCellFormula(null);
                }
            }
        }

        // 创建一个公式计算器，用来重新计算工作表中的公式
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        // 遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {
            // 获取当前行
            Row row = sheet.getRow(i);

            // 如果当前行为空，跳过
            if (row == null) {
                continue;
            }

            // 获取当前行的最后一个单元格的索引
            int lastCellNum = row.getLastCellNum();

            // 遍历每一个单元格
            for (int j = 0; j <= lastCellNum; j++) {
                // 获取当前单元格
                Cell cell = row.getCell(j);

                // 如果当前单元格为空，跳过
                if (cell == null) {
                    continue;
                }

                // 获取当前单元格的类型
                CellType cellType = cell.getCellType();

                // 如果当前单元格是公式类型，重新计算公式的值
                if ( cellType == CellType.FORMULA) {
                    evaluator.evaluateFormulaCell(cell);
                }
            }
        }
    }




    /**
     * 自定义的方法，用来将公式中的列引用指定方向移动colNum个位置（自动根据移动列数更新公式）
     * @param formula  公式
     * @param colNum  单元格移动的列数   （正数右移动，负数左边移动）
     * @return
     */
    public static String shiftFormula(String formula, int colNum) {
        // 定义一个字符串缓冲区，用来存储修改后的公式
        StringBuffer sb = new StringBuffer();

        // 定义一个正则表达式，用来匹配公式中的列引用，如A1，B2等
        String regex = "([A-Z]+)(\\d+)";

        // 定义一个模式，用来编译正则表达式
        Pattern pattern = Pattern.compile(regex);

        // 定义一个匹配器，用来匹配公式
        Matcher matcher = pattern.matcher(formula);

        // 遍历公式中的每一个匹配项
        while (matcher.find()) {
            // 获取匹配项的内容，如A1，B2等
            String match = matcher.group();

            // 获取匹配项中的列引用，如A，B等
            String colRef = matcher.group(1);

            // 获取匹配项中的行引用，如1，2等
            String rowRef = matcher.group(2);

            // 将列引用转换为数字，如A为1，B为2等
            int colNumRef = colNameToNumber(colRef);

            // 将列数字加上移动的位置，得到新的列数字
            int newColNumRef = colNumRef + colNum;

            // 将新的列数字转换为列引用，如1为A，2为B等
            String newColRef = colNumberToName(newColNumRef);

            // 将新的列引用和原来的行引用拼接起来，得到新的匹配项，如A1变为F1，B2变为G2等
            String newMatch = newColRef + rowRef;

            // 将原来的匹配项替换为新的匹配项
            matcher.appendReplacement(sb, newMatch);
        }

        // 将剩余的部分添加到字符串缓冲区
        matcher.appendTail(sb);

        // 返回修改后的公式
        return sb.toString();
    }

    /**
     * 自定义的方法，用来将列引用转换为数字，如A为1，B为2等
     * @param colName  列引用
     * @return
     */
    public static int colNameToNumber(String colName) {
        // 定义一个变量，用来存储列数字
        int colNum = 0;

        // 遍历列引用中的每一个字符
        for (int i = 0; i < colName.length(); i++) {
            // 获取当前字符，如A，B等
            char c = colName.charAt(i);

            // 将当前字符转换为数字，如A为1，B为2等
            int n = c - 'A' + 1;

            // 将列数字乘以26，再加上当前字符的数字，得到新的列数字
            colNum = colNum * 26 + n;
        }

        // 返回列数字
        return colNum;
    }

    /**
     * 自定义的方法，用来将数字转换为列引用，如1为A，2为B等
     * @param colNum
     * @return
     */
    public static String colNumberToName(int colNum) {
        // 定义一个字符串缓冲区，用来存储列引用
        StringBuffer sb = new StringBuffer();

        // 当列数字大于0时，循环执行
        while (colNum > 0) {
            // 将列数字减去1，得到新的列数字
            colNum--;

            // 将列数字除以26，得到商和余数
            int quotient = colNum / 26;
            int remainder = colNum % 26;

            // 将余数转换为字符，如0为A，1为B等
            char c = (char) (remainder + 'A');

            // 将字符插入到字符串缓冲区的最前面
            sb.insert(0, c);

            // 将商赋值给列数字，继续循环
            colNum = quotient;
        }

        // 返回列引用
        return sb.toString();
    }

    /**
     * 取消多个合并单元格
     *
     * @param sheet
     * @param startRow    开始行号
     * @param endRow      结束行号
     * @param startColumn 开始列号
     * @param endColumn   结束列号
     */
    public static void removeMerged(Sheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
        // if(startRow==null){
        //     startRow= sheet.getFirstRowNum();
        // }
        // if(endRow==null){
        //     endRow= sheet.getLastRowNum();
        // }
        //获取所有的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //用于保存要移除的那个合并单元格序号
        List<Integer> indexList = new ArrayList<>();
        for (int i = 0; i < sheetMergeCount; i++) {
            //获取第i个单元格
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) {
                indexList.add(i);
            }
        }
        sheet.removeMergedRegions(indexList);
    }



    /**
     * 合并单元格
     * @param sheet 工作表
     * @param firstRow 起始行
     * @param lastRow 终止行
     * @param firstCol 起始列
     * @param lastCol 终止列
     */
    public static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        // 创建一个合并单元格的范围对象
        CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

        // 将合并单元格范围添加到工作表中
        sheet.addMergedRegion(mergedRegion);
    }


    /**
     * 指定保留行数，删除多余的行
     * @param sheet  工作表
     * @param numRowsToKeep  保留的行数（从0开始计算）
     */
    public static void deleteRows(Sheet sheet, int numRowsToKeep) {
        //获取最大行数
        int lastRowNum = sheet.getLastRowNum();

        //实际行数比保留行数多，才需要删除多余的行
        if (lastRowNum > numRowsToKeep){
            // 遍历需要删除的行，并删除它们
            for (int i = lastRowNum; i > numRowsToKeep; i--) {
                //获取的行不为空才可以删除
                if (sheet.getRow(i) != null){
                    sheet.removeRow(sheet.getRow(i));
                }
            }
        }
    }



}

向右移动列的有点问题，后期有空优化一下，代码写的仓促也没怎么封装，水个帖先。