此文件功能使用SpringBoot来编写,包括写好的前端和后端代码。
使用SpringBoot+POI导出excel数据。
使用SpringBoot导出excel模板文件,防止用户导入错误。
先看是否需要在下载,功能演示都放在这篇文章了:https://cloud.tencent.com/developer/article/1670325
下载地址:点我下载
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();
}
}
模板内容
导入数据之前,先下载模板
下载成功: