easyexcel==省市区三级联动

省市区三级联动，不选前面的就没法选后面的

package com.example.demoeasyexcel.jilian2;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.write.handler.SheetWriteHandler;import java.util.List;
import java.util.Map;public class CascadeWriteHandler2 implements SheetWriteHandler {private List<String> provinceList; // 省份列表private Map<String, List<String>> cityMap; // 省份与城市的映射private Map<String, List<String>> districtMap; // 城市与区的映射public CascadeWriteHandler2(List<String> provinceList, Map<String, List<String>> cityMap, Map<String, List<String>> districtMap) {this.provinceList = provinceList;this.cityMap = cityMap;this.districtMap = districtMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 此处代码在创建Sheet前，通常为空}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet sheet = writeSheetHolder.getSheet();// 创建一个隐藏的Sheet存放省市区数据Sheet hideSheet = workbook.createSheet("siteInfo");workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);// 设置省份数据int rowNum = 0;Row provinceRow = hideSheet.createRow(rowNum++);provinceRow.createCell(0).setCellValue("省份列表");for (int i = 0; i < provinceList.size(); i++) {provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));}// 设置城市数据for (String province : provinceList) {List<String> cities = cityMap.get(province);Row cityRow = hideSheet.createRow(rowNum++);cityRow.createCell(0).setCellValue(province);for (int i = 0; i < cities.size(); i++) {cityRow.createCell(i + 1).setCellValue(cities.get(i));}Name cityRangeName = workbook.createName();cityRangeName.setNameName(province);String cityRange = getRange(1, rowNum, cities.size());cityRangeName.setRefersToFormula("siteInfo!" + cityRange);}// 设置区数据for (String city : cityMap.values().stream().flatMap(List::stream).toArray(String[]::new)) {List<String> districts = districtMap.get(city);Row districtRow = hideSheet.createRow(rowNum++);districtRow.createCell(0).setCellValue(city);for (int i = 0; i < districts.size(); i++) {districtRow.createCell(i + 1).setCellValue(districts.get(i));}Name districtRangeName = workbook.createName();districtRangeName.setNameName(city);String districtRange = getRange(1, rowNum, districts.size());districtRangeName.setRefersToFormula("siteInfo!" + districtRange);}// 设置数据验证DataValidationHelper dvHelper = sheet.getDataValidationHelper();DataValidationConstraint provinceConstraint = dvHelper.createExplicitListConstraint(provinceList.toArray(new String[0]));CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 999, 0, 0);setValidation(sheet, dvHelper, provinceConstraint, provinceRangeAddressList);// 城市数据验证for (int i = 2; i < 1000; i++) {DataValidationConstraint cityConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$" + i + ")");CellRangeAddressList cityRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 1, 1);setValidation(sheet, dvHelper, cityConstraint, cityRangeAddressList);}// 区数据验证for (int i = 2; i < 1000; i++) {DataValidationConstraint districtConstraint = dvHelper.createFormulaListConstraint("INDIRECT($B$" + i + ")");CellRangeAddressList districtRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 2, 2);setValidation(sheet, dvHelper, districtConstraint, districtRangeAddressList);}}private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {DataValidation validation = helper.createValidation(constraint, addressList);validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);sheet.addValidationData(validation);}public 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';if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-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;}}
}

