导入excel，读取excel数据导入excel，读取excel数据

第一种获取sheet的方法 HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(this.getExcel()));
HSSFSheet sheet = wb.getSheetAt(0);

//this.getExcel()为上传的excel文件 HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(this.getExcel())); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Json jsonMsg = new Json(); if(row == null){ jsonMsg.setSuccess(false); jsonMsg.setMsg("模板不规范,导入失败!"); }else{ String msg = getExcelContent(sheet); jsonMsg.setSuccess(true); jsonMsg.setMsg(msg); } this.writeJson(jsonMsg); return NONE;

getExcelContent 为处理excel数据的方法

private String getExcelContent(HSSFSheet sheet) {HSSFRow row; try { // 得到总行数 int rowNum = sheet.getLastRowNum(); // 正文内容应该从第四行开始,第一行为表头的标题 for (int i = 3; i <=rowNum; i++) { Grade grade = new Grade(); row = sheet.getRow(i); if(row != null){if(row.getCell(1) !=null){ // 身份证 Object [] objects = findIdByIdCard(row.getCell(1).toString() , this.clazzOpenId); if(objects.length == 0){ //不存在该身份证号的报名信息 idcardNotFoundNum++; continue; } Integer signDetailId = Integer.parseInt(objects[0].toString()); Integer personId = Integer.parseInt(objects[1].toString()); Integer clazzOpenId = this.clazzOpenId ; grade.setPersonId(personId); grade.setSignDetailId(signDetailId); grade.setClazzOpenId(clazzOpenId); }else{ idcardBlank++; continue; }if(row.getCell(5) !=null && row.getCell(5).toString() != "" ){ // 分数 String score = row.getCell(5).toString().trim(); if("缺考".equals(score)){ score = "-1"; }else if("作弊一".equals(score)){ score = "-2"; }else if("作弊二".equals(score)){ score = "-3"; }else if("免试".equals(score)){ score = "-4"; } grade.setScore(Double.parseDouble(score)); } if(StringUtils.isNotBlank(row.getCell(6).toString().trim())){ // 考试结果 String result = row.getCell(6).toString().trim(); if ("合格".equals(result)){ result = "1"; grade.setResult(Integer.parseInt(result)); }else if("不合格".equals(result)){ result = "0"; grade.setResult(Integer.parseInt(result)); } grade.setStatus(RELEASE_NO); }else{ grade.setStatus(RELEASE_NO); } if( grade.getPersonId() != null ){ Map map = scoreManageDao.countByPersonId(grade.getPersonId() , this.clazzOpenId); if(map.size() == 0 ){ grade.setCreateOperator(getLoginUser().getId().toString()); grade.setCreateTime(new Date()); scoreManageDao.save(grade); successNum++; }else{ if(map.get("id") != null ){ grade.setId(Integer.parseInt(map.get("id"))); } grade.setUpdateOperator(getLoginUser().getId().toString()); grade.setUpdateTime(new Date()); scoreManageDao.update(grade); updateNum++; } } } } }catch (Exception e) { e.printStackTrace(); }return sb.toString(); }

第二种获取sheet的方法 【导入excel，读取excel数据】File temp = null;
temp = new File(ExcelUploadFileUtil2.upload(file));
ExcelReader readerQuestions = null;
readerQuestions = ExcelUtil.getReader(temp, 0); //获取第一个分表

@RequestMapping(value = "https://www.it610.com/questions/",method = {RequestMethod.POST}, consumes = MediaType.ALL_VALUE, produces = MediaType.APPLICATION_JSON_VALUE) @ResponseBody public Result uploadQuestions(@CurrentAdmin UserAdminEntity admin, @RequestParam(value = "https://www.it610.com/article/file", required = true) MultipartFile file) throws Exception { // 获取封装数据对象 File temp = null; ExcelReader readerQuestions = null; ExcelReader readerOptions = null; List rePath = null; if (file != null && !file.isEmpty()){ try { temp = new File(ExcelUploadFileUtil2.upload(file)); readerQuestions = ExcelUtil.getReader(temp, 0); //获取第一个分表 readerOptions = ExcelUtil.getReader(temp, 1); //获取第二个分表 rePath = ExcelFileReadUtil.makePath(temp.getPath()); } catch (Exception e){ e.printStackTrace(); throw new FileUploadException("模板不正确"); }// 插入试题 List re = questionService.insertQuestionList(readerQuestions, admin.getUserName(), LocalUtil.create("q", new ArrayList()).get()); // 插入选项 questionService.insertQuestionOptionsList(readerOptions, admin.getUserName(), LocalUtil.get("q", ArrayList.class), LocalUtil.create("o", new ArrayList()).get()); // 插入附件 questionService.insertQuestionOptionPhotoList(QuestionsFileVo.make(rePath), admin.getUserName(), LocalUtil.get("q", ArrayList.class), LocalUtil.get("o", ArrayList.class)); // 清空缓存 LocalUtil.remove("q"); LocalUtil.remove("o"); return new Result(200, "", re); } throw new FileUploadException("文件上传失败"); }

处理表格数据

/** * 添加题目多 */ public List insertQuestionList(ExcelReader reader, String name, List rongQi) throws ParamException, ResultException, IOException, EmptyException { long pid = 0; // 读取excel对象 List> readAll = reader.read(); for (int i = 0; i < readAll.size(); i++){ if (i < 1){ continue; } List