EasyExcel导入模板下拉框问题

EasyExcel导入模板时设置下拉框问题，下拉框值超过excel下拉框255限制。此时导出的列下拉框为空，

解决办法：

用于在 Excel 导出时设置下拉框。实现了 Alibaba EasyExcel 库的 SheetWriteHandler 接口，通过该接口可以在生成 Excel 文件时添加自定义的处理逻辑。

具体来说，在 afterSheetCreate 方法中创建一个名为 "hidden" 的隐藏工作表，通过设置数据验证的方式将下拉框和该工作表关联起来。具体步骤如下：

1. 获取需要添加下拉框的列号和下拉框选项列表。
2. 创建一个隐藏工作表，用于存储下拉框选项。
3. 循环遍历下拉框选项列表，并将选项值写入隐藏工作表中。
4. 根据隐藏工作表的名称和列号，构造一个引用字符串。
5. 创建一个数据验证对象，并将其与当前工作表的指定单元格范围关联。
6. 将数据验证对象添加到当前工作表中。
7. 将隐藏工作表设置为隐藏。

package com.social.credits.business.specialnew.util;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

/**
 * 无需模板，导出时设置下拉框
 * <p>
 * 尝试创建sheet，突破下拉框255字段的限制
 *
 * @date 01/22/2021 04:09
 */
@Slf4j
public class MyDropSheetWriteHandlerForProvice implements SheetWriteHandler {


    /**
     * 下拉框内容map Integer数据所在列数，string[]下拉数据列表
     */
    private Map<Integer, String[]> spinnerMap;
    /**
     * 导出数据的list大小
     */
    private int dataSize;
    /**
     * 定义sheet的名称
     */
    private String hiddenName;

    public MyDropSheetWriteHandlerForProvice(Map<Integer, String[]> spinnerMap, int dataSize, String hiddenName) {
        this.spinnerMap = spinnerMap;
        this.dataSize = dataSize;
        this.hiddenName = hiddenName;
    }

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //获取一个workbook
        Sheet sheet = writeSheetHolder.getSheet();
        //设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();

        //1.创建一个隐藏的sheet 名称为 hidden
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet hidden = workbook.createSheet(hiddenName);
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenName);
        for (Map.Entry<Integer, String[]> entry : spinnerMap.entrySet()) {
            //下拉框的起始行,结束行,起始列,结束列
            CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize + 500, entry.getKey(), entry.getKey());
            //获取excel列名
            String excelLine = getExcelLine(entry.getKey());

            //2.循环赋值
            String[] values = entry.getValue();
            for (int i = 0, length = values.length; i < length; i++) {
                // 3:表示你开始的行数  3表示 你开始的列数
                Row row = hidden.getRow(i);
                if (row == null) {
                    row = hidden.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(values[i]);
            }

            //4.  =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
            String refers = "=" + hiddenName + "!$" + excelLine +
                    "$1:$" + excelLine + "$" + (values.length);
            //5 将刚才设置的sheet引用到你的下拉列表中
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation);
        }
        //设置列为隐藏
        int hiddenIndex = workbook.getSheetIndex(hiddenName);
        if (!workbook.isSheetHidden(hiddenIndex)) {
            workbook.setSheetHidden(hiddenIndex, true);
        }
    }

    /**
     * @param num 列数
     * @return java.lang.String
     * @Description 返回excel列标A-Z-AA-ZZ
     * @Author chou
     * @Date 2020/9/8
     */
    public static 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;
    }
}

 具体使用：

    public void templateInformation(ServletOutputStream outputStream) {
        List<LoanEnterprisesListImportRequest> exportResponses = new ArrayList<>();
        EasyExcel.write(outputStream, LoanEnterprisesListImportRequest.class).sheet("sheet名称")//sheet名称
                .registerWriteHandler(excelStyle())//标题样式
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自动列宽
                .registerWriteHandler(getBankDropDownNew())
                .registerWriteHandler(getProductDropDownNew())
                .doWrite(exportResponses);
    }

    private MyDropSheetWriteHandlerForProvice getBankDropDownNew() {
        List<BankSelectRespone> bankSelect = bankService.getBankSelect("500000");
        List<String> bankNames = bankSelect.stream()
                .map(BankSelectRespone::getName).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(bankNames)) {
            return null;
        }
        Map<Integer, String[]> bankMap = new HashMap<>();
        bankMap.put(2, bankNames.toArray(new String[0]));
        return new MyDropSheetWriteHandlerForProvice(bankMap, bankNames.size(), "hidden2");

    }