EasyExcel导出表格——下拉筛选

EasyExcel导出表格——下拉筛选

单元格添加下拉筛选

1. 确保输入内容规范和准确
2. 减少数据查询，提高用户使用感
3. 根据用户权限，下拉筛选项控制

Controller层

 /**
  * 模板下载
  * @param response
  */
@GetMapping(value = "/download")
public void download(HttpServletResponse response) {
    // 导出模板名称
    String fileName = "负责人批量修改模板.xlsx";
    ClassPathResource classPathResource = new ClassPathResource("template/payment_permission.xlsx");
    try (InputStream inputStream = classPathResource.getInputStream();
         OutputStream out = response.getOutputStream();) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode防止中文乱码
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")
                           + ";filename*=UTF-8" + URLEncoder.encode(fileName, "UTF-8"));
        // 表格填充数据
        List<PermissionExcel> excelList = permissionSettingService.listPermissionExcel();
        ExcelWriterBuilder writerBuilder = EasyExcel.write(out).withTemplate(inputStream);
        // PermissionExcelWriteHandler处理下拉的handler
        writerBuilder.sheet(0).registerWriteHandler(new PermissionExcelWriteHandler()).doFill(excelList);
    } catch (IOException e) {
        log.error(e.getMessage(), e);
        response.setStatus(500);
    }
}

处理字典项的Handler（下拉选项不超过50个）

public class PermissionExcelWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        // 设置下拉单元格的首行、末行、首列、末列
        CellRangeAddressList accountTypeCellRangeAddressList = new CellRangeAddressList(1, 2000, 2, 2);
        // AccountTypeEnum.dropIndex() 等于 String[] array = new String[]{"账户1", "账户2"};
        DataValidationConstraint accountTypeConstraint = helper.createExplicitListConstraint(AccountTypeEnum.dropIndex());
        DataValidation accountTypeDataValidation = helper.createValidation(accountTypeConstraint, accountTypeCellRangeAddressList);
        writeSheetHolder.getSheet().addValidationData(accountTypeDataValidation);
    }
}

当下拉选项值超过50个时，使用上述方式handler处理，导出的表格中下拉选项不显示，这时候需要将下拉选的内容存在另一个sheet页中。

处理字典项的Handler（下拉选项超过50个）

阿里云参考文档

public class SocialBillExtendExcelWriteHandler implements SheetWriteHandler {
	/**
	 * key 下拉选项需要填充的列序号，value存放对应的下拉选项内容
	 */
    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 SocialBillExtendExcelWriteHandler(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 (CollUtil.isEmpty(selectMap)) {
            return;
        }
        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "数据字典";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 创建数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        // 提升用户体验，隐藏数据字典的sheet页
        workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);
        for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(2, 100, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            // 设置字典sheet页的值 每一列一个字典项
            Iterator<String> iterator = entry.getValue().iterator();
            int rowIndex = 0;
            while (iterator.hasNext()) {
                Row row = dictSheet.getRow(rowIndex);
                if (row == null) {
                    row = dictSheet.createRow(rowIndex);
                }
                row.createCell(entry.getKey()).setCellValue(iterator.next());
                rowIndex++;
            }
            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
     */
    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;
    }
}