springboot导入导出(EasyPoi)

//原文地址https://mp.weixin.qq.com/s/dyJ2Fr7-Nnnxnhx_2Slhkg

-----------------------------------------简单导出-------------------------------

1.在SpringBoot中集成EasyPoi非常简单，只需添加如下一个依赖即可，真正的开箱即用！

cn.afterturn easypoi-spring-boot-starter 4.4.0

2.首先创建一个对象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;
  }

在此我们就可以看到EasyPoi的核心注解@Excel，通过在对象上添加@Excel注解，可以将对象信息直接导出到Excel中去，下面对注解中的属性做个介绍；

name：Excel中的列名；
width：指定列的宽度；
needMerge：是否需要纵向合并单元格；
format：当属性为时间类型时，设置时间的导出导出格式；
desensitizationRule：数据脱敏处理，3_4表示只显示字符串的前3位和后4位，其他为*号；
replace：对属性进行替换；
suffix：对数据添加后缀。
3.接下来我们在Controller中添加一个接口，用于导出会员列表到Excel，具体代码如下；

/**

• 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 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);
  }
  }

-----------------------------------------复杂导出-------------------------------

首先添加商品对象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 productList;
  }
  接下来在Controller中添加导出订单列表的接口，由于有些会员信息我们不需要导出，可以调用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 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);
  }
  }

---------------------------------------------简单导入--------------------------------

在Controller中添加会员信息导入的接口，这里需要注意的是使用@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 list = ExcelImportUtil.importExcel(
  file.getInputStream(),
  Member.class, params);
  return CommonResult.success(list);
  } catch (Exception e) {
  e.printStackTrace();
  return CommonResult.failed(“导入失败！”);
  }
  }
  }

自定义处理
如果你想对导出字段进行一些自定义处理，EasyPoi也是支持的，比如在会员信息中，如果用户没有设置昵称，我们添加下暂未设置信息。

我们需要添加一个处理器继承默认的ExcelDataHandlerDefaultImpl类，然后在exportHandler方法中实现自定义处理逻辑；
/**

• 自定义字段处理
• Created by macro on 2021/10/13.
  */
  public class MemberExcelDataHandler extends ExcelDataHandlerDefaultImpl {

@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);
}
}
然后修改Controller中的接口，调用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 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);
  }
  }