使用EasyExcel导出excel模版，表头字段动态生成下拉框选择，并且阻止输入不符合下拉框选项的值，会在表格进行提示。

为了避免excel下拉框选项过多，导致下拉框内容不显示，新建一个sheet页，将下拉框内容存储在新建的sheet页中，通过引用公式将下拉内容关联到表头字段上。

1、引入EasyExcel

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.1</version>
</dependency>

2、导出excel模板

public void export(HttpServletResponse response) throws IOException {String fileName = "test";response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileNameEncode = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ExcelTypeEnum.XLS.getValue());// 模拟下拉框内容Map<Integer, List<String>> selectMap = new HashMap<>();List<String> sexList = new ArrayList<>();sexList.add("男");sexList.add("女");selectMap.put(1, sexList);List<String> typeList = new ArrayList<>();typeList.add("架构部");typeList.add("基础部");typeList.add("技术部");selectMap.put(2, typeList);//模拟表头List<List<String>> list = new ArrayList<>();List<String> field1 = new ArrayList<>();List<String> field2 = new ArrayList<>();List<String> field3 = new ArrayList<>();field1.add("编号");field2.add("性别");field3.add("部门");list.add(field1);list.add(field2);list.add(field3);EasyExcelFactory.write(response.getOutputStream()).registerWriteHandler(new SelectSheetWriteHandler(selectMap)).excelType(ExcelTypeEnum.XLS).head(list).sheet("用户sheet").doWrite(new ArrayList<>());}

3、下拉框选项代码

public class SelectSheetWriteHandler implements SheetWriteHandler {private Map<Integer, List<String>> selectMap;private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L','M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap) {this.selectMap = selectMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (selectMap == null || selectMap.size() == 0) {return;}// 需要设置下拉框的sheet页Sheet curSheet = writeSheetHolder.getSheet();DataValidationHelper helper = curSheet.getDataValidationHelper();String dictSheetName = "字典sheet";Workbook workbook = writeWorkbookHolder.getWorkbook();// 数据字典的sheet页Sheet dictSheet = workbook.createSheet(dictSheetName);for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {// 设置下拉单元格的首行、末行、首列、末列CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());int rowLen = entry.getValue().size();// 设置字典sheet页的值 每一列一个字典项for (int i = 0; i < rowLen; i++) {Row row = dictSheet.getRow(i);if (row == null) {row = dictSheet.createRow(i);}row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));}String excelColumn = getExcelColumn(entry.getKey());// 下拉框数据来源 eg:字典sheet!$B1:$B2String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;// 创建可被其他单元格引用的名称Name name = workbook.createName();// 设置名称的名字name.setNameName("dict" + entry.getKey());// 设置公式name.setRefersToFormula(refers);// 设置引用约束DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());// 设置约束DataValidation validation = helper.createValidation(constraint, rangeAddressList);if (validation instanceof HSSFDataValidation) {validation.setSuppressDropDownArrow(false);} else {validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);}// 阻止输入非下拉框的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.createErrorBox("提示", "此值与单元格定义格式不一致！");// 添加下拉框约束writeSheetHolder.getSheet().addValidationData(validation);}}/*** 将数字列转化成为字母列** @param num* @return*/private String getExcelColumn(int num) {String column = "";int len = alphabet.length - 1;int first = num / len;int second = num % len;if (num <= len) {column = alphabet[num] + "";} else {column = alphabet[first - 1] + "";if (second == 0) {column = column + alphabet[len] + "";} else {column = column + alphabet[second - 1] + "";}}return column;}
}

4、效果