在我们平时工作中经常会遇到要操作Excel的功能,比如导出个用户信息或者订单信息的Excel报表。你肯定听说过POI这个东西,可以实现。但是POI实现的API确实很麻烦,它需要写那种逐行解析的代码(类似Xml解析)。今天给大家推荐一款非常好用的Excel导入导出工具EasyPoi,希望对大家有所帮助!
用惯了SpringBoot的朋友估计会想到,有没有什么办法可以直接定义好需要导出的数据对象,然后添加几个注解,直接自动实现Excel导入导出功能?
EasyPoi正是这么一款工具,如果你不太熟悉POI,想简单地实现Excel操作,用它就对了!
EasyPoi的目标不是替代POI,而是让一个不懂导入导出的人也能快速使用POI完成Excel的各种操作,而不是看很多API才可以完成这样的工作。
在SpringBoot中集成EasyPoi非常简单,只需添加如下一个依赖即可,真正的开箱即用!
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
接下来介绍下EasyPoi的使用,以会员信息和订单信息的导入导出为例,分别实现下简单的单表导出和具有关联信息的复杂导出。
我们以会员信息列表导出为例,使用EasyPoi来实现下导出功能,看看是不是够简单!
Member
,封装会员信息;/**
* 购物会员
* Created by macro on 2021/10/12.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
@Excel(name = "ID", width = 10)
private Long id;
@Excel(name = "用户名", width = 20, needMerge = true)
private String username;
private String password;
@Excel(name = "昵称", width = 20, needMerge = true)
private String nickname;
@Excel(name = "出生日期", width = 20, format = "yyyy-MM-dd")
private Date birthday;
@Excel(name = "手机号", width = 20, needMerge = true, desensitizationRule = "3_4")
private String phone;
private String icon;
@Excel(name = "性别", width = 10, replace = {"男_0", "女_1"})
private Integer gender;
}
@Excel
,通过在对象上添加@Excel
注解,可以将对象信息直接导出到Excel中去,下面对注解中的属性做个介绍;3_4
表示只显示字符串的前3
位和后4
位,其他为*
号;/**
* EasyPoi导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {
@ApiOperation(value = "导出会员列表Excel")
@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
public void exportMemberList(ModelMap map,
HttpServletRequest request,
HttpServletResponse response) {
List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
ExportParams params = new ExportParams("会员列表", "会员列表", ExcelType.XSSF);
map.put(NormalExcelConstants.DATA_LIST, memberList);
map.put(NormalExcelConstants.CLASS, Member.class);
map.put(NormalExcelConstants.PARAMS, params);
map.put(NormalExcelConstants.FILE_NAME, "memberList");
PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}
}
members.json
;导入功能实现起来也非常简单,下面以会员信息列表的导入为例。
@RequestPart
注解修饰文件上传参数,否则在Swagger中就没法显示上传按钮了;/**
* EasyPoi导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {
@ApiOperation("从Excel导入会员列表")
@RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
@ResponseBody
public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
try {
List<Member> list = ExcelImportUtil.importExcel(
file.getInputStream(),
Member.class, params);
return CommonResult.success(list);
} catch (Exception e) {
e.printStackTrace();
return CommonResult.failed("导入失败!");
}
}
}
当然EasyPoi也可以实现更加复杂的Excel操作,比如导出一个嵌套了会员信息和商品信息的订单列表,下面我们来实现下!
Product
,用于封装商品信息;/**
* 商品
* Created by macro on 2021/10/12.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class Product {
@Excel(name = "ID", width = 10)
private Long id;
@Excel(name = "商品SN", width = 20)
private String productSn;
@Excel(name = "商品名称", width = 20)
private String name;
@Excel(name = "商品副标题", width = 30)
private String subTitle;
@Excel(name = "品牌名称", width = 20)
private String brandName;
@Excel(name = "商品价格", width = 10)
private BigDecimal price;
@Excel(name = "购买数量", width = 10, suffix = "件")
private Integer count;
}
Order
,订单和会员是一对一关系,使用@ExcelEntity
注解表示,订单和商品是一对多关系,使用@ExcelCollection
注解表示,Order
就是我们需要导出的嵌套订单数据;/**
* 订单
* Created by macro on 2021/10/12.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class Order {
@Excel(name = "ID", width = 10,needMerge = true)
private Long id;
@Excel(name = "订单号", width = 20,needMerge = true)
private String orderSn;
@Excel(name = "创建时间", width = 20, format = "yyyy-MM-dd HH:mm:ss",needMerge = true)
private Date createTime;
@Excel(name = "收货地址", width = 20,needMerge = true )
private String receiverAddress;
@ExcelEntity(name = "会员信息")
private Member member;
@ExcelCollection(name = "商品列表")
private List<Product> productList;
}
ExportParams
中的setExclusions
方法排除掉;/**
* EasyPoi导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {
@ApiOperation(value = "导出订单列表Excel")
@RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
public void exportOrderList(ModelMap map,
HttpServletRequest request,
HttpServletResponse response) {
List<Order> orderList = getOrderList();
ExportParams params = new ExportParams("订单列表", "订单列表", ExcelType.XSSF);
//导出时排除一些字段
params.setExclusions(new String[]{"ID", "出生日期", "性别"});
map.put(NormalExcelConstants.DATA_LIST, orderList);
map.put(NormalExcelConstants.CLASS, Order.class);
map.put(NormalExcelConstants.PARAMS, params);
map.put(NormalExcelConstants.FILE_NAME, "orderList");
PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}
}
如果你想对导出字段进行一些自定义处理,EasyPoi也是支持的,比如在会员信息中,如果用户没有设置昵称,我们添加下
暂未设置
信息。
ExcelDataHandlerDefaultImpl
类,然后在exportHandler
方法中实现自定义处理逻辑;/**
* 自定义字段处理
* Created by macro on 2021/10/13.
*/
public class MemberExcelDataHandler extends ExcelDataHandlerDefaultImpl<Member> {
@Override
public Object exportHandler(Member obj, String name, Object value) {
if("昵称".equals(name)){
String emptyValue = "暂未设置";
if(value==null){
return super.exportHandler(obj,name,emptyValue);
}
if(value instanceof String&&StrUtil.isBlank((String) value)){
return super.exportHandler(obj,name,emptyValue);
}
}
return super.exportHandler(obj, name, value);
}
@Override
public Object importHandler(Member obj, String name, Object value) {
return super.importHandler(obj, name, value);
}
}
MemberExcelDataHandler
处理器的setNeedHandlerFields
设置需要自定义处理的字段,并调用ExportParams
的setDataHandler
设置自定义处理器;/**
* EasyPoi导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {
@ApiOperation(value = "导出会员列表Excel")
@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
public void exportMemberList(ModelMap map,
HttpServletRequest request,
HttpServletResponse response) {
List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
ExportParams params = new ExportParams("会员列表", "会员列表", ExcelType.XSSF);
//对导出结果进行自定义处理
MemberExcelDataHandler handler = new MemberExcelDataHandler();
handler.setNeedHandlerFields(new String[]{"昵称"});
params.setDataHandler(handler);
map.put(NormalExcelConstants.DATA_LIST, memberList);
map.put(NormalExcelConstants.CLASS, Member.class);
map.put(NormalExcelConstants.PARAMS, params);
map.put(NormalExcelConstants.FILE_NAME, "memberList");
PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}
}
体验了一波EasyPoi,它使用注解来操作Excel的方式确实非常好用。如果你想生成更为复杂的Excel的话,可以考虑下它的模板功能。
项目官网:https://gitee.com/lemur/easypoi
https://github.com/macrozheng/mall-learning/tree/master/mall-tiny-easypoi