原理
- 同样是将数据源放到一个新建的隐藏的sheet中，第一行是第一个列表的数据，第二行是每一个有下级菜单的菜单，他下面的行就是他下级菜单的每一值
- 使用MATCH函数从第二行找到上级菜单对应的列
- 根据OFFSET函数从2中获取的列，取得下级菜单值列表
这样就解决了上一篇中的所有缺点
代码
public class CascadeWriteHandler implements SheetWriteHandler {
private final List<CascadeCellBO> cascadeCellList;
private final Map<List<NameCascadeBO>, CellDataSourceBO> dataSourceCache;
public CascadeWriteHandler(List<CascadeCellBO> cascadeCellList) {
this.cascadeCellList = cascadeCellList;
this.dataSourceCache = new HashMap<>();
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//获取工作簿
Sheet sheet = writeSheetHolder.getSheet();
Workbook book = writeWorkbookHolder.getWorkbook();
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
cascadeCellList.stream().filter(c -> c.getMaxLevel() > 0).forEach(cascadeCellBO -> {
int maxLevel = cascadeCellBO.getMaxLevel();
int colIndex = cascadeCellBO.getColIndex();
int firstRowIndex = cascadeCellBO.getRowIndex();
int lastRowIndex = firstRowIndex + cascadeCellBO.getRowNum();
List<NameCascadeBO> nameCascadeList = cascadeCellBO.getNameCascadeList();
//如果大类都没有，就渲染maxLevel个空的下拉列表
if (nameCascadeList == null || nameCascadeList.isEmpty()) {
DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(new String[]{""});
CellRangeAddressList expRangeAddressList = new CellRangeAddressList(firstRowIndex, lastRowIndex, colIndex, colIndex + maxLevel - 1);
setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
} else {
CellDataSourceBO cellDataSourceBO = buildOrGetDataSource(book, nameCascadeList);
// 大类规则
String dataSourceName = cellDataSourceBO.getName();
int maxSelectRow = cellDataSourceBO.getMaxSelectRow();
String selectMaxColStr = cellDataSourceBO.getSelectMaxColStr();
//开始设置大类下拉框
String bigEndCol = colIndex2Str(nameCascadeList.size());
CellRangeAddressList expRangeAddressList = new CellRangeAddressList(firstRowIndex, lastRowIndex, colIndex, colIndex);
DataValidationConstraint bigFormula = dvHelper.createFormulaListConstraint("=" + dataSourceName + "!$A$1:$" + bigEndCol + "$1");
setValidation(sheet, dvHelper, bigFormula, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
// 开始设置小类下拉框小类规则(各单元格按个设置)
// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。
// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联
for (int num = 1; num < maxLevel; num++) {
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
int curRow = i + 1;
int curCol = colIndex + num;
String searchKey = IntStream.range(0, num)
.mapToObj(a -> colIndex2Str(colIndex + a + 1) + curRow)
.collect(Collectors.joining(",\"###\","));
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i, i, curCol, curCol);
//获取子菜单的个数
String rowNum = "COUNTA(OFFSET(" + dataSourceName + "!$A$3" +
",0" +
",MATCH(CONCATENATE(" + searchKey + ")," + dataSourceName + "!A2:" + selectMaxColStr + "2,0)-1" +
"," + (maxSelectRow - 1) +
",1))";
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("=OFFSET(" + dataSourceName + "!$A$3" +
",0" +
",MATCH(CONCATENATE(" + searchKey + ")," + dataSourceName + "!A2:" + selectMaxColStr + "2,0)-1" +
"," + rowNum +
",1)");
setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
}
}
}
});
}
private CellDataSourceBO buildOrGetDataSource(Workbook book, List<NameCascadeBO> nameCascadeList) {
//如果选项和之前的一样，则用之前的数据源否则新建一个
return dataSourceCache.computeIfAbsent(nameCascadeList, k1 -> {
//创建一个专门用来存放地区信息的隐藏sheet页
//因此不能在现实页之前创建，否则无法隐藏。
String dataSourceName = "dataSource" + System.currentTimeMillis();
Sheet hideSheet = book.createSheet(dataSourceName);
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
// 将具体的数据写入到每一行中，第一行是最外层菜单
// 第二行是有子菜单的菜单名（会和他所有父菜单进行拼接，用###分割开，防止重名）
// 下面行是这个菜单的子菜单列表。
// 设置大类数据源
Row row = hideSheet.createRow(0);
IntStream.range(0, nameCascadeList.size()).forEach(i ->
row.createCell(i).setCellValue(nameCascadeList.get(i).getName()));
//设置小类数据源
AtomicInteger selectColId = new AtomicInteger();
Map<Integer, Map<Integer, String>> cell2SetValueMap = new TreeMap<>();
buildSelectData(cell2SetValueMap, null, nameCascadeList, selectColId);
cell2SetValueMap.forEach((setRowIndex, colMap) -> {
Row setRow = hideSheet.createRow(setRowIndex);
colMap.forEach((setColIndex, value) -> setRow.createCell(setColIndex).setCellValue(value));
});
CellDataSourceBO cellDataSourceBO = new CellDataSourceBO();
cellDataSourceBO.setMaxSelectRow(cell2SetValueMap.size());
cellDataSourceBO.setSelectMaxColStr(colIndex2Str(selectColId.get()));
cellDataSourceBO.setName(dataSourceName);
return cellDataSourceBO;
});
}
private void buildSelectData(Map<Integer, Map<Integer, String>> cell2SetValueMap, String preName, List<NameCascadeBO> nameCascadeList, AtomicInteger colId) {
Optional.ofNullable(nameCascadeList).ifPresent(l -> l.forEach(nameCascadeBO -> {
List<NameCascadeBO> childList = nameCascadeBO.getNameCascadeList();
if (childList != null && !childList.isEmpty()) {
int curCol = colId.getAndIncrement();
String name = Optional.ofNullable(preName).map(p -> p + "###"
+ nameCascadeBO.getName()).orElse(nameCascadeBO.getName());
cell2SetValueMap.computeIfAbsent(1, k1 -> new HashMap<>()).put(curCol, name);
IntStream.range(0, childList.size()).forEach(r ->
cell2SetValueMap.computeIfAbsent(2 + r
, k1 -> new HashMap<>()).put(curCol, childList.get(r).getName()));
buildSelectData(cell2SetValueMap, name, childList, colId);
}
}));
}
public static int getMaxLevel(List<NameCascadeBO> nameCascadeList, int preLevel) {
int curLevel = preLevel + 1;
int maxLevel = curLevel;
for (NameCascadeBO nameCascadeBO : nameCascadeList) {
List<NameCascadeBO> childList = nameCascadeBO.getNameCascadeList();
if (childList != null && !childList.isEmpty()) {
int level = getMaxLevel(childList, curLevel);
maxLevel = Math.max(level, maxLevel);
}
}
return maxLevel;
}
/**
* 设置验证规则
*
* @param sheet sheet对象
* @param helper 验证助手
* @param constraint createExplicitListConstraint
* @param addressList 验证位置对象
* @param msgHead 错误提示头
* @param msgContext 错误提示内容
*/
private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox(msgHead, msgContext);
sheet.addValidationData(dataValidation);
}
public static String colIndex2Str(int column) {
if (column <= 0) {
return null;
}
String columnStr = "";
column--;
do {
if (columnStr.length() > 0) {
column--;
}
columnStr = ((char) (column % 26 + (int) 'A')) + columnStr;
column = (int) ((column - column % 26) / 26);
} while (column > 0);
return columnStr;
}
}使用
public static void main(String[] args) {
List<List<String>> header = new ArrayList<>();
header.add(Arrays.asList("sc2"));
header.add(Arrays.asList("sc3"));
int colIndex = header.size() - 1;
List<NameCascadeBO> nameCascadeList = new ArrayList<>();
NameCascadeBO nameCascadeBO = new NameCascadeBO();
nameCascadeBO.setName("第一层1");
List<NameCascadeBO> nameCascadeList2 = new ArrayList<>();
NameCascadeBO nameCascadeBO2 = new NameCascadeBO();
nameCascadeBO2.setName("第二层(相同)");
List<NameCascadeBO> nameCascadeList3 = new ArrayList<>();
IntStream.range(0, 400).forEach(i -> {
NameCascadeBO nameCascadeBO3 = new NameCascadeBO();
nameCascadeBO3.setName("第三层11" + i);
nameCascadeList3.add(nameCascadeBO3);
});
nameCascadeBO2.setNameCascadeList(nameCascadeList3);
nameCascadeList2.add(nameCascadeBO2);
nameCascadeBO2 = new NameCascadeBO();
nameCascadeBO2.setName("第二层2");
nameCascadeList2.add(nameCascadeBO2);
nameCascadeBO.setNameCascadeList(nameCascadeList2);
nameCascadeList.add(nameCascadeBO);
nameCascadeBO = new NameCascadeBO();
nameCascadeBO.setName("第一层2");
nameCascadeList2 = new ArrayList<>();
nameCascadeBO2 = new NameCascadeBO();
nameCascadeBO2.setName("第二层21");
nameCascadeList2.add(nameCascadeBO2);
nameCascadeBO2 = new NameCascadeBO();
nameCascadeBO2.setName("第二层(相同)");
nameCascadeBO2.setNameCascadeList(Collections.singletonList(new NameCascadeBO("第三层222")));
nameCascadeList2.add(nameCascadeBO2);
nameCascadeBO.setNameCascadeList(nameCascadeList2);
nameCascadeList.add(nameCascadeBO);
IntStream.range(2, 200).forEach(i -> {
NameCascadeBO item = new NameCascadeBO();
item.setName("第一层" + i);
nameCascadeList.add(item);
});
CascadeCellBO cascadeCellBO = new CascadeCellBO();
cascadeCellBO.setRowIndex(2);
cascadeCellBO.setRowNum(10);
cascadeCellBO.setMaxLevel(3);
cascadeCellBO.setColIndex(colIndex);
cascadeCellBO.setNameCascadeList(nameCascadeList);
CascadeWriteHandler cascadeWriteHandler = new CascadeWriteHandler(Collections.singletonList(cascadeCellBO));
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcelFactory.write(outputStream).head(header)
.registerWriteHandler(cascadeWriteHandler)
.sheet("导入信息").doWrite(new ArrayList<>());
FileUtils.save2File("/Users/admin/aa/导入模板ss.xlsx", outputStream.toByteArray());
}
