基于poi和JavaBean的excel导出

写在前面

• 示例写出时间：2022-08-03

这仅仅是excel导出的一个示例, 记录一下

poi依赖

其他spring、lombok等依赖就不列举了

<dependency>
    <groupId>org.apache.poigroupId>
    <artifactId>poiartifactId>
    <version>4.1.0version>
dependency>

<dependency>
    <groupId>org.apache.poigroupId>
    <artifactId>poi-ooxmlartifactId>
    <version>4.1.0version>
dependency>
1
2
3
4
5
6
7
8
9
10
11

核心类

注解ExcelColumn

package com.littlehow.excel;

import java.lang.annotation.*;

/**
 * excel字段注解
 * @author littlehow
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface ExcelColumn {
    /**
     * @return - 表头
     */
    String title();

    /**
     * @return - 顺序
     */
    int order();

    /**
     * @return 日期格式化
     */
    String format() default "";

    /**
     * @return 行高
     */
    short height() default 450;

    /**
     * @return - 列宽
     */
    int width() default 4800;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

列注解属性ExcelColumnMeta

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;

/**
 *  列的基本属性
 * @author littlehow
 */
@Setter
@Getter
@Accessors(chain = true)
public class ExcelColumnMeta {
    /**
     * 表头
     */
    private String title;

    /**
     * 行高
     */
    private short height;

    /**
     * 列宽
     */
    private int width;

    /**
     * 日期格式化
     */
    private DateTimeFormatter formatter;

