Excel导入
人员导入和部门导入
首先创建人员和部门导入的功能
核心的参数：
fileKey 文件的key
columnNameStartLine 名称开始行
columnValueStartLine 数据开始行
sheetIndex Excelsheet名称模板文件的样式：
名称用作构建DynaBean
核心的业务代码
导入部门
- 导入部门controller
/**
* 导入部门
*
* @param param
* @return
* @throws SQLException
*/
@RequestMapping(value = {"/doImportDepartment"}, method = {RequestMethod.POST},
produces = {"application/json; charset=utf-8"})
@ResponseBody
public BaseRespResult doImportDepartment(MethodArgument param) {
String fileKey = param.getRequest().getParameter("fileKey");
Integer columnNameStartLine = Integer.valueOf(param.getRequest().getParameter("columnNameStartLine"));
Integer columnValueStartLine = Integer.valueOf(param.getRequest().getParameter("columnValueStartLine"));
String sheetIndex = param.getRequest().getParameter("sheetIndex");
if (Strings.isNullOrEmpty(fileKey)) {
return BaseRespResult.errorResult("文件获取失败！");
}
ExcelImportSource excelImportSource = new ExcelImportSource(fileKey,columnNameStartLine,columnValueStartLine,sheetIndex);
DepartmentImportParser departmentImportParser = new DepartmentImportParser();
JSONObject customParameters = new JSONObject();
dpetExcelImportService.doImport(excelImportSource, departmentImportParser, customParameters);
return BaseRespResult.successResult("正在导入，请稍等！");
}- 导入部门代码service
@Component
public class DpetExcelImportService extends AbstractDynaBeanImportService {
@Autowired
MetaService metaService;
/**
* 导入回调
*
* @param list 导入之后的list
* @param customParameters 自定义参数
*/
@Override
public void customMethod(List<DynaBean> list, JSONObject customParameters) {
}
/**
* 排序
* @param dynaBeans
* @return
*/
@Override
public List<DynaBean> doSort(List<DynaBean> dynaBeans) {
return dynaBeans;
}- 部门格式化方法DepartmentImportParser
/**
* @program: je-platform
* @author: LIULJ
* @create: 2020/7/26
* @description：格式化方法，return ParseVo ，vo中包含两个参数，判断是否插入的作用适用于上下级，有些情况是在格式化完成后就要插入，如果为false，就是通一格式化后一起插入
* 参数1：deptMent要导入的部门 参数2：customParameters 自定义参数
*/
@Component
public class DepartmentImportParser extends AbstractDynaBeanImportParser {
@Override
public ParseVo parse(DynaBean deptMent, JSONObject customParameters) {
MetaService metaService = SpringContextHolder.getBean(MetaServiceImpl.class);
deptMent.setStr(BeanUtils.KEY_TABLE_CODE, "JE_CORE_DEPARTMENT");
// 部门类型,如果为空默认为部门
String rankName = deptMent.getStr("RANKNAME");
DynaBean deptMentType = metaService.selectOne("JE_CORE_DICTIONARYITEM",
ConditionsWrapper.builder().eq("DICTIONARYITEM_ITEMNAME", rankName).apply(
" AND DICTIONARYITEM_DICTIONARY_ID=(select JE_CORE_DICTIONARY_ID from JE_CORE_DICTIONARY WHERE DICTIONARY_DDCODE='JE_GSJB')"),
"DICTIONARYITEM_ITEMCODE");
if (deptMentType == null) {
deptMent.setStr("RANKCODE", "BM");
deptMent.setStr("RANKNAME", "部门");
} else {
deptMent.setStr("RANKCODE", deptMentType.getStr("DICTIONARYITEM_ITEMCODE"));
}
//部门名称格式 凯特/技术部门
String deptNames = deptMent.getStr("DEPTNAME");
String[] names;
if (deptNames.indexOf(";") >= 0) {
String[] szDeptNames = deptNames.split(";");
for (int i = 1; i < szDeptNames.length; i++) {
DynaBean newDeptMent = deptMent.setStr("DEPTNAME", szDeptNames[i]);
parse(newDeptMent, customParameters);
}
names = szDeptNames[0].split("/");
} else {
names = deptNames.split("/");
}
// 根据上级部门名称获取上级部门id，如果没有找到，默认放到Root下
String parentDeptId = addParentDept(names);
if (Strings.isNullOrEmpty(parentDeptId)) {
parentDeptId = "ROOT";
}
DynaBean parentDept = metaService.selectOne("JE_CORE_DEPARTMENT",
ConditionsWrapper.builder().eq("DEPTID", parentDeptId), "DEPTCODE,DEPTNAME,DEPTID,PATH,TREEORDERINDEX");
if (parentDept == null) {
parentDept = metaService.selectOne("JE_CORE_DEPARTMENT", ConditionsWrapper.builder().eq("DEPTID", "ROOT"),
"DEPTCODE,DEPTNAME,DEPTID,PATH,TREEORDERINDEX");
}
//set树形字段值
deptMent.setStr("DEPTNAME", names[names.length - 1]);
deptMent.setStr("DEPTID", JEUUID.uuid());
deptMent.setStr("DEPTCODE", JEUUID.uuid());
deptMent.setStr("PARENTCODE", parentDept.getStr("DEPTCODE"));
deptMent.setStr("PARENTNAME", parentDept.getStr("DEPTNAME"));
deptMent.setStr("PARENT", parentDept.getStr("DEPTID"));
deptMent.setStr("PARENTPATH", parentDept.getStr("PATH"));
deptMent.setStr("PATH", String.format("%s/%s", parentDept.getStr("PATH"), deptMent.getStr("DEPTID")));
deptMent.setStr("NODETYPE", "LEAF");
deptMent = buildDeptInfo(deptMent, parentDept);
return new ParseVo(deptMent, false);
}
/**
* 获取上级部门，如果没有部门没有找到，则添加新的部门，并返回父级id
* @param names
* @return
*/
private static String addParentDept(String[] names) {
String[] newNames = new String[names.length - 1];
for (int i = 0; i < newNames.length; i++) {
newNames[i] = names[i];
}
MetaService metaService = SpringContextHolder.getBean(MetaServiceImpl.class);
String parentId = "ROOT";
for (String name : newNames) {
DynaBean dept = metaService.selectOne("JE_CORE_DEPARTMENT",
ConditionsWrapper.builder().eq("DEPTNAME", name).like("PARENT", parentId),
"DEPTCODE,DEPTNAME,DEPTID,PATH,TREEORDERINDEX");
if (dept == null) {
if (name.equals("北京科技学院")) {
parentId = "ROOT";
}
dept = new DynaBean("JE_CORE_DEPARTMENT", true);
DynaBean parentDept = metaService.selectOne("JE_CORE_DEPARTMENT",
ConditionsWrapper.builder().eq("DEPTID", parentId), "DEPTCODE,DEPTNAME,DEPTID,PATH,TREEORDERINDEX");
parentId = JEUUID.uuid();
dept.setStr("DEPTNAME", name);
dept.setStr("DEPTID", parentId);
dept.setStr("DEPTCODE", parentId);
dept.setStr("PARENTCODE", parentDept.getStr("DEPTCODE"));
dept.setStr("PARENTNAME", parentDept.getStr("DEPTNAME"));
dept.setStr("PARENT", parentDept.getStr("DEPTID"));
dept.setStr("PARENTPATH", parentDept.getStr("PATH"));
dept.setStr("PATH", String.format("%s/%s", parentDept.getStr("PATH"), dept.getStr("DEPTID")));
dept.setStr("NODETYPE", "LEAF");
dept = buildDeptInfo(dept, parentDept);
}
parentId = dept.getStr("DEPTID");
}
return parentId;
}
/**
* 格式化初始值
* @param deptMent
* @param parentDept
* @return
*/
private static DynaBean buildDeptInfo(DynaBean deptMent, DynaBean parentDept) {
MetaService metaService = SpringContextHolder.getBean(MetaServiceImpl.class);
PCDynaServiceTemplate pcDynaServiceTemplate = SpringContextHolder.getBean(PCDynaServiceTemplate.class);
RoleManager roleManager = SpringContextHolder.getBean(RoleManager.class);
pcDynaServiceTemplate.buildModelCreateInfo(deptMent);
EndUser endUser = SecurityUserHolder.getCurrentUser();
deptMent.setStr("CREATEORG", endUser.getDeptCode());
deptMent.setStr("CREATEORGNAME", endUser.getDeptName());
deptMent.setStr("MODIFYTIME", deptMent.getStr("SY_CREATETIME"));
deptMent.setStr("CREATETIME", deptMent.getStr("SY_CREATETIME"));
deptMent.setStr("CREATEUSER", endUser.getUserCode());
deptMent.setStr("CREATEUSER", endUser.getUsername());
deptMent.setStr("JTGSMC", endUser.getJtgsMc());
deptMent.setStr("JTGSDM", endUser.getJtgsDm());
deptMent.setStr("JTGSID", endUser.getJtgsId());
deptMent.setStr("GSBMID", endUser.getGsbmId());
deptMent.setStr("RANKCODE", "BM");
deptMent.setStr("RANKNAME", "部门");
deptMent.setStr("ORDERINDEX", deptMent.getStr("DISPLAY_ORDER"));
deptMent.setStr("ZHID", endUser.getZhId());
deptMent.setStr("ZHMC", endUser.getZhMc());
deptMent.setStr("CREATEORG", endUser.getDeptCode());
deptMent.setStr("CREATEORGNAME", endUser.getDeptName());
deptMent.setStr("STATUS", "1");
deptMent.setStr("LAYER", "1");
deptMent.set("TREEORDERINDEX", parentDept.get("TREEORDERINDEX"));
roleManager.generateTreeOrderIndex(deptMent);
metaService.insert(deptMent);
if (parentDept.getStr("NODETYPE").equals("LEAF")) {
parentDept.setStr("NODETYPE", "GENERAL");
metaService.update(parentDept);
}
return deptMent;
}导入用户
- 导入用户controller
/**
* 导入用户
* @param param
* @return
* @throws SQLException
*/
@RequestMapping(value = {"/doImportUser"}, method = {RequestMethod.POST},
produces = {"application/json; charset=utf-8"})
@ResponseBody
public BaseRespResult doImportUser(MethodArgument param) {
//文件的key
String fileKey = param.getRequest().getParameter("fileKey");
if (Strings.isNullOrEmpty(fileKey)) {
return BaseRespResult.errorResult("文件获取失败！");
}
ExcelImportSource excelImportSource = new ExcelImportSource(fileKey, 2, 3, "Sheet1");
EndUserImportParser endUserImportParser = new EndUserImportParser();
JSONObject customParameters = new JSONObject();
userImportService.doImport(excelImportSource, endUserImportParser, customParameters);
return BaseRespResult.successResult("正在导入，请稍等！");
}- 导入用户代码service
public class UserImportService extends AbstractDynaBeanImportService {
@Override
public List<DynaBean> doSort(List<DynaBean> dynaBeans) {
return dynaBeans;
}
}- 导入用户格式化方法EndUserImportParser
public class EndUserImportParser extends AbstractDynaBeanImportParser {
@Override
public ParseVo parse(DynaBean user, JSONObject customParameters) {
MetaService metaService = SpringContextHolder.getBean(MetaService.class);
user.setStr(BeanUtils.KEY_TABLE_CODE, "JE_CORE_ENDUSER");
String userCode = user.getStr("USERCODE");
// 处理登录名重复
int count = metaService.select("JE_CORE_ENDUSER", ConditionsWrapper.builder().eq("USERCODE", userCode)).size();
if (count > 0) {
return new ParseVo(null, false);
}
user.setStr("BACKUSERCODE", userCode);
user.setStr("USERCODE", userCode);
String birthday = user.getStr("BIRTHDAY");
if (Strings.isNullOrEmpty(birthday)) {
birthday = DateUtils.formatDate(DateUtils.getDate(birthday, DateUtils.DAFAULT_DATE_FORMAT),
DateUtils.DAFAULT_DATE_FORMAT);
// 生日
user.setStr("BIRTHDAY", birthday);
}
// 邮箱
user.setStr("COMPANYEMAIL", user.getStr("COMPANYEMAIL"));
// 固话
String tel = user.getStr("PHONE");
user.setStr("ZUOJI", tel);
// 认证手机号
String phone = user.getStr("PHONE");
user.setStr("IDENTPHONE", phone);
// 手机号
user.setStr("PHONE", phone);
// 性别
if (!Strings.isNullOrEmpty(user.getStr("GENDER"))) {
DynaBean genderInfos = metaService.selectOne("JE_CORE_DICTIONARYITEM",
ConditionsWrapper.builder().eq("DICTIONARYITEM_ITEMNAME", user.getStr("GENDER")).apply(
" AND DICTIONARYITEM_DICTIONARY_ID=(select JE_CORE_DICTIONARY_ID from JE_CORE_DICTIONARY WHERE DICTIONARY_DDCODE='JE_SEX')"),
"DICTIONARYITEM_ITEMCODE");
user.setStr("GENDER", genderInfos.getStr("DICTIONARYITEM_ITEMCODE"));
} else {
user.setStr("GENDER", "MAN");
}
// 密码
Md5PasswordEncoder md5 = new Md5PasswordEncoder();
String defaulePassWord =
md5.encodePassword(StringUtil.getDefaultValue(WebUtils.getSysVar("JE_SYS_PASSWORD"), "123456"), null);
user.set("PASSWORD", defaulePassWord);
//
user.set("ISMANAGER", "0");
buildUserDeptInfo(user, metaService);
// buildUserRoleInfo(user, metaService);
// true 插入 false不插入
return new ParseVo(user, false);
}
/**
* 部门信息
*
* @param user
*/
private void buildUserDeptInfo(DynaBean user, MetaService metaService) {
String deptName = user.getStr("DEPTNAME");
String[] deptAllNames = deptName.split(";");
for(String deptAllName : deptAllNames){
user.setStr("USERID",JEUUID.uuid());
String[] deptNames = deptAllName.split("/");
deptName = deptNames[deptNames.length - 1];
DynaBean dept = null;
List<DynaBean> depts =
metaService.select("JE_CORE_DEPARTMENT", ConditionsWrapper.builder().eq("DEPTNAME", deptName));
if (depts.size() == 1) {
dept = depts.get(0);
} else if (depts.size() > 1) {
String deptId = "";
String parentDeptId = "";
int i = 0;
for (String str : deptNames) {
DynaBean addDept = new DynaBean();
if (i == 0) {
addDept =
metaService.selectOne("JE_CORE_DEPARTMENT", ConditionsWrapper.builder().eq("DEPTNAME", str));
parentDeptId = addDept.getStr("DEPTID");
deptId = addDept.getStr("DEPTID");
} else {
parentDeptId = metaService
.selectOne("JE_CORE_DEPARTMENT",
ConditionsWrapper.builder().eq("DEPTNAME", str).eq("PARENT", parentDeptId))
.getStr("DEPTID");
deptId = parentDeptId;
}
i++;
}
dept = metaService.selectOne("JE_CORE_DEPARTMENT", ConditionsWrapper.builder().eq("DEPTID", deptId));
}
if (dept == null) {
dept = metaService.selectOne("JE_CORE_DEPARTMENT", ConditionsWrapper.builder().eq("DEPTNAME", "系统管理部"));
}
user.setStr("DEPTCODE", dept.getStr("DEPTCODE"));
user.setStr("DEPTNAME", dept.getStr("DEPTNAME"));
user.setStr("DEPTID", dept.getStr("DEPTID"));
user.setStr("DEPTORDERINDEX", dept.getStr("TREEORDERINDEX"));
buildUserCreateInfo(user);
metaService.insert(user);
}
}
/**
* 角色信息
*
* @param user
*/
private void buildUserRoleInfo(DynaBean user, MetaService metaService) {
String roleNames = user.getStr("ROLENAMES");
List<String> roleNamesList = Arrays.asList(roleNames.split(","));
List<DynaBean> role = metaService.select("JE_CORE_ROLE",
ConditionsWrapper.builder().in("ROLENAME", roleNamesList).eq("ROLETYPE", "ROLE").groupBy("ROLENAME"));
if (role.size() == 0) {
throw new RuntimeException(roleNames + "角色没有找到数据异常！");
}
StringBuffer roleIds = new StringBuffer();
StringBuffer roleNamesStr = new StringBuffer();
StringBuffer roleCodes = new StringBuffer();
for (DynaBean roleDynaBean : role) {
DynaBean roleUser = new DynaBean("JE_CORE_ROLE_USER", true);
roleUser.setStr("USERID", user.getStr("USERID"));
roleUser.setStr("ROLEID", roleDynaBean.getStr("ROLEID"));
metaService.insert(roleUser);
if (Strings.isNullOrEmpty(roleIds.toString())) {
roleIds.append(roleDynaBean.getStr("ROLEID"));
roleNamesStr.append(roleDynaBean.getStr("ROLENAME"));
roleCodes.append(roleDynaBean.getStr("ROLECODE"));
} else {
roleIds.append("," + roleDynaBean.getStr("ROLEID"));
roleNamesStr.append("," + roleDynaBean.getStr("ROLENAME"));
roleCodes.append("," + roleDynaBean.getStr("ROLECODE"));
}
}
// 角色编码
user.setStr("ROLECODES", roleCodes.toString());
// 角色主键
user.setStr("ROLEIDS", roleIds.toString());
// 角色名称
user.setStr("ROLENAMES", roleNamesStr.toString());
metaService.update(user);
}
/**
* 基础信息
*
* @param user
*/
private void buildUserCreateInfo(DynaBean user) {
user.setStr("ISSYSUSER", "1");
user.setStr("FLAG", "1");
String ORDERINDEX = user.getStr("DISPLAY_ORDER");
user.set("USERORDER", ORDERINDEX);
user.setStr("INITPASSWORD", "1");
user.setStr("SENTRYCODES", "");
user.setStr("MONITORDEPTCODE", "");
user.setStr("MENUTYPE", "");
user.setStr("LOGINLOCKED", "0");
user.setStr("VALID", "1");
user.setStr("STATUS", "1");
PCDynaServiceTemplate pcDynaServiceTemplate = SpringContextHolder.getBean(PCDynaServiceTemplate.class);
pcDynaServiceTemplate.buildModelCreateInfo(user);
EndUser endUser = SecurityUserHolder.getCurrentUser();
user.setStr("CREATEORG", endUser.getDeptCode());
user.setStr("CREATEORGNAME", endUser.getDeptName());
user.setStr("CREATETIME", user.getStr("SY_CREATETIME"));
user.setStr("CREATEUSER", endUser.getUserCode());
user.setStr("CREATEUSERNAME", endUser.getUsername());
user.setStr("MODIFYUSER", endUser.getUserCode());
user.setStr("MODIFYUSERNAME", endUser.getUsername());
user.setStr("JTGSMC", endUser.getJtgsMc());
user.setStr("JTGSDM", endUser.getJtgsDm());
user.setStr("JTGSID", endUser.getJtgsId());
user.setStr("GSBMID", endUser.getGsbmId());
user.setStr("ZHID", endUser.getZhId());
user.setStr("ZHMC", endUser.getZhMc());
}最后编辑: 呼丽华 文档更新时间: 2024-03-05 11:49 作者：呼丽华
