EasyPOI的基本使用

1、什么是EasyPOI

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

2、基本使用

2.1 环境搭建

创建一个普通的maven项目，并引入以下依赖：

<!---->
    <dependencies>
        <!--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.12</version>
        </dependency>
        <!--hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4.7</version>
        </dependency>
    </dependencies>

2.2 注解介绍

easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model–row,filed–col 这样利用注解我们可以和容易做到excel到导入导出。这是官网上的一段话，意思是这样的：利用easypoi为我们提供的注解可以将一个实体类和我们的excel表进行对应，一个实体类对象就是我们excel表中的一行，对象的属性和excel表的列相互映射。

• @Excel:作用在实体类属性上，是对excel列的描述；
• @ExcelCollection:作用在一个集合属性上，主要针对一对多(关联关系)的导出;
• @ExcelEntity:作用在一个对象属性上，主要针对一对一(关联关系)的导出；
• @ExcelIngore:表示在导出时忽略该字段的导出，主要针对那些不希望展示的数据；
• @ExcelTarget:表示要导出的目标对象，作用在实体类上。

2.3 导出excel之基本数据导出

编写用户实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("users")
public class User implements Serializable {
    //name用于设置列名，orderNum是设置列的位置,默认为0
    @Excel(name = "编号",orderNum = "1")
    private String id;
    @Excel(name = "姓名",orderNum = "2")
    private String name;
    @Excel(name="年龄",orderNum = "3")
    private Integer age;
    //format用于设置显示格式，width用于设置列的宽度
    @Excel(name="出生日期",orderNum = "4",format = "yyyy-MM-dd",width = 35.0)
    private Date birth;
    //我们数据库中保存用户状态一般使用1、0等，但是导出到excel需要转换成对应的文字
    //信息，就可以使用replace，显示的文字信息和保存的状态码使用下划线_分隔
    @Excel(name = "用户状态",replace = {"激活_1","禁用_0"})
    private Integer status;
}

测试

public class BaseTest {
    private static List<User> users;

    static {
        users = new ArrayList<User>();
        users.add(new User("1","zs",23,new Date(),1));
        users.add(new User("2","ls",24,new Date(),0));
        users.add(new User("3","ww",25,new Date(),1));
        users.add(new User("4","zl",26,new Date(),0));
        users.add(new User("5","hh",27,new Date(),1));
    }

    public static void main(String[] args) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        FileOutputStream os = new FileOutputStream("D:\\111.xls");
        workbook.write(os);
        os.close();
    }
}

2.4 集合数据的导出

在实体类中增加属性

    @Excel(name = "爱好",orderNum = "5")
    private List<String> hobbies;

修改测试类并再次测试

    static {
        users = new ArrayList<User>();
        users.add(new User("1","zs",23,new Date(),1, Arrays.asList("喝酒","抽烟","打架")));
        users.add(new User("2","ls",24,new Date(),0,Arrays.asList("吃饭","睡觉","打豆豆")));
        users.add(new User("3","ww",25,new Date(),1,Arrays.asList("喝酒","抽烟","打架")));
        users.add(new User("4","zl",26,new Date(),0,Arrays.asList("喝酒","抽烟","打架")));
        users.add(new User("5","hh",27,new Date(),1,Arrays.asList("吃饭","睡觉","打豆豆")));
    }

运行结果：

可以看到，集合数据会被放入到一个中括号类似数组当中显示，如果希望使用其它格式进行显示，我们可以选择取出集合中的数据并自定义拼接方式：

    @ExcelIgnore  //不导出这个
    private List<String> hobbies;
    @Excel(name = "爱好",orderNum = "5",width = 35.0)
    private String hobbyStr;//这个是拼接用的字符串

    public String getHobbyStr() {
        StringBuilder sb = new StringBuilder();
        hobbies.forEach(h -> {
            sb.append(h);
            sb.append("、");
        });
        return sb.substring(0,sb.length()-1).toString();
    }

运行结果：

2.5 对象和对象集合的导出

首先，建立Card实体类

@ExcelTarget(value = "card")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Card implements Serializable {
    @Excel(name = "身份证号",width = 30.0,orderNum = "6")
    private String no;
    @Excel(name = "地址",width = 40.0,orderNum = "7")
    private String address;
}

在User中新增card属性

    @ExcelEntity
    private Card card;

测试结果：

接着再来看下对象集合的导出，先建立一个Order实体类

@ExcelTarget(value = "订单信息")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order implements Serializable {
    @Excel(name = "订单编号",orderNum = "8")
    private String no;
    @Excel(name = "商品名称",orderNum = "9")
    private String name;
}

修改User实体类

    @ExcelCollection(name = "订单列表")
    private List<Order> orders;

再次测试：

2.6 图片的导出

在User实体类中新增属性

    @Excel(name = "图片",width = 20.0,height = 20.0,type = 2,imageType = 1)//type=2表示图片类型
    private String photo;

测试结果：

2.7 基本数据导出

准备excel

准备对应的实体类Emp

@Data
@ExcelTarget(value = "emp")
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private int age;
    @Excel(name = "出生日期",format = "yyyy/MM/dd")
    private Date birth;
}

