jxl

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream file = multipartRequest.getFile("file").getInputStream();
String qSubjectId = request.getParameter("questionSubject");
String hiddenQuestionSubject = request.getParameter("hiddenQuestionSubject");
Workbook book = Workbook.getWorkbook(file);
//得到模板里第一个sheet
Sheet sheet = book.getSheet(0);
if (sheet == null) {
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", "导入的Excel文件没有工作表");
return mv;
}
Cell[] title = sheet.getRow(0);
if(!hiddenQuestionSubject.equals((title[0].getContents().toString()).split("：")[1])){
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", "导入的Excel文件科目和系统要求不匹配！");
return mv;
}
//得到模板里第二行，就是头信息
Cell[] titleRow = sheet.getRow(1);
String[] vb = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
int errorNum = 0;
String errorStr = "";
if (titleRow.length > 30) {
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", "导入的Excel文件标题行最多允许30列！");
return mv;
}
if (titleRow.length > 0) {
//校验标题
if (titleRow[0] == null || !"题目".equals(titleRow[0].getContents())) {
errorNum++;
errorStr += (errorStr.equals("") ? "" : "，") + "第A列标题应为[题目]";
}
if (titleRow[1] == null || !"题型".equals(titleRow[1].getContents())) {
errorNum++;
errorStr += (errorStr.equals("") ? "" : "，") + "第B列标题应为[题型]";
}
if (titleRow[2] == null || !"难度".equals(titleRow[2].getContents())) {
errorNum++;
errorStr += (errorStr.equals("") ? "" : "，") + "第C列标题应为[难度]";
}
if (titleRow[3] == null || !"正确答案".equals(titleRow[3].getContents())) {
errorNum++;
errorStr += (errorStr.equals("") ? "" : "，") + "第D列标题应为[正确答案]";
}
for (int i = 0; i < titleRow.length - 4; i++) {
if (titleRow[i + 4] == null || !("答案" + vb[i]).equals(titleRow[i + 4].getContents())) {
errorNum++;
errorStr += (errorStr.equals("") ? "" : "，") + "第" + (i + 5) + "列标题应为[答案" + vb[i] + "]";
}
}
if (errorNum != 0) {
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", "导入的Excel文件标题行有" + errorNum + "处错误：" + errorStr);
return mv;
}
}
Map map = testQuestionFacade.provingEexcel(sheet, qSubjectId, vb);
String message = (String) map.get("message");
int number = (Integer) map.get("number");
List alllist = (List) map.get("alllist");
if (number != 0) {
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", message);
return mv;
} else {
//通过读取用户的模板 向数据库表里插值
int n = testQuestionFacade.insertAnswerAndQuestionFormwork(alllist);
if (n == 1) {
ModelAndView mv = new ModelAndView("common/fail");
mv.addObject("url", "/testQuestion/toDeriveQuestion.do");
mv.addObject("message", "添加试题失败！");
return mv;
} else {
ModelAndView mv = new ModelAndView("common/success");
mv.addObject("url", "/testQuestion/selectAllTestQuestion.do");
mv.addObject("message", "添加试题成功！");
return mv;
}
}
}