使用EasyExcel导出带下拉框选项excel模板

使用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;
  }

  @Override
  public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  }

  @Override
  public 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:$B2
      String 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、效果