EasyPoi简介
POI是Java操作MicroOffice(如对Excel的导入导出)的一个插件。POI的全称是(Poor Obfuscation Implementation),POI官网地址是 http://poi.achache.org/index.html 。
EasyPoi对POI进行了优化,更加设计精巧,使用简单,接口丰富,扩展简单。EasyPOI的同类产品有Execel4J,Hutools等。EasyPoi官网地址是 https://gitee.com/lemur/easypoi
用惯了SpringBoot的朋友估计会想到,有没有什么办法可以直接定义好需要导出的数据对象,然后添加几个注解,直接自动实现Excel导入导出功能?
EasyPoi正是这么一款工具,如果你不太熟悉POI,想简单地实现Excel操作,用它就对了!
EasyPoi的目标不是替代POI,而是让一个不懂导入导出的人也能快速使用POI完成Excel的各种操作,而不是看很多API才可以完成这样的工作。
Springboot集成EasyPoi
在SpringBoot中集成EasyPoi非常简单,只需添加一个依赖即可,springboot会通过自己的自动装配功能将这个第三方组件加载到spring容器进行管理。
这里贴出完整的pom.xml,读者直接拷贝过去使用即可。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.2.5.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>springboot-export</artifactId><version>0.0.1-SNAPSHOT</version><name>springboot-export</name><description>springboot-export</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.4.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>RELEASE</version><scope>compile</scope></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>
创建一个用户对象User,封装用户信息:
- User中不加@Excel注解的字段,不会被导出到excel表格中
- BigDecimal, Date等类型的属性,可以直接导出到excel表格中
- 对于以Integer类型表达的性别,可以将其转为汉字,有对应的处理方式
package com.example.springbootexport.model;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.EqualsAndHashCode;import java.math.BigDecimal;
import java.util.Date;@Data
@EqualsAndHashCode(callSuper = false)
public class User {@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(name = "合同金额")private BigDecimal totalAmount;private String notExport;
}
- 在此我们就可以看到EasyPoi的核心注解
@Excel
,通过在对象上添加@Excel
注解,可以将对象信息直接导出到Excel中去,下面对注解中的属性做个介绍;- name:Excel中的列名;
- width:指定列的宽度;
- needMerge:是否需要纵向合并单元格;
- format:当属性为时间类型时,设置时间的导出导出格式;
- desensitizationRule:数据脱敏处理,
3_4
表示只显示字符串的前3
位和后4
位,其他为*
号; - replace:对属性进行替换;
- suffix:对数据添加后缀。
创建一个Controller,用于导出会员列表到Excel,具体代码如下
package com.example.springbootexport.controller;import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.view.PoiBaseView;
import com.example.springbootexport.model.User;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;@Controller
@RequestMapping("/easyPoi")
public class EasyPoiController {@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)public void exportMemberList(ModelMap map,HttpServletRequest request,HttpServletResponse response) {List<User> memberList = new ArrayList<>();User user1 = new User();user1.setBirthday(new Date());user1.setGender(1);user1.setIcon("png");user1.setId(1000L);user1.setNickname("bruce");user1.setPassword("123456");user1.setPhone("12365789018");user1.setUsername("bruce wang");user1.setNotExport("不导出");user1.setTotalAmount(BigDecimal.valueOf(10.50));User user2 = new User();user2.setBirthday(new Date());user2.setGender(0);user2.setIcon("jpf");user2.setId(2000L);user2.setNickname("bruce1");user2.setPassword("1234567890");user2.setPhone("12365789089");user2.setUsername("bruce-wang");user2.setNotExport("不导出");user2.setTotalAmount(BigDecimal.valueOf(11.50));memberList.add(user1);memberList.add(user2);ExportParams params = new ExportParams("会员列表", "会员列表", ExcelType.XSSF);map.put(NormalExcelConstants.DATA_LIST, memberList);map.put(NormalExcelConstants.CLASS, User.class);map.put(NormalExcelConstants.PARAMS, params);map.put(NormalExcelConstants.FILE_NAME, "memberList");PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);}
}
整个代码结构如下:
启动程序,调用接口导出excel表格:
http://localhost:8083/easyPoi/exportMemberList
导出的excel表格如下:
参考地址:
SpringBoot实现Excel导入导出,好用到爆,POI可以扔掉了! - 腾讯云开发者社区-腾讯云 (tencent.com)