数据文件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<String, ArrayList<String>> provinceCityMap = new HashMap<>(provinceJSONArray.size());
//市区Map key为市名字 value 为市下面的区名字
HashMap<String, ArrayList<String>> cityDistrictMap = new HashMap<>();
//省List 遍历
ArrayList<String> provinceList = Lists.newArrayList();
//落地省
for (Object p : provinceJSONArray) {
JSONObject province = (JSONObject) p;
//获取省下面的市集合
JSONArray cityJsonArray = province.getJSONArray("childList");
//存放省下面的市的list
ArrayList 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页名字为provinceCityDistrict
Sheet 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$1
String provinceRange = getRange(1, rowId, provinceList.size());
Name provinceName = book.createName();
//key不可重复
provinceName.setNameName("省列表");
//设置定义名称以引用的公式。例：provinceCityDistrict!$B$1:$K$1
String provinceFormula = "provinceCityDistrict!" + provinceRange;
provinceName.setRefersToFormula(provinceFormula);
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
//先填写省市
Set<Map.Entry<String, ArrayList<String>>> provinceCityEntries = provinceCityMap.entrySet();
for (Map.Entry<String, ArrayList<String>> provinceCityEntry : provinceCityEntries) {
String provinceKey = provinceCityEntry.getKey();
ArrayList<String> 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<Map.Entry<String, ArrayList<String>>> cityDistrictEntries = cityDistrictMap.entrySet();
for (Map.Entry<String, ArrayList<String>> cityDistrictEntry : cityDistrictEntries) {
String cityKey = cityDistrictEntry.getKey();
ArrayList<String> 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()]);
//设置当前表格的数据校验有效性helper
XSSFDataValidationHelper 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;
}
}
最终效果：
?