poi实现省市区3级联动限制Excel数据有效性

数据文件mine.json

[{"childList": [{"childList": [{"childList": [{"childList": [],"cityName": "海淀区","id": 6902,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "东城区","id": 6903,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "房山区","id": 6904,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "丰台区","id": 6905,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "怀柔区","id": 6906,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "延庆区","id": 6907,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "西城区","id": 6908,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "顺义区","id": 6909,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "昌平区","id": 6910,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "大兴区","id": 6911,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "平谷区","id": 6912,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "通州区","id": 6913,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "门头沟区","id": 6914,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "石景山区","id": 6915,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "密云区","id": 6916,"pid": 6901,"type": "3"}, {"childList": [],"cityName": "朝阳区","id": 6917,"pid": 6901,"type": "3"}],"cityName": "北京市","id": 6901,"pid": 6900,"type": "2"}],"cityName": "北京市","id": 6900,"pid": 6899,"type": "1"}],"cityName": "中国","id": 6899,"pid": 0,"type": "0"
}]

代码：

 public static void Cascade() throws IOException {/**mine.json存放地区信息*/String fileName = "D:\\poi\\mine.json";//原始json对象JSONArray originLocationNameJsonArray = JSONUtil.readJSONArray(new File(fileName), Charset.defaultCharset());//中国JSONObject china = (JSONObject) originLocationNameJsonArray.get(0);//省列表JSONArray provinceJSONArray = china.getJSONArray("childList");//省市Map key是省名字 value为省下面的市HashMap> provinceCityMap = new HashMap<>(provinceJSONArray.size());//市区Map key为市名字 value 为市下面的区名字HashMap> cityDistrictMap = new HashMap<>();//省List 遍历ArrayList provinceList = Lists.newArrayList();//落地省for (Object p : provinceJSONArray) {JSONObject province = (JSONObject) p;//获取省下面的市集合JSONArray cityJsonArray = province.getJSONArray("childList");//存放省下面的市的listArrayList cityList = Lists.newArrayList();//市for (Object c : cityJsonArray) {JSONObject city = (JSONObject) c;cityList.add(city.getStr("cityName"));JSONArray districtJsonArray = city.getJSONArray("childList");//区List集合ArrayList districtList = Lists.newArrayList();//区县名字获取for (Object d : districtJsonArray) {JSONObject district = (JSONObject) d;districtList.add(district.getStr("cityName"));}cityDistrictMap.put(city.getStr("cityName"), districtList);}//名称管理器中每个名称都是唯一的，所以处理下直辖市的名称String provinceName = province.getStr("cityName");switch (provinceName) {case "北京市":provinceName = "北京";break;case "天津市":provinceName = "天津";break;case "上海市":provinceName = "上海";break;case "重庆市":provinceName = "重庆";break;}provinceCityMap.put(provinceName, cityList);provinceList.add(provinceName);}/*** 模板所在位置*/String path = "D:\\poi\\ttl.xlsx";//读取模版FileInputStream fileInputStream = new FileInputStream(path);Workbook book = new XSSFWorkbook(fileInputStream);//获取第一个sheet页XSSFSheet sheet = (XSSFSheet) book.getSheet("sheet1");//创建一个专门用来存放地区信息的隐藏sheet页不能在现实页sheet1之前创建,sheet页名字为provinceCityDistrictSheet hideProvinceCityDistrictSheet = book.createSheet("provinceCityDistrict");//这一行作用是将sheet隐藏book.setSheetHidden(book.getSheetIndex(hideProvinceCityDistrictSheet), true);int rowId = 0;//设置第一行，存省的信息Row provinceRow = hideProvinceCityDistrictSheet.createRow(rowId++);provinceRow.createCell(0).setCellValue("省列表");for (int i = 0; i < provinceList.size(); i++) {Cell provinceCell = provinceRow.createCell(i + 1);provinceCell.setCellValue(provinceList.get(i));}// 添加名称管理器 $B$1:$K$1String provinceRange = getRange(1, rowId, provinceList.size());Name provinceName = book.createName();//key不可重复provinceName.setNameName("省列表");//设置定义名称以引用的公式。例：provinceCityDistrict!$B$1:$K$1String provinceFormula = "provinceCityDistrict!" + provinceRange;provinceName.setRefersToFormula(provinceFormula);// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。//先填写省市Set>> provinceCityEntries = provinceCityMap.entrySet();for (Map.Entry> provinceCityEntry : provinceCityEntries) {String provinceKey = provinceCityEntry.getKey();ArrayList cityList = provinceCityEntry.getValue();Row row = hideProvinceCityDistrictSheet.createRow(rowId++);row.createCell(0).setCellValue(provinceKey);for (int j = 0; j < cityList.size(); j++) {Cell cell = row.createCell(j + 1);cell.setCellValue(cityList.get(j));}// 添加名称管理器String range = getRange(1, rowId, cityList.size());Name name = book.createName();//key不可重复name.setNameName(provinceKey);String formula = "provinceCityDistrict!" + range;name.setRefersToFormula(formula);}//再填写市区Set>> cityDistrictEntries = cityDistrictMap.entrySet();for (Map.Entry> cityDistrictEntry : cityDistrictEntries) {String cityKey = cityDistrictEntry.getKey();ArrayList districtList = cityDistrictEntry.getValue();Row row = hideProvinceCityDistrictSheet.createRow(rowId++);row.createCell(0).setCellValue(cityKey);for (int j = 0; j < districtList.size(); j++) {Cell cell = row.createCell(j + 1);cell.setCellValue(districtList.get(j));}// 添加名称管理器String range = getRange(1, rowId, districtList.size());Name name = book.createName();//key不可重复name.setNameName(cityKey);String formula = "provinceCityDistrict!" + range;name.setRefersToFormula(formula);}String[] provinceArr = provinceList.toArray(new String[provinceList.size()]);//设置当前表格的数据校验有效性helperXSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);// 省规则DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provinceArr);// 四个参数分别是：起始行、终止行、起始列、终止列/*** "5"为区所在列的第几列，从0开始计数*/CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20000, 5, 5);DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);//验证provinceDataValidation.createErrorBox("error", "请选择正确的省份");provinceDataValidation.setShowErrorBox(true);provinceDataValidation.setSuppressDropDownArrow(true);sheet.addValidationData(provinceDataValidation);//对前2w行设置有效性for (int i = 2; i < 20000; i++) {/*** "F"为省份所在列，"7"为城市所在列的第几列+1(实际使用时会-1)，从0开始*/setDataValidation("F", sheet, i, 7);/*** "G"为城市所在列，"8"为区所在列的第几列+1(实际使用时会-1)，从0开始*/setDataValidation("G", sheet, i, 8);}FileOutputStream os = null;try {/*** 文件输出地址，使用时可能需要更改*/os = new FileOutputStream("D:\\poi\\bbb.xlsx");book.write(os);} catch (Exception e) {e.printStackTrace();} finally {IOUtils.closeQuietly(os);}System.out.println("-------------->");}/*** 设置有效性** @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);}/*** 加载下拉列表内容** @param formulaString      函数* @param naturalRowIndex    行数* @param naturalColumnIndex 列数* @param dvHelper* @return*/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** @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);if (colCount <= 25) {char end = (char) (start + colCount - 1);return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;} else {char endPrefix = 'A';char endSuffix = 'A';// 26-51之间，包括边界（仅两次字母表计算）if ((colCount - 25) / 26 == 0 || colCount == 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;}}

最终效果：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！