POI+自定义注解+反射导出Excel（已测试）

实现效果

涉及的简单的报表导出时，如果每个导出都手动写是一件很麻烦的事情，尤其当导出的样式模板都是一样的时候，因此，这边使用自定义注解+反射来实现动态导出的效果，避免重复代码工作。主要就是了解poi对excel的描绘机制(从上到下：先描绘行，从左到右：再描绘列)，以行+列定义出cell；另外就是注解和反射的知识稍微了解一下。

maven依赖

    <!-- servlet -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>4.0.1</version>
    </dependency>

    <!-- poi 选择3.8之后 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.11-beta2</version>
    </dependency>

poi的选择

依赖选择：详细介绍了各个依赖包的作用

3.8+后的POI：简单对比了一下，开发环境下1000/10000/100000条数据测试是会快一点，代码编写使用时没有太大差别

代码编写

1.定义注解

首先定义注解，为了后续可以用于动态控制导出字段以及做反射取值。

/**
 * @Auth: chang
 * @Date: 2020/12/15
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface ExcelHeader {

    String name();

    int sort() default 10;

    String getMethod();
}

2.定义数据体

把导出的excel文件抽象当作一个model，正常来说会有 sheet、title、header 、data四个属性，主要就是sheet的值，标题，表头和数据。

import java.util.List;

/**
 * @Auth: chang
 * @Date: 2020/12/17
 */
public class ExcelModel<T> {

    /**
     * sheet 名称
     */
    private String sheetName;

    /**
     * 标题名称
     */
    private String titleName;

    /**
     * 数据体
     */
    private List<T> data;

    public ExcelModel(String sheetName, String titleName, List<T> data) {
        this.sheetName = sheetName;
        this.titleName = titleName;
        this.data = data;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public String getTitleName() {
        return titleName;
    }

    public void setTitleName(String titleName) {
        this.titleName = titleName;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }
}

3.帮助处理类

做完准备工作就差动态处理了，这里的嵌套循环+反射的代码可以再优化优化。其中定义了一些接口，用于存常量和做扩展。常量比较好理解，扩展则是jdk1.8版本后的接口增强有了default方法，spring里也出现了不少过时Adapter改用接口代替。

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Auth: chang
 * @Date: 2020/12/15
 */

public class ExcelTemplate<T> {

    /**
     * 标题 行起始 下标
     */
    private int T_R;

    /**
     * 标题 列起始 下标
     */
    private int T_C;

    /**
     * 标题 所占行数
     */
    private int T_H;

    /**
     * 数据 列起始 下标 默认==标题行起始+标题高度
     */
    private int D_R;

    /**
     * 数据 列起始 下标 默认==标题列起始
     */
    private int D_C;

    /**
     * 文件写入路径
     */
    private String filePath;

    /**
     * 文件名称
     */
    private String fileName;

    /**
     * 网络请求响应
     */
    private HttpServletResponse response;

    /**
     * 数据体
     */
    private ExcelModel<T> model;

    /**
     * 数据样式
     */
    private ExcelStyle excelStyle;

    /**
     * 可以接收一个自定义style
     * @see ExcelStyle 实现类
     * @param excelStyle 自定义style
     */
    public void setExcelStyle(ExcelStyle excelStyle) {
        this.excelStyle = excelStyle;
    }

    public ExcelTemplate (ExcelModel<T> model, String fileName, String filePath){
        this(model, fileName, filePath, null);
    }

    public ExcelTemplate (ExcelModel<T> model, String fileName, HttpServletResponse response){
        this(model, fileName, null, response);
    }

    public ExcelTemplate (ExcelModel<T> model, String fileName, String filePath, HttpServletResponse response){
        T_R = 0;
        T_C = 0;
        T_H = 1;
        D_R = 1;
        D_C = 0;
        this.fileName = fileName;
        this.filePath = filePath;
        this.model = model;
        this.response = response;
    }

    public ExcelTemplate (int t_R, int t_C, int t_H, ExcelModel<T> model, String fileName, String filePath, HttpServletResponse response){
        T_R = t_R;
        T_C = t_C;
        T_H = t_H;
        D_R = T_R + T_H;
        D_C = T_C;
        this.fileName = fileName;
        this.filePath = filePath;
        this.model = model;
        this.response = response;
    }
    
