js读取导入的名称到对应的位置显示
$(function() {
$('#myFile').on('change', function() {
var originName = $(this).val();
var fileName = originName.substring(originName.lastIndexOf('\')+1);
fileName = '' + fileName + '';
$('#file-list').html(fileName);
});
});
function importExcel() {
var myFile = $("#myFile").val();
if (myFile.length <= 0) {
dialogFun("批量导入试题", "请选择导入内容", 0);
return false;
}
$("#importP").submit();
}
html代码实现
后台代码实现
/**
* 批量导入试题
* /admin/quest/importExcel
* @param request
* @param file
* @return
*/
@RequestMapping("/quest/importExcel")
public String importExcel(HttpServletRequest request, @RequestParam("myFile") MultipartFile file) {
try {
Long companyId = SingletonLoginUtils.getCurrentCompanyId(request);
logger.info("myFile:" + file.getName());
examQuestionService.updateImportQuestionExcel(file, companyId);
request.setAttribute("msg", "操作成功");
} catch (BaseException e) {
logger.error("QuestionAction.importExcel", e);
request.setAttribute("msg", e.getMessage());
return msgError;
} catch (RecordFormatException e) {
logger.error("QuestionAction.importExcel", e);
request.setAttribute("msg", "图片格式化失败，请勿导入图片");
return msgError;
}
catch (Exception e) {
logger.error("AdminQuestionAction.randomQuestion", e);
request.setAttribute("msg", e.getMessage());
return msgError;
}
return "/common/success";
}
方法实现
/**
* 批量导入试题
* @param file 文件
* @param companyId 公司ID
* @return
* @throws Exception
*/
public String updateImportQuestionExcel(MultipartFile file, Long companyId) throws Exception {
// datalist拼装List deadliest,
HSSFWorkbook wookbook = new HSSFWorkbook(file.getInputStream());
HSSFSheet sheet = wookbook.getSheetAt(0);
// 指的行数，一共有多少行+
int rows = sheet.getLastRowNum();
Calendar calendar = Calendar.getInstance();
List subjectR = nxbSubjectService.getSubjectListByType(SubjectType.EXAM.toString());
for (int i = 1; i <= rows; i++) {
// 读取左上端单元格
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 获取到Excel文件中的所有的列
int maxcell = row.getLastCellNum();
// **读取cell**
String cOntent= getCellValue(row.getCell((short) 0));// 试题内容
String subjectId = getCellValue(row.getCell((short) 1));// 专业ID
String pointId = trimZero(getCellValue(row.getCell((short) 2)));// 考点
String isAsr = getCellValue(row.getCell((short) 3));// 正确答案
String type = getCellValue(row.getCell((short) 4));// 题型
String level = trimZero(getCellValue(row.getCell((short) 5)));// 试题难度
String analyze = getCellValue(row.getCell((short) 6));// 解析
String optiOnA= getCellValue(row.getCell((short) 7)); // A
String optiOnB= getCellValue(row.getCell((short) 8)); // B
String optiOnC= getCellValue(row.getCell((short) 9)); // C
String optiOnD= getCellValue(row.getCell((short) 10)); // D
String optiOnE= getCellValue(row.getCell((short) 11)); // E
String optiOnF= getCellValue(row.getCell((short) 12)); // F
String optiOnG= getCellValue(row.getCell((short) 13)); // D
if(StringUtils.isEmpty(content)&&
StringUtils.isEmpty(subjectId)&&
StringUtils.isEmpty(isAsr)&&
StringUtils.isEmpty(type)&&
StringUtils.isEmpty(level)&&
StringUtils.isEmpty(analyze)){
break;
}
// 试题内容，专业，正确答案，试题类型，试题难度
if (StringUtils.isEmpty(content) || StringUtils.isEmpty(subjectId) || StringUtils.isEmpty(isAsr) || StringUtils.isEmpty(type) || StringUtils.isEmpty(level)) {
throw new BaseException(
"第" + i + "行，试题内容为<" + content + ">的那条数据数据不能为空（试题内容，专业，正确答案，试题类型，试题难度）");
}
logger.info("试题类型：" + type);
switch (type) {
case "单选":
type = "1";
break;
case "多选":
type = "2";
break;
case "判断":
type = "3";
break;
case "不定项":
type = "5";
break;
default:
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据试题类型不正确（试题类型只能输入单选,多选,判断,不定项）");
}
// 专业ID必须是大于0的正整数
if (!StringUtils.isNumber(subjectId)||Long.parseLong(subjectId)<=0) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据专业ID必须是大于0的正整数");
}
//该 专业ID必须是该公司所有
NxbQuerySubject nxbQuerySubject=new NxbQuerySubject();
nxbQuerySubject.setCompanyId(companyId);
nxbQuerySubject.setSubjectId(Long.parseLong(subjectId));
List subjectList = nxbSubjectDao.getSubjectList(nxbQuerySubject);
if (subjectList==null||subjectList.size()==0) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据专业ID必须是该分公司所拥有的");
}
// // 考点必须是大于0的正整数
// if (!StringUtils.isNumber(pointId)||Long.parseLong(pointId)<=0) {
// throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据考点ID必须是大于0的正整数");
// }
// 试题难度
logger.info("试题难度:" + level);
switch (level) {
case "一级":
level = "1";
break;
case "二级":
level = "2";
break;
case "三级":
level = "3";
break;
default:
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据的试题难度必须是一级、二级、三级其中的一个");
}
int typeInt = ConvertUtils.objectToInt(type);
// 如果为判断题最多2个选项
if (typeInt == 3) {
if (StringUtils.isEmpty(optionA) || StringUtils.isEmpty(optionB)) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据为判断题，选项A或选项B不能为空");
}
if (StringUtils.isNotEmpty(optionD) || StringUtils.isNotEmpty(optionE)
|| StringUtils.isNotEmpty(optionF) || StringUtils.isNotEmpty(optionG)) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据为判断题，选项D，E，F，G必须为空");
}
}
// 如果不是判断题，选项必须大于等于4个小于等于7个选项
if (typeInt != 3) {
if (StringUtils.isEmpty(optionA) || StringUtils.isEmpty(optionB) ) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据为选择题，选项A，B不能为空");
}
}
// 如果为多选题正确答案必须在两个以上
if (typeInt == 2 && isAsr.trim().length() <2) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据的为多选题，正确答案必须在两个以上（例：AB）");
}
// 如果为单选题或者判断题答案只能有一个
if (typeInt == 1 || typeInt == 3) {
if (isAsr.trim().length() > 1) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据的正确答案只能有一个（例：A）");
}
}
// 选项不能超过7个字符
if (isAsr.trim().length() > 7) {
throw new BaseException("第" + i + "行，试题内容为<" + content + ">的那条数据正确答案不能超过7个字符（例AB）");
}
// 验证正确答案不能输入其他字符
char[] asr = isAsr.toString().trim().toCharArray();
String asrStr = "";
for (int y = 0; y 的那条数据正确答案输入字符格式不正确（例AB）");
}
}
isAsr = asrStr.substring(0, asrStr.length() - 1);
Question question = new Question();
question.setStatus(1);
// 试题类型
int qstType = typeInt;
question.setQstType(qstType);
// 验证项目ID
Long subjectIdLOng= Long.valueOf(subjectId.trim());
for (int x = 0; x 的那条数据的专业id不匹配");
}
// 验证考点
if(StringUtils.isNotEmpty(pointId)){
Long pointIdLOng= Long.valueOf(pointId.trim());
ExamPoint point = new ExamPoint();
point.setSubjectId(subjectIdLong);
point.setId(pointIdLong);
List pointList = pointDao.getPointList(point);
if (!pointList.isEmpty()) {
question.setPointId(pointIdLong);
}
}
//调整答案顺序
if(typeInt==2){
String[] chars = isAsr.split(",");
Arrays.sort(chars);
if(ObjectUtils.isNotNull(chars)&&chars.length>1){
StringBuilder isAsrs=new StringBuilder();
for (String s:chars){
isAsrs.append(s);
isAsrs.append(",");
}
isAsr=isAsrs.substring(0,isAsrs.length()-1);
}
}
question.setLevel(ConvertUtils.objectToInt(level));
question.setQstContent(content);
question.setIsAsr(isAsr);
question.setQstAnalyze(analyze);
question.setAddTime(new Date());
question.setAuthor("admin");
question.setCompanyId(companyId); // 公司ID
questionDao.addOneQuestion(question);
int AASC = 64;
List str = new ArrayList();
// 把选项的值放入list中
str.add(optionA);
str.add(optionB);
str.add(optionC);
str.add(optionD);
str.add(optionE);
str.add(optionF);
str.add(optionG);
List optiOnList= new ArrayList<>();
for (int k = 0; k > map = new HashMap<>();
map.put("optionList", optionList);
optionDao.addOptionBatch(map);
}
}
return null;
}
/**
* 获得Hsscell内容
*
* @param cell
* @return
*/
public String getCellValue(HSSFCell cell) {
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue().trim();
break;
default:
value = "";
break;
}
}
return value.trim();
}
String trimZero(String str) {
if (StringUtils.isNotEmpty(str)) {
return str.replace(".0", "");
}
return str;
}
工具类
public abstract class ConvertUtils {
private static final DecimalFormat simpleFormat = new DecimalFormat("####");
public static final boolean objectToBoolean(Object o) {
return o != null?Boolean.valueOf(o.toString()).booleanValue():false;
}
public static final int objectToInt(Object o) {
if(o instanceof Number) {
return ((Number)o).intValue();
} else {
try {
if(o == null) {
return -1;
} else {
BigDecimal bigDecimal = new BigDecimal(o.toString());
bigDecimal.intValue();
return bigDecimal.intValue();
}
} catch (Exception var2) {
var2.printStackTrace();
return -1;
}
}
}
}
试题模板如下所示：
项目中所用的依赖为：
poi
poi-2.5.1-final
20040804
org.apache.ant
ant
1.9.3
数据库插入
INSERT INTO nxb_exam_options (
(#{item.id},#{item.qstId},#{item.optContent},
#{item.optOrder}, #{item.optAnswer},#{item.addTime})
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有