阿里EasyExcel动态头模板下载，以及下拉框设置

//动态头部数组
List> headFields;

String fileName = URLEncoder.encode("模板", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List> maps = new ArrayList<>();//空表格数据
EasyExcel.write(response.getOutputStream())
        .head(headFields).sheet("导入模板")
        .registerWriteHandler(new SelectedCellWriteHandler(type,headFields,groupData,taxRates))//在里面定义下拉框之类
        .doWrite(maps);

//动态头部的方法返回这样结构数组才可以成功设置头部

List> getExportDtoByBenefitType(List head){
    List> headlist = new ArrayList();
    for (int i = 0; i < head.size(); i++) {
        List headname = new ArrayList();
        if (null!=head.get(i)) {
            headname.add(head.get(i));
            headlist.add(headname);
        }
    }
    return headlist;
}

//获取动态类的属性值

private static Object getFieldValueByName(String fieldName, Object o) {
    try {
        String firstLetter = fieldName.substring(0, 1).toUpperCase();
        String getter = "get" + firstLetter + fieldName.substring(1);
        Method method = o.getClass().getMethod(getter, new Class[] {});
        Object value = method.invoke(o, new Object[] {});
        return value;
    } catch (Exception e) {
        log.error("获取属性值失败！" + e, e);
    }
    return null;
}

//下拉框设置  

public class SelectedCellWriteHandler implements SheetWriteHandler {

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

Map mapDropDown = new HashMap<>();//key第几列，value，下拉数据

mapDropDown.put(9,type.getBusines().toArray(new String[type.getBusines.size()]));

// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();

 // k 为存在下拉数据集的单元格下表 v为下拉数据集
        mapDropDown.forEach((k, v) -> {
            // 设置下拉单元格的首行 末行 首列 末列
            CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
                    if (v.length > LIMIT_NUMBER) {
                        //定义sheet的名称
                        //1.创建一个隐藏的sheet 名称为 hidden + k
                        String sheetName = "hidden" + k;
                        Workbook workbook = writeWorkbookHolder.getWorkbook();
                        Sheet hiddenSheet = workbook.createSheet(sheetName);
                        for (int i = 0, length = v.length; i < length; i++) {
                            // 开始的行数i，列数k
                            hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);
                        }
                        Name category1Name = workbook.createName();
                        category1Name.setNameName(sheetName);
                        String excelLine = getExcelLine(k);
                        // =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
                        String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
                        // 将刚才设置的sheet引用到你的下拉列表中
                        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
                        DataValidation dataValidation = helper.createValidation(constraint, rangeList);
                        writeSheetHolder.getSheet().addValidationData(dataValidation);
                        // 设置存储下拉列值得sheet为隐藏
                        int hiddenIndex = workbook.getSheetIndex(sheetName);
                        if (!workbook.isSheetHidden(hiddenIndex)) {
                            workbook.setSheetHidden(hiddenIndex, true);
                        }
                    }
            // 下拉列表约束数据
            DataValidationConstraint constraint1 = helper.createExplicitListConstraint(v);
            // 设置约束
            DataValidation validation = helper.createValidation(constraint1, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            if (validation instanceof XSSFDataValidation) {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            } else {
                validation.setSuppressDropDownArrow(false);
            }
//            validation.setShowErrorBox(true);
//            validation.setSuppressDropDownArrow(true);
//            validation.createErrorBox("提示", "此值与单元格定义格式不一致");
//             validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位");
            sheet.addValidationData(validation);

        });

}

/**
 * 返回excel列标A-Z-AA-ZZ
 *
 * @param num 列数
 * @return java.lang.String
 */
private String getExcelLine(int num) {
    String line = "";
    int first = num / 26;
    int second = num % 26;
    if (first > 0) {
        line = (char) ('A' + first - 1) + "";
    }
    line += (char) ('A' + second) + "";
    return line;
}

}