导入导出这么丝滑,你用的是 EasyPoi 吗?

导入导出这么丝滑,你用的是 EasyPoi 吗?POI:用来处理 Excel 等文件的技术。EasyPoi: 让没有接触过 POI 的人也能方便的写出 Excel 的导入导出功能。说白了就是用了 EasyPoi,文件的导入导出变得 so easy!

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

官网地址: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();
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

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();
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

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();
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

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();
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

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();
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

4. 导入Excel

4.1 导入基本属性

准备文件:

导入导出这么丝滑,你用的是 EasyPoi 吗?

定义用户类:

@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);
    });
}

执行结果:

导入导出这么丝滑,你用的是 EasyPoi 吗?

4.2 导入图片

准备文件:

导入导出这么丝滑,你用的是 EasyPoi 吗?

定义用户类(包含用户头像,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);
    });
}

执行结果: 导入导出这么丝滑,你用的是 EasyPoi 吗? 头像保存位置: 导入导出这么丝滑,你用的是 EasyPoi 吗?

5. 注意事项

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

导入导出这么丝滑,你用的是 EasyPoi 吗?

今天的文章导入导出这么丝滑,你用的是 EasyPoi 吗?分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/23564.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注