基于POI的Excel下拉框自动搜索，包括数据验证的单列删除

devtools/2025/3/4 7:57:10/

目录

目标

例子

1.搜索下拉框页

2.数据源页

3.效果

代码以及注意事项

1.代码

2.注意事项

1.基于Excel的话，相当于加入了一个【数据验证】

2.代码中的一些方法说明

目标

期望在Excel利用代码创建具备自动搜索功能的下拉框

例子

1.搜索下拉框页

2.数据源页

3.效果

代码以及注意事项

1.代码

java">package yourpackage;import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidations;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class YourClass {public static void main(String[] args) throws IOException {String pathExcel = "D:\\XXXX\\test.xlsx";File file = new File(pathExcel);FileInputStream fis = new FileInputStream(file);XSSFWorkbook book = new XSSFWorkbook(fis);XSSFSheet sheetTaget = book.getSheet("目标");XSSFSheet sheetEnum = book.getSheet("待搜索数据源");//参数说明://1.目标页； 2.目标页第一行表头的列名【自动搜索的数据】；3.数据页的最后一行；4.数据页的数据所在的列序号 5.数据页的sheet名resetDropDownPullerMenuListNoRemoveValidationUpdateFormula(sheetTaget,"自动搜索的数据",sheetEnum.getLastRowNum(),"A",sheetEnum.getSheetName());//写文件FileOutputStream fos = new FileOutputStream(file);book.write(fos);fos.close();fis.close();}public static void resetDropDownPullerMenuListNoRemoveValidationUpdateFormula(XSSFSheet sheet,String colunmName, int endNum,String indexEnum,String enumSheetName) {HashMap<String, Integer> headMap = getHeadMap(sheet);Integer indexTarget = headMap.get(colunmName)+1;Map<Integer, String> numToABCMap =  parseColumWithReturn();String targetIdexABC = numToABCMap.get(indexTarget);String sheetName = sheet.getSheetName();//找到原来数据验证项目:indexDelete并删除，否则会堆叠，报错CTDataValidations dataValidationsCurrent = sheet.getCTWorksheet().getDataValidations();int indexDelete = -1;if(null!=dataValidationsCurrent){List<CTDataValidation> dataValidationList = dataValidationsCurrent.getDataValidationList();for(int i=0;i<dataValidationList.size();i++){CTDataValidation ctDataValidation = dataValidationList.get(i);String region = ctDataValidation.getSqref().toString();//targer colunmif(region.startsWith(targetIdexABC)){indexDelete = i;break;}
//				//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();//获取当前页并设置验证【下拉框】，CT=current tablesheet.getCTWorksheet().setDataValidations(dataValidationsCurrent);//add new validationsheet.disableLocking();XSSFDataValidationHelper helper = (XSSFDataValidationHelper) sheet.getDataValidationHelper();Integer indexPhysical = headMap.get(colunmName);String indexABC = numToABCMap.get(indexPhysical+1);String enumCheetolumIndex = indexEnum;//设置搜索公式，该步骤等价于在【数据验证】中，选择序列，然后输入公式String offsetFormula = "OFFSET("+enumSheetName+"!$"+enumCheetolumIndex+"$1,MATCH(\"*\"&"+indexABC+"2&\"*\","+enumSheetName+"!$"+enumCheetolumIndex+":$"+enumCheetolumIndex+",0)-1,0,COUNTIFS("+enumSheetName+"!$"+enumCheetolumIndex+":$"+enumCheetolumIndex+",\"*\"&"+indexABC+"2&\"*\")+1,1)";XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createFormulaListConstraint(offsetFormula);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);}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;}//防止CELL为空public static String parseCell(XSSFCell cell) {if (cell!=null) {CellType cellType = cell.getCellType();String cellValue="";String tmp ="";switch (cellType) {case STRING:cellValue= cell.getStringCellValue();break;case BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());break;case BLANK:cellValue="";break;case NUMERIC:cellValue = cell.toString();if(cellValue.contains("E") && cellValue.contains(".")){
//                        String[] split = cellValue.split("\\.");
//                        cellValue = split[0];double number = Double.parseDouble(cellValue);// 创建 DecimalFormat 对象，设置格式模式为不使用科学计数法DecimalFormat df = new DecimalFormat("0.##########");// 格式化数字String result = df.format(number);cellValue = result;}else{if(cellValue.endsWith(".0")){cellValue = cellValue.replace(".0", "");}}break;case ERROR:break;case FORMULA:tmp=cell.toString();if (tmp.contains(",")) {cellValue = tmp.substring(tmp.indexOf(",")+2,tmp.length()-2);}else {cellValue = tmp;}break;}return cellValue;}else {return "";}}public static Map<Integer, String> parseColumWithReturn() {Map<Integer,String> outmap = new HashMap<Integer,String>();String base="ABCDEFGHIJKLMNOPQRSTUVWXYZ";char[] charArray = base.toCharArray();for (int i = 0; i < 50; i++) {if (i<26) {outmap.put(i+1, String.valueOf(charArray[i]));}else {outmap.put(i+1, "A"+String.valueOf(charArray[i-26]));}}return outmap;}}

