场景需求
最近在项目中遇到个需求，需要提供一个批量导入的功能，常规做法就是提交excel模版文件，然后就是解析该文件读取数据，这时的模版文件在用户输入方面是没有限制的，用户输入啥就是啥，后台在解析的时候对值进行判断数据是否合法；
在现有的业务场景下，是可以进一步限制用户的输入的，这一次的需求数据互相关联的，我在客户输入的时候让客户只能选择性的输入起关联数据，其他一概不接收；使用级联下拉框来进行限制，只允许用户选择我提前准备好的数据，每一个下拉框选项和ID都是一一对应，用户在sheet1进行选择，sheet收集选项ID，最后上传的时候，只需要去读取sheet2即可；
效果预览
主要实现
核心主要是三个：下拉框，名称管理器，XLOOKUP函数
// 下拉框
public void addDropDownBox(XSSFSheet sheet, String formula, int firstRow, int lastRow, int firstCol, int lastCol) {
XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(3, formula);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation validation = validationHelper.createValidation(constraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
}
// 名称管理器
XSSFName xssfName = workbook.createName();
xssfName.setNameName(grade.getName());
xssfName.setRefersToFormula(formula);
// 单元格写入XLOOKUP函数公式
String formula = "XLOOKUP(sheet1!A2,选项一!A:A,选项一!B:B)&"""
cell.setCellFormula(formula)
// 复制行填充公式
CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
cellCopyPolicy.setCopyCellFormula(true);
int flag = 1;
while (flag < lastRow){
valSheet.copyRows(0,0,flag,cellCopyPolicy);
flag ++;
}
注意！级联选项框需要用到INDIREDT()函数
整体实现思路
- 准备sheet
- 准备主sheet
- 准备实现级联选择框需要用到的隐藏sheet
- 准备数据收集sheet
- 隐藏sheet赋值，期间要将需要的级联相关的数据放进名称管理器
- 主sheet添加下拉框
- 数据收集sheet的单元格添加XLOOPUP公式，复制行填充公式
隐藏sheet格式
名称管理器
