EasyPoi使用案例

EasyPoi使用案例

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

注解介绍

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

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

环境介绍

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

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

导出带下拉格式的数据

1. 创建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;
}

2. 创建Sex枚举类

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

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

public class ExcelDiceAddressListHandlerImpl implements IExcelDictHandler {/** 获取字典的所有值*/@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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;}
}

4. 创建测试方法

@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：xlsxExportParams 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站登记下拉数据为深度睡眠、中度睡眠、浅睡，状态下拉数据为初始化、正常、注销。

枚举测试

1. 创建枚举数据实体类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;
}

2. 创建状态枚举类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;}
}

3. 编写测试方法

@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枚举值MANclient.setSex(Sex.MAN);//状态使用StatusEnum的getByMessage获取到message的值client.setStatus(StatusEnum.Init);//基础状态使用了StatusEnum的Readyclient.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();
}

超链接测试

1. 创建实体类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;
}

2. 编写测试方法

@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() {@Overridepublic 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中的百度可以链接到百度的首页。

添加水印测试

1. 编写测试方法

@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，默认是LEFTPoiWatermarkUtil.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打开没有水印，导出时会带有水印。

分组导出测试

1. 创建实体类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;
}

2. 创建测试方法

@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

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

1. 创建实体类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;
}

2. 创建实体类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;
}

3. 创建测试方法

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

4. 导入模板如下

枚举数据导入测试

1. 创建测试方法

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

2. 导入模板如下

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

枚举数据导入测试

1. 创建测试方法

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

2. 导入模板如下