基于EasyExcel实现模板导出功能（含查数据库下拉，市州区县级联下拉）

最近的项目涉及到excel导入导出功能，既然涉及到导入，那给客户提供导入模板是必不可少的功能。本文总结了csdn各大前辈的精华，能够实现模板导出功能，并包含指定字段查库下拉选，指定字段级联下拉选（例如市州区县）。

目录

1.引入easyexcel相关依赖

2.设置处理下拉的handler

3.设置级联下拉的handler

4.创建对应的导入实体类

5.在对应模块的实现类编写获取导入模板的方法

6.控制层编写

1.引入easyexcel相关依赖

com.alibabaeasyexcel2.2.10

ps：如果遇到各种bug就先看看自己的pom文件那几个对应的poi版本是不是一致的。如果你也遇到了项目存在老版本poi导致poi冲突的话可以看看我写的另一篇文章：

解决easyexcel的poi版本冲突_JesseXW的博客-CSDN博客

2.设置处理下拉的handler

/** * 处理选定的工作表 * * @author: Jesse * @date: 2023/6/27 */public class SelectedSheetWriteHandler implements SheetWriteHandler {private final Map<Integer, List> selectedMap;/** * Called before create the sheet */@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}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'};/** * Called after the sheet is created */@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (selectedMap == null || selectedMap.size() == 0) {return;}// 需要设置下拉框的sheet页Sheet curSheet = writeSheetHolder.getSheet();DataValidationHelper helper = curSheet.getDataValidationHelper();String dictSheetName = "sheet2";Workbook workbook = writeWorkbookHolder.getWorkbook();// 数据字典的sheet页Sheet dictSheet = workbook.createSheet(dictSheetName);// 隐藏数据字典的sheet页workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);for (Map.Entry<Integer, List> entry : selectedMap.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);}}public Map<Integer, List> getSelectedMap() {return selectedMap;}public SelectedSheetWriteHandler(Map<Integer, List> selectedMap) {this.selectedMap = selectedMap;}/** * 将数字列转化成为字母列 * @param num * @author: Jesse * @date: 2023/6/27 * @return: String */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;}}

ps：此处已针对下拉结果超50不显示问题进行了对应的优化~（解决方案为额外创建了张隐藏表，将下拉选项都放在隐藏表中）

3.设置级联下拉的handler

