导入导出这么丝滑，你用的是 EasyPoi 吗？

知否技术

于 2021-05-25 12:59:57 发布

阅读量343

点赞数

分类专栏： Java 文章标签： poi EasyPoi java

本文链接：https://blog.csdn.net/GUANYAQI1996/article/details/117253746

版权

Java 专栏收录该内容

40 篇文章 1 订阅

订阅专栏

丝滑的 EasyPoi

POI：用来处理 Excel、Word 等文件的技术。EasyPoi：让没有接触过 POI 的人也能方便的写出 Excel 的导入导出功能。说白了就是用了 EasyPoi，文件的导入导出变得 so easy！

官网地址：http://easypoi.mydoc.io/

1. SpringBoot整合EasyPoi

引入依赖

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

<!--easy-poi-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.0.0</version>
</dependency>

2. 相关注解

2.1 @ExcelTarget

作用在实体类上，表明这是一个可以用 EasyPoi 操作导入导出的实体类

常用属性:

value：唯一标识，不能重复。但是可以省略不写，例如 “user”

使用方法：

@ExcelTarget("user")
public class User implements Serializable {
}

2.2 @Excel

作用在实体类中的属性上面，表明这是 Excel 中的一列。

常用属性：

name：Excel表中的列名，例如 name=“姓名”
orderNum：Excel表中列的排列顺序，例如 orderNum=“1”
type：导出类型，1 文本，2 图片，3 函数，10 数字，默认是文本
width：列的宽度
height：列的高度
savePath：图片的保存位置
exportformat：Excel 导出的时间格式，例如 exportformat=“yyyy-MM-dd HH:mm:ss”
importFormat：Excel 导入的时间格式
format：同时设置导入导出的时间格式
isStatistics：是否合计该列？默认 false
needMerge：是否纵向合并单元格，默认false
replace：替换值，例如 replace = {“女_0”, “男_1”}，表示如果值为 1，则导出 ‘男’，如果值为 0，则导出 ‘女’。

使用方法：

@ExcelTarget("user")
public class User implements Serializable {

  @Excel(name = "姓名", width = 30, orderNum = "1")
  private String name;

  @Excel(name = "年龄", width = 10, orderNum = "2",isSta    tistics = true)
  private Integer age;

  @Excel(name = "性别", width = 10, orderNum = "3", repl    ace = {"女_0", "男_1"})
  private Integer sex;

  @Excel(name = "生日", width = 30, orderNum = "4", form    at = "yyyy-MM-dd HH:mm:ss")
  private Date birthday;

  @Excel(name = "头像信息", type = 2, width = 20,height = 30,savePath = "D:\\WorkSpace\\easy-poi\\src\\main\\resources")
  private String head;
}

2.3 @ExcelEntity

作用在实体类中的类上面，表明这是导出的类中的类。

常用属性：

name：唯一标识，不能重复。

使用方法：

@ExcelTarget("user")
public class User implements Serializable {

    @ExcelEntity(name = "address")
    private Address address;
}

@ExcelTarget("address")
public class Address implements Serializable {

    @Excel(name = "省", width = 30, orderNum = "6")
    private String province;

    @Excel(name = "市", width = 30, orderNum = "7")
    private String city;

    @Excel(name = "县", width = 30, orderNum = "8")
    private String county;
}

2.4 @ExcelCollection

表明这是一个集合，一对多的关系

常用属性：

name：集合名，唯一标识
orderNum：Excel 表中列的排列顺序

使用方法：

@ExcelTarget("user")
public class User implements Serializable {

  @ExcelCollection(name = "购买的水果", orderNum = "9")
  private List<Fruit> fruits;
  
}

@ExcelTarget("fruit")
public class Fruit implements Serializable {

    @Excel(name = "水果名")
    private String name;

    @Excel(name = "价格/元")
    private Double price;

    @Excel(name = "数量")
    private Integer count;
    
}

2.5 @ExcelIgnore

作用在属性上面，导出时跳过这一列

使用方法：

@ExcelIgnore 
@Excel(name = "荣誉", width = 60, orderNum = "5")
private List<String> honors;

3. 导出 Excel

3.1 导出基本属性

定义用户类：

/**
 * @Description 导入导出实体类
 * @Author 一颗雷布斯
 * @Date 2021/5/24 15:38
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1" )
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;
}

测试方法：

@Test
void exportBasic() throws IOException {
    //新建测试数据
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        User user = new User();
        user.setName("周芷若" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        userList.add(user);
    }
    /*---------------------下面是使用EasyPoi导出的核心方法----------------------------*/
    // ExportParams() 两个参数，一个是title 表格标题, 一个是sheetName
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
    //导出文件到指定位置
    FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

执行结果：

3.2 导出图片

定义用户类（包含用户头像）：

/**
 * @Description 导入导出实体类
 * @Author 一颗雷布斯
 * @Date 2021/5/24 15:38
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1" )
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;
    
    //切记图片的 type = 2
    @Excel(name = "头像信息", type = 2, width = 20,height = 30)
    private String head;
}

测试方法：

@Test
void exportBasic() throws IOException {
    //新建测试数据
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        User user = new User();
        user.setName("周芷若" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        //指定图像所在的位置
        user.setHead("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\static\\image\\head.jpeg");
        userList.add(user);
    }
    /*---------------------下面是使用EasyPoi导出的核心方法----------------------------*/
    // ExportParams() 两个参数，一个是title 表格标题, 一个是sheetName
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
    //导出文件到指定位置
    FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

