EasyPOI的使用

---

前言

在实际的开发中，我们操作数据的数据的同时往往需要导出一些数据到excel,使用eaypoi支持多种功能，excel的导出和导入，比如说，普通字段，可隐藏不想导出的字段，支持插入图片，一对一，一对多关系等，满足基本业务需求。

【官网地址】

---

一、EasyPOI常用注解？

@Excel
@ExcelTarget
@ExcelEntity
@ExcelCollection
@ExcelIgnore

二、使用步骤

1.引入依赖

 <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>

2.声明导入导出对象

/*举例子：*/
@Data
//参数为标识
@ExcelTarget("users")
//写入文件需要序列化
public class User implements Serializable {
    // 不导出
    @ExcelIgnore
    @Excel(name = "编号",orderNum = "1")
    private String id;

    // 导出列名
    @Excel(name = "姓名",suffix = "-->后缀")
    private String name;

    // 格式化
    @Excel(name = "生日",format = "yyyy年MM月dd日",width = 30.0)
    private Date bir;

    @Excel(name = "身份证号",width = 30.0)
    private String no;

    @Excel(name = "籍贯")
    private String address;

    // 为了自定义导出格式
    @ExcelIgnore
    private List<String> hobbies;

    @Excel(name = "爱好",width = 50.0,orderNum = "5")
    private String hobbyStr;

    public String getHobbyStr() {
        StringBuilder sb = new StringBuilder();
        hobbies.stream().forEach(e->{
            sb.append(e).append("、");
        });
        if (sb.length() > 0){
            sb.deleteCharAt(sb.length()-1);
        }
        return sb.toString();
    }

    @ExcelEntity
    private Card card;

    @ExcelCollection(name = "订单信息")
    private List<Order> orders;

    @Excel(name = "头像",width = 20.0,type = 2)
    private String avatar;
}

3.调用API

/**
 * 导出excel
 *
 * @param response
 * @throws IOException
 */
@RequestMapping("export")
public void ExportExcel(HttpServletResponse response) throws IOException {
    List<User> users = userService.findAll();
    users.stream().forEach(e ->{
    	// 模拟集合数据
        ArrayList<String> hobbies = new ArrayList<>();
        hobbies.add("打篮球");
        hobbies.add("喝酒");
        e.setHobbies(hobbies);
		// 模拟一对多
        ArrayList<Order> orders = new ArrayList<>();
        orders.add(new Order(1,"订单1",new BigDecimal(12)));
        orders.add(new Order(2,"订单2",new BigDecimal(12)));
        orders.add(new Order(3,"订单3",new BigDecimal(12)));
        e.setOrders(orders);
		// 模拟一对一
        Card card = new Card("2243435435465465","地址");
        e.setCard(card);

        e.setAvatar("https://img2.baidu.com/it/u=458108495,2818727394&fm=26&fmt=auto&gp=0.jpg");
    });
    // ExcelExportUtil工具类
    // ExportParams(String title, String sheetName)
    // 标题  工作簿名
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户列表信息", "用户信息"), User.class, users);
    response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("用户列表.xls", "UTF-8"));
    ServletOutputStream outputStream = response.getOutputStream();
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
/**
 * excel导入
 *
 * @param excelFile
 * @return
 * @throws Exception
 */
@RequestMapping("/import")
public String importExcel(MultipartFile excelFile) throws Exception {
    //excel导入
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    // 工具类导入
    List<User> users = ExcelImportUtil.importExcel(excelFile.getInputStream(), User.class, params);
    userService.saveAll(users);
    return "redirect:/user/findAll";
}

4.如果数据量一万以上可参考官方文档采用分段的方式进行数据导入和导出

@Test
 public void bigDataExport() throws Exception {

     List<MsgClient> list = new ArrayList<MsgClient>();
     Workbook workbook = null;
     Date start = new Date();
     ExportParams params = new ExportParams("大数据测试", "测试");
     for (int i = 0; i < 1000000; i++) {  //一百万数据量
         MsgClient client = new MsgClient();
         client.setBirthday(new Date());
         client.setClientName("小明" + i);
         client.setClientPhone("18797" + i);
         client.setCreateBy("JueYue");
         client.setId("1" + i);
         client.setRemark("测试" + i);
         MsgClientGroup group = new MsgClientGroup();
         group.setGroupName("测试" + i);
         client.setGroup(group);
         list.add(client);
         if(list.size() == 10000){
             workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, list);
             list.clear();
         }
     }
     ExcelExportUtil.closeExportBigExcel();
     System.out.println(new Date().getTime() - start.getTime());
     File savefile = new File("D:/excel/");
     if (!savefile.exists()) {
         savefile.mkdirs();
     }
     FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportBigData.bigDataExport.xlsx");
     workbook.write(fos);
     fos.close();
 }

5. 合并单元格（其它博客有详解）

三 如过涉及大导入数据有校验的时候，可以考虑使用hibernate-validator

<!-- JSR 303 规范验证包 -->
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

可自定义检查规则：参考博客

总结

简单注解实现功能