EasyPoi使用案例

2023-12-31 12:36:11

EasyPoi使用案例

easypoi旨在简化Excel和Word的操作。基于注解的导入导出，修改注解就可以修改Excel；支持常用的样式自定义；基于map可以灵活定义表头字段；支持一对多的导入导出；支持模板的导出；支持HTML/Excel转换；支持word的导出以及图片。

注解介绍

EasyPoi最初的模板是实体和Excel的对应，model-row，field-col。

@Excel作用到字段上面对Excel列的一个描述；
@ExcelCollection表示集合，主要针对一对多的导出，比如一个老师对应多个科目，科目可以使用集合表示；
@ExcelEntity表示一个继续渗入到处的实体，只是告诉系统这个对象中同样有导出的字段；
@ExcelIgnore表示忽略导出这个字段；
@ExcelTarget作用于最外层对象，描述对象的id，以便支持一个对象可以针对不同导出不同处理。

环境介绍

本文使用maven构建，引入EasyPoi配置。

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.4.0</version>
</dependency>

导出带下拉格式的数据

创建AddressListEntity类

@Data 
public class AddressListEntity {
    @Excel(name="名称")
    private String name;
    
    @Excel(name = "性别")
    private Sex sex;

    @Excel(name = "B站登记", dict = "level", addressList = true)
    private int bilibili;

    @Excel(name = "状态", replace = {"初始化_0", "正常_1", "注销_2"}, addressList = true)
    private String status;
}

创建Sex枚举类

public Enum Sex {
    /**
     * 男
     */
    MAN,
    /**
     * 女
     */
    WOMAN
}

创建ExcelDiceAddressListHandlerImpl类，模拟字典的实现

public class ExcelDiceAddressListHandlerImpl implements IExcelDictHandler {
    /*
     * 获取字典的所有值
     */
    @Override
    public List<Map> getList(String dict) {
        List<Map> list = new ArrayList<>();
        Map<String, String> dictMap = new HashMap<>();
        dictMap.put("dictKey", "0");
        dictMap.put("dictValue", "深度睡眠");
        list.add(dictMap);
        dictMap = new HashMap<>();
        dictMap.put("dictKey", "1");
        dictMap.put("dictValue", "中度睡眠");
        list.add(dictMap);
        dictMap = new HashMap<>();
        dictMap.put("dictKey", "2");
        dictMap.put("dictValue", "浅睡");
        list.add(dictMap);
        return list;
    }
    
    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        if ("level".equals(dict)) {
            int level = Integer.parseInt(value.toString());
            switch (level) {
                case 0:
                    return "深度睡眠";
                case 1:
                    return "中度睡眠";
                case 2:
                    return "浅睡";
            }
        }
        return null;
    }
    
    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        if ("level".equals(dict)) {
            int level = Integer.parseInt(value.toString());
            switch (level) {
                case 0:
                    return "深度睡眠";
                case 1:
                    return "中度睡眠";
                case 2:
                    return "浅睡";
            }
        }
        return null;
    }
}

创建测试方法

@Test
public void testOneXls() throws Exception {
    List<AddressListEntity> list = new ArrayList<>();
    for (int i=0;i<100;i++) {
        AddressListEntity client = new AddressListEntity();
        client.setName("小明" + i);
        client.setSex(Sex.MAN);
        client.setStatus(i%3 + "");
        client.setBilibili(i%3);
        list.add(client);
    }
    Date start = new Date();
    //HSSF：xls，XSSF：xlsx
    ExportParams params = new ExportParams("下拉测试", "测试", ExcelType.HSSF);
    params.setDictHandler(new ExcelDiceAddressListHandlerImpl());
    Workbook workbook = ExcelExportUtil.exportExcel(params, AddressListEntity.class, list);
    System.out.println(new Date().getTime() - start.getTime());
    FileOutputStream fos = new FileOutputStream("AddressListTest.testOne.xls");
    workbook.write(fos);
    fos.close();
}

结果：输出AddressListTest.testOne.xls，一级标题“下拉测试”合并单元格并居中显示，二级标题为名称、性别、B站登记和状态，B站登记下拉数据为深度睡眠、中度睡眠、浅睡，状态下拉数据为初始化、正常、注销。

枚举测试

创建枚举数据实体类EnumDataEntity.java

package org.lxx.stream.easy.poi.enums;

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

@Data
public class EnumDataEntity {

    @Excel(name = "名字")
    private String name;

    @Excel(name = "性别")
    private Sex sex;

    @Excel(name = "基础状态")
    private StatusEnum baseStatus;

