java删除excel中下拉框内容，解决java POI库中无法删除数据(dataValidation)有效性的问题

SugarFreeOixi

已于 2024-11-23 14:55:14 修改

阅读量1.2k

点赞数 1

文章标签： excel

于 2023-05-11 14:42:33 首次发布

本文链接：https://blog.youkuaiyun.com/qq_40221737/article/details/130619101

版权

文章提供了一个Java代码示例，展示如何使用ApachePOI库来创建和删除Excel文件中的数据有效性，特别是数据验证。关键代码在于`sheet.getCTWorksheet().unsetDataValidations()`，这行代码用于删除数据有效性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当我们利用poi时，创建下拉框或者其他数据有效性的时候，删除或者覆盖数据有效性的方法在网上看了一些，但是尝试无果。以下方法我能成功删除有效性，下面是关键代码部分

1.全部有效性删除

getCTWorksheet().unsetDataValidations();

//创建表和数据验证
//fis是excel输入流，假设已经被你创建好
XSSFWorkbook book = new XSSFWorkbook(fis);
XSSFSheet sheet = book.getSheet("yourSheet");

/**核心部分   sheet.getCTWorkSheet.getDataValidations().removeDataValidation(0)  */

ArrayList<XSSFDataValidation> dataValidations = sheet.getDataValidations();
if(dataValidations.size()!=0){
  //重置validations
  tmpSheet.getCTWorksheet().unsetDataValidations();
}

2.局部有效性删除，比如只删除'C'列的下拉框

关键在于：

不要用sheet.getDatavalidations()这个方法。

而是用【 sheet.getCTWorksheet().getDataValidations()】中的内容

其中CT可能表示的是：Current的意思，指的是当前工作表

		//delete colunm name: C列
		String targetIdexABC = "C";
		//delete old colunm
		CTDataValidations dataValidationsCurrent = sheet.getCTWorksheet().getDataValidations();
		List<CTDataValidation> dataValidationList = dataValidationsCurrent.getDataValidationList();
		int indexDelete = -1;
		for(int i=0;i<dataValidationList.size();i++){
			CTDataValidation ctDataValidation = dataValidationList.get(i);
			String region = ctDataValidation.getSqref().toString();
			//targer colunm
			if(region.startsWith(targetIdexABC)){
				indexDelete = i;
			}
		}
		if(-1!=indexDelete){
			dataValidationsCurrent.removeDataValidation(indexDelete);
		}
sheet.getCTWorksheet().setDataValidations(dataValidationsCurrent);

3.移动有效性的位置

ctDataValidation.setSqref(Collections.singletonList(newRegion));

