SpringBoot使用EasyExcel导出Excel(含设置下拉框、表头冻结)

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);