人员卡片信息报表导入导出

人员卡片信息报表导入导出


人员信息的导出：
jsp界面：
url="/personAction!exportPersonToExcel.action";


PersonAction：
public void exportPersonToExcel(){
personInfoService.exportPersonInfo();
}



PersonInfoService:

private OutputStream getOutputStreamForExcelExport() {
OutputStream os = null;
HttpServletResponse response = ServletActionContext.getResponse();
try {
os = response.getOutputStream();
} catch (IOException e) {
}
response.reset();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss");
String dateString = sdf.format(new Date());
response.setHeader("Content-disposition",
"attachment; filename=\"person" + dateString + ".xls\"");
response.setContentType("application/msexcel");
return os;
}

public void exportPersonInfo() {
OutputStream os = getOutputStreamForExcelExport();
WritableWorkbook wbook = null;
InputStream is = null;
try {
is = new FileInputStream(ConstParamCommon.getOnecardWebFileRoot()+ ConstParamOnecard.PERSON_EXCEL_PATH);
wbook = Workbook.createWorkbook(os, Workbook.getWorkbook(is));
WritableSheet sheet = wbook.getSheet(0);
exportPersonToSheet(sheet);
addOncardConfigLog("logContent.ocs.exportPerson",null);
} catch (IOException e) {
LogUtils.logException(e);
} catch (BiffException e) {
LogUtils.logException(e);
} finally {
try {
if (wbook != null) {
wbook.write();
wbook.close();
}
if (os != null) {
os.flush();
os.close();
}
if (is != null) {
is.close();
}
} catch (Exception e) {
LogUtils.logException(e);
}
}
}

private void exportPersonToSheet(WritableSheet sheet) {
int row = 0;
int col = 0;
try {
// 增加人员信息
List<PersonInfo> personList = personDao
.getPersonByDepartmentCasecade(departmentService
.getDefaultDepartment());
if (personList != null && personList.size() > 0) {
for (PersonInfo p : personList) {
col = 0;
row++;// 第一行为标题信息，模板中有
sheet.addCell(new Label(col++, row, p.getPersonCode()));// 编号
sheet.addCell(new Label(col++, row, p.getName()));// 姓名
sheet.addCell(new Label(col++, row, p.getDepartment()
.getDepartmentNumber()));// 部门编号
sheet.addCell(new Label(col++, row, p.getSex() == null ? ""
: ConstParamOnecard.PERSONGENDER_DESCRIPTION[p.getSex()]));// 性别
sheet.addCell(new Label(
col++,
row,
p.getIdentityType() == null ? ""
: ConstParamOnecard.IDENTITY_TYPE_DESCRIPTION[p
.getIdentityType()]));// 证件类型
sheet.addCell(new Label(col++, row, p.getIdentityId()));// 证件号码
sheet.addCell(new Label(
col++,
row,
p.getBirthday() == null ? "": DateTimeUtil.dateToString(
p.getBirthday(),
DateTimeUtil.DATA_FORMAT_yyyy_MM_dd)));// 出生日期
sheet.addCell(new Label(col++, row, p.getPhone()));// 电话
sheet.addCell(new Label(col++, row, p.getPinyin()));// 拼音代码
sheet.addCell(new Label(col++, row, p.getAddress()));// 联系地址
sheet.addCell(new Label(col++, row, p.getEnglishName()));// 英文名
sheet.addCell(new Label(col++, row, p.getEmail()));// E-MAIL
sheet.addCell(new Label(
col++,
row,
p.getInaugurationDate() == null ? ""
: DateTimeUtil.dateToString(p.getInaugurationDate(),
DateTimeUtil.DATA_FORMAT_yyyy_MM_dd)));// 到职日期
sheet.addCell(new Label(
col++,
row,
p.getLeaveJobDate() == null ? "": DateTimeUtil.dateToString(
p.getLeaveJobDate(),
DateTimeUtil.DATA_FORMAT_yyyy_MM_dd)));// 离职日期
sheet.addCell(new Label(col++, row, p.getEducational()));// 学历
sheet.addCell(new Label(col++, row, p.getPeople()));// 民族
sheet.addCell(new Label(col++, row, p.getRemark()));// 备注
}
}


} catch (RowsExceededException e) {
LogUtils.logException(e);
} catch (WriteException e) {
LogUtils.logException(e);
}
}

人员信息的导入：


url = "/personAction!importPersonFromExcel.action";
PersonAction：
public void importPersonFromExcel(){
OperateResult result = new OperateResult();
if(excel == null){
result.setResult(false);
result.setMsg("请选择要上传的文件！");
}else{
try{
result = personInfoService.saveBatchImportPersonInfo(excel);
}catch (Exception e) {
LogUtils.logException(e);
AjaxUtil.ajaxWrite(false, e.getMessage());
}
}
AjaxUtil.ajaxWrite(result);
}
PersonInfoService:
public OperateResult saveBatchImportPersonInfo(File excel) {
OperateResult result = new OperateResult();
result.setResult(true);// 默认成功
List<String> msg = new ArrayList<String>();
String className = PersonImport.class.getName();
String[] fieldNames = {"personCode","name","deptCode","gender","IDType","identityId","birthday","phone","pinyin","address","englishName","email","inaugurationDate","leaveJobDate","educational","people","remark"};
List<PersonImport> personImportList = FileImportUtil.parseExcel(excel,className,fieldNames);
for(PersonImport personImport:personImportList){
   if(personImport.getPersonCode()==null||personImport.getPersonCode().trim().length()==0){
       personImport.setPersonCode(SysCodeUtil.generateByRandom());
   }
}
boolean validate = validateImportPerson(personImportList,msg);
validate = validate?batchSavePersonImport(personImportList,msg):validate;
result.setResult(validate);
result.setMsg(StringUtils.join(msg,"<br/>"));
addOncardConfigLog("logContent.ocs.importPerson",null);
return result;
}

