<script type="text/javascript"> //批量上傳題庫 function fileSubmit() { var questionFile = new FormData(); var fileObj = document.getElementById("questionFile").files[0];        // js 獲取文件對(duì)象，questionFile為文件選擇框的Id questionFile.append("file", fileObj); var teacherId=localStorage.getItem("teacherId"); questionFile.append("teacherId",teacherId); var courseId=localStorage.getItem("courseId"); questionFile.append("courseId",courseId); $.ajax({  async: false,  type:"post",  url:"/questions/batchUpload",  data:questionFile,  processData : false, //必須false才會(huì)避開jQuery對(duì) formdata 的默認(rèn)處理  contentType : false, //必須false才會(huì)自動(dòng)加上正確的Content-Type  success:function (data) {  layer.msg("上傳成功");  example.ajax.reload();  } }); } </script>

@ApiOperation(value = "批量上傳題庫") @RequestMapping(value = "/batchUpload",method = RequestMethod.POST) public void batchUploadQuestions(HttpServletRequest request) throws Exception{ Collection<Part> files = request.getParts(); questionsService.batchUploadQuestions(files); }

//題庫的批量上傳 @Override public void batchUploadQuestions(Collection<Part> files) throws Exception { Iterator<Part> it = files.iterator(); Part file = it.next(); Workbook workbook = null; if (file.getSubmittedFileName().endsWith("xlsx")) {  workbook = new XSSFWorkbook(file.getInputStream()); } else if (file.getSubmittedFileName().endsWith("xls")) {  workbook = new HSSFWorkbook(file.getInputStream()); } Cell cell = null; List<Questions> questionsList = new ArrayList<>(); //判斷Excel中有幾張表，目前設(shè)定為一張表 Sheet sheet = workbook.getSheetAt(0);//獲取sheet表 for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) { //獲取到一行  Row row = sheet.getRow(rowIndex);  if (row == null) {  continue;  }  Questions questions = new Questions();  List<String> strList = new ArrayList<>();  for (int i = 1; i < row.getLastCellNum(); i++) {        //獲取到一列，第一列為序號(hào)不需要存入數(shù)據(jù)庫，所以從1開始讀  cell = row.getCell(i);  String value = "";  switch (cell.getCellTypeEnum()) {   case _NONE:   break;   case STRING:   value = cell.getStringCellValue();   break;   case NUMERIC:   Pattern points_ptrn = Pattern.compile("0.0+_*[^/s]+");   if (DateUtil.isCellDateFormatted(cell)) {//日期    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));   } else if ("@".equals(cell.getCellStyle().getDataFormatString())    || "General".equals(cell.getCellStyle().getDataFormatString())    || "0_".equals(cell.getCellStyle().getDataFormatString())) {    //文本 or 常規(guī) or 整型數(shù)值    DecimalFormat df = new DecimalFormat("0");    value = df.format(cell.getNumericCellValue());   } else if (points_ptrn.matcher(cell.getCellStyle().getDataFormatString()).matches()) {//正則匹配小數(shù)類型    value = String.valueOf(cell.getNumericCellValue());//直接顯示   }   break;   default:   value = cell.toString();  }  if ((i == 2 || i == 3) && value.equals("")) {//此處設(shè)計(jì)不需要讀入的單元格   strList.clear();   break;  }  strList.add(value);  }  if (strList.size() == 9) {         //對(duì)應(yīng)數(shù)據(jù)庫屬性進(jìn)行存儲(chǔ)  questions.setChapter(strList.get(0));  questions.setSection(strList.get(1));  questions.setType(strList.get(2));  questions.setQuestion(strList.get(3));  questions.setAnswerA(strList.get(4));  questions.setAnswerB(strList.get(5));  questions.setAnswerC(strList.get(6));  questions.setAnswerD(strList.get(7));  questions.setAnswerTrue(strList.get(8));  questionsList.add(questions);  } }     //將前臺(tái)存進(jìn)的teacherId也當(dāng)做文件進(jìn)行讀取 Part file1 = it.next(); InputStream inputStream = file1.getInputStream(); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream)); String line = null; String teacherId = ""; while ((line = bufferedReader.readLine()) != null) {  teacherId = line; }         //將前臺(tái)傳入的courseId當(dāng)做文件讀取 Part file2 = it.next(); InputStream inputStream1 = file2.getInputStream(); BufferedReader bufferedReader1 = new BufferedReader(new InputStreamReader(inputStream1)); String line1 = null; String courseId = ""; while ((line1 = bufferedReader1.readLine()) != null) {  courseId = line1; } batchSaveQuestionList(teacherId, courseId, questionsList); }     //SQL 語句拼接后傳入DAO層進(jìn)行數(shù)據(jù)插入 public void batchSaveQuestionList(String teacherId,String courseId,List<Questions> questionsList){ String sql = "replace into questions(questionId,courseId,teacherId,chapter,section,type,question,answerA,answerB,answerC,answerD,answerTrue) values"; for(int i = 0;i<questionsList.size();i++){  String questionId = String.valueOf(System.currentTimeMillis())+i;  if(i==0){  sql+="('"+questionId+"','"+courseId+"','"+teacherId+"','"+questionsList.get(i).getChapter()+"','"+questionsList.get(i).getSection()   + "','"+questionsList.get(i).getType()+"','"+questionsList.get(i).getQuestion()+ "','"+ questionsList.get(i).getAnswerA()   +"','"+questionsList.get(i).getAnswerB()+"','"+questionsList.get(i).getAnswerC()+"','"+questionsList.get(i).getAnswerD()   +"','"+questionsList.get(i).getAnswerTrue()+"')";  }else{  sql+=",('"+questionId+"','"+courseId+"','"+teacherId+"','"+questionsList.get(i).getChapter()+"','"+questionsList.get(i).getSection()   + "','"+questionsList.get(i).getType()+"','"+questionsList.get(i).getQuestion()+ "','"+ questionsList.get(i).getAnswerA()   +"','"+questionsList.get(i).getAnswerB()+"','"+questionsList.get(i).getAnswerC()+"','"+questionsList.get(i).getAnswerD()   +"','"+questionsList.get(i).getAnswerTrue()+"')";  } } questionsDao.batchSaveQuestionList(sql); }

@Insert("${sql}")void batchSaveQuestionList(@Param("sql") String sql);