发生情景:
最近使用到了模板导出功能，其中有两个下拉框是有关联的，一开始做完下拉之后，对自己做的并不满意，就考虑实现级联下拉的功能，通过第一个选项的值来决定下一个下拉框的选项范围；于是就有了这一篇文章，话不多说，贴代码~！
代码：
- maven引入POI的包，注意两个包的版本需一致，不然会报错。
org.apache.poi poi 3.10-FINAL org.apache.poi poi-ooxml 3.10-FINAL
- 在你的Excel工具类中添加下面方法
public static void setSeconCascadeDropDownBox(XSSFWorkbook wb, String typeName, String hiddenSheetName, Map values, Integer fatherCol, Integer sonCol) {
//获取所有sheet页个数
int sheetTotal = wb.getNumberOfSheets();
//处理下拉数据
if (values != null && values.size() != 0) {
//新建一个sheet页
XSSFSheet hiddenSheet = wb.getSheet(hiddenSheetName);
if (hiddenSheet == null) {
hiddenSheet = wb.createSheet(hiddenSheetName);
sheetTotal++;
}
// 获取数据起始行
int startRowNum = hiddenSheet.getLastRowNum() + 1;
int endRowNum = startRowNum;
Set keySet = values.keySet();
for (String key : keySet) {
XSSFRow fRow = hiddenSheet.createRow(endRowNum++);
fRow.createCell(0).setCellValue(key);
String[] sons = values.get(key);
for (int i = 1; i <= sons.length; i++) {
fRow.createCell(i).setCellValue(sons[i - 1]);
}
// 添加名称管理器
String range = getRange(1, endRowNum, sons.length);
Name name = wb.createName();
//key不可重复
name.setNameName(key);
String formula = hiddenSheetName + "!" + range;
name.setRefersToFormula(formula);
}
//将数据字典sheet页隐藏掉
wb.setSheetHidden(sheetTotal - 1, true);
// 设置父级下拉
//获取新sheet页内容
String mainFormula = hiddenSheetName + "!$A$" + ++startRowNum + ":$A$" + endRowNum;
XSSFSheet mainSheet = wb.getSheet(typeName);
// 设置下拉列表值绑定到主sheet页具体哪个单元格起作用
mainSheet.addValidationData(SetDataValidation(wb, mainFormula, 1, 65535, fatherCol, fatherCol));
// 设置子级下拉
// 当前列为子级下拉框的内容受父级哪一列的影响
String indirectFormula = "INDIRECT($" + decimalToTwentyHex(fatherCol + 1) + "2)"; //=INDIRECT($C2)
mainSheet.addValidationData(SetDataValidation(wb, indirectFormula, 1, 65535, sonCol, sonCol));
}
}
public static String getRange(int offset, int rowId, int colCount) {
char start = (char) ('A' + offset);
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;
}
}
public static DataValidation SetDataValidation(Workbook wb, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) wb.getSheet("typelist"));
DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
return dvHelper.createValidation(formulaListConstraint, regions);
}
public static String decimalToTwentyHex(int decimalNum) {
StringBuilder result = new StringBuilder();
while(decimalNum > 0) {
int remainder = decimalNum % 26;
result.append((char)(remainder + 64));//大写A的ASCII码值为65
decimalNum = decimalNum/26;
}
return result.reverse().toString();
}
- 如何调用
以上POI对设置级联下拉选项的工具方法，希望对大家有所帮助！
