POI实现省市级联（二级下拉框）
- POI级联下拉框
- 直接上代码
- 测试结果
- 参考文章
POI级联下拉框
业务上经常会用到POI做Excel的导出，有时导出需求比较复杂，这里记录一下自己参考网上大神的@水月境的博文完成的一个导出Excel省市级连下拉框需求，其中有些地方花了一段时间才摸清楚，我封装了一下方法，希望能给急用的小伙伴一点帮助。只是简单的修改了一下还有很多瑕疵，有些参数不规范大家可以自己改一下，有问题可以及时沟通。
直接上代码
private final static String[] specialCode= {"@","-",",","，"," ","(",")","（","）","&","#","'",".","/","*"};
/* *
* @Author
* @Description 为excel工作表添加省市级联下拉框
* @Date 11:51 2020/12/10
* @Param [book 工作表, pullDownStartRow 下拉框起始行, pullDownEndRow 下拉框终止行, provinceCol 省份列, areaMap 以省份为key，城市数组为value] 行和列都是以0开始
* @return void
**/
public static void cascade(Workbook book,int pullDownStartRow,int pullDownEndRow,int provinceCol,Map<String,String[]> areaMap,String sheetName) {
// 获取需要用户填写的sheet（数据页，这里暂时为0，不开放，需要的时候再改）
XSSFSheet sheetPro = (XSSFSheet) book.getSheetAt(0);
// 获取省份（key）
String[] provinceArr = areaMap.keySet().toArray(new String[0]);
// 有子集的地址（可拓展为三级级联）
String[] areaFatherNameArr = provinceArr;
//------------------------------------------------------------隐藏sheet开始（不需要修改）------------------------
//创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建，否则无法隐藏。
// sheetName 例子中为area
Sheet hideSheet = book.createSheet(sheetName);
//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
int rowId = 0;
// 设置第一行，存省的信息
Row provinceRow = hideSheet.createRow(rowId++);
provinceRow.createCell(0).setCellValue("一级列表");
for(int i = 0; i < provinceArr.length; i ++){
Cell provinceCell = provinceRow.createCell(i + 1);
provinceCell.setCellValue(provinceArr[i]);
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
for(int i = 0;i < areaFatherNameArr.length;i++){
String key = areaFatherNameArr[i];
String[] son = areaMap.get(key);
Row row = hideSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for(int j = 0; j < son.length; j ++){
Cell cell = row.createCell(j + 1);
cell.setCellValue(son[j]);
}
// 添加名称管理器
String range = getRange(1, rowId, son.length);
Name name = book.createName();
// 解决首字符只能是字母或者下划线问题
/*if(!Character.isLetter(key.charAt(0))){
key = "_"+key;
}*/
// 2024-01更新，这里可以直接替换掉特殊符号
key = getAllowedKey(key);
//key不可重复
name.setNameName(key);
String formula = sheetName+"!" + range;
name.setRefersToFormula(formula);
}
//------------------------------------------------------------隐藏sheet结束（不需要修改）------------------------
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheetPro);
// 省规则
DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(sheetName+"!"+getRange(1,1,areaFatherNameArr.length));
//DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(areaFatherNameArr);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(pullDownStartRow, pullDownEndRow, provinceCol, provinceCol);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
//验证
provinceDataValidation.createErrorBox("error", "请选择正确的值");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheetPro.addValidationData(provinceDataValidation);
//对行设置有效性（endRow需要多加1才对的上省级级联共同的行数）
for(int i = pullDownStartRow+1;i < pullDownEndRow+2;i++){
setDataValidation(transCharByNum(provinceCol) ,sheetPro,i,provinceCol+2);
}
}
/* *
* @Author
* @Description 输入0返回A 输入1返回B
* @Date 11:17 2020/12/10
* @Param [num]
* @return java.lang.String
**/
public static String transCharByNum(Integer num){
return new Character((char)(num.toString().getBytes()[0]+17)).toString();
}
/**
* 设置有效性
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
public static void setDataValidation(String offset,XSSFSheet sheet, int rowNum,int colNum) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
DataValidation data_validation_list;
data_validation_list = getDataValidationByFormula(
"INDIRECT($" + offset +"$"+ (rowNum) + ")", rowNum, colNum,dvHelper);
sheet.addValidationData(data_validation_list);
}
/* *
* @Author
* @Description 定义公式校验
* @Date 12:43 2020/12/10
* @Param [formulaString, naturalRowIndex, naturalColumnIndex, dvHelper]
* @return org.apache.poi.ss.usermodel.DataValidation
**/
private static DataValidation getDataValidationByFormula(
String formulaString, int naturalRowIndex, int naturalColumnIndex,XSSFDataValidationHelper dvHelper) {
// 加载下拉列表内容
// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
//如果A2是江苏省，那么此处就是江苏省下的市信息。
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
int firstRow = naturalRowIndex -1;
int lastRow = naturalRowIndex - 1;
int firstCol = naturalColumnIndex - 1;
int lastCol = naturalColumnIndex - 1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
// 数据有效性对象
// 绑定
XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof XSSFDataValidation) {
data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
// 设置输入错误提示信息
//data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
return data_validation_list;
}
/**
* 计算formula（因为下拉框poi导出用createExplicitListConstraint(arr)最多只能有52个，所以需要更换方法，
* 更换为createFormulaListConstraint(listFormula).传入参数是一个引用，引用某张表的连续数据，所以应该是传入$B$1:$K$1类似的值）
* 此方法为计算传入参数
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*
*/
public static String getRange(int offset, int rowId, int colCount) {
char start = (char)('A' + offset);
// 25和26以及51是因为Excel的列名是从A-Z,然后AA-AZ，然后BA-BZ以此类推，每过26个字母需要做不同的处理，所以这些常数这里不用改
if (colCount <= 25) {
char end = (char)(start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) % 26 == 0) {// 边界值
endSuffix = (char)('A' + 25);
} else {
endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char)('A' + 25);
endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
endPrefix = (char)(endPrefix + (colCount - 25) / 26);
}
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
private static String getAllowedKey(String key){
key = "_"+key;
for (String s : specialCode) {
key = key.replace(s,"_");
}
return key;
}
public static void main(String[] args) {
Map<String,String []> map = new HashMap<>();
map.put("一级字段1",new String[]{"二级字段1.1","二级字段1.2","二级字段1.3"});
map.put("一级字段2",new String[]{"二级字段2.1","二级字段2.2","二级字段2.3","二级字段2.4"});
XSSFWorkbook sheets = new XSSFWorkbook();
XSSFSheet aaa = sheets.createSheet("aaa");
aaa.createRow(0).createCell(0).setCellValue(0);
cascade(sheets,1,100,3,map,"area");
FileOutputStream os = null;
try {
os = new FileOutputStream("D:/testabc.xlsx");
sheets.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}测试结果
2024-01更新-------------------------------------------------------------------------------------------------------------
解决下拉里有特殊符号出现错误的问题（
修改方法：cascade（）的 key = getAllowedKey(key);
）
参考文章
https://stackoverflow.com/questions/39867531/excel-drop-down-using-datavalidationhelper-poi
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
