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

这是一个最简单的例子,使用了Springboot/Thymeleaf/mybatis等技术完成了初步的导入,给和我一样的初学者提供一点参考,我来为大家讲解一下关于easyexcel 上传文件?跟着小编一起来看一看吧!

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

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

    分享
    投诉
    首页