EasyPoi实现excel文件导入导出_easypoi导出excel

EasyPoi学习实践

1 简介

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法.

gitee开源: https://gitee.com/lemur/easypoi

官方文档1: http://easypoi.mydoc.io/

官方文档2: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8

B站视频: https://www.bilibili.com/video/BV1Uz4y1f7un

2 使用EasyPoi

2.1 环境搭建

引入相关依赖

<dependencies>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.4.0</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.30</version>
            <scope>test</scope>
        </dependency>
   
    </dependencies>
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

2.2 相关注解

easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应, model–row,filed–col 这样利用注解我们可以很容易做到excel的导入导出. 经过一段时间发展,现在注解有5个类分别是:

• @Excel 作用到filed上面, 是对Excel一列的一个描述
• @ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
• @ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
• @ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导出
• @ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理

@ExcelTarget

用在实体类上标识一个可以通过EasyPoi导入导出的实体,相关属性:

@Excel

这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,涵盖了常用的Excel需求, 主要分为基础,图片处理,时间处理,合并处理几块.

@ExcelEntity

标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id

@ExcelCollection

一对多的集合注解,用以标记集合数据以及集合的整体排序

@ExcelIgnore

忽略这个属性, 被标识的属性不会导出到Excel文件中 ,多使用需循环引用中

2.3 导出Excel

2.3.1 导出基本数据

