引入
為什么使用EasyExcel?
Java解析嗜历、生成Excel比較有名的框架有Apache poi、jxl奥邮。但他們都存在一個(gè)嚴(yán)重的問題就是非常的耗內(nèi)存腥光，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷阳掐，比如07版Excel解壓縮以及解壓后存儲(chǔ)都是在內(nèi)存中完成的始衅，內(nèi)存消耗依然很大。
EasyExcel重寫了poi對(duì)07版Excel的解析缭保，一個(gè)3M的excel用POI sax解析依然需要100M左右內(nèi)存汛闸，改用EasyExcel可以降低到幾M，并且再大的excel也不會(huì)出現(xiàn)內(nèi)存溢出艺骂；03版依賴POI的sax模式诸老，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡(jiǎn)單方便钳恕。
官網(wǎng)GitHub地址：
https://github.com/alibaba/easyexcel
官方文檔地址：
https://www.yuque.com/easyexcel/doc/easyexcel
實(shí)例
創(chuàng)建初始數(shù)據(jù)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NULL DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`create_time` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 1, 'banq', '13588888888', '2021-11-19');
SET FOREIGN_KEY_CHECKS = 1;
創(chuàng)建SpringBoot項(xiàng)目
導(dǎo)入EasyExcel依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
User
@Data
public class User {
@ExcelIgnore //生成報(bào)表時(shí)忽略
private Long id;
@ExcelProperty(value = "用戶ID",index = 0) // 定義表頭名稱和位置,0代表第一列
private Long userId;
@ExcelProperty(value = "用戶名稱",index = 1)
private String username;
@ExcelProperty(value = "電話號(hào)碼",index = 2)
private String phone;
@ExcelProperty(value = "創(chuàng)建日期",index = 3)
@DateTimeFormat("yyyy年MM月dd日")
private Date createTime;
}
監(jiān)聽器
@Slf4j
public class UserDataListener extends AnalysisEventListener<User> {
private UserService userService;
public UserDataListener(UserService userService) {
this.userService = userService;
}
/**
* 每隔5條存儲(chǔ)數(shù)據(jù)庫(kù)别伏，實(shí)際使用中可以3000條，然后清理list 忧额，方便內(nèi)存回收
*/
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<User>();
@Override
public void invoke(User data, AnalysisContext context) {
log.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有數(shù)據(jù)解析完成厘肮！");
}
/**
* 加上存儲(chǔ)數(shù)據(jù)庫(kù)
*/
private void saveData() {
log.info("{}條數(shù)據(jù)，開始存儲(chǔ)數(shù)據(jù)庫(kù)睦番！", list.size());
if (!CollectionUtils.isEmpty(list)) {
userService.saveBatch(list);
}
log.info("存儲(chǔ)數(shù)據(jù)庫(kù)成功类茂！");
}
}
Dao層
@Mapper
@Repository
public interface UserMapper {
void batchInsert(List<User> list);
List<User> queryAll();
}
<insert id="batchInsert">
insert into user (user_id,username,phone,create_time) values
<foreach collection="list" item="user" separator=",">
(#{user.userId},#{user.username},#{user.phone},#{user.createTime})
</foreach>
</insert>
<select id="queryAll" resultType="ink.banq.demo.entity.User">
select id,user_id,username,phone,create_time from user
</select>
Service層
public interface UserService {
void saveBatch(List<User> list);
List<User> selectAll();
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public void saveBatch(List<User> list) {
userMapper.batchInsert(list);
}
@Override
public List<User> selectAll() {
return userMapper.queryAll();
}
}
Controller層
@RestController
public class OperateExcelController {
@Autowired
private UserService userService;
/**
* 上傳文件
* @param file
* @return
* @throws IOException
*/
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userService)).sheet().doRead();
return "success";
}
/**
* 導(dǎo)出文件
* @param response
* @throws IOException
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 設(shè)置上下文類型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 設(shè)置編碼
response.setCharacterEncoding("utf-8");
// 防止中文亂碼
String fileName = URLEncoder.encode("文件名稱", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()).sheet("模板").doWrite(userService.selectAll());
}
}
測(cè)試
（1）測(cè)試導(dǎo)入功能，使用Postman測(cè)試托嚣，如下圖所示：
1.jpg
（2）測(cè)試導(dǎo)出功能巩检，瀏覽器訪問：http://localhost:8080/download
2.jpg
