引入
为什么使用EasyExcel?
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
EasyExcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用EasyExcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便。
官网GitHub地址:
https://github.com/alibaba/easyexcel
官方文档地址:
https://www.yuque.com/easyexcel/doc/easyexcel
实例
创建初始数据
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;
创建SpringBoot项目
导入EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
User
@Data
public class User {
@ExcelIgnore //生成报表时忽略
private Long id;
@ExcelProperty(value = "用户ID",index = 0) // 定义表头名称和位置,0代表第一列
private Long userId;
@ExcelProperty(value = "用户名称",index = 1)
private String username;
@ExcelProperty(value = "电话号码",index = 2)
private String phone;
@ExcelProperty(value = "创建日期",index = 3)
@DateTimeFormat("yyyy年MM月dd日")
private Date createTime;
}
监听器
@Slf4j
public class UserDataListener extends AnalysisEventListener<User> {
private UserService userService;
public UserDataListener(UserService userService) {
this.userService = userService;
}
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<User>();
@Override
public void invoke(User data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
if (!CollectionUtils.isEmpty(list)) {
userService.saveBatch(list);
}
log.info("存储数据库成功!");
}
}
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";
}
/**
* 导出文件
* @param response
* @throws IOException
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 设置上下文类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 设置编码
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());
}
}
测试
(1)测试导入功能,使用Postman测试,如下图所示:
1.jpg(2)测试导出功能,浏览器访问:http://localhost:8080/download
网友评论