前端发起请求
upload.render({
elem: ‘#file’
,headers: {
token: storage.getToken()
}
,url: 'nightOut/upload' //改成您自己的上传接口
,method: 'post'
,accept: 'file' //普通文件
,done: function(res){
layer.msg('上传成功');
window.reload();
console.log(res);
}
});
Controller层
@PostMapping(“upload”)
@ResponseBody
public Result excelImport(@RequestParam(value = “file”) MultipartFile file){
int result = 0;
try{
result = nightOutService.addToDatabase(file);
}catch(Exception e){
e.printStackTrace();
}
if (result > 0){
return Result.ok("评分记录导入成功!");
}else {
return Result.fail("导入失败了!");
}
}
service层
public int addToDatabase(MultipartFile file)throws Exception {
int result = 0;
List nightOutList = new ArrayList();
String fileName = file.getOriginalFilename(); //获取上传名
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1); //获取文件名的后缀
System.out.println(“文件后缀为:” + suffix);
InputStream inputStream = file.getInputStream(); //输入流获取文件
Workbook workbook = null; //新建工作簿
if (suffix.equals(“xlsx”)) {
workbook = new XSSFWorkbook(inputStream); // Excel2007版本及以后 用XSSF(即Poi-ooxml)处理
} else {
workbook = new HSSFWorkbook(inputStream); // Excel2003版本 用HSSF(即Poi)处理
}
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
for (int i = 1; i <= sheet.getLastRowNum(); i++) { //从第二行开始遍历每一行(备注第一行是字段名称)
Row row = sheet.getRow(i); //获取一行记录所有信息
List list = new ArrayList<>();
for (Cell cell : row) { //遍历每个单元格
if (cell != null) {
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){//判断是否字符串
String cellValue = cell.getStringCellValue();
list.add(cellValue);
}
else if (HSSFDateUtil.isCellDateFormatted(cell)){//判断是否时间类型
Date date = cell.getDateCellValue();
//格式转换
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String format = sdf.format(date);
list.add(format);
}
else {
cell.setCellType(Cell.CELL_TYPE_STRING); // 将单元格值转化为字符串
String cellValue = cell.getStringCellValue(); // 获取字符串形式的值
list.add(cellValue);
}
}
}
Integer buildId = Integer.parseInt(list.get(0));
Integer roomId = Integer.parseInt(list.get(1));
String studentName = list.get(2);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date offendTime = df.parse(list.get(3));
// 构造一个夜不归宿违纪表对象,并将从个单元格获取的值赋给它
NightOut nightOut = new NightOut();
nightOut.setBuildId(buildId);
nightOut.setRoomId(roomId);
nightOut.setStudentName(studentName);
nightOut.setOffendTime(offendTime);
nightOutList.add(nightOut);
}
}
//插入数据
for(NightOut nightOut:nightOutList){
result =nightOutMapper.add(nightOut);
}
return result;
}