JAVA动态实现Excel三级级联菜单
如何实现如下图所示的Excel三级级联菜单的效果呢？
.1 Excel的关键点
.1.1 名称管理器
Excel内置了名称管理器、可以存储类似Java的键值对
如图，名称管理器里面，名称和数值就是键、值的关系，一个省份对应若干地市，一个地市对应若干区域，这不正是实现级联的基础吗？
请注意下方的引用位置里面的表达式：
=市区!$B$249:$B$262
这里如何理解呢？
市区!表示数据要从这个sheet里面去找
$B$249:$B$262表示从B列第249行的单元格到B列第262行的单元格
如下图：
.1.2 数据有效性
既然实现级联菜单，我们需要控制每个Excel下拉框的数据；在Excel里面，这是通过数据有效性来控制的
接下来，就是如何设置数据有效性了
.1.2.1 省份的数据有效性
省份是第一个下拉框，我们直接使用静态数据，把数据有效性的范围指定为省份sheet的数据
.1.2.2 地市的数据有效性
地市的数据有效性，需要引用名称管理器里面的数据；为什么呢，因为省份动态选择后，我们需要找到相应省份下属的地市，只有这些地市才应该纳入到选择的范围。
例如，选择了山西省，则地市应该全部是山西下属的地市。
我们需要让地市的范围指向刚才的名称管理器里的对应项，我们把这个名称管理器里面的项目的名字和省份的名字设置为一致，就能建立关联
再回到名称管理器，我们看到有一个叫山西省的项目：
接下来，我们让地市下拉框的数据有效性指向它：
如图，通过INDIRECT这个函数，指向了名称管理器，=INDIRECT($M$2)。这样，地市下拉框的数据范围就是对应名称管理器里面的数据，也就实现了地市的级联下拉；$M$2代表省份选择的单元格，这个单元格我们让它为动态的，就能实现每一行都是级联的，这个我们后面通过java来实现。
.1.2.3 区县的数据有效性
区域的数据有效性原理同地市，不过由于全国范围内的地市可能重名，所以我们的名称管理器是按XX省XX市这样来建立的，对应的数据有效性也是这样设置的：
=INDIRECT(CONCATENATE($M$2,$N$2))，这里把上级的省份和地市拼接到一起，再去关联名称管理器：
如图，对应的名称管理器选项就是山西省长治市
.2 Java创建名称管理器
我们用Apache的POI来操作Excel，简单点，先上代码：
/**
* 创建名称
* @param wb
* @param name
* @param expression
* @return
*/
public static HSSFName createName(HSSFWorkbook wb, String name, String expression){
HSSFName refer = wb.createName();
refer.setRefersToFormula(expression);
refer.setNameName(name);
return refer;
}
Wb就是工作表，name就是你要新建的名称管理器里面的名称，expression是一个表达式，也就是前面提过的引用位置，例如=市区!$B$249:$B$262；
首先我们得有市区这个sheet，这个sheet我们可以手动建立，我这里是从mysql中查询数据后，直接粘贴上去的；当然也可以动态用java去建立
有了这个sheet后，接下来就是表达式的后面部分：$B$249:$B$262
这里的关键是定位到这个范围，是sheet里面的249行到262行；这里，我们让数据库的行和Excel的行完全对应起来；用sql查询出来是多少行，我们就让它在excel是多少行，这样就简化了问题
List<DistrictResp> provinceList = locationService.selectProvinces();
//省市映射
List<Map<String,Object>>provinceAndCitiesList = locationService.getAllProvincesAndCities();
//市区映射
List<Map<String,Object>>citiesAndAreas = locationService.getAllCitiesAndAreas();
for(DistrictResp province : provinceList){
//为地市下拉框创建名称(地市引用上级省份）
ExcelUtil.createName(wb, province.getName(), "省市!"+findRange(provinceAndCitiesList,"B",province.getId()));
List<DistrictResp> cityList = locationService.selectCities(province.getId());
for(DistrictResp city : cityList){
//为区县下拉框创建名称，为防止地市名字重复加上省份名字（区县引用上级地市）
ExcelUtil.createName(wb, province.getName()+city.getName(), "市区!"+findRange(citiesAndAreas,"B",city.getId()));
}
}
private String findRange(List<Map<String,Object>> list,String excelColumn,String locationId){
int firstRow = 1;
int lastRow = 1;
int rownum = 1;
boolean finded = false;
for(Map<String,Object> map : list){
Integer parentId = (Integer)map.get("parentId");
if(parentId.toString().equals(locationId)){
if(!finded){
firstRow = rownum;
lastRow = rownum;
finded = true;
}else{
lastRow++;
}
}else if(finded){
break;
}
rownum ++;
}
return "$"+excelColumn+"$"+firstRow+":"+"$"+excelColumn+"$"+lastRow;
}
.3 Java动态设置数据有效性
* 获取数据有效性对象
* @paramname
* @paramfirstRow
* @paramendRow
* @paramfirstCol
* @paramendCol
* @return
*/
public static HSSFDataValidation getDataValidation(String name,int firstRow, int endRow, int firstCol, int endCol,String errorMsg){
//加载下拉列表内容
DVConstraint constraint = DVConstraint.createFormulaListConstraint(name);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList((short)firstRow, (short)endRow,(short)firstCol,(short)endCol);
// 数据有效性对象
HSSFDataValidationvalidation = new HSSFDataValidation(regions, constraint);
if(!StringUtil.isEmpty(errorMsg))validation.createErrorBox("error", errorMsg);
return validation;
}
for(int i=1;i<2000;i++){
//为地市下拉框设置数据有效性
HSSFDataValidation cityValicity = ExcelUtil.getDataValidation("INDIRECT($M$"+(i+1)+")", i, i, 13, 13,cityErrMsg);
sheet1.addValidationData(cityValicity);
//为区县下拉框设置数据有效性
HSSFDataValidation areaValicity = ExcelUtil.getDataValidation("INDIRECT(CONCATENATE($M$"+(i+1)+",$N$"+(i+1)+"))", i, i, 14, 14,areaErrMsg);
sheet1.addValidationData(areaValicity);
}
2000行足够了，我们这里给Excel的前面2000行设置了数据有效性