easypoi导入导出

easypoi实现简单的导入导出

概况

今天做Excel导出时，发现了一款非常好用的POI框架EasyPoi，其 使用起来简洁明了。现在我们就来介绍下EasyPoi,首先感谢EasyPoi 的开发者 Lemur开源

easypoi 简介

easypoi 是为了让开发者快速的实现excel，word,pdf的导入导出，基于Apache poi基础上的一个工具包。

特性

• 基于注解的导入导出，修改注解就可以修改Excel
• 支持常用的样式自定义
• 基于map可以灵活定义的表头字段
• 支持一对多的导出，导入
• 支持模板的导出，一些常见的标签，自定义标签
• 支持HTML/Excel转换
• 支持word的导出，支持图片，Excel

常用注解

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

导入依赖

    <!--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 为自动生成get set 方法的依赖 -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.18</version>
    </dependency>
        <!--测试-->


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

创建实体类

POIDepartment 部门实体类

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

@Data
@ExcelTarget("department")//目标对象
public class POIDepartment {

    @Excel(name = "部门编号_department")
    private Long id;
    @Excel(name = "所属部门_employee,部门名称_department")//表示如果是员工那边导出，则该字段的表头为所属部门，如果是部门这边导出，表头为部门名称
    private String name;

    public POIDepartment(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public POIDepartment() {
    }
}

POIEmployee 员工实体类

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

import java.util.Date;
@Data
@ExcelTarget("employee")//目标对象
public class POIEmployee {

    @Excel(name = "编号")//该字段对应的表头设置
    private Long id;

    @Excel(name = "用户名")
    private String username;

    @Excel(name = "邮箱", width = 30)
    private String email;

    @Excel(name = "年龄")
    private Integer age;

    @Excel(name = "性别", replace = {"男_true", "女_false"})
    private Boolean gender;

    @Excel(name = "生日", format = "yyyy-MM-dd", width = 20)
    private Date birthday = new Date();

    /**
     * @ExcelEntity表示导出当前对象数据的时候还要顺便导出这个关联对象中加了@Excel注解的那些属性值
     */
    @ExcelEntity
    private POIDepartment department;

    public POIEmployee() {
    }

    public POIEmployee(Long id, String username, String email, Integer age, Boolean gender) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.age = age;
        this.gender = gender;

    }

    public POIEmployee(Long id, String username, String email, Integer age, Boolean gender, POIDepartment department) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.age = age;
        this.gender = gender;
        this.department = department;
    }

}

导出测试

普通导出

package test;

import Entity.POIDepartment;
import Entity.POIEmployee;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
public class test1 {
    /**
     * 测试普通导出
     */
    @Test
    public void testExport02() throws Exception {
        List<POIEmployee> list = new ArrayList<POIEmployee>();
        list.add(new POIEmployee(1L, "admin1", "admin1@qq.com", 20, true));
        list.add(new POIEmployee(2L, "admin2", "admin2@qq.com", 21, true));
        list.add(new POIEmployee(3L, "admin3", "admin3@qq.com", 22, false));
        list.add(new POIEmployee(4L, "admin4", "admin4@qq.com", 23, false));
        list.add(new POIEmployee(5L, "admin5", "admin5@qq.com", 24, true));
        /**
         * easypoi导出数据到excel
         *  第一个参数：ExportParams entity, 导出参数[包含导出的表格标题以及sheet名称]
         *  第二个参数：Class<?> pojoClass POIEmployee.class 导出数据domain实体类的字节码对象
         *  第三个参数：Collection<?> dataSet 导出的数据集合
         */
        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams("员工信息列表", "员工信息"),
                POIEmployee.class,
                list);
        //输出流输出数据
        OutputStream out = new FileOutputStream("D:/excel/员工信息列表.xls");
        workbook.write(out);
        out.flush();
        out.close();
    }

}

导出成功

测试导出员工关联的部门信息

   /**
     * 测试导出员工关联的部门信息
     */
    @Test
    public void testExport04() throws Exception {
        POIDepartment department01 = new POIDepartment(1L, "销售部");
        POIDepartment department02 = new POIDepartment(2L, "小卖部");
        POIDepartment department03 = new POIDepartment(3L, "零售部");
        POIDepartment department04 = new POIDepartment(4L, "人事部");


        List<POIEmployee> list = new ArrayList<POIEmployee>();
        list.add(new POIEmployee(1L, "admin1", "admin1@qq.com", 20, true, department01));
        list.add(new POIEmployee(2L, "admin2", "admin2@qq.com", 21, true,  department03));
        list.add(new POIEmployee(3L, "admin3", "admin3@qq.com", 22, false, department04));
        list.add(new POIEmployee(4L, "admin4", "admin4@qq.com", 23, false, department02));
        list.add(new POIEmployee(5L, "admin5", "admin5@qq.com", 24, true, department01));

        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams("员工信息列表", "员工信息"),
                POIEmployee.class,
                list);
        //输出流输出数据
        OutputStream out = new FileOutputStream("D:/excel/员工信息列表.xls");
        workbook.write(out);
        out.flush();
        out.close();
    }

测试成功

测试只导出部门信息

    /**
     * 测试只导出部门信息
     */
    @Test
    public void testExport05() throws Exception {
        POIDepartment department01 = new POIDepartment(1L, "销售部");
        POIDepartment department02 = new POIDepartment(2L, "小卖部");
        POIDepartment department03 = new POIDepartment(3L, "零售部");
        POIDepartment department04 = new POIDepartment(4L, "人事部");

        List<POIDepartment> list = new ArrayList<POIDepartment>();
        list.add(department01);
        list.add(department02);
        list.add(department03);
        list.add(department04);
        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams("部门信息列表", "部门信息"),
                POIDepartment.class,
                list);
        //输出流输出
        OutputStream out = new FileOutputStream("D:/excel/部门信息列表.xls");
        workbook.write(out);
        out.flush();
        out.close();
    }

测试成功

导入测试

  @Test
    public void testImport() throws Exception {
        //导入参数
        ImportParams params = new ImportParams();
        params.setTitleRows(1);     //导入数据的时候排除标题行
        params.setHeadRows(1);      //导入数据的时候排除表头行
        FileInputStream in = new FileInputStream("D:/excel/部门信息列表.xls");
        List<POIDepartment> list = ExcelImportUtil.importExcel(
                in,
                POIDepartment.class, params);
        list.forEach(e -> System.out.println(e));
    }

导入成功