/**
* 验证导入人员信息的正确性
*/
private boolean validateImportPerson(List<PersonImport> personImportList,List<String> msg) {
DetachedCriteria criteria = DetachedCriteria.forClass(PersonInfo.class);
criteria.add(Restrictions.eq("status", ConstParamOnecard.PERSONSTATU_NOMAL));
List<PersonInfo> personList = personDao.findAllByCriteria(criteria);
Set<String> personCodes  = new HashSet<String>();
Set<String> personNames  = new HashSet<String>();
Set<String> typeAndIDs = new HashSet<String>();
for(PersonInfo person:personList){
personCodes.add(person.getPersonCode());
personNames.add(person.getName());
if(OneCardUtil.validateStrNotNull(person.getIdentityId()) && person.getIdentityType()!=null){
typeAndIDs.add(person.getIdentityType()+ConstParamOnecard.UNDERLINE+person.getIdentityId().trim());
}
}
List<String> deptCodes = departmentService.findDeptCodes();
boolean result = true;
StringBuffer validateMsg;
int i = 0;
Integer idType = null;
for(PersonImport personImport:personImportList){
i++;
validateMsg = new StringBuffer();
validateMsg.append(PersonValidate.validatePersonCode(personImport.getPersonCode(), personCodes));
validateMsg.append(PersonValidate.validatePersonName(personImport.getName(), personNames));
validateMsg.append(DepartmentValidate.validateDeptCodeExisting(personImport.getDeptCode(), deptCodes));
validateMsg.append(PersonValidate.validateGender(personImport.getGender()));
validateMsg.append(PersonValidate.validateIDWithType(personImport.getIdentityId(),personImport.getIDType(),typeAndIDs));
validateMsg.append(PersonValidate.validateBirthday(personImport.getBirthday()));
validateMsg.append(PersonValidate.validatePhone(personImport.getPhone()));
validateMsg.append(PersonValidate.validatePinyin(personImport.getPinyin()));
validateMsg.append(PersonValidate.validateAddress(personImport.getAddress()));
validateMsg.append(PersonValidate.validateEnglishName(personImport.getEnglishName()));
validateMsg.append(PersonValidate.validateEmail(personImport.getEmail()));
validateMsg.append(PersonValidate.validateCmpDate(personImport.getInaugurationDate(), personImport.getLeaveJobDate()));
validateMsg.append(PersonValidate.validateEducational(personImport.getEducational()));
validateMsg.append(PersonValidate.validateNation(personImport.getPeople()));
validateMsg.append(PersonValidate.validateRemark(personImport.getRemark()));
if(validateMsg.length()>0){
msg.add("第"+(i)+"行，【"+personImport.getName()+"】数据错误:"+validateMsg.toString());
result = false;
}
personNames.add(OneCardUtil.stringTrim(personImport.getName()));
personCodes.add(OneCardUtil.stringTrim(personImport.getPersonCode()));
if((idType=ConstParamOnecard.IDENTITYTYPEREVERSEMAP.get(personImport.getIDType()))!=null && OneCardUtil.validateStrNotNull(personImport.getIdentityId())){
typeAndIDs.add(idType+ConstParamOnecard.UNDERLINE+personImport.getIdentityId().trim());
}
}
return result;
}

private boolean batchSavePersonImport(List<PersonImport> personImportList,List<String> msg) {
List<PersonInfo> personList = new ArrayList<PersonInfo>();
Set<String> deptCodes = new HashSet<String>();
for(PersonImport personImport:personImportList){
deptCodes.add(personImport.getDeptCode());
}
List<DepartmentInfo> deptList = departmentService.findByDeptCode(deptCodes);
Map<String,DepartmentInfo> deptMap = new HashMap<String, DepartmentInfo>();
for(DepartmentInfo dept:deptList){
deptMap.put(dept.getDepartmentNumber(), dept);
}
PersonInfo person = null;
DepartmentInfo dept = null;
try{
for(PersonImport personImport:personImportList){
person = new PersonInfo();
OneCardUtil.cloneObject(personImport, person);
dept = deptMap.get(personImport.getDeptCode());
person.setIdentityType(ConstParamOnecard.IDENTITYTYPEREVERSEMAP.get(personImport.getIDType()));
person.setSex(ConstParamOnecard.GENDERREVERSEMAP.get(personImport.getGender()));
person.setDepartment(dept);
person.setStatus(ConstParamOnecard.PERSONSTATU_NOMAL);
person.setPath(dept.getPath()+ JsonTreeNodeUtil.DEPARTMENT_PERFIX+ dept.getDepartmentId()+ PATH_SEPARATOR);
personList.add(person);
}
}catch(Exception e){
msg.add(e.getMessage());
LogUtils.logException(e);
return false;
}
savePersons(personList);
return true;
}


PersonValidate {
public final static String validatePersonCode(String personCode,Collection<String> personCodeList){
String result = OneCardUtil.validateStrNotNull(personCode)?"":"人员编号不允许为空;";
result = result == ""?(personCode.trim().length()<21?result:"人员编号的长度超过20;"):result;
result = result == ""?(RegexUtil.test(personCode,RegexUtil.PATTERN_INDEXCODE)?result:"编号只能为数字;"):result;
result = result == ""?(personCodeList.contains(personCode.trim())?"人员编号已存在;":result):result;
return result;
}
}