package com.example.demoeasyexcel.jilian2;import com.alibaba.excel.EasyExcel;
import com.example.demoeasyexcel.jilian.CascadeVO;import java.io.File;
import java.util.*;public class Test22 {public static void main(String[] args) {List<CascadeVO> dataList = new ArrayList<>();/// 准备省市aqu测试数据List<String> provinceList = Arrays.asList("apro1", "apro2", "apro3");Map<String, List<String>> cityMap = new HashMap<>();cityMap.put("apro1", Arrays.asList("acity11", "acity12"));cityMap.put("apro2", Arrays.asList("acity21", "acity22"));cityMap.put("apro3", Arrays.asList("acity31", "acity32"));Map<String, List<String>> districtMap = new HashMap<>();districtMap.put("acity11", Arrays.asList("aqu111", "aqu112"));districtMap.put("acity12", Arrays.asList("aqu121", "aqu122"));districtMap.put("acity21", Arrays.asList("aqu211", "aq212"));districtMap.put("acity22", Arrays.asList("aqu221", "aqu222"));districtMap.put("acity31", Arrays.asList("aqu311", "aqu312"));districtMap.put("acity32", Arrays.asList("aqu321", "aqu322"));// 创建写入的SheetFile file = new File("D:\\data\\test.xlsx");EasyExcel.write(file, CascadeVO.class).sheet("sheet1").registerWriteHandler(new CascadeWriteHandler2(provinceList, cityMap, districtMap)).doWrite(dataList);}
}

再添加第四列，第四列的下拉不受到级联的影响

package com.example.demoeasyexcel.jilian3;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.write.handler.SheetWriteHandler;import java.util.List;
import java.util.Map;public class CascadeWriteHandler3 implements SheetWriteHandler {private List<String> provinceList; // 省份列表private Map<String, List<String>> cityMap; // 省份与城市的映射private Map<String, List<String>> districtMap; // 城市与区的映射private String[] contactTypes = new String[]{"手机", "座机", "呼机"}; //public CascadeWriteHandler3(List<String> provinceList, Map<String, List<String>> cityMap, Map<String, List<String>> districtMap) {this.provinceList = provinceList;this.cityMap = cityMap;this.districtMap = districtMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 此处代码在创建Sheet前，通常为空}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet sheet = writeSheetHolder.getSheet();// 创建一个隐藏的Sheet存放省市区数据Sheet hideSheet = workbook.createSheet("siteInfo");workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);// 设置省份数据int rowNum = 0;Row provinceRow = hideSheet.createRow(rowNum++);provinceRow.createCell(0).setCellValue("省份列表");for (int i = 0; i < provinceList.size(); i++) {provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));}// 设置城市数据for (String province : provinceList) {List<String> cities = cityMap.get(province);Row cityRow = hideSheet.createRow(rowNum++);cityRow.createCell(0).setCellValue(province);for (int i = 0; i < cities.size(); i++) {cityRow.createCell(i + 1).setCellValue(cities.get(i));}Name cityRangeName = workbook.createName();cityRangeName.setNameName(province);String cityRange = getRange(1, rowNum, cities.size());cityRangeName.setRefersToFormula("siteInfo!" + cityRange);}// 设置区数据for (String city : cityMap.values().stream().flatMap(List::stream).toArray(String[]::new)) {List<String> districts = districtMap.get(city);Row districtRow = hideSheet.createRow(rowNum++);districtRow.createCell(0).setCellValue(city);for (int i = 0; i < districts.size(); i++) {districtRow.createCell(i + 1).setCellValue(districts.get(i));}Name districtRangeName = workbook.createName();districtRangeName.setNameName(city);String districtRange = getRange(1, rowNum, districts.size());districtRangeName.setRefersToFormula("siteInfo!" + districtRange);}// 设置数据验证DataValidationHelper dvHelper = sheet.getDataValidationHelper();DataValidationConstraint provinceConstraint = dvHelper.createExplicitListConstraint(provinceList.toArray(new String[0]));CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 999, 0, 0);setValidation(sheet, dvHelper, provinceConstraint, provinceRangeAddressList);// 城市数据验证for (int i = 2; i < 1000; i++) {DataValidationConstraint cityConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$" + i + ")");CellRangeAddressList cityRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 1, 1);setValidation(sheet, dvHelper, cityConstraint, cityRangeAddressList);}// 区数据验证for (int i = 2; i < 1000; i++) {DataValidationConstraint districtConstraint = dvHelper.createFormulaListConstraint("INDIRECT($B$" + i + ")");CellRangeAddressList districtRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 2, 2);setValidation(sheet, dvHelper, districtConstraint, districtRangeAddressList);}// 新增联系方式的数据验证DataValidationConstraint contactTypeConstraint = dvHelper.createExplicitListConstraint(contactTypes);CellRangeAddressList contactTypeRangeAddressList = new CellRangeAddressList(2, 999, 3, 3);setValidation(sheet, dvHelper, contactTypeConstraint, contactTypeRangeAddressList);}private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {DataValidation validation = helper.createValidation(constraint, addressList);validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);sheet.addValidationData(validation);}public 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';if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-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;}}
}

再添加个表头

package com.example.demoeasyexcel.jilian3;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.write.handler.SheetWriteHandler;import java.util.List;
import java.util.Map;public class CascadeWriteHandler3 implements SheetWriteHandler {private List<String> provinceList; // 省份列表private Map<String, List<String>> cityMap; // 省份与城市的映射private Map<String, List<String>> districtMap; // 城市与区的映射private String[] contactTypes = new String[]{"手机", "座机", "呼机"}; //public CascadeWriteHandler3(List<String> provinceList, Map<String, List<String>> cityMap, Map<String, List<String>> districtMap) {this.provinceList = provinceList;this.cityMap = cityMap;this.districtMap = districtMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 此处代码在创建Sheet前，通常为空}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet sheet = writeSheetHolder.getSheet();// 创建标题行并合并单元格Row titleRow = sheet.createRow(0); // 创建第一行作为标题行titleRow.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints()); // 设置行高为默认的两倍Cell titleCell = titleRow.createCell(0); // 在第一行第一个单元格中设置标题titleCell.setCellValue("人员信息\n完整的"); // 设置标题内容，并添加换行// 设置单元格样式为左对齐并自动换行CellStyle titleStyle = workbook.createCellStyle();titleStyle.setAlignment(HorizontalAlignment.LEFT); // 设置水平对齐为左对齐titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直对齐为居中titleStyle.setWrapText(true); // 设置自动换行titleCell.setCellStyle(titleStyle); // 应用样式到单元格// 合并标题行的单元格sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));// 设置列标题Row headerRow = sheet.createRow(1);headerRow.createCell(0).setCellValue("省");headerRow.createCell(1).setCellValue("市");headerRow.createCell(2).setCellValue("区");headerRow.createCell(3).setCellValue("联系方式");// 设置列宽以便显示标题sheet.setColumnWidth(0, 256 * 16); // 设置"省"的列宽sheet.setColumnWidth(1, 256 * 16); // 设置"市"的列宽sheet.setColumnWidth(2, 256 * 16); // 设置"区"的列宽sheet.setColumnWidth(3, 256 * 16); // 设置"联系方式"的列宽// 创建隐藏的Sheet存放省市区数据Sheet hideSheet = workbook.createSheet("siteInfo");workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);// 省份列表处理逻辑int rowNum = 0;Row provinceRow = hideSheet.createRow(rowNum++);provinceRow.createCell(0).setCellValue("省份列表");for (int i = 0; i < provinceList.size(); i++) {provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));}// 城市列表处理逻辑for (String province : provinceList) {List<String> cities = cityMap.get(province);Row cityRow = hideSheet.createRow(rowNum++);cityRow.createCell(0).setCellValue(province);for (int i = 0; i < cities.size(); i++) {cityRow.createCell(i + 1).setCellValue(cities.get(i));}Name cityRangeName = workbook.createName();cityRangeName.setNameName(province);String cityRange = getRange(1, rowNum, cities.size());cityRangeName.setRefersToFormula("siteInfo!" + cityRange);}// 区列表处理逻辑for (String city : cityMap.values().stream().flatMap(List::stream).toArray(String[]::new)) {List<String> districts = districtMap.get(city);Row districtRow = hideSheet.createRow(rowNum++);districtRow.createCell(0).setCellValue(city);for (int i = 0; i < districts.size(); i++) {districtRow.createCell(i + 1).setCellValue(districts.get(i));}Name districtRangeName = workbook.createName();districtRangeName.setNameName(city);String districtRange = getRange(1, rowNum, districts.size());districtRangeName.setRefersToFormula("siteInfo!" + districtRange);}// 省份数据验证DataValidationHelper dvHelper = sheet.getDataValidationHelper();DataValidationConstraint provinceConstraint = dvHelper.createExplicitListConstraint(provinceList.toArray(new String[0]));CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(2, 1000, 0, 0);setValidation(sheet, dvHelper, provinceConstraint, provinceRangeAddressList);// 城市数据验证for (int i = 3; i <= 1000; i++) {DataValidationConstraint cityConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$" + i + ")");CellRangeAddressList cityRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 1, 1);setValidation(sheet, dvHelper, cityConstraint, cityRangeAddressList);}// 区数据验证for (int i = 3; i <= 1000; i++) {DataValidationConstraint districtConstraint = dvHelper.createFormulaListConstraint("INDIRECT($B$" + i + ")");CellRangeAddressList districtRangeAddressList = new CellRangeAddressList(i - 1, i - 1, 2, 2);setValidation(sheet, dvHelper, districtConstraint, districtRangeAddressList);}// 联系方式数据验证DataValidationConstraint contactTypeConstraint = dvHelper.createExplicitListConstraint(new String[]{"手机", "座机", "呼机"});CellRangeAddressList contactTypeRangeAddressList = new CellRangeAddressList(2, 1000, 3, 3);setValidation(sheet, dvHelper, contactTypeConstraint, contactTypeRangeAddressList);}// 其他辅助方法和类成员不变private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {DataValidation validation = helper.createValidation(constraint, addressList);validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);sheet.addValidationData(validation);}public 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';if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-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;}}
}

package com.example.demoeasyexcel.jilian3;import com.alibaba.excel.EasyExcel;
import com.example.demoeasyexcel.jilian.CascadeVO;
import com.example.demoeasyexcel.jilian2.CascadeWriteHandler2;import java.io.File;
import java.util.*;public class Test33 {public static void main(String[] args) {List<CascadeVO> dataList = new ArrayList<>();/// 准备省市aqu测试数据List<String> provinceList = Arrays.asList("apro1", "apro2", "apro3");Map<String, List<String>> cityMap = new HashMap<>();cityMap.put("apro1", Arrays.asList("acity11", "acity12"));cityMap.put("apro2", Arrays.asList("acity21", "acity22"));cityMap.put("apro3", Arrays.asList("acity31", "acity32"));Map<String, List<String>> districtMap = new HashMap<>();districtMap.put("acity11", Arrays.asList("aqu111", "aqu112"));districtMap.put("acity12", Arrays.asList("aqu121", "aqu122"));districtMap.put("acity21", Arrays.asList("aqu211", "aq212"));districtMap.put("acity22", Arrays.asList("aqu221", "aqu222"));districtMap.put("acity31", Arrays.asList("aqu311", "aqu312"));districtMap.put("acity32", Arrays.asList("aqu321", "aqu322"));// 创建写入的SheetFile file = new File("D:\\data\\test.xlsx");EasyExcel.write(file).sheet("sheet1").registerWriteHandler(new CascadeWriteHandler3(provinceList, cityMap, districtMap)).doWrite(dataList);}
}