前段时间，因为项目需要，要将excel模板下载功能从附件下载迭代为后台动态生成；但是这个表格里面包含了下拉框，耗时两天才算是把这个excel表格搞完，现在记录一下，方便后续回顾。
首先，要实现的效果是这样：
根据D列的检查类型,E列弹出不同的下拉选项，F列弹出不同的问题属性；
Service层导出excel代码：
@Transactional(rollbackFor = Exception.class)public void download(HttpServletResponse response) {//导出文件格式response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");//设置导出文件名String filename = "";try {filename = URLEncoder.encode("Excel名称" + DateUtil.format(new Date(), "yyyy-MM-dd"), "UTF-8").replaceAll("\\+", "%20");} catch (UnsupportedEncodingException e) {e.printStackTrace();}response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + "." + ExcelFormat.XLS.getValue());//模板实体类List<SecondInspProbExcelDTO> secondInspProbExcelDTOS = new ArrayList<>();//普通下拉数据Map<Integer, String[]> map = ordinarySelect();//检查类型-子类Map<String, List<String>> inspNameMap = inspNameSelect();//检查类型-父类String[] proInspNameMap = proInspNameSelect();//问题属性Map<String, List<String>> probPropsNameMap = probPropsNameSelect();try(OutputStream outputStream = response.getOutputStream()) {EasyExcel.write(outputStream, SecondInspProbExcelDTO.class).registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap)).excelType(ExcelTypeEnum.XLS).sheet("sheet1").doWrite(secondInspProbExcelDTOS);} catch (IOException e) {e.printStackTrace();}}
其中，这几个方法是为了创建下拉数据
//普通下拉数据Map<Integer, String[]> map = ordinarySelect();//检查类型-子类Map<String, List<String>> inspNameMap = inspNameSelect();//检查类型-父类String[] proInspNameMap = proInspNameSelect();//问题属性Map<String, List<String>> probPropsNameMap = probPropsNameSelect();
普通下拉数据，就是如同性别、是否之类的数据；Map中的key就是要关联的列，注意：excel列从0开始，value就是下拉数据的数组
/*** 普通下拉数据* @return*/private Map<Integer, String[]> ordinarySelect() {Map<Integer, String[]> map = new HashMap<>();//是否现场已完成整改 是否直接作业类问题String[] isZgArray = {"是", "否"};//二级单位检查类型String[] inspPlanTypeArray = {"无计划", "有计划", "计划外新增"};map.put(2, isZgArray);map.put(6, isZgArray);map.put(12, inspPlanTypeArray);return map;}
级联的数据，父类：
/*** 检查类型-父类* @return*/private String[] proInspNameSelect() {String[] proInspNameList = new String[10];proInspNameList[2] = "专项检查";return proInspNameList;}
子类：
/*** 检查类型-子类* @return*/private Map<String, List<String>> inspNameSelect() {Map<String, List<String>> inspNameArray = new HashMap<>();//检查类型-专项检查(子类)List<String> zxjcInspNameList = new ArrayList<>();zxjcInspNameList.add("季节性");zxjcInspNameList.add("节前");zxjcInspNameList.add("专业性重大危险源");inspNameArray.put("专项检查", zxjcInspNameList);return inspNameArray;}
其中，子类返回的map中，key就是父类返回数组的值，value就是子类列的下拉列表
接下来就是核心的拦截器，需实现
SheetWriteHandler接口
直接上代码，代码中有注释
import com.alibaba.excel.write.handler.SheetWriteHandler;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 java.util.Iterator;import java.util.List;import java.util.Map;public class CustomCellWriteHandler implements SheetWriteHandler {/*** 普通下拉数据*/private Map<Integer, String[]> map;/*** 检查类型-父类*/private String[] proInspNameArray;/*** 检查类型-子类*/private Map<String, List<String>> inspNameArray;/*** 问题属性*/private Map<String, List<String>> probPropsNameArray;public CustomCellWriteHandler(Map<Integer, String[]> map, String[] proInspNameArray, Map<String, List<String>> inspNameArray, Map<String, List<String>> probPropsNameArray) {this.map = map;this.proInspNameArray = proInspNameArray;this.inspNameArray = inspNameArray;this.probPropsNameArray = probPropsNameArray;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 获取第一个sheet页Sheet sheet = writeSheetHolder.getCachedSheet();// 获取sheet页的数据校验对象DataValidationHelper helper = sheet.getDataValidationHelper();// 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页Workbook workbook = writeWorkbookHolder.getWorkbook();// 普通数据，迭代索引，用于存放下拉数据的字典sheet数据页命名int index = 1;for (Map.Entry<Integer, String[]> entry : map.entrySet()) {// 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好String dictSheetName = "dict_hide_sheet" + index;Sheet dictSheet = workbook.createSheet(dictSheetName);// 隐藏字典sheet页workbook.setSheetHidden(index++, true);// 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的// 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实// 际业务设置真正的数据开始行，如果要设置到最后一行，那么一定注意，// 最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表// 失效，出不来CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());int rowLen = entry.getValue().length;for (int i = 0; i < rowLen; i++) {// 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定// 义从第几行还是写，写的时候注意一下行索引是从0开始的即可dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue()[i]);}// 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().length;Name name = workbook.createName();name.setNameName(dictSheetName);// 将关联公式和sheet页做关联name.setRefersToFormula(refers);// 将上面设置好的下拉列表字典sheet页和目标sheet关联起来DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);setValidation(sheet, helper, constraint, infoList);}//检查类型-父类Sheet hideSheet = workbook.createSheet("site");workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), false);// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。int rowId = 0;Row proviRow = hideSheet.createRow(rowId++);proviRow.createCell(0).setCellValue("大类列表");for (int i = 0; i < proInspNameArray.length; i++) {Cell proviCell = proviRow.createCell(i + 1);proviCell.setCellValue(proInspNameArray[i]);}//检查类型-子类Iterator<String> keyIterator = inspNameArray.keySet().iterator();while (keyIterator.hasNext()) {String key = keyIterator.next();List<String> son = inspNameArray.get(key);Row row = hideSheet.createRow(rowId++);row.createCell(0).setCellValue(key);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 = workbook.createName();name.setNameName(key);String formula = "site!" + range;name.setRefersToFormula(formula);}//问题属性Iterator<String> probPropsNameIterator = probPropsNameArray.keySet().iterator();while (probPropsNameIterator.hasNext()) {String key = probPropsNameIterator.next();List<String> son = probPropsNameArray.get(key);Row row = hideSheet.createRow(rowId++);row.createCell(0).setCellValue(key);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 = workbook.createName();name.setNameName(key);String formula = "site!" + range;name.setRefersToFormula(formula);}///开始设置（大类小类）下拉框DataValidationHelper dvHelper = sheet.getDataValidationHelper();// 大类规则DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(proInspNameArray);CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 1048575, 3, 3);setValidation(sheet, dvHelper, expConstraint, expRangeAddressList);//检查类型-子类// 小类规则(各单元格按个设置)// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联for (int i = 2; i < inspNameArray.size() + 10; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 4, 4);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($D$" + i + ")");setValidation(sheet, dvHelper, formula, rangeAddressList);}//问题属性for (int i = 2; i < probPropsNameArray.size() + 10; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 5, 5);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($E$" + i + ")");setValidation(sheet, dvHelper, formula, rangeAddressList);}}/*** 设置验证规则* @param sheetsheet对象* @param helper验证助手* @param constraintcreateExplicitListConstraint* @param addressList验证位置对象*/private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {DataValidation dataValidation = helper.createValidation(constraint, addressList);dataValidation.createErrorBox("错误提示", "您输入的内容，不符合限制条件");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 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;}}}
这个拦截器是在service层使用的，就是最上面的代码
try(OutputStream outputStream = response.getOutputStream()) {EasyExcel.write(outputStream, SecondInspProbExcelDTO.class).registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap)).excelType(ExcelTypeEnum.XLS).sheet("sheet1").doWrite(secondInspProbExcelDTOS);
导出excel表头背景色，文字颜色大小
@Data//设置表头背景色@HeadStyle(fillForegroundColor = 49)//设置表头行高，默认单位磅@HeadRowHeight(value = 34)//设置表头问题颜色，大小，黑色8，白色7；@HeadFontStyle(color = 8, fontHeightInPoints = 12)@ExcelIgnoreUnannotatedpublic class SecondInspProbExcelDTO {
颜色对照表