    //指定枚举导出使用的字段和使用的函数
    @Excel(name = "状态", enumExportField = "message", 
           enumImportMethod = "getByMessage")
    private StatusEnum status;
}

创建状态枚举类StatusEnum.java

package org.lxx.stream.easy.poi.enums;

public enum StatusEnum {
    Init(0, "初始化"),
    Ready(1, "正常"),
    ChangePassword(2, "需要修改密码"),
    Frozen(4, "冻结"),
    Disabled(64, "禁用");
    
    private final Integer _code;
    private final String _message;
    
    StatusEnum(Integer code, String message) {
        _code = code;
        _message = message;
    }
    
    public Integer getValue() {
        return _code;
    }

    public String getMessage() {
        return _message;
    }
    
    public static StatusEnum getByMessage(String message) {
        StatusEnum[] values = StatusEnum.values();
        for (StatusEnum value:values) {
            if (value._message.equals(message)) {
                return value;
            }
        }
        return null;
    }
}

编写测试方法

@Test
public void test() throws Exception {
    List<EnumDataEntity> list = new ArrayList<>();
    for (int i=0;i<100;i++) {
        EnumDataEntity client = new EnumDataEntity();
        client.setName("小华" + i);
        //性别使用Sex枚举值MAN
        client.setSex(Sex.MAN);
        //状态使用StatusEnum的getByMessage获取到message的值
        client.setStatus(StatusEnum.Init);
        //基础状态使用了StatusEnum的Ready
        client.setBaseStatus(StatusEnum.Ready);
        list.add(client);
    }
    Date start = new Date();
    ExportParams params = new ExportParams("枚举测试", "测试", ExcelType.XSSF);
    Workbook workbook = ExcelExportUtil
        .exportExcel(params, EnumDataEntity.class, list);
    System.out.println(new Date().getTime() - start.getTime());
    FileOutputStream fos = new FileOutputStream("EnumDataEntity.xlsx");
    workbook.write(fos);
    fos.close();
}

超链接测试

创建实体类HyperLinkEntity.java

package org.lxx.stream.easy.poi.entity;

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

@Data
public class HyperLinkEntity {

    @Excel(name = "名称", isHyperlink = true)
    private String name;

    @Excel(name = "URL")
    private String url;
}

编写测试方法

@Test
public void test() throws Exception {
    List<HyperLinkEntity> list = new ArrayList<>();
    HyperLinkEntity client = new HyperLinkEntity();
    client.setName("百度");
    client.setUrl("https://www.baidu.com/");
    list.add(client);
    client = new HyperLinkEntity();
    client.setName("新浪");
    client.setUrl("http://www.sina.com.cn/");
    list.add(client);
    Date start = new Date();
    ExportParams params = new ExportParams("title", "测试", ExcelType.XSSF);
    params.setDataHandler(new ExcelDataHandlerDefaultImpl() {
        @Override
        public Hyperlink getHyperlink(CreationHelper creationHelper, Object obj, 
                                      String name, Object value) {
            //创建给定类型的超链接
            Hyperlink link = creationHelper.createHyperlink(HyperlinkType.URL);
            HyperLinkEntity e = (HyperLinkEntity) obj;
            link.setAddress(e.getUrl());
            link.setLabel(e.getName());
            return link;
        }
    });
    Workbook workbook = ExcelExportUtil.exportExcel(params, HyperLinkEntity.class, 
                                                    list);
    System.out.println(new Date().getTime() - start.getTime());
    FileOutputStream fos = new FileOutputStream("ExcelExportForLink.xlsx");
    workbook.write(fos);
    fos.close();
}

结果：点击生成的Excel中的百度可以链接到百度的首页。

添加水印测试

编写测试方法

