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

目录

目标

例子

1.搜索下拉框页

2.数据源页

3.效果

代码以及注意事项

1.代码

2.注意事项

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

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

---

目标

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

例子

1.搜索下拉框页

2.数据源页

3.效果

代码以及注意事项

1.代码

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并删除的内容