	/**
     * 这里是使用的 XSSFWorkbook
     */
    /*public void download() throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IOException {
        if (null == excelStyle){
            excelStyle = new ExcelStyle() {};
        }

        if (null == filePath && null == response){
            throw new RuntimeException("filePath和response不可同时为null");
        }
        if (null == model.getData() || model.getData().size() == 0){
            return;
        }
        //根据注解判断长度
        Class clazz = model.getData().get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<ExcelHeader> excelHeaders = Arrays.stream(fields)
                .filter(field -> field.isAnnotationPresent(ExcelHeader.class))
                .map(field -> field.getAnnotation(ExcelHeader.class))
                .sorted(Comparator.comparing(ExcelHeader::sort))
                .collect(Collectors.toList());
        //创建工作薄对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        //默认使用一个sheet
        XSSFSheet sheet = workbook.createSheet();
        //如果指定sheetName
        if (null != model.getSheetName()){
            workbook.setSheetName(0, model.getSheetName());
        }

        //绘制标题
        //1.起始行
        XSSFRow titleRow = sheet.createRow(T_R);
        //2.起始列
        XSSFCell titleCell = titleRow.createCell(T_C);
        //3.按表头长度合并
        sheet.addMergedRegion(new CellRangeAddress(T_R, T_R+T_H-1, T_C, T_C+excelHeaders.size()-1));
        //4.设置标题
        titleCell.setCellValue(model.getTitleName());
        //5.设置样式
        //titleCell.setCellStyle(excelStyle.getTitleStyle(workbook));

        //绘制表头
        //1.定义表头行 默认占一行
        XSSFRow tableHeader = sheet.createRow(D_R);
        //2.遍历描绘表头
        int t = 0;
        for (int i = T_C; i< T_C+excelHeaders.size(); i++){
            //表头格
            XSSFCell headerCell = tableHeader.createCell(i);
            //类型为字符串
            headerCell.setCellType(XSSFCell.CELL_TYPE_STRING);
            //取出定义表名
            XSSFRichTextString textString = new XSSFRichTextString(excelHeaders.get(t).name());
            //表头赋值
            headerCell.setCellValue(textString);
            //设置样式
            //headerCell.setCellStyle(excelStyle.getHeaderStyle(workbook));
            //为同步遍历headers
            t++;
        }

        //绘制表体 默认表头起始+1
        int height = model.getData().size() + D_R + 1;
        //遍历数据列表
        int i = 0;
        for (int row=D_R + 1; row<height; row++){
            //创建数据行
            XSSFRow dataRow = sheet.createRow(row);
            //遍历描绘数据
            int width = excelHeaders.size() + D_C;
            //遍历表头列表
            int j = 0;
            for (int col=D_C; col<width; col++){
                //描绘格子
                XSSFCell dataCell = dataRow.createCell(col);
                T entity = model.getData().get(i);
                String value = clazz.getMethod(excelHeaders.get(j).getMethod()).invoke(entity).toString();
                dataCell.setCellValue(value);
                //dataCell.setCellStyle(excelStyle.getCellStyle(workbook));
                j++;
            }
            i++;
        }

        //自适应列宽
        for (int start = T_C; start< T_C+ excelHeaders.size(); start++){
            sheet.autoSizeColumn(start);
        }

        if (null != filePath && !"".equals(filePath)){
            FileOutputStream outputStream = new FileOutputStream(filePath + fileName);
            workbook.write(outputStream);
            outputStream.close();
        }

        if (response != null) {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();
        }

    }*/
	
	/**
     * 这里是使用的 SXSSFWorkbook
     */
    public void download() throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IOException {

        if (null == filePath && null == response){
            throw new RuntimeException("filePath和response不可同时为null");
        }

        if (null == model.getData() || model.getData().size() == 0){
            return;
        }

        if (!fileName.contains(".")){
            fileName = fileName + ExcelConatant.XLSX_SUFFIX;
        }

        if (!fileName.toLowerCase().endsWith(ExcelConatant.XLS_SUFFIX)
            && !fileName.toLowerCase().endsWith(ExcelConatant.XLSX_SUFFIX)){
            throw new RuntimeException("文件后缀只能是xls或xlsx");
        }

        if (null == excelStyle){
            excelStyle = new ExcelStyle() {};
        }
        //根据注解判断长度
        Class clazz = model.getData().get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<ExcelHeader> excelHeaders = Arrays.stream(fields)
                .filter(field -> field.isAnnotationPresent(ExcelHeader.class))
                .map(field -> field.getAnnotation(ExcelHeader.class))
                .sorted(Comparator.comparing(ExcelHeader::sort))
                .collect(Collectors.toList());
        //创建工作薄对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //默认使用一个sheet
        Sheet sheet = workbook.createSheet();
        //如果指定sheetName
        if (null != model.getSheetName()){
            workbook.setSheetName(0, model.getSheetName());
        }

        //绘制标题
        //1.起始行
        Row titleRow = sheet.createRow(T_R);
        //2.起始列
        Cell titleCell = titleRow.createCell(T_C);
        //3.按表头长度合并
        sheet.addMergedRegion(new CellRangeAddress(T_R, T_R+T_H-1, T_C, T_C+excelHeaders.size()-1));
        //4.设置标题
        titleCell.setCellValue(model.getTitleName());
        //5.设置样式
        titleCell.setCellStyle(excelStyle.getTitleStyle(workbook));

        //绘制表头
        //1.定义表头行 默认占一行
        Row tableHeader = sheet.createRow(D_R);
        //2.遍历描绘表头
        int t = 0;
        for (int i = T_C; i< T_C+excelHeaders.size(); i++){
            //表头格
            Cell headerCell = tableHeader.createCell(i);
            //类型为字符串
            headerCell.setCellType(XSSFCell.CELL_TYPE_STRING);
            //取出定义表名
            XSSFRichTextString textString = new XSSFRichTextString(excelHeaders.get(t).name());
            //表头赋值
            headerCell.setCellValue(textString);
            //设置样式
            headerCell.setCellStyle(excelStyle.getCellStyle(workbook, (short) 10));
            //为同步遍历headers
            t++;
        }

        //绘制表体 默认表头起始+1
        int height = model.getData().size() + D_R + 1;
        //遍历数据列表
        int i = 0;
        for (int row=D_R + 1; row<height; row++){
            //创建数据行
            Row dataRow = sheet.createRow(row);
            //遍历描绘数据
            int width = excelHeaders.size() + D_C;
            //遍历表头列表
            int j = 0;
            for (int col=D_C; col<width; col++){
                //描绘格子
                Cell dataCell = dataRow.createCell(col);
                T entity = model.getData().get(i);
                String value = clazz.getMethod(excelHeaders.get(j).getMethod()).invoke(entity).toString();
                dataCell.setCellValue(value);
                dataCell.setCellStyle(excelStyle.getCellStyle(workbook, (short) 9));
                j++;
            }
            i++;
        }

        //自适应列宽
        for (int start = T_C; start< T_C+ excelHeaders.size(); start++){
            sheet.autoSizeColumn(start);
        }

        //写入磁盘空间
        if (null != filePath && !"".equals(filePath)){
            FileOutputStream outputStream = new FileOutputStream(filePath + fileName);
            workbook.write(outputStream);
            outputStream.close();
        }

        //写入response
        if (response != null) {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();
        }
    }

}

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

/**
 * @Auth: chang
 * @Date: 2020/12/17
 */
public interface ExcelStyle {

    default CellStyle getTitleStyle(Workbook workbook){
        //设置字体
        Font font = workbook.createFont();
        font.setBoldweight((short) 400);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        //设置样式
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        style.setFillBackgroundColor((short) 1);
        return style;
    }

    default CellStyle getCellStyle(Workbook workbook, short fontSize){
        //设置字体
        Font font = workbook.createFont();
        font.setBoldweight((short) 400);
        font.setFontName("宋体");
        font.setFontHeightInPoints(fontSize);
        //设置样式
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

/**
 * @Auth: chang
 * @Date: 2020/12/17
 */
public interface ExcelConatant {

    String XLS_SUFFIX = ".xls";

    String XLSX_SUFFIX = ".xlsx";
}

4.简单说明

个人感觉代码还是比较好理解的，和开头说的差不多，主要是利用注解+反射来动态定义导出字段和获取值，再利用poi去做绘制。这里留下了一个Style的接口可以自定义实现，便于不同的需求；然后自定义起始绘制坐标等等，有需要多个sheet也可以copy再自己实现一下。

演示

import excel.ExcelHeader;

/**
 * @Auth: chang
 * @Date: 2020/12/16
 */
public class User {

    @ExcelHeader(name = "姓名", sort = 3, getMethod = "getName")
    private String name;

    @ExcelHeader(name = "年龄", sort = 5, getMethod = "getAge")
    private int age;

    @ExcelHeader(name = "住址", sort = 1, getMethod = "getAddr")
    private String addr;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }
}

import excel.ExcelModel;
import excel.ExcelTemplate;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Auth: chang
 * @Date: 2020/12/16
 */
public class Test {

    public static void main(String[] args) throws Exception{
        List<User> users = new ArrayList<>();
        for (int i=0; i<1000; i++){
            User user = new User();
            user.setName("用户"+i);
            user.setAge(18+i);
            user.setAddr("家庭住址"+i);
            users.add(user);
        }

        ExcelModel<User> model = new ExcelModel<>("文档", "12月用户信息档案", users);
        ExcelTemplate template = new ExcelTemplate<>(model, "测试文档3", "F:\\项目文档\\项目文档\\");
        System.out.println(new Date().toString());
        template.download();
        System.out.println(new Date().toString());
    }
}

导出效果图

这里可以自定义起始位置，代码有标注。
练练手，很多基础知识都忘了或者开始就没掌握牢，本来想模仿一下mybatis的反射，但是有点复杂了些，想着后面可能再优化优化，再加个解析的方法。