自定义注解导出Excel，可导出多个Sheet

1、定义 @ExcelCol 注解

package com.ly.education.elective.api.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 *
 * FIELD:用于描述域
 * RUNTIME: 在运行时有效（即运行时保留）
 * @auther: fly
 * @date: 2019/10/5 10:59
 * @param:
 * @return:
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelCol {

    /**
     * 列名称
     * @return
     */
    String name() default "";

    /**
     * 列宽度
     * @return
     */
    int length() default 15;

    /**
     * 列排序
     * @return
     */
    int colNum() default 0;

}

2、创建 ExcelUtil 工具类

package com.ly.education.elective.server.utils;

import com.ly.education.elective.api.annotation.ExcelCol;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelUtils
 * @Description:
 * @Author fly
 * @Date 2019/10/5 9:40
 */
@Slf4j
public class ExcelUtils {

    /**
     * 文件后缀名: .xls
     */
    private final static String EXCEL2003 = "xls";

    /**
     * 文件后缀名: .xlsx
     */
    private final static String EXCEL2007 = "xlsx";

    /**
     *
     * 功能描述: 导出 excel
     * @auther: fly
     * @date: 2019/10/5 11:12
     * @param: response     HttpServletResponse
     * @param: dataList        每个sheet数据源集合
     * @param: titleNameList    title 标题集合名称
     * @param: sheetNameList    sheet 集合名称
     * @param: cls    实体类
     * @param: filePath    导出 excel 文件路径
     * @return:
     */
    public static <T> void exportExcel(HttpServletResponse response, List<List<T>> dataList, List<String> titleNameList,
                                       List<String> sheetNameList, Class<T> cls, String filePath) {
        // 该类属性数组
        Field[] fields = cls.getDeclaredFields();

        // 应用了 @ExcelCol 注解的属性, 且 colNum 大于 0 的属性集合
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelCol annotation = field.getAnnotation(ExcelCol.class);
                    // 该属性有引用该注解, 且列排序号大于 0
                    if (annotation != null && annotation.colNum() > 0) {
                        // 当该字段用 private 修饰时 isAccessible() 得到的值是 false, 必须要改成 true 才可以访问
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelCol annotation = field.getAnnotation(ExcelCol.class);
                    if (annotation != null) {
                        col = annotation.colNum();
                    }
                    return col;
                })).collect(Collectors.toList());

        Workbook workbook = new XSSFWorkbook();

        if (CollectionUtils.isNotEmpty(dataList)) {
            AtomicInteger ak = new AtomicInteger();
            // 循环 sheet
            dataList.forEach(list -> {
                // sheet 名称
                String sheetName = "";
                int akIndex = ak.getAndIncrement();
                if (sheetNameList != null && akIndex < sheetNameList.size()) {
                    sheetName = sheetNameList.get(akIndex);
                } else {
                    sheetName = "sheet" + akIndex;
                }
                Sheet sheet = workbook.createSheet(sheetName);
                AtomicInteger ai = new AtomicInteger();

                // excel 标题
                if (titleNameList != null && akIndex < titleNameList.size()) {
                    // 创建合并单元格
                    CellRangeAddress region = new CellRangeAddress(0, 0, 0, fieldList.size() - 1);
                    // 在 sheet 里增加合并单元格
                    sheet.addMergedRegion(region);
                    Row titleRow = sheet.createRow(ai.getAndIncrement());

                    Cell cell = titleRow.createCell(0);

                    CellStyle cellStyle = workbook.createCellStyle();

                    // 水平居中
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                    // 垂直居中
                    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

                    Font font = workbook.createFont();
                    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    font.setFontHeightInPoints((short) 20);
                    cellStyle.setFont(font);

                    String titleName = "";
                    if (titleNameList != null && akIndex < titleNameList.size()) {
                        titleName = titleNameList.get(akIndex);
                    } else {
                        titleName = "标题";
                    }

                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(titleName);
                }

                // 列标题
                {
                    // 创建新的一行
                    Row row = sheet.createRow(ai.getAndIncrement());
                    AtomicInteger aj = new AtomicInteger();
                    // 写入标题
                    fieldList.forEach(field -> {
                        ExcelCol annotation = field.getAnnotation(ExcelCol.class);
                        String columnName = "";
                        if (annotation != null) {
                            columnName = annotation.name();
                            sheet.setColumnWidth(annotation.colNum() - 1, annotation.length() * 256);
                        }
                        // 创建新的一列
                        Cell cell = row.createCell(aj.getAndIncrement());

                        CellStyle cellStyle = workbook.createCellStyle();
                        // 水平居中
                        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                        // 垂直居中
                        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);


                        Font font = workbook.createFont();
                        // 字体
                        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                        cellStyle.setFont(font);

                        // 列样式
                        cell.setCellStyle(cellStyle);
                        // 列名称
                        cell.setCellValue(columnName);
                    });
                }

                // 循环数据集合
                if (CollectionUtils.isNotEmpty(list)) {
                    list.forEach(t -> {
                        // 创建新的一行
                        Row row1 = sheet.createRow(ai.getAndIncrement());
                        AtomicInteger aj = new AtomicInteger();

                        // 循环属性集合
                        fieldList.forEach(field -> {
                            // 属性类型
                            Class<?> type = field.getType();
                            Object value = "";
                            try {
                                // 属性值
                                value = field.get(t);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                            Cell cell = row1.createCell(aj.getAndIncrement());

                            CellStyle cellStyle = workbook.createCellStyle();
                            // 水平居中
                            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                            // 垂直居中
                            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

                            cell.setCellStyle(cellStyle);
                            if (value != null) {
                                // 如果属性类型为 Date
                                if (type == Date.class) {
                                    cell.setCellValue(value.toString());
                                } else {
                                    cell.setCellValue(value.toString());
                                }
                            }
                        });
                    });
                }
            });
        }

        String sheet = "";
        if (sheetNameList != null && !sheetNameList.isEmpty()) {
            sheet = sheetNameList.get(0);
        } else {
            sheet = "sheet0";
        }
        // 浏览器下载 excel
        buildExcelDocument("abbot.xlsx", workbook, response, filePath);
    }

    /**
     *
     * 功能描述: 浏览器下载 Excel
     * @auther: fly
     * @date: 2019/10/5 11:40
     * @param:
     * @return:
     */
    private static void buildExcelDocument(String fileName, Workbook workbook, HttpServletResponse response, String filePath) {
        try {

            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

3、在实体类的属性上标上注解

package com.ly.education.elective.api.vo;

import com.ly.education.elective.api.annotation.ExcelCol;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.io.Serializable;

/**
 * @author fly
 *
 */
@Getter
@Setter
@ToString
public class CourseSelectionListVo implements Serializable {

    /**
     * 序列号
     */
    private static final long serialVersionUID = -9003609292510835997L;

    /**
     * 学生学号
     */
    @ExcelCol(name = "学号", colNum = 1)
    private String studentCode;

    /**
     * 学生姓名
     */
    @ExcelCol(name = "姓名", colNum = 2)
    private String studentName;

    /**
     * 学生行政班级名称
     */
    @ExcelCol(name = "行政班级", colNum = 5)
    private String administrativeClassName;

    /**
     * 专业名称
     */
    @ExcelCol(name = "年级专业", colNum = 4, length = 25)
    private String gradeMajorName;

    /**
     * 学生学院名称
     */
    @ExcelCol(name = "学院", colNum = 3)
    private String studentDepartmentName;

     
}