    public String getDateString(TemporalAccessor accessor) {
        if (formatter != null) {
            return formatter.format(accessor);
        }
        return accessor.toString();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

列字段属性ExcelMetaData

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.temporal.TemporalAccessor;

/**
 * 字段属性以及基本获取值的方法
 * @author littlehow
 */
@Setter
@Getter
public class ExcelMetaData {
    /**
     * 字段
     */
    private Field field;

    /**
     * 对应的get方法
     */
    private Method method;

    /**
     * 排序
     */
    private int order;

    /**
     * 日期顶级接口
     */
    private boolean isTemporalAccessor = false;

    /**
     * 是否为decimal类型
     */
    private boolean isDecimal = false;

    /**
     * 字段属性
     */
    private ExcelColumnMeta columnMeta;

    /**
     * 获取对象的值，做简单类型的格式化
     * @param obj - 对象
     * @return - obj对应该字段的格式化值
     */
    public String getString(Object obj) {
        if (obj == null) {
            return "";
        }
        Object value = getValue(obj);
        if (value == null) {
            return "";
        }

        if (isTemporalAccessor) {
            return columnMeta.getDateString((TemporalAccessor) value);
        } else if (isDecimal) {
            return ((BigDecimal) value).stripTrailingZeros().toPlainString();
        } else {
            return value.toString();
        }
    }

    /**
     * 获取对象的值, 优先get方法获取，没有get方法的将使用field获取，所以如果没有提供get方法，需要将字段访问属性设置为public
     * 这里就不对字段进行field.setAccessible设置
     * @param obj  - 对象
     * @return - 对应对应的值
     */
    private Object getValue(Object obj) {
        try {
            if (method != null) {
                return method.invoke(obj);
            } else {

                return field.get(obj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

excel导出属性ExcelMeta

package com.littlehow.excel;

import lombok.Getter;
import lombok.Setter;

import java.util.List;

/**
 * 记录表宽度、表头、行高以及excel字段信息
 * @author littlehow
 */
@Setter
@Getter
public class ExcelMeta {
    /**
     * 列宽
     */
    private List<Integer> width;

    /**
     * 行高
     */
    private short height;

    /**
     * 表头
     */
    private List<String> header;

    /**
     * 字段属性集
     */
    private List<ExcelMetaData> excelMetaData;
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

excel属性缓存类ExcelClassCache

package com.littlehow.excel;

import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author littlehow
 */
public class ExcelClassCache {
    private static Map<Class, ExcelMeta> EXCEL_MATE_CACHE = new HashMap<>();

    /**
     * 获取meta配置数据
     * @param clazz - 类
     * @return  -
     */
    static ExcelMeta getMeta(Class clazz) {
        ExcelMeta meta = EXCEL_MATE_CACHE.get(clazz);
        if (meta != null) {
            return meta;
        }
        meta = new ExcelMeta();
        // 解析class
        List<Field> fields = new ArrayList<>();
        getAllField(fields, clazz);
        List<ExcelMetaData> metaData = new ArrayList<>();
        fields.forEach(o -> {
            if (o.isAnnotationPresent(ExcelColumn.class)) {
                ExcelMetaData excelMetaData = new ExcelMetaData();
                ExcelColumn excelColumn = o.getAnnotation(ExcelColumn.class);
                ExcelColumnMeta columnMeta = new ExcelColumnMeta()
                        .setHeight(excelColumn.height())
                        .setTitle(excelColumn.title())
                        .setWidth(excelColumn.width());
                if (TemporalAccessor.class.isAssignableFrom(o.getType())) {
                    excelMetaData.setTemporalAccessor(true);
                    String format = excelColumn.format();
                    if (StringUtils.hasText(format)) {
                        columnMeta.setFormatter(DateTimeFormatter.ofPattern(format));
                    }
                } else if (o.getType() == BigDecimal.class) {
                    excelMetaData.setDecimal(true);
                }
                excelMetaData.setField(o);
                excelMetaData.setMethod(getMethod(o, clazz));
                excelMetaData.setColumnMeta(columnMeta);
                excelMetaData.setOrder(excelColumn.order());
                metaData.add(excelMetaData);
            }
        });
        Assert.notEmpty(metaData, "excel config not found");
        metaData.sort(Comparator.comparing(ExcelMetaData::getOrder));
        meta.setExcelMetaData(metaData);
        meta.setWidth(metaData.stream().map(o -> o.getColumnMeta().getWidth()).collect(Collectors.toList()));
        meta.setHeight(metaData.get(0).getColumnMeta().getHeight());
        meta.setHeader(metaData.stream().map(o -> o.getColumnMeta().getTitle()).collect(Collectors.toList()));
        EXCEL_MATE_CACHE.put(clazz, meta);
        return meta;
    }

    /**
     * 获取所有字段，包括父类的字段
     * @param fieldList -
     * @param clazz -
     */
    private static void getAllField(List<Field> fieldList, Class clazz) {
        if (clazz == Object.class) {
            return;
        }
        Field[] fields = clazz.getDeclaredFields();
        fieldList.addAll(Arrays.asList(fields));
        getAllField(fieldList, clazz.getSuperclass());

    }

    /**
     * 获取字段对应的get方法
     * @param field -
     * @param clazz -
     * @return -
     */
    private static Method getMethod(Field field, Class clazz) {
        try {
            String name = field.getName();
            if (name.length() > 1) {
                return  clazz.getMethod("get" + name.substring(0, 1).toUpperCase() + name.substring(1));
            } else {
                return clazz.getMethod("get" + name.toUpperCase());
            }
        } catch (Exception e) {
            // skip;
            return null;
        }
    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

excel导出类Excel

package com.littlehow.excel;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * excel导出工具
 * @author littlehow
 */
public class Excel<T> {
    private SXSSFWorkbook workbook;

    private SXSSFSheet sheet;

    private int rowIndex = 0;

    private boolean create = false;

    private ExcelMeta excelMeta;

    private Excel() {
        // 不开放
    }

    /**
     * 构建excel导出类
     * @param clazz - 导出类型
     * @param  -
     * @return - 导出类
     */
    public static <T> Excel<T> build(Class<T> clazz) {
        Excel<T> excel = new Excel<>();
        excel.excelMeta = ExcelClassCache.getMeta(clazz);
        excel.workbook = new SXSSFWorkbook();
        excel.sheet = excel.workbook.createSheet();
        // 设置列头以及列宽
        excel.fullHeader();
        return excel;
    }

    /**
     * 填充类容
     * @param content - 类容
     */
    public Excel<T> write(List<T> content) {
        Assert.isTrue(!create, "excel already created");
        if (CollectionUtils.isEmpty(content)) {
            return this;
        }
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        content.forEach(t -> {
            // 创建表头
            SXSSFRow row = sheet.createRow(this.rowIndex++);
            row.setHeight(excelMeta.getHeight());
            setContent(row, getContent(t), style);
        });
        return this;
    }

    /**
     * 输出 本方法没有关闭外部输出流
     * @param os - 输出流
     * @throws IOException  - io异常
     */
    public void create(OutputStream os) throws IOException {
        workbook.write(os);
        create = true;
    }


    /**
     * 填充表头以及设置列宽
     */
    private void fullHeader() {

        setColumnWidth(this.sheet, excelMeta.getWidth());
        // 设置表头
        SXSSFRow header = this.sheet.createRow(this.rowIndex++);
        header.setHeight(excelMeta.getHeight());
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        setContent(header, excelMeta.getHeader(), style);
    }

    /**
     * 获取字段内容
     * @param t - 对象
     * @return -
     */
    private List<String> getContent(T t) {
        List<ExcelMetaData> excelMetaData = excelMeta.getExcelMetaData();
        List<String> values = new ArrayList<>();
        excelMetaData.forEach(data -> values.add(data.getString(t)));
        return values;
    }


    /**
     * 设置内容
     * @param row        - 行
     * @param content    - 内容
     * @param style      - 样式
     */
    private static void setContent(SXSSFRow row, List<String> content, XSSFCellStyle style) {
        // 设置第一行
        for (int i = 0, len = content.size(); i < len; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(content.get(i));
            // 创建一个单元格样式
            cell.setCellStyle(style);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
    }


    /**
     * 设置列宽
     * @param sheet          - sheet
     * @param columnWidths   - 列宽配置
     */
    private static void setColumnWidth(SXSSFSheet sheet, List<Integer> columnWidths) {
        for (int i = 0, len = columnWidths.size(); i < len; i++) {
            sheet.setColumnWidth(i, columnWidths.get(i));
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

测试类

测试对象父类Biology

package com.littlehow.excel.test;

import com.littlehow.excel.ExcelColumn;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDateTime;

/**
 * 生物对象
 * @author littlehow
 */
@Setter
@Getter
public class Biology {
    @ExcelColumn(title = "发布时间", order = 9, format = "yyyy-MM-dd HH:mm:ss", width = 5200)
    private LocalDateTime time;
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

测试对象子类Person

package com.littlehow.excel.test;

import com.littlehow.excel.ExcelColumn;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.math.BigDecimal;
import java.time.LocalDate;

/**
 * @author littlehow
 */
@Getter
@Setter
@Accessors(chain = true)
public class Person extends Biology {

    @ExcelColumn(title = "用户编号", order = 2)
    private String userId;

    @ExcelColumn(title = "生日", order = 1, format = "yyyyMMdd")
    private LocalDate birthday;

    @ExcelColumn(title = "年龄", order = 4)
    private Integer age;

    @ExcelColumn(title = "公司", order = 6)
    private String company;

    @ExcelColumn(title = "存款", order = 5)
    private BigDecimal balance;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

导出测试类ExcelTest

package com.littlehow.excel.test;

import com.littlehow.excel.Excel;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @author littlehow
 */
public class ExcelTest {
    public static void main(String[] args) throws Exception {
        List<Person> person = new ArrayList<>();
        person.add(new Person().setAge(18).setBirthday(LocalDate.now().minusYears(18)).setUserId("101").setCompany("正义公司").setBalance(BigDecimal.valueOf(123.5500)));
        person.add(new Person().setAge(20).setBirthday(LocalDate.now().minusYears(20)).setUserId("104").setCompany("正义公司").setBalance(BigDecimal.valueOf(88891.102900)));
        person.add(new Person().setAge(22).setBirthday(LocalDate.now().minusYears(22)).setUserId("110").setCompany("正义公司").setBalance(BigDecimal.valueOf(123.5570)));
        person.add(new Person().setAge(24).setBirthday(LocalDate.now().minusYears(24)).setUserId("120").setCompany("正义公司").setBalance(BigDecimal.valueOf(3333)));
        person.forEach(o -> o.setTime(LocalDateTime.now().minusDays(5)));
        Excel<Person> excel = Excel.build(Person.class);
        excel.write(person)
                 // 这里可以多次写入，所以不需要一次性将所有数据都加载到内存
                .write(person)
                .write(person)
                // 这里就会生成对应的excel文件
                .create(new FileOutputStream("/Users/littlehow/Desktop/person.xlsx"));
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

导出结果