执行结果：

3.3 导出对象中的对象

定义用户类（包含用户地址对象）：

/**
 * @Description 导入导出实体类
 * @Author 一颗雷布斯
 * @Date 2021/5/24 15:38
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1")
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;

    @ExcelEntity(name = "address")
    private Address address;
}

地址类：

/**
 * @Description 地址
 * @Author 一颗雷布斯
 * @Date 2021/5/24 16:32
 */
@Data
@AllArgsConstructor
@ExcelTarget("address")
public class Address implements Serializable {

    @Excel(name = "省", width = 30, orderNum = "5")
    private String province;

    @Excel(name = "市", width = 30, orderNum = "6")
    private String city;

    @Excel(name = "县", width = 30, orderNum = "7")
    private String county;

}

测试方法：

@Test
void exportUserTest() throws IOException {
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("周芷若" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        //设置地址
        user.setAddress(new Address("湖南","长沙","宁乡"));
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
    //导出文件到指定位置
    FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

执行结果：

3.4 导出对象中的list集合

定义用户类（包含用户所得荣誉信息）：

/**
 * @Description 导入导出实体类
 * @Author 一颗雷布斯
 * @Date 2021/5/24 15:38
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1")
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;

    @Excel(name = "荣誉", width = 60, orderNum = "5")
    private List<String> honors;
}

测试方法：

@Test
void exportUserTest() throws IOException {
    List<User> userList = new ArrayList<>();
    String honorList="吃饭比赛一等奖、国际睡觉大赛二等奖";
    List<String> honors = Arrays.asList(honorList.split("、"));
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("周芷若" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(0);
        //设置用户所得荣誉
        user.setHonors(honors);
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
    //导出文件到指定位置
    FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

执行结果：

3.5 导出一对多关系

定义用户类（包含用户购买的水果信息，这里一个用户购买多个水果）：

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1",needMerge = true)
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2",needMerge = true)
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"},needMerge = true)
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss",needMerge = true)
    private Date birthday;

    @ExcelCollection(name = "购买的水果", orderNum = "5")
    private List<Fruit> fruits;
}

水果类：

/**
 * @Description 水果实体类
 * @Author 一颗雷布斯
 * @Date 2021/5/24 16:43
 */
@Data
@AllArgsConstructor
@ExcelTarget("fruit")
public class Fruit implements Serializable {

    @Excel(name = "水果名")
    private String name;

    @Excel(name = "价格/元")
    private Double price;

    @Excel(name = "数量")
    private Integer count;
}

测试方法：

@Test
void exportUserTest() throws IOException {
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("周芷若" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(0);
        //设置用户购买的水果信息
        user.setFruits(Arrays.asList(new Fruit("苹果",23.5,11),new Fruit("香蕉",4.98,21)));
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
    //导出文件到指定位置
    FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

执行结果：

4. 导入Excel

4.1 导入基本属性

准备文件：

定义用户类:

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1")
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;
}

测试方法：

@Test
void importUserTest() {
    ImportParams importParams = new ImportParams();
    //表格标题行数,默认0
    importParams.setTitleRows(1);
    //表头行数,默认1
    importParams.setHeadRows(1);
    //导出数据 参数1:当如excel文件  参数2:导入对象的类型 参数3:导入参数配置
    List<User> userList = ExcelImportUtil.importExcel(new File("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls"), User.class,importParams);
   if(!CollectionUtils.isEmpty(userList))
    userList.forEach(user->{
        System.out.println(user);
    });
}

执行结果：

4.2 导入图片

准备文件：

定义用户类（包含用户头像，savePath 指定用户头像保存位置）：

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @Excel(name = "姓名", width = 30, orderNum = "1")
    private String name;

    @Excel(name = "年龄", width = 10, orderNum = "2")
    private Integer age;

    @Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
    private Integer sex;

    @Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;

    @Excel(name = "头像信息", type = 2, width = 20,height = 30,savePath = "D:\\WorkSpace\\easy-poi\\src\\main\\resources\\head")
    private String head;
}

测试方法：

@Test
void importUserTest() {
    ImportParams importParams = new ImportParams();
    //表格标题行数,默认0
    importParams.setTitleRows(1);
    //表头行数,默认1
    importParams.setHeadRows(1);
    //导出数据 参数1:当如excel文件  参数2:导入对象的类型 参数3:导入参数配置
    List<User> userList = ExcelImportUtil.importExcel(new File("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls"), User.class,importParams);
   if(!CollectionUtils.isEmpty(userList))
    userList.forEach(user->{
        System.out.println(user);
    });
}

执行结果：

头像保存位置：

5. 注意事项

导出 Excel 文件时，切记先把原来导出的 Excel 文件关掉，不然会报错！

微信公众号：eclipse编程。专注于编程技术分享，坚持终身学习。

知否技术

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
导入导出这么丝滑，你用的是 EasyPoi 吗？

丝滑的 EasyPoiPOI：用来处理 Excel、Word 等文件的技术。EasyPoi：让没有接触过 POI 的人也能方便的写出 Excel 的导入导出功能。说白了就是用了 EasyPoi，文件的导入导出变得 so easy！官网地址：http://easypoi.mydoc.io/1. SpringBoot整合EasyPoi引入依赖<dependency> <groupId>org.projectlombok</groupId> &l
复制链接

扫一扫