//数据库中测评id
List<Object> listIdFromDB=exchangeCourseDetailsServiceImpl.getTestId();
//数据库中已存在的账号
List<Object> listAccountFromDB=exchangeCourseDetailsServiceImpl.getTestAccount();
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("upoadFileName");
// 获得文件名:
String realFileName = file.getOriginalFilename();
System.out.println("获得文件名:" + realFileName);
// 获取路径
String ctxPath = request.getContextPath();
ctxPath = request.getSession().getServletContext().getRealPath("/")
+ "uploads\\exchangeCourse\\";
System.out.println("路径:" + ctxPath);
// 创建文件
File dirPath = new File(ctxPath);
if (!dirPath.exists()) {
dirPath.mkdir();
}
// 构建随机文件名
SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
String dateFileName = format1.format(new Date());
// 随机文件名
String newFileName = ctxPath + dateFileName + realFileName;
File uploadFile = new File(newFileName);
FileCopyUtils.copy(file.getBytes(), uploadFile);
jxl.Workbook rwb = Workbook.getWorkbook(file.getInputStream());
Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getRow(0);
int length = cell.length;
//列名与列标映射
Map<String, Integer> keyMap = null;
if (length > 0) {
keyMap = new HashMap<String, Integer>();
for (int i = 0; i < length; i++) {
String columnName = cell[i].getContents().trim();
if (columnName != null) {
keyMap.put(columnName, i);
}
}
}
//验证有效列数
int idIndex=-1;
int accountIndex=-1;
int passwordIndex=-1;
if(cell.length>3){
request.setAttribute("errorMsg","上传表格的列数不符合要求,请参照模版!");
return list(modelMap, request, sessionUserData);
}
try{
idIndex=keyMap.get("测评类ID");
accountIndex=keyMap.get("账号");
passwordIndex=keyMap.get("密码");
}catch(Exception e){
e.printStackTrace();
if(idIndex==-1){
request.setAttribute("errorMsg","缺少测评类ID这一列");
return list(modelMap, request, sessionUserData);
}
if(accountIndex==-1){
request.setAttribute("errorMsg","缺少账户这一列");
return list(modelMap, request, sessionUserData);
}
if(passwordIndex==-1){
request.setAttribute("errorMsg","缺少密码这一列");
return list(modelMap, request, sessionUserData);
}
return list(modelMap, request, sessionUserData);
}
Cell[] idCells = rs.getColumn(idIndex);//测评类id列
Cell[] accountCells = rs.getColumn(accountIndex);//账号列
Cell[] accountCellsTemp=accountCells; //账号列副本
Cell[] passwordCells = rs.getColumn(passwordIndex);//密码列
//逐行验证数据有效性
for(int i=1;i<rs.getRows();i++){
//********检验测评类id列数据*****************//
if(idCells.length<rs.getRows()){
request.setAttribute("errorMsg","第"+(idCells.length+1)+"行,测评类id不能为空!");
return list(modelMap, request, sessionUserData);
}
if(idCells[i].getContents().trim()==""){
request.setAttribute("errorMsg","第"+(i+1)+"行,测评类id不能为空!");
return list(modelMap, request, sessionUserData);
}
boolean isHaveId=false;//标记测评id是否在数据库有记录
for(Object obj:listIdFromDB){
if(idCells[i].getContents().trim().equals(obj.toString())){
isHaveId=true;
}
}
if(isHaveId==false){
request.setAttribute("errorMsg","第"+(i+1)+"行,测评类id在数据库中无记录,请核对正确性!");
return list(modelMap, request, sessionUserData);
}
//********检验账号列数据*****************//
if(accountCells.length<rs.getRows()){
request.setAttribute("errorMsg","第"+(accountCells.length+1)+"行,账号不能为空!");
return list(modelMap, request, sessionUserData);
}
if(accountCells[i].getContents().trim()==""){
request.setAttribute("errorMsg","第"+(i+1)+"行,账号不能为空!");
return list(modelMap, request, sessionUserData);
}
//验证表格本身账号是否有重复
for(int k=1;k<accountCellsTemp.length;k++){
//排除自身
if (k != i) {
if (accountCells[i].getContents().trim().equals(
accountCellsTemp[k].getContents().trim())) {
request.setAttribute("errorMsg", "第" + (i + 1) + "行与第"
+ (k + 1) + "行的账号相同,请确保账号唯一性!");
return list(modelMap, request, sessionUserData);
}
}
}
boolean isHaveAccount=false;//标记账号是否在数据库有记录
//验证账号是否在数据库中已存在
for(Object obj:listAccountFromDB){
if(accountCells[i].getContents().trim().equals(obj.toString())){
isHaveAccount=true;
}
}
if(isHaveAccount==true){
request.setAttribute("errorMsg","第"+(i+1)+"行,账号在数据库中已存在!");
return list(modelMap, request, sessionUserData);
}
//********检验密码列数据*****************//
if(passwordCells.length<rs.getRows()){
request.setAttribute("errorMsg","第"+(passwordCells.length+1)+"行,密码不能为空!");
return list(modelMap, request, sessionUserData);
}
if(passwordCells[i].getContents().trim()==""){
request.setAttribute("errorMsg","第"+(i+1)+"行,密码不能为空!");
return list(modelMap, request, sessionUserData);
}
}
//通过验证后,把账号密码插入数据库
try{
exchangeCourseDetailsServiceImpl.saveDataFromImport(idCells, accountCells, passwordCells, sessionUserData.getUid());
}catch(Exception e){
e.printStackTrace();
request.setAttribute("errorMsg","保存上传数据出错……");
return list(modelMap, request, sessionUserData);
}
request.setAttribute("errorMsg","上传账号密码成功");
return list(modelMap, request, sessionUserData);