此文件功能使用SpringBoot来编写，包括写好的前端和后端代码。
使用SpringBoot+POI导出excel数据。
使用SpringBoot导出excel模板文件，防止用户导入错误。
先看是否需要在下载，功能演示都放在这篇文章了：
下载地址：点我下载
项目结构
controller层
package edu.sdjsjxy.jc.controller; import com.alibaba.fastjson.JSONObject; import com.fasterxml.jackson.databind.util.JSONPObject; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import edu.sdjsjxy.jc.dao.ImportJCFileDataDao; import edu.sdjsjxy.jc.entity.ImportJCEntity; import edu.sdjsjxy.jc.entity.JCEntity; import edu.sdjsjxy.jc.entity.StudentEntity; import edu.sdjsjxy.jc.service.ImportJCFileDataService; import edu.sdjsjxy.jc.util.ExcelUtils; import edu.sdjsjxy.jc.util.FileUtil; import org.apache.ibatis.annotations.Param; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.*; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import org.springframework.web.multipart.MultipartException; import org.springframework.web.multipart.MultipartFile; import javax.activation.MimetypesFileTypeMap; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.List; @Controller @RequestMapping("edu/sdjsjxy/jc/importExcel") public class ImportExcelController { @RequestMapping("") public String index() { return "jc/importExcel"; } @Autowired ImportJCFileDataService importJCFileDataService; /** * http://127.0.0.1:8080/edu/sdjsjxy/jc/index * * @param file 文件 * @param seasonNo 季号 * @param issue 期号 * @return */ @RequestMapping(value = "file/upload", method = RequestMethod.POST) @ResponseBody public String uploadFileCOntroller(@RequestParam("file") MultipartFile file, @RequestParam("seasonNo") int seasonNo, @RequestParam("issue") int issue) { if (file == null) { return ("上传的文件不允许为空"); } String filename = file.getOriginalFilename(); try { if (filename.length() < 6 || !filename.substring(filename.length() - 5).equals(".xlsx")) { return ("文件格式错误"); } } catch (MultipartException m) { System.out.println("前台上传错误！"); } try { //拿到上传文件中的数据 List<ImportJCEntity> list = ExcelUtils.excelToShopIdList(file.getInputStream());//解析并拿到上传的数据 //拿到数据库中的学生数据 List<StudentEntity> studentDataAll = importJCFileDataService.getDataAll(); List<JCEntity> jcEntities = new ArrayList<>(); for (ImportJCEntity ijcEntity : list) { for (StudentEntity studentEntity : studentDataAll) { //拿去读过的 if (ijcEntity.getStudentName().equals(studentEntity.getStudentName())) { JCEntity jcEntity = new JCEntity(); jcEntity.setStudentNumber(studentEntity.getStudentNumber()); jcEntity.setPhoneNumber(ijcEntity.getPhoneNumber()); jcEntity.setAccessTime(ijcEntity.getAccessTime()); jcEntity.setSeasonNo(seasonNo); jcEntity.setIssue(issue); jcEntities.add(jcEntity); } } } System.out.println("读过的：" + jcEntities.size()); //JCEntity{studentNumber='201911101057', phoneNumber='151****7915', accessTime='17-三月-2020', seasonNo=8, issue=5} //正在插入数据 importJCFileDataService.insertJCFileData(jcEntities); System.out.println("插入数据成功"); } catch (IOException e) { System.out.println("上传文件出错，错误代码：" + e.getMessage()); } return ("上传成功！"); } /** * http://127.0.0.1:8080/edu/sdjsjxy/jc/index/getJCdata?seasonNo=8&issue=5&className=软测181&pageNum=1&pageSize=10&submitState=false * * @param pageNum 第几页 * @param pageSize 每页有多少个 * @param seasonNo //第几季 * @param issue //第几期 * @param className //班级名 * @param submitState //提交状态：已阅读或为阅读人员 * @return */ @RequestMapping("getJCdata") @ResponseBody public String getJCData(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize, @Param("seasnoNo") Integer seasonNo, @Param("issue") Integer issue, @Param("className") String className, @Param("submitState") String submitState) { JSONObject json = new JSONObject(); if (seasonNo == null || issue == null || className == null) { json.put("msg", "参数不允许为空"); return json.toString(); } System.out.println("查询的状态：" + submitState); //利用PageHelper分页查询 注意：这个一定要放查询语句的前一行,否则无法进行分页,因为它对紧随其后第一个sql语句有效 PageHelper.startPage(pageNum, pageSize); if (submitState.equals("true")) { List<JCEntity> jcEntityList = importJCFileDataService.getJCSelectTrueData(seasonNo, issue, className); PageInfo<JCEntity> pageInfo = new PageInfo<>(jcEntityList); json.put("pageInfo", pageInfo); } else { List<JCEntity> jcEntityList = importJCFileDataService.getJCSelectFalseData(seasonNo, issue, className); PageInfo<JCEntity> pageInfo = new PageInfo<>(jcEntityList); json.put("pageInfo", pageInfo); } return json.toString(); } /** * 导出 * * @param seasonNo * @param issue * @param className * @param submitState * @param response * @param request * @return * @throws IOException */ @RequestMapping(value = "/exportJCExcelFileData") public String excel2007(@Param("seasnoNo") Integer seasonNo, @Param("issue") Integer issue, @Param("className") String className, @Param("submitState") String submitState, HttpServletResponse response, HttpServletRequest request) { JSONObject json = new JSONObject(); if (seasonNo == null || issue == null || className == null) { json.put("msg", "参数不允许为空"); return json.toString(); } //利用PageHelper分页查询 注意：这个一定要放查询语句的前一行,否则无法进行分页,因为它对紧随其后第一个sql语句有效 List<JCEntity> jcEntityList = null; if (submitState.equals("true")) { jcEntityList = importJCFileDataService.getJCSelectTrueData(seasonNo, issue, className); } else { jcEntityList = importJCFileDataService.getJCSelectFalseData(seasonNo, issue, className); } Workbook workbook = null; try { workbook = ExcelUtils.exportDailyBill(jcEntityList, seasonNo, issue); response.setHeader("Content-type", "application/vnd.ms-excel"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xlsx"); workbook.write(response.getOutputStream()); workbook.close(); } catch (IOException e) { System.out.println("错误了：" + e.getMessage()); } return json.toString(); } /** * 下载导入excel模板 * @throws IOException */ @RequestMapping(value = "downModel") public void download( ) throws IOException { ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes(); HttpServletResponse response = requestAttributes.getResponse(); String filename = "导入模板.xlsx"; // 设置信息给客户端不解析 String type = new MimetypesFileTypeMap().getContentType(filename); // 设置contenttype，即告诉客户端所发送的数据属于什么类型 response.setHeader("Content-type",type); // 设置编码 String hehe = new String(filename.getBytes("utf-8"), "iso-8859-1"); // 设置扩展头，当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型。 response.setHeader("Content-Disposition", "attachment;filename=" + hehe); FileUtil.download(filename, response); } }
工具类
import javax.servlet.http.HttpServletResponse; import java.io.*; public class FileUtil { public static void download(String filename, HttpServletResponse res) throws IOException { // 发送给客户端的数据 OutputStream outputStream = res.getOutputStream(); byte[] buff = new byte[1024]; BufferedInputStream bis = null; // 读取filename bis = new BufferedInputStream(new FileInputStream(new File("./file/" + filename))); int i = bis.read(buff); while (i != -1) { outputStream.write(buff, 0, buff.length); outputStream.flush(); i = bis.read(buff); } bis.close(); outputStream.close(); } }
文件存放位置
模板内容
前端
导入数据之前，先下载模板
下载成功：
导出
