需求

我们经常会遇到需要生成excel文件的需要，这个时候通常会使用apache提供的poi，但原生poi提供的api使用起来比较繁琐，所以我们实现利用注解的方式来快速封装

对象注入

@ExcelName 单页excel的名称
@ExcelRow 每行标题以及位置

@ExcelName("user list")
public class ReportData {
    @ExcelRow(headerName = "User Name", columnIndex = 0)
    private String username;
    @ExcelRow(headerName = "Age", columnIndex = 1)
    private int age;
    @ExcelRow(headerName = "Sex", columnIndex = 2)
    private String sex;
    @ExcelRow(headerName = "User Id", columnIndex = 3)
    private String userId;
}

调用封装方法

在封装的方法中传入对象的class，包含数据的list数组reportData，再输出到一个输出流就完成了

 ExcelUtil.newSingleExcel(ReportData.class)
                .fillData(reportData)
                .flush(outputStream);

具体代码实现

定义两个我们需要的注解

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelName {
    String value() default "";
}

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelRow {
    String headerName() default "";
    int columnIndex();
}

核心ExcelUtil类

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public final class ExcelUtil {
    public static InnerExcelClass newSingleExcel(Class<?> clazz) {
        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet(clazz.getAnnotation(ExcelName.class).value());
        initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook));

        return new InnerExcelClass(workbook, clazz);
    }

    // 这里先把excel的第一行设置为头，同时把样式设置好，字体加粗
    private static void initHeader(XSSFRow row, Class<?> clazz, XSSFCellStyle headerStyle) {
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            ExcelRow cellAnnotation = field.getAnnotation(ExcelRow.class);
            XSSFCell cell = row.createCell(cellAnnotation.columnIndex());
            cell.setCellStyle(headerStyle);
            cell.setCellValue(cellAnnotation.headerName());
        }
    }

    private static XSSFCellStyle getHeaderStyle(XSSFWorkbook workbook) {
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(font);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return headerStyle;
    }

    public static class InnerExcelClass {
        private final XSSFWorkbook workbook;
        private final Class<?> clazz;

        private final XSSFSheet sheet;
        private final XSSFCellStyle cellStyle;
        private final static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd");
        private final Logger logger = LogManager.getLogger(ExcelUtil.class);

        private InnerExcelClass(XSSFWorkbook xssfWorkbook, Class<?> clazz) {
            this.workbook = xssfWorkbook;
            this.clazz = clazz;

            this.sheet = workbook.getSheetAt(0);
            this.cellStyle = getCellStyle();
        }

        public InnerExcelClass fillData(List<?> reportData) {
            for (int index = 0; index < reportData.size(); index++) {
                XSSFRow row = sheet.createRow(index + 1);
                fillRowData(reportData.get(index), row);
            }
            // 填充完所有数据后， 把所有数据表格的宽度自适应一下
            autoSizeColumn();
            return this;
        }

        // 你可以输出到文件流，也可以是 httpresponse 的相应流
        void flush(OutputStream outputStream) throws IOException {
            workbook.write(outputStream);
        }

        private void fillRowData(Object reportData, XSSFRow row) {
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                boolean accessible = field.isAccessible();
                field.setAccessible(true);

                ExcelRow excelCell = field.getAnnotation(ExcelRow.class);
                XSSFCell cell = row.createCell(excelCell.columnIndex());
                getAndFillCellValue(reportData, field, cell);
                cell.setCellStyle(cellStyle);

                field.setAccessible(accessible);
            }
        }
        // 支持基础数据类型和 Date 时间类型，可以继续添加自定义类型
        private void getAndFillCellValue(Object reportData, Field field, XSSFCell cell) {
            Class<?> fieldClass = field.getType();
            try {
                if (fieldClass == Integer.class || fieldClass == int.class) {
                    int value = field.getInt(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Short.class || fieldClass == short.class) {
                    short value = field.getShort(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Long.class || fieldClass == long.class) {
                    long value = field.getShort(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == String.class) {
                    String value = field.get(reportData).toString();
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (fieldClass == Double.class || fieldClass == double.class) {
                    double value = field.getDouble(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Float.class || fieldClass == float.class) {
                    float value = field.getFloat(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Byte.class || fieldClass == byte.class) {
                    byte value = field.getByte(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Character.class || fieldClass == char.class) {
                    char value = field.getChar(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (fieldClass == Boolean.class) {
                    boolean value = field.getBoolean(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                } else if (fieldClass == Date.class) {
                    String value = DATE_FORMAT.format((Date) field.get(reportData));
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else {
                    throw new RuntimeException(fieldClass + " is not supported.");
                }
            } catch (NullPointerException npe) {
                // 如果没有拿到数据，报NPE，我们设置为空
                cell.setCellValue("");
                cell.setCellType(Cell.CELL_TYPE_STRING);
            } catch (IllegalAccessException e) {
                logger.error(e.getMessage());
            }
        }

        private void autoSizeColumn() {
            int columnCount = clazz.getDeclaredFields().length;
            while (columnCount > 0) {
                sheet.autoSizeColumn(--columnCount);
            }
        }

        private XSSFCellStyle getCellStyle() {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            return cellStyle;
        }
    }
}