1.在pom.xml中添加poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
2.在业务层添加导入代码
package com.iflytek.edu.hnezzhxy.service;
import com.iflytek.edu.hnezzhxy.common.config.Constants;
import com.iflytek.edu.hnezzhxy.common.enums.ResponseCodeEnum;
import com.iflytek.edu.hnezzhxy.dao.ImportExcelDao;
import com.iflytek.edu.hnezzhxy.model.ZsbmStudentResult;
import com.iflytek.edu.hnezzhxy.util.StringUtils;
import com.iflytek.edu.hnezzhxy.vo.ResultVO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.*;
/**
* @version 1.0
* @description 导入excel
* @create 2020/06/30 10:16
*/
@Service
public class ImportExcelService {
@Autowired
private ImportExcelDao importExcelDao;
/**
* @description 导入座位号
* @param file excel文件
*/
public ResultVO importExcelZsbmStudentResult(MultipartFile file) {
int flag = 0;
StringJoiner buffer = new StringJoiner("\n");
String fileName = file.getOriginalFilename();
List<ZsbmStudentResult> list=new ArrayList<ZsbmStudentResult>();
try {
if (fileName != null) {
InputStream inputStream = file.getInputStream();
Workbook workbook = null;
if (fileName.endsWith(Constants.EXCEL2007)) {
workbook = new XSSFWorkbook(inputStream);
}
if (fileName.endsWith(Constants.EXCEL2003)) {
workbook = new HSSFWorkbook(inputStream);
}
Sheet sheet = workbook.getSheetAt(0);
int allRowNum = sheet.getLastRowNum();
if (allRowNum == 0) {
//flag是进度条的值
flag = 100;
buffer.add("导入文件数据为空");
}
for (int i = 1; i <= allRowNum; i++) {
Boolean hasItemError=false;
if (flag < 100) {
flag = flag + (100 / i);
} else {
flag = 100;
//我需要插入的数据类型
ZsbmStudentResult bean = new ZsbmStudentResult();
//获取第i行
Row row = sheet.getRow(i);
boolean rowEmpty = this.isRowEmpty(row);
if (rowEmpty) {
//忽略空白行
continue;
}else{
//学生id
if (row.getCell(0)!=null||StringUtils.isBlank(row.getCell(0).toString())) {
buffer.add("第" + (i-1) + "行的第1列的学生id不能为空!");
hasItemError=true;
} else {
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String value=row.getCell(0).getStringCellValue();
if(value.length()>32){
buffer.add("第" + (i-1)+ "行的第1列的学生id长度不能超过32位!");
hasItemError=true;
}else{
bean.setSid(value);
}
}
//考场号
if (row.getCell(1)!=null||StringUtils.isBlank(row.getCell(1).toString())) {
buffer.add("第" + (i-1) + "行的第2列的考场号不能为空");
hasItemError=true;
} else {
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String value=row.getCell(1).getStringCellValue();
if(value.length()>20){
buffer.add("第" + (i-1) + "行的第2列的考场号长度不能大于20!");
hasItemError=true;
}else{
bean.setExaminationRoomNum(value);
}
}
//座位号
if (StringUtils.isBlank(row.getCell(2).toString())) {
buffer.add("第" + (i-1) + "行的第3列的座位号不能为空");
hasItemError=true;
} else {
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String value=row.getCell(2).getStringCellValue();
if(value.length()>20){
buffer.add("第" + (i-1) + "行的第3列的座位号长度不能大于20!");
hasItemError=true;
}else{
bean.setNum(value);
}
}
//考试成绩
if (row.getCell(3)!=null||StringUtils.isBlank(row.getCell(3).toString())){
buffer.add("第" + (i-1) + "行的第4列的考试成绩不能为空!");
hasItemError=true;
} else {
try {
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(3).getStringCellValue();
double num = Double.parseDouble(value);
if(num<0){
buffer.add("第" + (i-1) + "行的第4列的考试成绩必须大于等于0 !");
}else{
bean.setScore(num);
}
}catch (Exception e){
buffer.add("第" + (i-1) + "行的第4列的考试成绩必须为double类型,例如:653.5 || 701 !");
hasItemError=true;
}
}
if (!hasItemError) {
list.add(bean);
}
}
}
}
}
} catch(Exception e){
e.printStackTrace();
}
if(StringUtils.isBlank(buffer.toString())){
//保存sid不重复
List<String> sidList=new ArrayList<>();
//保存考场号和座位号拼接后的值不重复
List<String> examinationRoomNumAndNumList =new ArrayList();
//excel填写重复错误信息
Set<String> ExcelWriteError=new HashSet<>();
if(!CollectionUtils.isEmpty(list)){
for (ZsbmStudentResult z:list) {
String sid=z.getSid();
String examinationRoomNumAndNum=new StringBuilder().append(z.getExaminationRoomNum()).append(":").append(z.getNum()).toString();
if(sidList.contains(sid)){
ExcelWriteError.add(sid);
}else{
sidList.add(sid);
}
if(examinationRoomNumAndNumList.contains(examinationRoomNumAndNum)){
ExcelWriteError.add(examinationRoomNumAndNum);
}else{
examinationRoomNumAndNumList.add(examinationRoomNumAndNum);
}
}
if(ExcelWriteError.isEmpty()){
//查询sid是否有重复
if(!CollectionUtils.isEmpty(sidList)){
for (String sid:sidList) {
//判断学生表是否存在该学生
boolean studentFlag=importExcelDao.isExistSid(sid);
if(studentFlag){
//学生表学生存在,但是座位号表sid跟即将要导入的数据重复
Integer sidCount =importExcelDao.isRepeatSid(sid);
if(sidCount!=null&&sidCount>0){
buffer.add("学生sid值必须唯一不能重复导入!数据库中座位号表中已经存在学生编号为"+sid+"的学生编号!");
}
}else{
buffer.add("数据库中学生表不存在编号为"+sid+"的学生编号,不能为其导入座位号!");
}
}
}
//查询考场号和座位号是否重复
if(!CollectionUtils.isEmpty(examinationRoomNumAndNumList)){
for (String item:examinationRoomNumAndNumList) {
String[] value = item.split(":");
//判断是否有相同考场号和座位号
Integer examinationRoomNumAndNumCount=importExcelDao.isExistExaminationRoomNumAndNum(value[0],value[1]);
if(examinationRoomNumAndNumCount!=null&&examinationRoomNumAndNumCount>0){
buffer.add("相同的考场号座位号必须唯一!数据库中已经存在考场号为"+value[0]+",并且座位号为"+value[1]+"的记录!");
}
}
}
}else{
Iterator<String> it = ExcelWriteError.iterator();
while(it.hasNext()){
String next = it.next();
//考场号和座位号
if(next.contains(":")){
String[] item = next.split(":");
String value="相同的考场号座位号必须唯一!请检查下考场号为"+item[0]+",并且座位号为"+item[1]+"的座位号!";
buffer.add(value);
}else{
//sid学生编号
String item="学生sid值必须唯一不能重复导入!请检查下学生sid为"+next+"的编号!";
buffer.add(item);
}
}
}
if(StringUtils.isBlank(buffer.toString())){
//无错批量新增
importExcelDao.batchImportZbsmStudentResult(list);
return new ResultVO(ResponseCodeEnum.IMPORT_SUCCESS.getCode(),ResponseCodeEnum.IMPORT_SUCCESS.getMessage(),null,true);
}
}
}
return new ResultVO(ResponseCodeEnum.IMPORT_ERROR.getCode(),ResponseCodeEnum.IMPORT_ERROR.getMessage(),buffer.toString(),false);
}
/**
* 判断是否有空行
* @param row
* @return
*/
private boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
}
}
3.准备好的excel文件
4.postman测试导入
注意:由于测试excel导入所以需要选择将text改为file
5.数据库数据导入成功!