1. 導入EasyExcel依賴

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.11</version>
</dependency>

2. 方法定義如下

public void exportExcel(List<ExcelData> dataList, HttpServletResponse response)

3. 基本代碼

3.1 實體類

@Data
public class ExcelData{

    @ExcelProperty(index = 0, value = "字段0")
    private String field0;

    @ExcelProperty(index = 1, value = "字段1")
    private String field1;

    @ExcelProperty(index = 2, value = "字段2")
    private String field2;

    //自定義的一個注解，用來實現下拉框
    @ExcelSelected(contents = {"1","2","3"})
    @ExcelProperty(index = 3, value = "字段3")
    private String field3;

    @ExcelSelected(contents = {"是","否"})
    @ExcelProperty(index = 4, value = "字段4")
    private String field4;
}

3.2 數據導出?

//設置響應體的一些東西
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=excel.xlsx");
try {
    //創建一個excelWriter
    ExcelWriter excelWriter = EasyExcel
           .write(response.getOutputStream())
           .build();
    //創建一個sheet
    WriteSheet sheet = EasyExcel.writerSheet("data")
           //指定表頭
           .head(ExcelData.class)
           //注冊一個自定義的sheet的處理器(重點：用來設置下拉框和表頭凍結)
           .registerWriteHandler(new CustomSheetWriteHandler())
           .build();
    //將數據寫入Sheet并將Sheet設置進入Excel文檔
    excelWriter.write(dataList, sheet);
    //數據寫入完成
    excelWriter.finish();
} catch (IOException e) {
  throw new BizException("導出失敗！");
}

4. 設置下拉框詳細步驟(含表頭凍結)

4.1 自定義一個注解，用以攜帶下拉框數據

@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSelected {

    //下拉框內容，默認為空
    String[] contents() default {};

    //下拉框起始行，默認為第二行(索引為1)
    int firstRow() default 1;

    //下拉框結束行，默認為最后一行
    int lastRow() default 0x10000;

}

4.2 創建一個類存放字段通過ExcelSelected注解攜帶的數據

@Data
public class ExcelSelectedResolve {

    private String[] contents;

    private int firstRow;

    private int lastRow;

    public String[] resolveSelected(ExcelSelected excelSelected){
        if(excelSelected != null && excelSelected.contents().length > 0){
            return excelSelected.contents();
        }
        return null;
    }
}

4.3 添加ExcelSelected注解的字段解析并建立映射關系的方法(關鍵,后續調用)

public static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotations(Class<T> clazz){
    //字段所在列和解析出來的下拉框值的映射關系
    Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

    Field[] fields = clazz.getDeclaredFields();
    //遍歷類所有字段
    for (int i = 0; i < fields.length; i++) {

        Field field = fields[i];
        //獲取字段上兩個注解的信息
        ExcelSelected excelSelected = field.getAnnotation(ExcelSelected.class);
        ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);

        //如果字段上標注了ExcelSelected注解
        if(excelSelected != null){
            ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
            String[] selected = excelSelectedResolve.resolveSelected(excelSelected);
            if(selected != null && selected.length > 0){
                excelSelectedResolve.setContents(selected);
                excelSelectedResolve.setFirstRow(excelSelected.firstRow());
                excelSelectedResolve.setLastRow(excelSelected.lastRow());
                //將注解中的值提取出來,以index為鍵，存進map中
                if(excelProperty != null && excelProperty.index() >= 0){
                    selectedMap.put(excelProperty.index(), excelSelectedResolve);
                //如果沒有指定excelProperty注解或者沒有指定excelProperty注解的index值，則默認為字段遍歷的順序值
                }else {
                    selectedMap.put(i,excelSelectedResolve);
                }
            }
        }
    }
    //最后返回所有帶ExcelSelected注解字段的列值和ExcelSelectedResolve解析值的映射關系
    return selectedMap;
}

4.4 自定義的CustomSheetWriteHandler

public class CustomSheetWriteHandler extends AbstractSheetWriteHandler {
    
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();

        DataValidationHelper helper = sheet.getDataValidationHelper();
        
        //調用上述的方法，解析ExcelSelected注解的值
        Map<Integer, ExcelSelectedResolve> selectedResolveMap = selectedResolveMap = EasyExcelExportUtil.resolveSelectedAnnotations(ExcelData.class);

        if(CollectionUtil.isNotEmpty(selectedResolveMap)){
            selectedResolveMap.forEach((k,v) -> {
                //設置從第一行到最后一行，當前字段所在列的所有單元格樣式
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                //將設定好的下拉框值解析到excel
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getContents());
                //創建數據校驗規則
                DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
                //設置填入錯誤值時的報錯樣式
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                //是否顯示錯誤框
                validation.setShowErrorBox(true);
                //是否隱藏下拉箭頭
                validation.setSuppressDropDownArrow(true);
                //創建錯誤框
                validation.createErrorBox("提示","只能選擇下拉框中的值！");
                //將數據校驗規則設置進入sheet表格
                sheet.addValidationData(validation);
            });
        }
    }
}

4.5 一行代碼設置表頭凍結

//設置一二行凍結
sheet.createFreezePane(0,2,0,0);