EasyExcel设置动态的下拉框选择范围以及内容

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.NoArgsConstructor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;

import java.util.Map;


/**
 * @author leishen
 */
@NoArgsConstructor
public class FreezeAndFilter implements SheetWriteHandler {

    /**
     * 默认一个sheet页的长度
     */
    private Integer firstRow =0;

    /**
     * 默认一个sheet页的长度
     */
    private Integer lastRow =65535;


    private Map<Integer,String []> selectMap;

    /**
     * 设置下拉框
     * @param firstRow 开始行号
     * @param lastRow 结束行号
     * @param selectMap 下拉框选择 列号+下拉框选项
     */
    public FreezeAndFilter(Integer firstRow, Integer lastRow, Map<Integer,String []> selectMap){
        this.firstRow = firstRow;
        this.lastRow = lastRow;
        this.selectMap = selectMap;
    }

    /**
     * 设置下拉框
     * @param firstRow 开始行号
     * @param selectMap 下拉框选择 列号+下拉框选项
     */
    public FreezeAndFilter(Integer firstRow,Map<Integer,String []> selectMap){
        this.firstRow = firstRow;
        this.selectMap = selectMap;
    }

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //设置下拉框
        if (!CollectionUtils.isEmpty(selectMap)) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            selectMap.forEach((colIndex, content)->{
                DataValidationConstraint constraint = helper.createExplicitListConstraint(content);
                CellRangeAddressList cellRangeAddressList=new CellRangeAddressList(firstRow,lastRow,colIndex,colIndex);
                DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示", "请输入下拉选项中的内容");
                sheet.addValidationData(validation);
            });

        }
    }
}

具体使用：

//设置下拉选择
Map<Integer,String []> map= Maps.newHashMap();
map.put(3,new String []{"男","女"});
String[] ageArr = new String[99];
for (int i = 0; i < 99; i++) {
   ageArr[i]=String.valueOf(i+1);
}
map.put(2,ageArr);
EasyExcel.write(out,StudentExport.class).registerWriteHandler(new FreezeAndFilter(2,51,map)).sheet("zyp").doWrite(studentList);