public static void resetDropDownPullerMenuListNoRemoveValidation(XSSFSheet sheet, List<String> list, String colunmName, int endNum) {
		HashMap<String, Integer> headMap = getHeadMap(sheet);
		Integer indexTarget = headMap.get(colunmName)+1;
		LinkedHashMap<Integer, String> numToABCMap = CommonUtils.parseColumRetrun();
		String targetIdexABC = numToABCMap.get(indexTarget);

		//delete old colunm
		CTDataValidations dataValidationsCurrent = sheet.getCTWorksheet().getDataValidations();
		List<CTDataValidation> dataValidationList = dataValidationsCurrent.getDataValidationList();
		int indexDelete = -1;
		for(int i=0;i<dataValidationList.size();i++){
			CTDataValidation ctDataValidation = dataValidationList.get(i);
			String region = ctDataValidation.getSqref().toString();
			//targer colunm
			if(region.startsWith(targetIdexABC)){
				indexDelete = i;
			}
			//change the rest order
			int oldIndex = CommonUtils.mapStringToNumber(String.valueOf(region.charAt(0)))+1;
			if(oldIndex>=indexTarget){
				String Indexnew = numToABCMap.get(oldIndex + 1);
				String newRegion = region.replace(String.valueOf(region.charAt(0)),Indexnew);
				ctDataValidation.setSqref(Collections.singletonList(newRegion));
			}
		}
//		if(-1!=indexDelete){
//			dataValidationsCurrent.removeDataValidation(indexDelete);
//		}
//		sheet.getCTWorksheet().unsetDataValidations();
		sheet.getCTWorksheet().setDataValidations(dataValidationsCurrent);

		//add new validation
		sheet.disableLocking();
		XSSFDataValidationHelper helper = (XSSFDataValidationHelper) sheet.getDataValidationHelper();
		XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(list.toArray(new String[list.size()]));
		CellRangeAddressList address = new CellRangeAddressList(1, endNum+1,headMap.get(colunmName),headMap.get(colunmName));
		XSSFDataValidation validationNew = (XSSFDataValidation) helper.createValidation(constraint, address);
		validationNew.setSuppressDropDownArrow(true);
		validationNew.setShowErrorBox(false);
		sheet.addValidationData(validationNew);
		List<XSSFDataValidation> dataValidations = sheet.getDataValidations();

	}


	public static HashMap<String, Integer> getHeadMap(XSSFSheet sheet) {
		HashMap<String,Integer> headMap=new HashMap<String, Integer>();
		XSSFRow row = sheet.getRow(0);
		for (int i = 0; i < row.getLastCellNum(); i++) {
			String value = parseCell(row.getCell(i));
			if (value.equals("")) {
				continue;
			}
			headMap.put(value, i);
		}
		return headMap;
	}


	public static LinkedHashMap<Integer,String> parseColumRetrun() {
		LinkedHashMap<Integer,String> out = new LinkedHashMap<>();
		String base="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		char[] charArray = base.toCharArray();
		for (int i = 0; i < 50; i++) {
			if (i<26) {
				out.put(i+1, String.valueOf(charArray[i]));
			}else {
				out.put(i+1, "A"+String.valueOf(charArray[i-26]));
			}
		}
		return out;
	}

	public static int mapStringToNumber(String input) {
		input = input.toUpperCase(); // 将输入字符串转换为大写，方便统一处理
		if (input.length() == 1 && input.charAt(0) >= 'A' && input.charAt(0) <= 'Z') {
			return input.charAt(0) - 'A';
		} else {
			// 如果输入不符合要求，返回 -1
			return -1;
		}
	}

以下是完整代码例子(全局删除)


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExample {

  public static void main(String[] args) throws Exception {
    // 创建工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    workbook.setForceFormulaRecalculation(true);
    XSSFSheet sheet = workbook.createSheet("TestSheet");

    // 创建数据验证 ---在表TestSheet中的A1单元格
    DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
    DataValidationConstraint validationConstraint = validationHelper.createExplicitListConstraint(new String[]{"数据有效性1", "数据有效性2", "数据有效性3"});
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DataValidation validation = validationHelper.createValidation(validationConstraint, addressList);
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(false);
    
    sheet.addValidationData(validation);
    
    

    // 将工作簿保存到文件
    try {
      FileOutputStream fileOut = new FileOutputStream("example.xlsx");
      workbook.write(fileOut);
      fileOut.close();
      workbook.close();
    } catch (IOException e) {
      e.printStackTrace();
    }
    
    //删除有效性
    FileInputStream fis = new FileInputStream("example.xlsx");
    XSSFWorkbook tmpBook = new XSSFWorkbook(fis);
    tmpBook.setForceFormulaRecalculation(true);
    XSSFSheet tmpSheet = tmpBook.getSheet("TestSheet");
    
    /**核心部分   tmpSheet.getCTWorksheet().unsetDataValidations();  */
    List<XSSFDataValidation> dataValidationsDeleted = tmpSheet.getDataValidations();
    if(dataValidationsDeleted.size()!=0){
    	//方法一  推荐使用
    	tmpSheet.getCTWorksheet().unsetDataValidations();
    	
          //方法二 缺点：打开execl会报错，但是点击自动修复，也能正常打开
//        while(tmpSheet.getCTWorksheet().getDataValidations().sizeOfDataValidationArray()!=0){
//            // 可以调试时候看validations的数据结构，里面有你之前积累的数据有效性
//            //在这里全部删除
//        	tmpSheet.getCTWorksheet().getDataValidations().removeDataValidation(0);
//        	
//        }
    }
    
    fis.close();
    FileOutputStream fos = new FileOutputStream("exampleDeletedValidation.xlsx");
    tmpBook.write(fos);
    tmpBook.close();
    fos.close();
    
    
  }
}