@Test
public void testWaterMarkOne() {
    List<AddressListEntity> list = new ArrayList<>();
    for (int i=0;i<200;i++) {
        AddressListEntity client = new AddressListEntity();
        client.setName("小马" + i);
        client.setSex(Sex.MAN);
        client.setStatus(i%3 + "");
        client.setBilibili(i%3);
        list.add(client);
    }

    Date start = new Date();
    ExportParams params = new ExportParams("下拉测试", "测试", ExcelType.XSSF);
    params.setDictHandler(new ExcelDiceAddressListHandlerImpl());
    Workbook workbook = ExcelExportUtil.exportExcel(params, AddressListEntity.class,
                                                    list);
    try {
        //水印添加的位置可以是LEFT,CENTER,RIGHT，默认是LEFT
       PoiWatermarkUtil.putWaterRemarkToExcel(workbook.getSheetAt(0),"watermark.png", 
                                              "CENTER");
        System.out.println(new Date().getTime() - start.getTime());
        FileOutputStream fos = new FileOutputStream("WaterMarkTest.testTwo.xlsx");
        workbook.write(fos);
        fos.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

结果：输出Excel带有水印图片；WPS打开没有水印，导出时会带有水印。

分组导出测试

创建实体类GroupNameEntity.java

package org.lxx.stream.easy.poi.entity;

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

import java.util.Date;

@Data
public class GroupNameEntity {

    private String id;

    @Excel(name = "电话号码", groupName = "联系方式", orderNum = "1")
    private String clientPhone;

    @Excel(name = "姓名", orderNum = "2")
    private String clientName;

    @Excel(name = "备注", orderNum = "6")
    private String remark;

    @Excel(name = "出生日期", format = "yyyy-MM-dd", width = 20, groupName = "时间", 
           orderNum = "4")
    private Date birthday;

    @Excel(name = "创建时间", groupName = "时间", orderNum = "3")
    private String createTime;
}

创建测试方法

@Test
public void base() throws Exception {
    List<GroupNameEntity> list = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        GroupNameEntity client = new GroupNameEntity();
        client.setBirthday(new Date());
        client.setCreateTime("2022-10-22");
        client.setClientName("小明" + i);
        client.setClientPhone("18888" + i);
        client.setId("1" + i);
        client.setRemark("测试" + i);
        list.add(client);
    }

    ExportParams params = new ExportParams("GroupName测试", "测试", ExcelType.XSSF);
    Workbook workbook = ExcelExportUtil.exportExcel(params, GroupNameEntity.class, 
                                                    list);
    FileOutputStream fos = new FileOutputStream("groupName.xlsx");
    workbook.write(fos);
    fos.close();
}

分组导入测试1

创建测试方法，导入数据模板如下

@Test
public void groupNameTest() {
    ImportParams params = new ImportParams();
    params.setHeadRows(2);
    List<GroupNameEntity> list = ExcelImportUtil.importExcel(
          new File("groupName.xlsx"), GroupNameEntity.class, params);
    Assert.assertEquals(10, list.size());
    Assert.assertEquals("187970", list.get(0).getClientPhone());
    Assert.assertEquals("小明0", list.get(0).getClientName());
    System.out.println(ReflectionToStringBuilder.toString(list.get(0)));
}

分组导入测试2

创建实体类GnStudentEntity.java

package org.lxx.stream.easy.poi.entity;

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

import java.util.Date;

@Data
public class GnStudentEntity {

    @Excel(name = "学生姓名", height = 20, width = 30, orderNum = "2")
    private String name;

    @Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", orderNum = "3")
    private int sex;

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

    @Excel(name = "进校日期", format = "yyyy-MM-dd", orderNum = "5")
    private Date registrationDate;
}

创建实体类GnEntity.java

package org.lxx.stream.easy.poi.entity;

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

@Data
public class GnEntity {

    @Excel(name = "电话号码", groupName = "联系方式", orderNum = "1")
    private String clientPhone;

    @Excel(name = "姓名")
    private String clientName;

    @ExcelEntity(name = "学生", show = true)
    private GnStudentEntity studentEntity;
}

创建测试方法

@Test
public void groupNameEntityTest() {
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(2);
    List<GnEntity> list = ExcelImportUtil.importExcel(
        new File("groupName_GnEntity.xlsx"), GnEntity.class, params);
    Assert.assertEquals(10, list.size());
}

导入模板如下

枚举数据导入测试

创建测试方法

@Test
public void test() {
    try {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        List<EnumDataEntity> list = ExcelImportUtil.importExcel(
            new FileInputStream(new File("EnumDataEntity.xlsx")),
            EnumDataEntity.class, params);
        Assert.assertEquals(6, list.size());
    } catch (Exception e) {
        e.printStackTrace();
    }
}

导入模板如下

[外链图片转存中…(img-5ZDK2rVz-1703995003189)]

枚举数据导入测试

创建测试方法

@Test
public void test() {
    try {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        List<EnumDataEntity> list = ExcelImportUtil.importExcel(
            new FileInputStream(new File("EnumDataEntity.xlsx")),
            EnumDataEntity.class, params);
        Assert.assertEquals(6, list.size());
    } catch (Exception e) {
        e.printStackTrace();
    }
}

导入模板如下

文章来源:https://blog.csdn.net/dolly_baby/article/details/135314671
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！