如何通过编程移除Excel文件中指定列的数据验证？

我有一个Excel文件，每个表头都设置了数据验证，分为两种类型：

• Any value
• List

需求是删除指定列，同时移除该列对应的数据验证，但目前代码只能成功删除列，数据验证仍保留。

原代码片段：

package org.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;

public class RemoveDataValidations {

public static void main(String[] args) {
    String filePath = "C:\\Users\\Downloads\\Test.xlsx";
    int columnToRemove = 2; // Replace with the actual column index to be removed

    try (FileInputStream fis = new FileInputStream(filePath);
         Workbook workbook = new XSSFWorkbook(fis)) {

        Sheet sheet = workbook.getSheetAt(0); // Assuming the first sheet, change as needed

        // Remove data validations for the specified column
        for (int i = sheet.getDataValidations().size() - 1; i >= 0; i--) {
            DataValidation dataValidation = sheet.getDataValidations().get(i);
            CellRangeAddressList addressList = dataValidation.getRegions();
            if (addressList != null && addressList.getCellRangeAddresses()[0].getFirstColumn() == columnToRemove) {
                sheet.getDataValidations().remove(i);
            }
        }

        // Remove the column (shift columns to the left)
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cellToRemove = row.getCell(columnToRemove);
                if (cellToRemove != null) {
                    row.removeCell(cellToRemove);
                }
            }
        }

        // Update the sheet to reflect the changes
        sheet.shiftColumns(columnToRemove + 1, sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() - 1, -1);

        // Save the modified workbook
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }

        System.out.println("Data validations removed successfully.");

    } catch (Exception e) {
        e.printStackTrace();
    }
}
}

（注：原文件中存在类似'Col 5'的列，删除该列时需同步移除其对应的列表验证）

问题分析 #

原代码的核心问题在于：

1. 仅检查数据验证区域的首列是否等于待删除列，忽略了验证区域覆盖多列、或待删除列是区域中某一列的情况
2. 只遍历了验证区域的第一个CellRangeAddress，遗漏了多区域数据验证的场景
3. 手动删除每行单元格的操作冗余，shiftColumns方法本身就能完成列的删除和移位

修正后的代码 #

package org.example;

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

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class RemoveDataValidations {

    public static void main(String[] args) {
        String filePath = "C:\\Users\\Downloads\\Test.xlsx";
        int columnToRemove = 2; // 替换为实际要删除的列索引（从0开始）

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 操作第一个工作表，按需修改

            // 移除指定列相关的所有数据验证
            for (int i = sheet.getDataValidations().size() - 1; i >= 0; i--) {
                DataValidation dv = sheet.getDataValidations().get(i);
                CellRangeAddressList regions = dv.getRegions();
                if (regions == null) continue;

                boolean shouldRemove = false;
                // 遍历所有验证区域，检查是否包含待删除列
                for (CellRangeAddress range : regions.getCellRangeAddresses()) {
                    if (columnToRemove >= range.getFirstColumn() && columnToRemove <= range.getLastColumn()) {
                        shouldRemove = true;
                        break;
                    }
                }

                if (shouldRemove) {
                    sheet.getDataValidations().remove(i);
                }
            }

            // 直接删除列并移位，无需手动删除每行单元格
            int lastColumn = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() - 1;
            sheet.shiftColumns(columnToRemove, lastColumn, -1);

            // 保存修改后的文件
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
            }

            System.out.println("列及对应数据验证已成功删除");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

关键修改说明 #

• 遍历数据验证的所有区域，通过列范围判断待删除列是否属于该验证区域，确保不会遗漏任何相关验证
• 简化列删除逻辑：shiftColumns方法会自动处理列的移位和单元格删除，无需逐行操作
• 增加空值判断，避免regions为空时出现空指针异常

内容的提问来源于stack exchange，提问作者Tanvi Garg