easyPOI 入门使用

easyPOI简介

在项目中，有时候会出现需要将数据库数据导出报表等功能，这时就会用到POI库，但是Apache提供的POI工具使用过于繁琐。esayPOI就是对POI的封装，虽然减少了对excel文件的格式美化，但简化了操作，便于用户操作。
项目地址：https://download.csdn.net/download/qq_43739109/16068064?spm=1001.2014.3001.5501

easyPOI入门使用

1.添加相关依赖

        <!--easyPOI相关依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!--建议加入lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>        

注意：由于依赖传递的关系，只引入easy-poi.web也能正常使用

2.编写相关实体，添加注解（实体要实现序列化接口）

• @Excel 这个是最基本常用的注解，注解在模型字段上，可添加列名、列的排序、列宽、格式等属性
• @ExcelTarget 用于外层的模型实体，可注解行高、字体大小等属性
• @ExcelEnity 用于标记实体内部类是否继续穿透
• @ExcelCollection 用于注解集合字段
• @ExcelIgnore 忽略这个属性

User实体（主要实体）

package entity;

import cn.afterturn.easypoi.excel.annotation.*;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;

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

    @Excel(name = "编号",orderNum = "0")
    private String id;
    @Excel(name = "姓名",orderNum = "1")
    private String name;
    @Excel(name = "年龄",suffix = "岁",orderNum = "2")
    private Integer age;
    @Excel(name = "出生日期",width = 35.0,format = "YYYY-MM-DD HH:MM",orderNum = "4")//可以设置格式
    private Date bir;
    @Excel(name="性别",replace = {"男_1","女_2"},orderNum = "3")
    private String sex;

    @ExcelIgnore
    private List<String> habbys;

    //导出集合类型
    @Excel(name = "爱好",orderNum = "5",width = 20)
    private String habbystr;

    public String getHabbystr(){
        //设置格式
        StringBuilder stringBuilder = new StringBuilder();
        habbys.forEach(e->{
            stringBuilder.append(e).append("、");});
        return stringBuilder.toString();
    }

    //标识一对一关系
    @ExcelEntity
    private Card card;

    //标识一对多关系
    @ExcelCollection(name = "订单列表",type = Order.class,orderNum = "8")
    private List<Order> orders;

    //导出图片
    @Excel(name = "头像",type = 2,width = 20,height = 20)//type = 2 为图片格式
    private String photo;//头像信息
}

Card实体（用户描述1对1关系）

package entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

@Data
public class Card implements Serializable {
    @Excel(name = "身份证号码",width = 20,orderNum = "6")
    private String no;
    @Excel(name = "籍贯",orderNum = "7",width = 20)
    private String address;
}

Order实体（用户描述一对多的关系）

package entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@AllArgsConstructor//添加构造方法
@NoArgsConstructor
public class Order implements Serializable {

    @Excel(name = "订单编号",orderNum = "1",width = 20)
    private String no;
    @Excel(name = "订单名称",orderNum = "2",width = 20)
    private String name;

}

3.生成Workbook对象,导出excel文件

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import entity.Card;
import entity.Order;
import entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

//POI测试
public class TestPOI {

    //模拟查询所有方法
    public List<User> queryAll(){
        List<User> users = new ArrayList<>();
        for (int i = 1; i <=10 ; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("小王"+i);
            user.setAge(12+i);
            user.setBir(new Date());
            //身份证信息
            Card card = new Card();
            card.setNo("123456789");
            card.setAddress("云南省昆明市");
            user.setCard(card);
            //头像图片（绝对路径）
            user.setPhoto("C:/Users/wang9/Desktop/头像.jpg");
            if(i%2==0){
                user.setSex("1");
                //爱好信息
                user.setHabbys(Arrays.asList("看书","运动","干饭"));
                //订单信息
                List<Order> orders = new ArrayList<>();
                orders.add(new Order("13579","PS5"));
                orders.add(new Order("13578","Swich"));
                orders.add(new Order("13577","Xbox"));
                user.setOrders(orders);
            }else{
                user.setSex("2");
                user.setHabbys(Arrays.asList("抽烟","喝酒","烫头"));
                List<Order> orders = new ArrayList<>();
                orders.add(new Order("13571","超短裙"));
                orders.add(new Order("13572","连衣裙"));
                orders.add(new Order("13573","裙子"));
                user.setOrders(orders);
            }
            users.add(user);
        }
        return users;
    }

    //测试导出Excel方法
    @Test
    public void testExport() throws IOException {
        //获取数据（一般从数据库中获取，这里进行了模拟）
        List<User> users = queryAll();
        //导出Excel
        //参数1 ：导出配置对象（XSSF表示输出类型为xlsx） 参数2：导出类型 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息", ExcelType.XSSF), User.class, users);
        //导出excel到指定位置
        FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/wang9/Desktop/user.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }
}

4.输出结果