定义对象, 数据模板必须实现序列化接口 Serializable

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("users")
public class UserExcel implements Serializable {
    @Excel(name = "编号", orderNum = "1", width = 10)
    private Integer id;
    @Excel(name = "姓名", orderNum = "2", width = 30)
    private String name;
    @Excel(name = "年龄", orderNum = "4", width = 10)
    private Integer age;
    @Excel(name = "性别", orderNum = "5", width = 10)
    private String gender;
    @Excel(name = "生日", orderNum = "3", width = 20.0, 
           exportFormat = "yyyy年MM月dd日", importFormat = "yyyy年MM月dd日")
    private Date birthday;
    // lombok自动生成setter/getter方法
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

数据准备

public List<UserExcel> getUserExcelData() {

    List<UserExcel> userExcels = new ArrayList<>();

    for (int i = 1; i <= 10; i++) {
        UserExcel user = UserExcel.builder()
                .id(i)
                .name("admin" + i)
                .age(i * 10)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .build();
        userExcels.add(user);
    }
    return userExcels;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

导出Excel代码

@Test
public void testWriteExcel() throws IOException {
    // 准备数据
    List<UserExcel> userExcels = getUserExcelData();
    // 导出
    ExportParams params = new ExportParams();
    params.setTitle("远大公司");
    params.setSheetName("用户信息");
    Workbook workbook = ExcelExportUtil.exportExcel(params, UserExcel.class, userExcels);
    // 指定写出的文件
    FileOutputStream outputStream = new FileOutputStream("D:\\study\\excel\\easypoi.xlsx");
    workbook.write(outputStream);
}
1
2
3
4
5
6
7
8
9
10
11
12
13

效果:

2.3.2 导出指定字段

使用@ExcelIgnore注解可以忽略字段属性, 不导出到Excel中

@ExcelIgnore
private Integer id;
1
2

导出Excel代码与上面一样.

效果:

2.3.3 导出list集合

有时候导出的对象中含有数组或集合, 需要导出这样的数据可以直接使用@Excel进行导出. 此处是导出List<String>类型.

定义属性模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("users")
public class UserExcel implements Serializable {
    @Excel(name = "编号", orderNum = "1", width = 10)
//    @ExcelIgnore
    private Integer id;
    @Excel(name = "姓名", orderNum = "2", width = 30)
    private String name;
    @Excel(name = "年龄", orderNum = "4", width = 10)
    private Integer age;
    @Excel(name = "性别", orderNum = "5", width = 10)
    private String gender;
    @Excel(name = "生日", orderNum = "3", width = 20.0, 
           exportFormat = "yyyy年MM月dd日", importFormat = "yyyy年MM月dd日")
    private Date birthday;
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private List<String> hobbys; // 集合字段
    // lombok自动生成setter/getter方法
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

数据准备

public List<UserExcel> getUserExcelData() {
    List<UserExcel> userExcels = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        UserExcel user = UserExcel.builder()
                .id(i)
                .name("admin" + i)
                .age(i * 10)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .hobbys(Arrays.asList("打篮球", "听英语", "看书"))
                .build();
        userExcels.add(user);
    }
    return userExcels;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

导出Excel代码与上面一样.

效果:

上面的效果是默认格式, 我们可以自定义集合输出格式, 通过小技巧实现.

修改数据模板

//@Excel(name = "爱好", orderNum = "6", width = 20.0)
@ExcelIgnore
private List<String> hobbys;
@Excel(name = "爱好", orderNum = "6", width = 20.0)
private String hobbyStr;
// lombok自动生成setter/getter方法
// 重写getHobbyStr()方法, 将爱好通过hobbyStr字段导出, 输出格式通过遍历hobbys处理
public String getHobbyStr() {
    StringBuilder sb = new StringBuilder();
    hobbys.forEach(e -> {
        sb.append(e).append(", ");
    });
    return sb.toString();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

效果:

2.3.4 导出一对一关系(对象)

案例1: 导出用户(身份)信息

用户-身份信息card-一对一关系

用户信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("users")
public class UserExcel implements Serializable {
    @Excel(name = "编号", orderNum = "1", width = 10)
//    @ExcelIgnore
    private Integer id;
    @Excel(name = "姓名", orderNum = "2", width = 20)
    private String name;
    @Excel(name = "年龄", orderNum = "4", width = 10)
    private Integer age;
    @Excel(name = "性别", orderNum = "5", width = 10)
    private String gender;
    @Excel(name = "生日", orderNum = "3", width = 20.0,format = "yyyy年MM月dd日")
    private Date birthday;
    //    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    @ExcelIgnore
    private List<String> hobbys;
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private String hobbyStr;
    @ExcelEntity // 标识一对一的关系
    private Card card;
    // lombok自动生成setter/getter方法
    // 重写 getHobbyStr()方法
    public String getHobbyStr() {
        StringBuilder sb = new StringBuilder();
        hobbys.forEach(e -> {
            sb.append(e).append(", ");
        });
        return sb.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

身份信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget(value = "card")
public class Card implements Serializable {
    @Excel(name = "身份证号码", width = 25.0, orderNum = "7", needMerge = true)
    private String idNo;
    @Excel(name = "籍贯", orderNum = "8", needMerge = true)
    private String address;
}
1
2
3
4
5
6
7
8
9
10
11

数据准备

public List<UserExcel> getUserExcelData() {
    List<UserExcel> userExcels = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        UserExcel user = UserExcel.builder()
                .id(i)
                .name("admin" + i)
                .age(i * 10)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .hobbys(Arrays.asList("打篮球", "听英语", "看书"))
                .card(new Card("43082119930505" + i, "广东深圳"))
                .orders(orders)
                .build();
        userExcels.add(user);
    }
    return userExcels;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

导出Excel代码

@Test
public void testWriteExcel() throws IOException {
    // 准备数据
    List<UserExcel> userExcels = getUserExcelData();
    // 导出
    ExportParams params = new ExportParams();
    params.setTitle("远大公司用户信息列表");
    params.setSheetName("用户信息");
    Workbook workbook = ExcelExportUtil.exportExcel(params, UserExcel.class, userExcels);
    // 指定写出的文件
    FileOutputStream outputStream = new FileOutputStream("D:\\study\\excel\\easypoi.xlsx");
    workbook.write(outputStream);
}
1
2
3
4
5
6
7
8
9
10
11
12
13

效果:

案例2: 导出课程(老师)信息

课程-老师teacher-一对一关系

课程信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("courseEntity")
public class CourseEntity {
    /** 主键 */
    private Integer id;
    /** 课程名称 */
    @Excel(name = "课程名称", orderNum = "1", width = 25)
    private String name;
    /** 老师主键 */
    @ExcelEntity(id = "absent") //代课老师
   // @ExcelEntity(id = "major")// 主讲老师
    private TeacherEntity mathTeacher;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

教课老师数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("teacherEntity")
public class TeacherEntity implements java.io.Serializable {

    private Integer id;
    /** name */
    @Excel(name = "主讲老师_major,代课老师_absent", orderNum = "2", 
           isImportField = "true_major,true_absent")
    private String name;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

数据准备

public List<CourseEntity> getCourseEntities() {
        List<CourseEntity> courseEntities = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            CourseEntity course = CourseEntity.builder()
                    .id(i)
                    .name("课程" + i)
                    .mathTeacher(new TeacherEntity(i, "老师" + i))
                    .build();
            courseEntities.add(course);
        }
        return courseEntities;
 }
1
2
3
4
5
6
7
8
9
10
11
12

导出Excel代码

@Test
public void testWriteExcel2() throws IOException {
    // 准备数据
    List<CourseEntity> courseEntities = getCourseEntities();
    // 导出
    ExportParams params = new ExportParams();
    params.setTitle("课程信息");
    params.setSheetName("课程信息sheet表");
    Workbook workbook = ExcelExportUtil.exportExcel(params, CourseEntity.class, 
    courseEntities);
    // 指定写出的文件
    FileOutputStream outputStream = new FileOutputStream("D:\\study\\excel\\easypoi_course.xlsx");
    workbook.write(outputStream);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

效果

2.3.5 导出一对多关系(对象)

案例1: 导出用户(订单)信息

用户-订单信息orders-一对多关系

用户信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("users")
public class UserExcel implements Serializable {
    @Excel(name = "编号", orderNum = "1", width = 10, needMerge = true)
//    @ExcelIgnore
    private Integer id;
    @Excel(name = "姓名", orderNum = "2", width = 20, needMerge = true)
    private String name;
    @Excel(name = "年龄", orderNum = "4", width = 10, needMerge = true)
    private Integer age;
    @Excel(name = "性别", orderNum = "5", width = 10, needMerge = true)
    private String gender;
    @Excel(name = "生日", orderNum = "3", width = 20.0, needMerge = true, 
           format = "yyyy年MM月dd日")
    private Date birthday;
    //    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    @ExcelIgnore
    private List<String> hobbys;
    @Excel(name = "爱好", orderNum = "6", width = 20.0, needMerge = true)
    private String hobbyStr;
    @ExcelEntity // 标识一对一的关系
    private Card card;
    @ExcelCollection(name = "订单信息", orderNum = "9") // 一对多关系
    private List<Order> orders; 
    // lombok自动生成setter/getter方法
    public String getHobbyStr() {
        StringBuilder sb = new StringBuilder();
        hobbys.forEach(e -> {
            sb.append(e).append(", ");
        });
        return sb.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

订单信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("orders")
public class Order {

    @Excel(name = "订单编号", width = 20.0)
    private String no;
    @Excel(name = "订单名称", width = 15.0)
    private String name;
}
1
2
3
4
5
6
7
8
9
10
11
12

数据准备

public List<UserExcel> getUserExcelData() {
    List<UserExcel> userExcels = new ArrayList<>();
    List<Order> orders = new ArrayList<>();
    for (int i = 1; i <= 2; i++) {
        Order order = new Order("no1234" + i, "商品" + i);
        orders.add(order);
    }
    for (int i = 1; i <= 10; i++) {
        UserExcel user = UserExcel.builder()
                .id(i)
                .name("admin" + i)
                .age(i * 10)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .hobbys(Arrays.asList("打篮球", "听英语", "看书"))
                .card(new Card("43082119930505" + i, "广东深圳"))
                .orders(orders)
                .build();
        userExcels.add(user);
    }
    return userExcels;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

导出Excel代码与上面一样.

效果

案例2: 导出课程(学生)信息

课程-学生student-一对多关系

课程信息数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("courseEntity")
public class CourseEntity {
    /** 主键 */
    private Integer id;
    /** 课程名称 */
    @Excel(name = "课程名称", orderNum = "1", width = 25, needMerge = true)
    private String name;
    /** 老师主键 */
    // @ExcelEntity(id = "absent") //代课老师
   @ExcelEntity(id = "major")// 主讲老师
    private TeacherEntity mathTeacher;
    @ExcelCollection(name = "学生", orderNum = "3")
    private List<StudentEntity> students;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

学生信息模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("studentEntity")
public class StudentEntity {
    private Integer id;
    @Excel(name = "学生姓名", orderNum = "1", width = 20)
    private String name;
    @Excel(name = "学生性别", orderNum = "2", width = 20)
    private String gender;
    @Excel(name = "出生日期", orderNum = "3", width = 20, format = "yyyy-MM-dd")
    private Date birthday;
    @Excel(name = "进校日期", orderNum = "4", width = 20, format = "yyyy-MM-dd")
    private Date eduDate;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

数据准备

public List<CourseEntity> getCourseEntities() {
    List<CourseEntity> courseEntities = new ArrayList<>();
    List<StudentEntity> studentEntities = new ArrayList<>();
    for (int i = 1; i <= 2; i++) {
        StudentEntity student = StudentEntity.builder()
                .id(i)
                .name("学生" + i)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .eduDate(new Date())
                .build();
        studentEntities.add(student);
    }
    for (int i = 1; i <= 10; i++) {
        CourseEntity course = CourseEntity.builder()
                .id(i)
                .name("课程" + i)
                .mathTeacher(new TeacherEntity(i, "老师" + i))
                .students(studentEntities)
                .build();
        courseEntities.add(course);
    }
    return courseEntities;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

导出Excel代码和上面一样.

效果

2.3.6 导出图片

往往随着业务不断变化, 可能需要在导出excel时将图片信息也一并导出, 如商品图标, 用户头像信息等数据.

注意, 4.4.0版本easypoi的升级改动比较大, 导出图片会失败

本次测试成功的是easypoi的4.4.0以下的版本且是2003版本Excel文件.

包含图片属性的数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("companyLogo")
public class CompanyLogo {
    // 定义图片信息, 直接写指定图片路径,type=2表示字段类型为图片; imageType = 1表示从file读取
    @Excel(name = "头像信息", orderNum = "1", type = 2, imageType = 1, width = 12)
    private String logo;
    @Excel(name = "公司名称", orderNum = "2")
    private String companyName;
}
1
2
3
4
5
6
7
8
9
10
11
12

导出图片代码

@Test
public void testWriteImage() throws IOException {
    // 准备数据
    List<CompanyLogo> companyLogos = new ArrayList<>();
    CompanyLogo companyLogo = CompanyLogo.builder()
            .logo("D:\\study\\excel\\1.jpg")
            .companyName("远大公司")
            .build();
    companyLogos.add(companyLogo);
    // 导出
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), 
                                                   CompanyLogo.class, companyLogos);
    workbook.write(new FileOutputStream("D:\\study\\excel\\image.xlsx"));
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

效果

2.3.7 大数据量导出

大数据导出是当我们的数据量在几万-上百万数据时,一次从数据库查询这么多数据加载到内存后写入文件会对我们的内存和CPU产生压力, 需要分页一样处理分段写入Excel缓解压力. 强制使用 xssf版本的Excel

注意: 4.1.0版本easypoi的升级改动比较大, 小喵还没仔细研究新版本大数据量导出相关的api使用.

此处使用的是3.0.3版本easypoi, 亲测可用. xls 和 xlsx都可以.

数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget("users")
public class UserExcel implements Serializable {
    @Excel(name = "编号", orderNum = "1", width = 10, needMerge = true)
//    @ExcelIgnore
    private Integer id;
    @Excel(name = "姓名", orderNum = "2", width = 20, needMerge = true)
    private String name;
    @Excel(name = "年龄", orderNum = "4", width = 10, needMerge = true)
    private Integer age;
    @Excel(name = "性别", orderNum = "5", width = 10, needMerge = true)
    private String gender;
    @Excel(name = "生日", orderNum = "3", width = 20.0, needMerge = true, 
           exportFormat = "yyyy年MM月dd日", importFormat = "yyyy年MM月dd日")
    private Date birthday;
    //    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    @ExcelIgnore
    private List<String> hobbys;
    @Excel(name = "爱好", orderNum = "6", width = 20.0, needMerge = true)
    private String hobbyStr;
    @ExcelEntity // 标识一对一的关系
    private Card card;
    @ExcelCollection(name = "订单信息", orderNum = "9")
//    @ExcelIgnore
    private List<Order> orders;
    // lombok自动生成setter/getter方法
    public String getHobbyStr() {
        StringBuilder sb = new StringBuilder();
        hobbys.forEach(e -> {
            sb.append(e).append(", ");
        });
        return sb.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

数据准备

public List<UserExcel> getUserExcelData() {
    List<UserExcel> userExcels = new ArrayList<>();
    List<Order> orders = new ArrayList<>();
    for (int i = 1; i <=2 ; i++) {
        Order order = new Order("no1234" + i, "商品" + i);
        orders.add(order);
    }
   // 10万记录写入
    for (int i = 1; i <= 100000; i++) {
        UserExcel user = UserExcel.builder()
                .id(i)
                .name("admin" + i)
                .age(i * 10)
                .gender(i % 2 == 0 ? "男" : "女")
                .birthday(new Date())
                .hobbys(Arrays.asList("打篮球", "听英语", "看书"))
                .card(new Card("43082119930505" + i, "广东深圳"))
                .orders(orders)
                .build();
        userExcels.add(user);
    }
    return userExcels;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

导出Excel代码

@Test
public void testWriteBigExcel() throws IOException {
    // 准备数据
    List<UserExcel> userExcels = getUserExcelData();
    // 导出
    ExportParams params = new ExportParams();
    params.setTitle("远大公司用户信息列表");
    params.setSheetName("用户信息");
    params.setHeight((short) 15);
    Workbook workbook = ExcelExportUtil.exportBigExcel(params, 
                                                       UserExcel.class, userExcels);
    // 指定写出的文件
    FileOutputStream outputStream = new 	
       				FileOutputStream("D:\\study\\excel\\easypoi_big.xlsx");
    workbook.write(outputStream);
    ExcelExportUtil.closeExportBigExcel();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

效果, 不知为什么, 数据量稍微大一点, 就跑死了, 这个性能也绝了. (非电脑配置问题)

2.3.8 导出多个sheet

核心代码

@Test
public void testBatchWriteExcel() throws IOException {
    List<Map<String, Object>> sheets = new ArrayList<>();
    List<UserExcel> userDatas1 = getUserExcelData();
    List<UserExcel> userDatas2 = getUserExcelData();

    Map<String, Object> map1 = new HashMap<>();
    ExportParams params1 = new ExportParams();
    params1.setSheetName("用户信息1");
    map1.put("title", params1);
    map1.put("entity", UserExcel.class);
    map1.put("data", userDatas1);

    Map<String, Object> map2 = new HashMap<>();
    ExportParams params2 = new ExportParams();
    params2.setSheetName("用户信息2");
    map2.put("title", params2);
    map2.put("entity", UserExcel.class);
    map2.put("data", userDatas2);

    sheets.add(map1);
    sheets.add(map2);

    // 写入Excel
    Workbook workbook = ExcelExportUtil.exportExcel(sheets, ExcelType.HSSF);
    workbook.write(new FileOutputStream("D:\\study\\excel\\easypoi2.xls"));
}
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

2.4 导入Excel

2.4.1 导入基本数据

读取的Excel数据

数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget(value = "emps")
public class Emp implements Serializable {
    @Excel(name = "编号")
    private Integer id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日", format = "yyyy年MM月dd日")
    private Date birthday;
    @Excel(name = "状态", replace = {"锁定_0", "激活_1"})
    private String status;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

读取Excel的代码

@Test
public void testReadExcel() throws Exception {
    // 加载Excel文件
    FileInputStream inputStream = new 
       FileInputStream("D:\\study\\excel\\easypoi_read.xls");
    // 选择Sheet,params默认选择第一个sheet
    ImportParams params = new ImportParams();
    // 读取第几个sheet
    params.setStartSheetIndex(0);
    // 每次读取几个sheet
    params.setSheetNum(1);
    // 标题占几行
    params.setTitleRows(1);
    // 头部占几行
    params.setHeadRows(1);
    // 校验导入字段
    params.setImportFields(new String[]{"编号"});
    // 数据解析(mapping映射), pojoClass映射
    List<Emp> resultList = ExcelImportUtil.importExcel(inputStream, Emp.class, 
                                                       params);
    resultList.forEach(obj -> System.out.println(obj));
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

效果

2.4.2 导入小技巧

1. 读取指定的sheet, 比如要读取第二个sheet, 那么通过设置startSheetIndex=1实现;

2. 读取几个sheet, 比如读取前2个sheet页, 通过设置 sheetNum=2实现;

3. 读取第二个到第五个sheet, 设置 startSheet=1(索引从0开始), 然后设置sheetNum=4;

4. 读取全部的sheet, sheetNum设置比较大的值就可以了;

5. 判断一个Excel是不是合法的Excel, importFields设置, 表示表头必须至少包含的字段, 如果缺少一个就是不合法的excel, 不会导入.

2.4.3 带图片导入

图片导入的配置和导出一样, 但是需要设置保存路径.

带图片的Excel数据

导入数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget(value = "emps")
public class Emp implements Serializable {
    @Excel(name = "编号")
    private Integer id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日", format = "yyyy年MM月dd日")
    private Date birthday;
    @Excel(name = "状态", replace = {"锁定_0", "激活_1"})
    private String status;
    @Excel(name = "头像", type = 2, imageType = 1)
    private String head;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

带图片导入代码

@Test
public void testReadExcelImage() throws Exception {
    // 加载Excel文件
    FileInputStream inputStream = new 
       FileInputStream("D:\\study\\excel\\easypoi_readimage.xls");
    // 选择Sheet,params默认选择第一个sheet
    ImportParams params = new ImportParams();
    // 读取第几个sheet
    params.setStartSheetIndex(0);
    // 每次读取几个sheet
    params.setSheetNum(1);
    // 标题占几行
    params.setTitleRows(1);
    // 头部占几行
    params.setHeadRows(1);
    // 校验导入字段
    params.setImportFields(new String[]{"编号"});
    // 设置文件保存路径saveUrl
    params.setSaveUrl("upload/excelUpload");
    // 是否需要保存上传的Excel文件
    params.setNeedSave(false);
    // 数据解析(mapping映射), pojoClass映射
    List<Emp> resultList = ExcelImportUtil.importExcel(inputStream, Emp.class, 
                                                       params);
    resultList.forEach(obj -> System.out.println(obj));

}
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

效果

2.5 集成Web实现导入导出

2.5.1 搭建springboot + mybatis项目环境

创建springboot项目

引入相关依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.19</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-tomcat</artifactId>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.3.0</version>
    </dependency>

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.3.0</version>
    </dependency>

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.3.0</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>
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

编写配置文件

server.port=8989
spring.application.name=easypoi-web
## 取消界面缓存
spring.thymeleaf.cache=false
## 数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/easypoidb?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
## mybatis配置
mybatis.mapper-locations=classpath:com/easypoi/mapper/*.xml
mybatis.type-aliases-package=com.easypoi.entity
## log
logging.level.com.easypoi.mapper=debug
## 开启静态访问目录
upload.dir=D:\\develops\\workspace\\IdeaProjects\\easypoi-web\\src\\main\\resources\\static\\imgs
spring.web.resources.static-locations=file:${upload.dir}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

创建包结构

检测环境是否搭建成功

2.5.2 开发测试页面

index.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>导入Excel的主页面</title>
    <link rel="stylesheet" 
   href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css"
   integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" 
   crossorigin="anonymous">
</head>
<body>
<div class="container-fluid">
    <div class="row">
        <div class="col-md-12">
            <h1>选择Excel文件导入</h1>
            <form action="" th:action="@{/user/importExcel}" method="post" 
               enctype="multipart/form-data" class="form-inline">
                <div class="form-group">
                    <input class="form-control" type="file" name="file"/>
                    <input type="submit" class="btn btn-danger" value="导入"/>
                </div>
            </form>
        </div>

        <div class="col-md-12">
            <h1>显示导入的数据列表</h1>
            <table class="table table-bordered">
                <tr>
                    <td>编号</td>
                    <td>姓名</td>
                    <td>年龄</td>
                    <td>生日</td>
                    <td>身份证号</td>
                    <td>家庭住址</td>
                    <td>爱好</td>
                    <td>头像</td>
                </tr>
                <tr th:each="user: ${users}">
                    <td th:text="${user.id}"></td>
                    <td th:text="${user.name}"></td>
                    <td th:text="${user.age}"></td>
                    <td th:text="${#dates.format(user.birthday,'yyyy-MM-dd')}"></td>
                    <td th:text=" ${user.no}
                    "></td>
                    <td th:text="${user.address}"></td>
                    <td th:text="${user.hobbys}"></td>
                    <td><img src=""
                             th:src="${'/'+user.photo}" 
                                style="height: 40px;width: 80px"
                             alt=""/></td>
                </tr>
            </table>
            <hr/>
            <a th:href="@{/user/exportExcel}" class="btn btn-info">导出excel</a>
        </div>
    </div>
</div>
</body>
</html>
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

启动应用, 访问主页

2.5.3 查询所有

Excel数据模板

根据Excel抽取表结构

Create Table: CREATE TABLE `t_user` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(80) DEFAULT NULL COMMENT '姓名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `hobbys` varchar(255) DEFAULT NULL COMMENT '爱好',
  `no` varchar(30) DEFAULT NULL COMMENT '身份证号',
  `address` varchar(200) DEFAULT NULL COMMENT '家庭地址',
  `photo` varchar(255) DEFAULT NULL COMMENT '图像',
  `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO t_user VALUES(0,'admin',23,'看书','4430821199305058632','深圳龙华','1.jpg',CURRENT_TIMESTAMP())
1
2
3
4
5
6
7
8
9
10
11
12
13

创建实体类

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ExcelTarget(value = "user")
public class User implements Serializable {
    @Excel(name = "编号")
    private Integer id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日", format = "yyyy-MM-dd", width = 20.0)
    private Date birthday;
    @Excel(name = "身份证号", width = 25.0)
    private String no;
    @Excel(name = "家庭住址", width = 20.0)
    private String address;
    @Excel(name = "爱好", width = 25.0)
    private String hobbys;
    @Excel(name = "头像", type = 2, width = 25.0, savePath = 
           "D:\\develops\\workspace\\IdeaProjects\\
           easypoi-web\\src\\main\\resources\\static\\imgs")
    private String photo;
}
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

开发mapper接口及映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easypoi.mapper.UserMapper">

    <select id="findAll" resultType="user">
        select id, name,age,hobbys,no,address,photo, birthday from t_user;
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10

开发Service及实现

public interface UserService {
    List<User> findAll();
}
1
2
3

impl实现

@Service
@Transactional
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<User> findAll() {
        return userMapper.findAll();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

开发Controller

@Slf4j
@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @RequestMapping("/findAll")
    public String findAll(Model model) {
        List<User> users = userService.findAll();
        model.addAttribute("users", users);
        return "index";
    }
   
    /**
     * 简单的异常处理, 以json视图返回异常信息
     * @param e
     * @return
     */
    @ExceptionHandler(Exception.class)
    @ResponseBody
    public String exceptionHandler(Exception e) {
        return e.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

界面如果要访问到图片, 加入将图片放在服务器resources/static/imgs下面, 需要在配置文件中指定静态资源访问路径.

## 开启静态访问目录
upload.dir=D:\\develops\\workspace\\IdeaProjects\\easypoi-web\\src\\main\\resources\\static\\imgs
spring.web.resources.static-locations=file:${upload.dir}
1
2
3

界面效果

2.5.4 导入数据

开发mapper接口及映射

@Mapper
public interface UserMapper {
    List<User> findAll();
    void save(User user);
}
1
2
3
4
5

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easypoi.mapper.UserMapper">


    <select id="findAll" resultType="user">
        select id, name,age,hobbys,no,address,photo, birthday from t_user;
    </select>

    <insert id="save" parameterType="user" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(#{id}, #{name},#{age},#{hobbys},#{no},#{address},
       #{photo}, #{birthday} );

    </insert>

</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

开发Service接口及实现

public interface UserService {
    List<User> findAll();
    void saveAll(List<User> users);
}
1
2
3
4

@Service
@Transactional
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<User> findAll() {
        return userMapper.findAll();
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED, isolation = 
                   Isolation.READ_COMMITTED)
    public void saveAll(List<User> users) {
        users.forEach(user -> {
            user.setId(null);
            String filename = 
               // 保存到数据库的图片名称不带路径, 仅仅是保存文件名称
               user.getPhoto().substring(user.getPhoto().lastIndexOf("\\") + 1);
            user.setPhoto(filename);
            userMapper.save(user);
        });
    }
}
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

开发Controller

@RequestMapping("/importExcel")
public String importExcel(MultipartFile file) throws Exception {

    if (file.isEmpty()) {
        throw new RuntimeException("上传文件为空");
    }

    log.info("file is: {}", file.getOriginalFilename());
    ImportParams importParams = new ImportParams();
    importParams.setTitleRows(1);
    importParams.setHeadRows(1);
    List<User> users = ExcelImportUtil.importExcel(file.getInputStream(), 
                                                   User.class, importParams);
    users.forEach(System.out::println);
    userService.saveAll(users);

    return "redirect:/user/findAll"; // 上传完成后,跳转到查询所有的方法路径上
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

界面效果

导入Excel后, 系统保存数据到db, 然后重定向到/user/findAll方法, 在index.html页面展示所有数据(包含刚导入的数据)

2.5.5 导出数据

开发Controller

service可以复用上面的findAll方法.

@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
    // 查询所有数据
    List<User> users = userService.findAll();
    users.forEach(user -> {
        // 拼接全路径
        user.setPhoto(realPath + File.separator + user.getPhoto());
    });
    // 设置响应头
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 设置防止中文名乱码
    String filename = URLEncoder.encode("用户列表", "utf-8");
    // 文件下载方式(附件下载还是在当前浏览器打开)
    response.setHeader("Content-disposition", 
                       "attachment;filename=" + filename + ".xls");

    log.info("导出的用户信息:{}", users);

    // 生成excel
    ExportParams exportParams = new ExportParams();
    exportParams.setSheetName("用户信息");
    exportParams.setTitle("员工信息");
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, users);
    workbook.write(response.getOutputStream());
    workbook.close();
}
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

开发html页面

<a th:href="@{/user/exportExcel}" class="btn btn-info">导出excel</a>
1

导出效果

浏览器下载中

写入的Excel效果

3 模板导出

模板导出自己去官方资料学习…

相关推荐

数据分流写入Excel

Poi版本升级优化

StringTemplate实现Excel导出

Poi模板技术

SAX方式实现Excel导入

DOM方式实现Excel导入

Poi实现Excel导出

EasyExcel实现Excel文件导入导出

EasyPoi实现excel文件导入导出

个人博客

欢迎各位访问我的个人博客: https://www.crystalblog.xyz/

备用地址: https://wang-qz.gitee.io/crystal-blog/