Java读取并导入Excel题库

//like12 add,20161107,Java读取并导入Excel题库

@Expose

public String netExcelImportAction(Map<String,Object>parameter) throws Exception{

System.out.println("Excel题库导入----");

if(parameter == null){

return "parameter不能为空";

}

String fileName = (String)parameter.get("fileName");

String strLicenseType = (String)parameter.get("strLicenseType");

String strSubject = (String)parameter.get("strSubject");

String strChapterParam = (String)parameter.get("strChapterParam");

if(StringUtils.isEmpty(fileName)

|| StringUtils.isEmpty(strLicenseType)

|| StringUtils.isEmpty(strSubject)

|| StringUtils.isEmpty(strChapterParam)

){

return "fileName等参数不能为空";

}

//读取指定的文件

File fIn = new File("d:" + File.separator + fileName);

FileInputStream readFile = null;

HSSFWorkbook wb = null;

try {

readFile = new FileInputStream(fIn);

wb = new HSSFWorkbook(readFile);

} catch (Exception e2) {

// TODO Auto-generated catch block

e2.printStackTrace();

}

HSSFSheet sheet = wb.getSheetAt(0);

HSSFRow row = sheet.getRow(0);

//获取总行数

int rowNum = sheet.getLastRowNum();

System.out.println("---Size:" + rowNum);

//HSSFCell cell = row.getCell(0);

//System.out.println(cell.getRichStringCellValue());

try {

readFile.close();

} catch (IOException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

String sortOrder = null;

String question = null;

String type = null;

String an1 = null;

String an2 = null;

String an3 = null;

String an4 = null;

String correctAnswer = null;

//like12 add,20171117,解释 与 图片

String explanation = null;

String imageUrl = null;

String strChapter = null;

int sortOrderInt = 0;

for(int i=1; i<=rowNum; i++){

row = sheet.getRow(i);

sortOrder = getCellValue(row.getCell(0));

type = getCellValue(row.getCell(1));

question = getCellValue(row.getCell(2));

an1 = getCellValue(row.getCell(3));

an2 = getCellValue(row.getCell(4));

an3 = getCellValue(row.getCell(5));

an4 = getCellValue(row.getCell(6));

correctAnswer = getCellValue(row.getCell(7));

sortOrderInt = Integer.parseInt(sortOrder);

//like12 add,20171117,解释 与 图片

explanation = getCellValue(row.getCell(8));

imageUrl = getCellValue(row.getCell(9));

strChapter = getCellValue(row.getCell(10));

if(StringUtils.isEmpty(strChapter)){//没有时才用参数覆盖

strChapter = strChapterParam;

}

StdExamLibs stdExamLibs = new StdExamLibs();

stdExamLibs.setSortOrder(sortOrderInt);

stdExamLibs.setQuestion(question);

stdExamLibs.setAn1(an1);

stdExamLibs.setAn2(an2);

stdExamLibs.setAn3(an3);

stdExamLibs.setAn4(an4);

if("A".equals(correctAnswer)){

correctAnswer = "1";

}else if("B".equals(correctAnswer)){

correctAnswer = "2";

}else if("C".equals(correctAnswer)){

correctAnswer = "3";

}else if("D".equals(correctAnswer)){

correctAnswer = "4";

}else if("AB".equals(correctAnswer)){

correctAnswer = "12";

}else if("AC".equals(correctAnswer)){

correctAnswer = "13";

}else if("AD".equals(correctAnswer)){

correctAnswer = "14";

}else if("BC".equals(correctAnswer)){

correctAnswer = "23";

}else if("BD".equals(correctAnswer)){

correctAnswer = "24";

}else if("CD".equals(correctAnswer)){

correctAnswer = "34";

}else if("ABC".equals(correctAnswer)){

correctAnswer = "123";

}else if("ABD".equals(correctAnswer)){

correctAnswer = "124";

}else if("ACD".equals(correctAnswer)){

correctAnswer = "134";

}else if("BCD".equals(correctAnswer)){

correctAnswer = "234";

}else if("ABCD".equals(correctAnswer)){

correctAnswer = "1234";

}else{

System.out.println("--Error 既不是 A 也不是 B...");

return sortOrderInt + " 既不是 A 也不是 B";

}

stdExamLibs.setCorrectAnswer(correctAnswer);

if("判断".equals(type)){

stdExamLibs.setType(1);//1判断2单选3多选

}else if("单选".equals(type)){

stdExamLibs.setType(2);

}else if("多选".equals(type)){

stdExamLibs.setType(3);

}else{

System.out.println("--Error 既不是 判断 也不是单选...");

return sortOrderInt + " 既不是 判断 也不是单选";

}

//like12 add,20171117,解释 与 图片

stdExamLibs.setExplanation(explanation);

stdExamLibs.setImageUrl(imageUrl);

stdExamLibs.setLicenseType(strLicenseType);//车型

stdExamLibs.setChapter(strChapter);//章节

stdExamLibs.setSubject(strSubject);//科目

System.out.println("--- " + i + "/" + rowNum

+ "--" + stdExamLibs.getSortOrder()

+ " " + stdExamLibs.getQuestion()

+ " " + stdExamLibs.getAn1()

+ " " + stdExamLibs.getAn2()

+ " " + stdExamLibs.getAn3()

+ " " + stdExamLibs.getAn4()

+ " " + stdExamLibs.getCorrectAnswer()

+ " " + stdExamLibs.getExplanation()

+ " " + stdExamLibs.getImageUrl()

+ " " + stdExamLibs.getLicenseType()

+ " " + stdExamLibs.getSubject()

+ " " + stdExamLibs.getType()

+ " " + stdExamLibs.getChapter()

);

//保存题库

stdExamLibsDS.saveStdExamLibs(stdExamLibs, null);

}

System.out.println("---finish");

return "success";

}

private String getCellValue(HSSFCell cell) {

if(cell == null){

return null;

}

String cellValue = "";

//like12 modified bug,20171124,不能保留小数

//DecimalFormat df = new DecimalFormat("#");//不保留小数

DecimalFormat df = new DecimalFormat("#.##");//最多保留x位小数

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

cellValue = cell.getRichStringCellValue().getString().trim();

break;

case HSSFCell.CELL_TYPE_NUMERIC:

cellValue = df.format(cell.getNumericCellValue()).toString();

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

cellValue = String.valueOf(cell.getBooleanCellValue()).trim();

break;

case HSSFCell.CELL_TYPE_FORMULA:

cellValue = cell.getCellFormula();

break;

default:

cellValue = "";

}

return cellValue;

    }