第一種獲取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("模板不規(guī)范,導(dǎo)入失敗!");
}else{
    String msg = getExcelContent(sheet);
        jsonMsg.setSuccess(true);
        jsonMsg.setMsg(msg);
}
this.writeJson(jsonMsg);
return NONE;

getExcelContent 為處理excel數(shù)據(jù)的方法

 private String getExcelContent(HSSFSheet sheet) {
       
    
        HSSFRow row;
     
        try {
            // 得到總行數(shù)
            int rowNum = sheet.getLastRowNum();
            // 正文內(nèi)容應(yīng)該從第四行開(kāi)始,第一行為表頭的標(biāo)題
            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){ //不存在該身份證號(hào)的報(bào)名信息
                            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() != "" ){ // 分?jǐn)?shù)
                        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())){ // 考試結(jié)果
                        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<String , String> 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的方法

File temp = null;
temp = new File(ExcelUploadFileUtil2.upload(file));
ExcelReader readerQuestions = null;
readerQuestions = ExcelUtil.getReader(temp, 0); //獲取第一個(gè)分表

 @RequestMapping(value = "/questions/",method = {RequestMethod.POST}, consumes = MediaType.ALL_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    @ResponseBody
    public Result uploadQuestions(@CurrentAdmin UserAdminEntity admin, @RequestParam(value = "file", required = true) MultipartFile file) throws Exception {
        // 獲取封裝數(shù)據(jù)對(duì)象
        File temp = null;
        ExcelReader readerQuestions = null;
        ExcelReader readerOptions = null;
        List<String> rePath = null;
        if (file != null && !file.isEmpty()){
            try {
                temp = new File(ExcelUploadFileUtil2.upload(file));
                readerQuestions = ExcelUtil.getReader(temp, 0);   //獲取第一個(gè)分表
                readerOptions = ExcelUtil.getReader(temp, 1); //獲取第二個(gè)分表
                rePath = ExcelFileReadUtil.makePath(temp.getPath());  
            } catch (Exception e){
                e.printStackTrace();
                throw new FileUploadException("模板不正確");
            }

            // 插入試題
            List<Map> re = questionService.insertQuestionList(readerQuestions, admin.getUserName(), LocalUtil.create("q", new ArrayList<Questions>()).get());

            // 插入選項(xiàng)
            questionService.insertQuestionOptionsList(readerOptions, admin.getUserName(), LocalUtil.get("q", ArrayList.class), LocalUtil.create("o", new ArrayList<QuestionsOptions>()).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("文件上傳失敗");
    }

處理表格數(shù)據(jù)

  /**
     * 添加題目多
     */
    public List<Map> insertQuestionList(ExcelReader reader, String name, List<Questions> rongQi) throws ParamException, ResultException, IOException, EmptyException {
        long pid = 0;
        // 讀取excel對(duì)象
        List<List<Object>> readAll = reader.read();
        for (int i = 0; i < readAll.size(); i++){
            if (i < 1){
                continue;
            }
            List<Object> reaa = readAll.get(i);
            try {
                if (reaa.get(7) == null || reaa.get(7).toString().equals("")){
                    continue;
                }
                Long rea = handler(reaa, pid, takeKnowledge(reaa.get(5)), name, rongQi);
                pid = rea;
            } catch (Exception e){
                e.printStackTrace();
                List<Map> rea = new ArrayList<>();
                rea.add(handlerResult(readAll.get(i), e.getMessage()));
                return rea;
            }
        }
        return new ArrayList<>();
    }