easyexcel 上传文件（记录一次使用Easyexcel上传导入数据库和导出EXCEL的实现过程）
这是一个最简单的例子，使用了Springboot/Thymeleaf/mybatis等技术完成了初步的导入，给和我一样的初学者提供一点参考，我来为大家讲解一下关于easyexcel 上传文件?跟着小编一起来看一看吧!
easyexcel 上传文件
这是一个最简单的例子，使用了Springboot/Thymeleaf/mybatis等技术完成了初步的导入，给和我一样的初学者提供一点参考。
一、数据表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(255) NULL DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 350 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
二、实体类
package pers.gl.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class User {
private String id;
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private Integer age;
}
三、mybatis的xml文件
使用了批量插入数据的语句foreach。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.gl.dao.UserMapper">
<!-- 查询全部 -->
<select id="findAll" resultType="pers.gl.entity.User">
select * from user order by id desc
</select>
<!-- 单条插入数据-->
<insert id="insertData" parameterType="pers.gl.entity.User">
insert into user(name,age) values(#{name},#{age})
</insert>
<!-- 批量插入数据-->
<insert id="saveList">
insert into user(name,age) values
<foreach item="item" index="index" collection="list" separator=",">
(#{item.name},#{item.age})
</foreach>
</insert>
</mapper>
四、mapper接口
package pers.gl.dao;
import java.util.List;
import pers.gl.entity.User;
public interface UserMapper {
// 全部数据
public List<User> findAll();
// 插入数据
public int insertData(User user);
// 批量增加保存
public int saveList(List<User> list);
}
五、Service
package pers.gl.service;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.alibaba.excel.EasyExcel;
import pers.gl.dao.UserMapper;
import pers.gl.entity.User;
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
// 全部数据
public List<User> findAll() {
return userMapper.findAll();
}
// 插入数据
public int insertData(User user) {
return userMapper.insertData(user);
}
// 批量增加保存
public int saveList(List<User> list) {
return userMapper.saveList(list);
}
//导出EXCEL文件
public void exportExcel(HttpServletResponse response) {
// 需要导出的数据
List<User> list = findAll();
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("UTF-8");
// 设置文件名字
String fileName = "downFile_" System.currentTimeMillis() ".xlsx";
try {
// 设置响应头信息
response.setHeader("Content-Disposition",
"attachment; filename=" java.net.URLEncoder.encode(fileName, "UTF-8"));
// 写入文件数据
EasyExcel.write(response.getOutputStream(), User.class).sheet("download").doWrite(list);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
六、ExcelListener监听
package pers.gl.service;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import pers.gl.entity.User;
public class ExcelListener extends AnalysisEventListener<User> {
private static final Logger logger = LoggerFactory.getLogger(User.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<>();
private UserService userService;
// 构造函数，一定要写，添加到监听中
public ExcelListener(UserService userService) {
this.userService = userService;
}
@Override
public void invoke(User data, AnalysisContext context) {
// TODO Auto-generated method stub
logger.info("invoke方法被调用");
logger.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO Auto-generated method stub
System.out.println("doAfterAllAnalysed方法 被调用");
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
logger.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
logger.info("{}条数据，开始存储数据库！", list.size());
userService.saveList(list);
logger.info("存储数据库成功！");
}
}
七、Controller控制器
package pers.gl.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.excel.EasyExcel;
import pers.gl.entity.User;
import pers.gl.service.ExcelListener;
import pers.gl.service.UserService;
@Controller
@RequestMapping("user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("list")
public String list(Model model) {
List<User> users = userService.findAll();
model.addAttribute("users", users);
return "userlist";
}
@GetMapping("/download")
public void excelExport02(HttpServletResponse response) {
userService.exportExcel(response);
}
@GetMapping("/import")
public String toImportExcelPage() {
return "importexcel";
}
@PostMapping("/uploadExcel")
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new ExcelListener(userService)).sheet().doRead();
return "redirect:/user/list";
}
}
八、前台上传页面
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>导入EXCEL</title>
</head>
<body>
<form th:action="@{/user/uploadExcel}" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" value="上传文件">
</form>
</body>
</html>
免责声明：本文仅代表文章作者的个人观点，与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺，请读者仅作参考，并自行核实相关内容。文章投诉邮箱：anhduc.ph@yahoo.com
