@SuppressWarnings("rawtypes")
@RequestMapping(params = "excelImport",method=RequestMethod.POST)
@ResponseBody
public AjaxJson excelImport( HttpServletRequest request,@RequestParam("cExcel") MultipartFile file) {
AjaxJson j = new AjaxJson();
try {
if(!file.isEmpty()){
String filename = file.getOriginalFilename();
String _ext = filename.substring(filename.lastIndexOf("."));
if(".xls".equals(_ext)||".xlsx".equals(_ext)){
ImportExcel imp = new ImportExcel();
List list = imp.read(filename, file.getInputStream());
//保存数据库
StringBuffer msg = new StringBuffer();
Integer rowNum = 1;
Integer errorNum = 0;
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
List row = (List) iterator.next();
Boolean errorFlag = false;
for(int i = 0; i < row.size(); i++){
if((String)row.get(i) == null || "".equals((String)row.get(i))){
msg.append(rowNum+", ");
errorFlag = true;
errorNum++;
break;
}
}
rowNum++;
if(errorFlag){
continue;
}
int i = 0;
SysUser sysUser = new SysUser();
sysUser.setUserId(StringUtil.getUUid());
String realName = (String)row.get(i++);
HashMap<String, Object> userParam = new HashMap<String, Object>();
userParam.put("realName", realName);
List<SysUser> userList = sysUserService.getByCondition(userParam);
if(userList != null && userList.size() > 0){
msg.append(rowNum+", ");
errorNum++;
continue;
}else{
sysUser.setRealName(realName);
}
String userName = (String)row.get(i++);
HashMap<String, Object> userParam2 = new HashMap<String, Object>();
userParam2.put("userName", userName);
userList = sysUserService.getByCondition(userParam2);
if(userList != null && userList.size() > 0){
msg.append(rowNum+", ");
errorNum++;
continue;
}else{
sysUser.setUserName(userName);
}
String password = (String)row.get(i++);
password = password.indexOf(".")==-1? password : password.substring(0, password.indexOf("."));
sysUser.setPassword(password);
String phone = (String)row.get(i++);
phone = phone.indexOf(".")==-1? phone : phone.substring(0, phone.indexOf("."));
sysUser.setPhone(phone);
String userTel = (String)row.get(i++);
userTel = userTel.indexOf(".")==-1? userTel : userTel.substring(0, userTel.indexOf("."));
sysUser.setUserTel(userTel);
sysUser.setEmail((String)row.get(i++));
sysUser.setStatus(Constant.SYSTEM_STATUS_00);
sysUser.setStatusTime(DateUtil.getNowTime());
sysUserService.saveData(sysUser);
}
if(errorNum > 0){
msg.insert(0, "共 "+(rowNum-1)+" 行,执行成功 "+(rowNum-errorNum-1)+" 行,失败 "+errorNum+" 行,第 ");
msg.append("行内容有误");
}else{
msg.insert(0, "共 "+(rowNum-1)+" 行,执行成功 "+(rowNum-errorNum-1)+" 行,失败 "+errorNum+" 行");
}
j.setSuccess(true);
j.setMsg(msg.toString());
}else{
j.setSuccess(false);
j.setMsg("导入的文件格式必须是.xls或者.xlsx");
return j;
}
}
} catch (IOException e) {
e.printStackTrace();
j.setSuccess(false);
j.setMsg("导入失败,请确保导入的excel是编辑过的模板文件");
}
return j;
}
}
@RequestMapping(params = "excelImport",method=RequestMethod.POST)
@ResponseBody
public AjaxJson excelImport( HttpServletRequest request,@RequestParam("cExcel") MultipartFile file) {
AjaxJson j = new AjaxJson();
try {
if(!file.isEmpty()){
String filename = file.getOriginalFilename();
String _ext = filename.substring(filename.lastIndexOf("."));
if(".xls".equals(_ext)||".xlsx".equals(_ext)){
ImportExcel imp = new ImportExcel();
List list = imp.read(filename, file.getInputStream());
//保存数据库
StringBuffer msg = new StringBuffer();
Integer rowNum = 1;
Integer errorNum = 0;
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
List row = (List) iterator.next();
Boolean errorFlag = false;
for(int i = 0; i < row.size(); i++){
if((String)row.get(i) == null || "".equals((String)row.get(i))){
msg.append(rowNum+", ");
errorFlag = true;
errorNum++;
break;
}
}
rowNum++;
if(errorFlag){
continue;
}
int i = 0;
SysUser sysUser = new SysUser();
sysUser.setUserId(StringUtil.getUUid());
String realName = (String)row.get(i++);
HashMap<String, Object> userParam = new HashMap<String, Object>();
userParam.put("realName", realName);
List<SysUser> userList = sysUserService.getByCondition(userParam);
if(userList != null && userList.size() > 0){
msg.append(rowNum+", ");
errorNum++;
continue;
}else{
sysUser.setRealName(realName);
}
String userName = (String)row.get(i++);
HashMap<String, Object> userParam2 = new HashMap<String, Object>();
userParam2.put("userName", userName);
userList = sysUserService.getByCondition(userParam2);
if(userList != null && userList.size() > 0){
msg.append(rowNum+", ");
errorNum++;
continue;
}else{
sysUser.setUserName(userName);
}
String password = (String)row.get(i++);
password = password.indexOf(".")==-1? password : password.substring(0, password.indexOf("."));
sysUser.setPassword(password);
String phone = (String)row.get(i++);
phone = phone.indexOf(".")==-1? phone : phone.substring(0, phone.indexOf("."));
sysUser.setPhone(phone);
String userTel = (String)row.get(i++);
userTel = userTel.indexOf(".")==-1? userTel : userTel.substring(0, userTel.indexOf("."));
sysUser.setUserTel(userTel);
sysUser.setEmail((String)row.get(i++));
sysUser.setStatus(Constant.SYSTEM_STATUS_00);
sysUser.setStatusTime(DateUtil.getNowTime());
sysUserService.saveData(sysUser);
}
if(errorNum > 0){
msg.insert(0, "共 "+(rowNum-1)+" 行,执行成功 "+(rowNum-errorNum-1)+" 行,失败 "+errorNum+" 行,第 ");
msg.append("行内容有误");
}else{
msg.insert(0, "共 "+(rowNum-1)+" 行,执行成功 "+(rowNum-errorNum-1)+" 行,失败 "+errorNum+" 行");
}
j.setSuccess(true);
j.setMsg(msg.toString());
}else{
j.setSuccess(false);
j.setMsg("导入的文件格式必须是.xls或者.xlsx");
return j;
}
}
} catch (IOException e) {
e.printStackTrace();
j.setSuccess(false);
j.setMsg("导入失败,请确保导入的excel是编辑过的模板文件");
}
return j;
}
}