2.注意事项

1.基于Excel的话，相当于加入了一个【数据验证】
=OFFSET(待搜索数据源!$A$1,MATCH("*"&A2&"*",待搜索数据源!$A:$A,0)-1,0,COUNTIFS(待搜索数据源!$A:$A,"*"&A2&"*")+1,1)

2.代码中的一些方法说明

（1）主要功能函数

resetDropDownPullerMenuListNoRemoveValidationUpdateFormula(XSSFSheet sheet,String colunmName, int endNum,String indexEnum,String enumSheetName)

参数说明：

①.目标页；

②.目标页第一行表头的列名【自动搜索的数据】；

③.数据页的最后一行；

④.数据页的数据所在的列序号

⑤.数据页的sheet名

(2)辅助函数

①getHeadMap(XSSFSheet sheet) 获取表头序列，返回一个Map<String,Integer>

②parseCell(XSSFCell cell) 将任何类型的Cell对象转换成字符串类型

③parseColumWithReturn() 返回一个Map，做数字和字母的映射。比如Excel中，1对应A，2对应B，3对应C

(3)该代码还有一些关于数据验证删除的功能，能删除旧的数据有效性。参考阅读方法：

resetDropDownPullerMenuListNoRemoveValidationUpdateFormula中关于indexDelete并删除的内容


http://www.ppmy.cn/devtools/164403.html

相关文章

macOS 设置屏幕常亮 不休眠

macOS 设置屏幕常亮 不休眠

Apple M1 Pro macOS Sonoma设置“永不”防止进入休眠 macOS Sonoma 设置“永不” 防止进入休眠
阅读更多...
JAVA最新版本详细安装教程（附安装包）

JAVA最新版本详细安装教程（附安装包）

目录 文章自述 一、JAVA下载 二、JAVA安装 1.首先在D盘创建【java/jdk-23】文件夹 2.把下载的压缩包移动到【jdk-23】文件夹内&#xff0c;右键点击【解压到当前文件夹】 3.如图解压会有【jdk-23.0.1】文件 4.右键桌面此电脑&#xff0c;点击【属性】 5.下滑滚动条&…
阅读更多...
【算法系列】经典的堆排序算法

【算法系列】经典的堆排序算法

文章目录 堆排序算法什么是堆排序&#xff1f;二叉堆的概念 堆排序的基本步骤堆排序的详细流程构建最大堆维护最大堆排序过程Java代码实现 堆排序的图示步骤1. 初始的数组与堆2. 构建最大堆2.1. 检查节点9&#xff08;序号为3&#xff09;2.2. 检查节点6&#xff08;序号为2&am…
阅读更多...
自动驾驶FSD技术的核心算法与软件实现

自动驾驶FSD技术的核心算法与软件实现

引言&#xff1a;FSD技术的定义与发展背景 在当今快速发展的科技领域中&#xff0c;自动驾驶技术已经成为全球关注的焦点之一。其中&#xff0c;“FSD”&#xff08;Full Self-Driving&#xff0c;全自动驾驶&#xff09;代表了这一领域的最高目标——让车辆在无需人类干预的情…
阅读更多...
kafka stream对比flink

kafka stream对比flink

Kafka Streams 和 Apache Flink 虽然都支持实时计算&#xff0c;但它们的定位、架构和适用场景存在显著差异。选择哪一个取决于具体的需求、场景和技术栈。以下是两者的核心区别和适用场景分析&#xff1a; 1. 定位与架构差异 Kafka Streams 定位&#xff1a;轻量级库&#x…
阅读更多...
Java高频笔试题01(含答案及解析)

Java高频笔试题01(含答案及解析)

1.属于面向对象的特征的是( )[多选] A.封装 B.重写 C.重载 D.继承 答案：AD 解析：面向对象的四大特征包括封装、继承、多态和抽象。重写和重载是多态的具体实现方式，但不直接作为特征。 2. ArrayList和LinkedList的哪个是需要动态调整容量的( )[单选] A.都不是 B.都是 C. Lin…
阅读更多...
虚拟机缩放比例问题处理

虚拟机缩放比例问题处理

上班打开虚拟机的样子。 最开始判断可能是vmtools 异常重启安装后发现没有效果 通过 xrandr 功能查询显示器信息获取显示器名 设置显示器 同时设置分辨率 也可以同时设置刷新率 注意下图中设置的关键字
阅读更多...
LeetCode 349. 两个数组的交集 java题解

LeetCode 349. 两个数组的交集 java题解

https://leetcode.cn/problems/intersection-of-two-arrays/description/ 使用哈希集合来解决。先将一个数组的元素存入哈希集合&#xff0c;然后遍历另一个数组&#xff0c;检查元素是否在哈希集合中&#xff0c;如果存在则将其加入结果集合&#xff0c;最后将结果集合转换为数…
阅读更多...
最新文章

Copyright @ 2022~2023