/** * 级联下拉处理器 * * @author: Jesse * @date: 2023/6/27 */public class CascadeWriteHandler implements SheetWriteHandler {private List largeList;// 大类的字符串集合Map<String, List> siteMap;// 大类和小类的对应关系的map集合public CascadeWriteHandler(List largeList, Map<String, List> siteMap) {this.largeList = largeList;this.siteMap = siteMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//获取工作簿Sheet sheet = writeSheetHolder.getSheet();Workbook book = writeWorkbookHolder.getWorkbook();//创建一个专门用来存放地区信息的隐藏sheet页//因此不能在现实页之前创建，否则无法隐藏。Sheet hideSheet = book.createSheet("site");book.setSheetHidden(book.getSheetIndex(hideSheet), true);// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。int rowId = 0;Row proviRow = hideSheet.createRow(rowId++);proviRow.createCell(0).setCellValue("大类列表");for (int i = 0; i < largeList.size(); i++) {Cell proviCell = proviRow.createCell(i + 1);proviCell.setCellValue(largeList.get(i));}Iterator keyIterator = siteMap.keySet().iterator();while (keyIterator.hasNext()) {String key = keyIterator.next();List son = siteMap.get(key);Row row = hideSheet.createRow(rowId++);for (int i = 0; i < son.size(); i++) {Cell cell = row.createCell(i + 1);cell.setCellValue(son.get(i));}// 添加名称管理器String range = getRange(1, rowId, son.size());Name name = book.createName();name.setNameName(key);String formula = "site!" + range;name.setRefersToFormula(formula);}///开始设置（大类小类）下拉框DataValidationHelper dvHelper = sheet.getDataValidationHelper();// 大类规则DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(largeList.toArray(new String[]{}));CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 4, 4);//todo 调整模板后位置需同步修改firstColsetValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");// 小类规则(各单元格按个设置)// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联for (int i = 2; i < 1000; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 5, 5);//todo 调整模板后位置需同步修改firstColDataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($E$" + i + ")");//todo 调整模板后位置需同步修改字母setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");}}/** * 设置验证规则 * @param sheetsheet对象 * @param helper验证助手 * @param constraint createExplicitListConstraint * @param addressList验证位置对象 * @param msgHead 错误提示头 * @param msgContext 错误提示内容 */private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {DataValidation dataValidation = helper.createValidation(constraint, addressList);dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.setShowErrorBox(true);dataValidation.setSuppressDropDownArrow(true);dataValidation.createErrorBox(msgHead, msgContext);sheet.addValidationData(dataValidation);}/** * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId第几行 * @param colCount 一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 * @author denggonghai 2016年8月31日 下午5:17:49 */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;}}}

ps：级联下拉处理器这是csdn一个大佬写的，他的文章对于每块代码是干什么的写的很详尽，强烈建议各位去拜读一下，对代码的实际运行更为印象深刻，链接我会贴在文末。（但不知道这个大佬是故意的还是啥，他给的示例代码里面有个错误会导致级联失效，但是你仔细阅读他后面写的实现逻辑你就会发现他哪写错了，这里的代码我已经修正了，各位读者感兴趣可以去看看，写的真的很6！！）

4.创建对应的导入实体类

/** * 项目的excel模板类 * * @author: Jesse * @date: 2023/6/26 */@ColumnWidth(22)@HeadRowHeight(30)public class ExcelProjectDto {/** * 项目名称 */@ExcelProperty(value = "项目名称")//表头注解private String projName;/** * 项目所在企业名称 */@ExcelProperty(value = "企业名称")private String enterpriseName;/** * 项目所在地级名称 */@ExcelProperty(value = "地市")private String area20Name;/** * 项目所在县级名称 */@ExcelProperty(value = "区县")private String area30Name;public String getProjName() {return projName;}public void setProjName(String projName) {this.projName = projName;}public String getEnterpriseName() {return enterpriseName;}public void setEnterpriseName(String enterpriseName) {this.enterpriseName = enterpriseName;}public String getArea20Name() {return area20Name;}public void setArea20Name(String area20Name) {this.area20Name = area20Name;}public String getArea30Name() {return area30Name;}public void setArea30Name(String area30Name) {this.area30Name = area30Name;}public ExcelProjectDto(String projName, String enterpriseName, String area20Name, String area30Name) {this.projName = projName;this.enterpriseName = enterpriseName;this.area20Name = area20Name;this.area30Name = area30Name;}public ExcelProjectDto() {}}

ps：根据自己的业务逻辑进行编写即可，可以使用Lombok的@Data让代码更简洁

5.在对应模块的实现类编写获取导入模板的方法

public void getImportTemplate(HttpServletResponse response) {String fileName = "项目信息导入模板" + System.currentTimeMillis() + ".xlsx";//这里的时间戳可以不要，fileName改为你想要的名称即可//获取当前桌面路径直接导出文件到桌面上（自测可以解开这些注释，但是也注意注释其他对应的代码）//String path = FileSystemView.getFileSystemView().getHomeDirectory().getPath();//String exportFileName = path + "/" + fileName;//解决编码问题try {fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setCharacterEncoding("utf-8");response.setDateHeader("Expires", -1);//设置响应头部信息，格式为附件，以及文件名response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);ArrayList dataList = new ArrayList();//设置市州区县级联信息(根据你项目实际情况来进行查询)List areaListNodeVos = areaService.getChildrenAreaListNodeVos("510000");//四川省区划idList provNameList = areaListNodeVos.stream().map(AreaListNodeVo::getName).collect(Collectors.toList());HashMap<String, List> siteMap = new HashMap(36);for (AreaListNodeVo areaNodeVo : areaListNodeVos) {List area30Vos = areaService.getChildrenAreaListNodeVos(areaNodeVo.getId());List area30Names = area30Vos.stream().map(AreaListNodeVo::getName).collect(Collectors.toList());siteMap.put(areaNodeVo.getName(), area30Names);}Map<Integer, List> selectedMap = new HashMap(64);List nodeVoList = enterpriseService.getAllListNodeVos();List enterpriseNameList = nodeVoList.stream().map(EnterpriseListNodeVo::getEnterpriseName).collect(Collectors.toList());if (CollectionUtil.isNotEmpty(enterpriseNameList)) {selectedMap.put(3, enterpriseNameList);//这里的3是指在导出的模板excel的第4列，因为是从0开始的，之前我给出的实体类我删掉了一些敏感字段}WriteSheet writeSheet = EasyExcel.writerSheet(0, "项目信息").head(ExcelProjectDto.class).registerWriteHandler(new CascadeWriteHandler(provNameList, siteMap)).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).build();//ExcelWriter excelWriter = EasyExcel.write(exportFileName).build();ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();//此处只导出实体类中的数据所以只new 一个空的listexcelWriter.write(dataList, writeSheet);excelWriter.finish();} catch (UnsupportedEncodingException e) {logger.error("===========导出Excel编码异常:" + e.getMessage() + "===========");} catch (IOException e) {logger.error("===========导出Excel文件异常" + e.getMessage() + "===========");}}

6.控制层编写

@ApiOperation("获取导入模板")@RequestMapping(value = "/get-import-template", method = RequestMethod.GET)public void getImportTemplate(HttpServletResponse response) {demoService.getImportTemplate(response);}

ps：@ApiOperation(“获取导入模板”)是swagger的，可以不加

=========================================================================

最终效果：

其中企业名称作为查库下拉，市州级联的会作为隐藏sheet

查库下拉效果：

地市区县级联效果：

ps：但是还是会出现这种bug：

1. 先手动输入区县，再下拉选择地市，可以直接成功保存

2.直接复制粘贴进去，不管是否正确，都可以直接保存

只有手动按照顺序选择地市，再选择区县才是100%正确的！真的是防止不了用户手贱啊！（扶额）只能后端在最终导入数据时多做做校验了……

=========================================================================

最后贴上各位大佬的链接：

• Easyexcel导出带下拉框选项excel模板（解决下拉框超50个的问题）_easyexcel下载模板 多个动态下拉框_renijngkai的博客-CSDN博客

• 使用EasyExcel导出模板并设置级联下拉及其原理分析_easyexcel 级联_流水武qin的博客-CSDN博客

强烈推荐！！！

• 使用EasyExcel导出带下拉框的Excel_easyexcel导出下拉框_√Angelの爱灬的博客-CSDN博客

如果想更优雅一点通过注解实现easyexcel下拉，可以参考这篇