excel 导入功能
一:示例代码
//InputStream fis = new FileInputStream(tomcaturl+this.awardTask.getFileRoute());
//可以通过上述方式获得InputStream
private List<XueBaQuestionEntity> GetAllImportQuestion(InputStream inputstream) { // POIFSFileSystem fs; // HSSFWorkbook wb; // HSSFSheet sheet; // HSSFRow row; // HSSF只支持excel2003 List<XueBaQuestionEntity> questionList = new ArrayList<XueBaQuestionEntity>(); List<XueBaOptionEntity> optionList; XueBaQuestionEntity question = null; try{ // fs = new POIFSFileSystem(inputstream); // wb = new HSSFWorkbook(fs); Workbook wb = WorkbookFactory.create(inputstream); /** 遍历sheet **/ for (int i = 0; i < wb.getNumberOfSheets(); i++) { /** 获得当前sheet **/ Sheet sheet = wb.getSheetAt(i); int num = 1; for (int j = 1; j < sheet.getPhysicalNumberOfRows() ; j++) { num++; try{ question = new XueBaQuestionEntity(); optionList = new ArrayList<XueBaOptionEntity>(); /** 获得当前行情 **/ Row row = sheet.getRow(j); if(row != null){ String qContent = getCellFormatValue(row.getCell(0)).trim(); String aOption = getCellFormatValue(row.getCell(1)).trim(); //题目或A选项为空就不保存 if(StringUtil.isEmpty(qContent) || StringUtil.isEmpty(aOption)){ logger.info("题库第" + num + "行题库或A选项为空,未保存"); continue; } String bOption = getCellFormatValue(row.getCell(2)).trim(); String cOption = getCellFormatValue(row.getCell(3)).trim(); String dOption = getCellFormatValue(row.getCell(4)).trim(); String eOption = getCellFormatValue(row.getCell(5)).trim(); String answer = getCellFormatValue(row.getCell(6)).trim(); System.out.println("qcontent:"+qContent); /* 赋值问题实体 */ question.setContent(qContent); if(answer.indexOf(",")>0){ question.setType(1); }else{ question.setType(0); } question.setAnswer(answer); //赋值选项实体 if (StringUtil.isNotEmpty(aOption)) { XueBaOptionEntity aOptionEntity = new XueBaOptionEntity(); aOptionEntity = DealOption(aOptionEntity,aOption); optionList.add(aOptionEntity); } if (StringUtil.isNotEmpty(bOption)) { XueBaOptionEntity bOptionEntity = new XueBaOptionEntity(); bOptionEntity = DealOption(bOptionEntity,bOption); optionList.add(bOptionEntity); } if (StringUtil.isNotEmpty(cOption)) { XueBaOptionEntity cOptionEntity = new XueBaOptionEntity(); cOptionEntity = DealOption(cOptionEntity,cOption); optionList.add(cOptionEntity); } if (StringUtil.isNotEmpty(dOption)) { XueBaOptionEntity dOptionEntity = new XueBaOptionEntity(); dOptionEntity = DealOption(dOptionEntity,dOption); optionList.add(dOptionEntity); } if (StringUtil.isNotEmpty(eOption)) { XueBaOptionEntity eOptionEntity = new XueBaOptionEntity(); eOptionEntity = DealOption(eOptionEntity,eOption); optionList.add(eOptionEntity); } question.setXueBaOptionList(optionList); questionList.add(question); } }catch (Exception e) { e.printStackTrace(); logger.info("题库第" + num + "行解析异常"); } } } }catch (Exception e) { e.printStackTrace(); } return questionList; } private XueBaOptionEntity DealOption(XueBaOptionEntity optionEntity, String option) { int start = option.indexOf("、"); // System.out.println("option:"+option+" start:"+start); String optionTitle = option.substring(0, start); String optionContent = option.substring(start+1); optionEntity.setTitle(optionTitle); optionEntity.setContent(optionContent); return optionEntity; } private String getCellFormatValue(Cell cell) { String cellvalue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { // 如果当前Cell的Type为NUMERIC case Cell.CELL_TYPE_NUMERIC:{ BigDecimal big = new BigDecimal(cell.getNumericCellValue()); cellvalue = big.toString(); break; } case Cell.CELL_TYPE_FORMULA: { BigDecimal bigula = new BigDecimal(cell .getCachedFormulaResultType()); cellvalue = bigula.toString(); break; } // 如果当前Cell的Type为STRING case Cell.CELL_TYPE_STRING: // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; // 默认的Cell值 default: cellvalue = " "; } } else { cellvalue = ""; } return cellvalue; }