EasyExcel的使用（包含动态表头）

EasyExcel的简单介绍

官方文档：https://easyexcel.opensource.alibaba.com/docs/current/api/
依赖：

		<dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
        dependency>
1
2
3
4

常用的注解

写Excel

最终成果：
首先最重要的就是Excel实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import java.util.Date;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * @Author: yangypeng
 * @date: 2022/11/11 22:25
 * @Version: 1.0
 * @Description:
 */
@Data
@EqualsAndHashCode
public class VoteExcel {

    @ExcelProperty({"投票主题","描述","投票时间","序号"})
    private Integer id;

    @ExcelProperty({"${title}","${describe}","${voteTime}","投票时间"})
    @DateTimeFormat("yyyy-MM-dd HH:mm::ss")
    private Date voteTime;

    @ExcelProperty({"${title}","${describe}","${voteTime}","投票人"})
    private String voteName;

    @ExcelProperty({"${title}","${describe}","${voteTime}","${title}"})
    private String voteOption;
}
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

在这个实体类内可以给每一个字段加上标题，可以给对应的值加一些转换等。在这里我采用了${title}的占位符，之后会用实际值代替这个占位符。

替换标题中的占位符

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;

/**
 * @Author: yangypeng
 * @date: 2022/11/14 21:17
 * @Version: 1.0
 * @Description:
 */
public class VoteTitleHandler implements CellWriteHandler {

    private String title;

    private String describe;

    private Date voteTime;

    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public VoteTitleHandler(String title,String describe, Date voteTime) {
        this.title = title;
        this.describe = describe;
        this.voteTime = voteTime;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head != null) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                properties.setProperty("title", title);
                properties.setProperty("describe", describe);
                properties.setProperty("voteTime",
                        new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(voteTime));
                for (int i = 0; i < headNameList.size(); i++) {
                    headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
                }
            }
        }
    }

}
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

重写CellWriteHandler中的beforeCellCreate，将占位符替换掉。

通用的方法

    public static String exportVoteExcel(String fileName, String sheetName, Class clazz,
            List dataList, String title, String describe, Date voteDate) throws IOException {
        File result = File.createTempFile(fileName, ".xlsx",new File("/Users/yangyapeng/IdeaProjects/myProject/"));
        EasyExcel.write(result)
                .head(clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .registerWriteHandler(new VoteTitleHandler(title, describe, voteDate))
                .sheet(sheetName)
                .doWrite(dataList);
        return null;
    }
1
2
3
4
5
6
7
8
9
10
11

Test类

    public static void main(String[] args) throws IOException {
        List<VoteExcel> data = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            VoteExcel topicExcel = new VoteExcel();
            topicExcel.setId(i);
            topicExcel.setVoteTime(new Date());
            topicExcel.setVoteName("name" + i);
            topicExcel.setVoteOption("b");
            data.add(topicExcel);
        }
        ExcelUtils.exportVoteExcel("测试导出excel表","sheet1", VoteExcel.class, data,
                "测试","测试这次是否成功", new Date());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13