测试

 public static void main(String[] args) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//设置标题占的行数
        params.setHeadRows(1);//设置header占的行数
        List<Emp> list = ExcelImportUtil.importExcel(FileUtil.file("D:\\111.xlsx"), Emp.class, params);
        list.forEach(emp -> {
            System.out.println(emp);
        });
    }

3、整合SpringBoot

3.1 项目准备

需要准备一个员工信息的excel表

3.2 搭建环境

首先，需要创建一个SpringBoot项目，并导入下列依赖：

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!--mp-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--thymeleaf-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--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>
    </dependencies>

然后，创建一个数据库，并建表emp

接着，编写配置文件application.properties

server.port=8889
spring.application.name=easypoi

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/easypoi?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456

mybatis.mapper-locations=classpath:com/hzp/mapper/*.xml
mybatis.type-aliases-package=com.hzp.entity

编写实体类Emp

@Data
@ExcelTarget(value = "emps")
@TableName("emp")
public class Emp implements Serializable {
    @Excel(name = "编号")
    @TableId("id")
    private String id;
    @TableField("name")
    @Excel(name = "姓名")
    private String name;
    @TableField("age")
    @Excel(name = "年龄")
    private int age;
    @TableField("birth")
    @Excel(name = "出生日期",format = "yyyy/MM/dd")
    private Date birth;
    @TableField("hobbies")
    @Excel(name="爱好")
    private String hobbies;
    @TableField("idcard")
    @Excel(name = "身份证号")
    private String idCard;
    @TableField("address")
    @Excel(name = "家庭住址")
    private String address;
    @TableField("photo")
    @Excel(name = "头像信息",type = 2,savePath = "E:\\IDEA-workspace\\codes\\easypoi_springboot\\src\\main\\resources\\static\\imgs")
    private String photo;
}

编写mapper接口

@Mapper
public interface EmpMapper extends BaseMapper<Emp> {

}

编写service

public interface EmpService {
    List<Emp> findAll();
}

@Service
@Transactional
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public List<Emp> findAll() {
        return empMapper.selectList(null);
    }
}

编写Controller

@Controller
@RequestMapping("/emp")
public class EmpController {

    @Autowired
    private EmpService empService;

    @RequestMapping("/index")
    public String index(Model model) {
        List<Emp> all = empService.findAll();
        model.addAttribute("emps",all);
        return "index";
    }
}

最后，在templates下创建index.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <form th:action="@{/emp/upload}" enctype="multipart/form-data" method="post">
        请选择文件：<input type="file" name="file"/>
        <input type="submit" value="提交"/>
    </form>
    <table border="1">
        <tr>
            <td>编号</td>
            <td>姓名</td>
            <td>年龄</td>
            <td>出生日期</td>
            <td>爱好</td>
            <td>身份证号</td>
            <td>家庭住址</td>
            <td>头像信息</td>
        </tr>
        <tr th:each="emp : ${emps}">
            <td th:text="${emp.id}"></td>
            <td th:text="${emp.name}"></td>
            <td th:text="${emp.age}"></td>
            <td th:text="${#dates.format(emp.birth,'yyyy-MM-dd')}"></td>
            <td th:text="${emp.hobbies}"></td>
            <td th:text="${emp.idCard}"></td>
            <td th:text="${emp.address}"></td>
            <!--图片存放在static下的imgs-->
            <td><img th:src="${'/imgs/'+emp.photo}"/></td>
        </tr>
    </table>
</body>
</html>

测试，往表里插几条数据，看是否可以显示出来

3.3 导入实现

首先，定义service接口并提供实现

void upload(List<Emp> list);

@Override
    public void upload(List<Emp> list) {
        list.forEach(emp -> {
        //只保存文件名
            emp.setPhoto(emp.getPhoto().substring(emp.getPhoto().lastIndexOf("\\")+1));
            empMapper.insert(emp);
        });
}

在controller类中定义upload方法

@PostMapping("/upload")
    public String upload(MultipartFile file) throws Exception {
        System.out.println(file.getOriginalFilename());
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        List<Emp> emps = ExcelImportUtil.importExcel(file.getInputStream(), Emp.class, params);
        empService.upload(emps);
        return "redirect:/emp/index";//上传后回到index
    }

3.4 导出实现

导出只需要在controller中加个导出的方法即可

 @RequestMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        List<Emp> emps = empService.findAll();
        emps.forEach(emp -> {
            //获得photo属性设置的savePath保存路径，并拼接到查询出来的photo
            try {
                Excel photo = emp.getClass().getDeclaredField("photo").getAnnotation(Excel.class);
                emp.setPhoto(photo.savePath()+"\\"+emp.getPhoto());
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        });
        System.out.println(response);
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("员工信息列表", "员工信息"), Emp.class, emps);
        response.setHeader("content-disposition","attachment;fileName="+ URLEncoder.encode("用户列表.xls","UTF-8"));
        OutputStream out = response.getOutputStream();
        
        workbook.write(out);
        out.close();
        workbook.close();
    }