easyexcel 上传文件记录一次使用Easyexcel上传导入数据库和导出EXCEL的实现过程

导读这是一个最简单的例子，使用了Springboot/Thymeleaf/mybatis等技术完成了初步的导入，给和我一样的初学者提供一点参考，我来为大家讲解一....

这是一个最简单的例子，使用了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;@Datapublic 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;@Servicepublic class UserService {@Autowiredprivate 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;}@Overridepublic void invoke(User data, AnalysisContext context) {// TODO Auto-generated method stublogger.info("invoke方法被调用");logger.info("解析到一条数据:{}", JSON.toJSONString(data));list.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (list.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listlist.clear();}}/** * 所有数据解析完成了 都会来调用 * * @param context */@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// TODO Auto-generated method stubSystem.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 {@Autowiredprivate 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>