目录
目标
期望在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 colunm
if(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 table
sheet.getCTWorksheet().setDataValidations(dataValidationsCurrent);
//add new validation
sheet.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并删除的内容
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
