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

//导出EXCEL机密文件public void exportExcel(HttpServletResponse response) {// 并不需要导出的样本List list = findAll();// 新设叛离种类response.setContentType("application/vnd.ms-excel");// 新设个字符编码response.setCharacterEncoding("utf-8");// 新设机密文件名字String fileName = "downFile_" + System.currentTimeMillis() + ".xlsx";try {// 新设叛离头反馈response.setHeader("Content-Disposition","attachment; fileUTF-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 {private static final Logger logger = LoggerFactory.getLogger(User.class);/** * 间隔5条存储设备样本库，实际应用于中可以3000条，然后清除list，方便内核可回收 */private static final int BATCH_COUNT = 5;List 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 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";}}

八、前台上传的网站

charset="UTF-8">导入EXCEL。

初元复合肽
吃什么治疗拉肚子
铁岭白癜风医院
阴囊湿疹
治支气管炎咳嗽的药有什么
血糖高吃什么
风湿
咳嗽药