继阅读，含导入上传数据的思路；
package com.kingdee.shr.batchAffair.io.impl;
import java.sql.SQLException;
import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;import com.kingdee.bos.dao.IObjectPK;import com.kingdee.bos.dao.ormapping.ObjectUuidPK;import com.kingdee.bos.metadata.entity.DataType;import com.kingdee.bos.sql.ParserException;import com.kingdee.bos.util.BOSUuid;import com.kingdee.eas.base.message.util.StringUtil;import com.kingdee.eas.base.permission.UserInfo;import com.kingdee.eas.basedata.person.PersonFactory;import com.kingdee.eas.basedata.person.PersonHisFactory;import com.kingdee.eas.basedata.person.PersonHisInfo;import com.kingdee.eas.basedata.person.PersonInfo;import com.kingdee.eas.common.EASBizException;import com.kingdee.eas.framework.CoreBaseInfo;import com.kingdee.eas.framework.ICoreBase;import com.kingdee.eas.hr.base.AffairActionReasonFactory;import com.kingdee.eas.hr.base.DimissionReasonFactory;import com.kingdee.eas.hr.base.EmpLaborRelationFactory;import com.kingdee.eas.hr.base.EmpLaborRelationHisFactory;import com.kingdee.eas.hr.base.EmpLaborRelationHisInfo;import com.kingdee.eas.hr.base.EmpLaborRelationInfo;import com.kingdee.eas.hr.base.EmpPosOrgRelationFactory;import com.kingdee.eas.hr.base.EmpPosOrgRelationInfo;import com.kingdee.eas.hr.base.EmployeeTypeFactory;import com.kingdee.eas.hr.base.HRBizDefineFactory;import com.kingdee.eas.hr.base.VariationReasonFactory;import com.kingdee.eas.hr.emp.PersonPositionFactory;import com.kingdee.eas.hr.emp.PersonPositionHisFactory;import com.kingdee.eas.hr.emp.PersonPositionHisInfo;import com.kingdee.eas.hr.emp.PersonPositionInfo;import com.kingdee.eas.util.app.ContextUtil;import com.kingdee.eas.util.app.DbUtil;import com.kingdee.jdbc.rowset.IRowSet;import com.kingdee.shr.base.syssetting.SHRFileTemplateFieldsCollection;import com.kingdee.shr.base.syssetting.SHRFileTemplateFieldsInfo;import com.kingdee.shr.base.syssetting.app.io.BaseColumnInfo;import com.kingdee.shr.base.syssetting.app.io.ExcelUtil;import com.kingdee.shr.base.syssetting.app.io.impl.ResignBizBillHrManFileService;import com.kingdee.shr.base.syssetting.app.io.impl.util.PersonResignOSDChangeImportCheck;import com.kingdee.shr.base.syssetting.context.SHRContext;import com.kingdee.util.StringUtils;import com.kingdee.util.UuidException;public class PersonResignOSDChangeFileService extends ResignBizBillHrManFileService {
private List<Map<String,Object>> dataMaps=new ArrayList(); private Map<String, Object> dataMap = new HashMap(); private static boolean ISRUNNING = false; protected void addSelfDefineColumnInfo(SHRFileTemplateFieldsCollection fields) { SHRFileTemplateFieldsInfo personNumber = new SHRFileTemplateFieldsInfo(); personNumber.setName("员工编码"); personNumber.setIsNeed(false); personNumber.setPropName("empNumber"); personNumber.setIsPrimayField(true); fields.addObject(0, personNumber); SHRFileTemplateFieldsInfo personName = new SHRFileTemplateFieldsInfo(); personName.setName("员工名字"); personName.setIsNeed(true); personName.setPropName("empName"); personName.setIsPrimayField(false); fields.addObject(1, personName); } @Override public Map<String, BaseColumnInfo> getColumnInfo() { Map columnInfos = super.getColumnInfo(); columnInfos.put("员工编码", new BaseColumnInfo("empNumber",BaseColumnInfo.SimpleType, DataType.STRING, null, false)); columnInfos.put("员工名字", new BaseColumnInfo("empName",BaseColumnInfo.SimpleType, DataType.STRING, null, true)); return columnInfos; } protected void addExampleFieldValue(SHRFileTemplateFieldsCollection fields,XSSFWorkbook wb, Row row) { Map columnInfoMap = getColumnInfo(); CellStyle blue = wb.createCellStyle(); Font font = wb.createFont(); font.setColor(IndexedColors.ROYAL_BLUE.getIndex()); font.setItalic(true); blue.setFont(font); CellStyle other = wb.createCellStyle(); Font font2 = wb.createFont(); font2.setItalic(true); other.setFont(font2); other.setAlignment((short) 1); other.setDataFormat(wb.createDataFormat().getFormat("@")); for (int i = 0; i < fields.size(); ++i) { SHRFileTemplateFieldsInfo field = fields.get(i); String name = field.getName(); BaseColumnInfo clumnInfo = (BaseColumnInfo) columnInfoMap.get(name); Cell cell = row.createCell(i); if ("empName".equals(clumnInfo.getPropName())){ //员工姓名 cell.setCellValue("张三"); cell.setCellStyle(other); } else if ("empNumber".equals(clumnInfo.getPropName())) {//员工编号 cell.setCellValue("Y03235"); cell.setCellStyle(other); } else if ("hrBizDefine".equals(clumnInfo.getPropName())) {//变动操作-离职操作 cell.setCellValue("辞退"); cell.setCellStyle(other); } else if ("affairActionReason".equals(clumnInfo.getPropName())) {//变动类型-离职类型 cell.setCellValue("辞退"); cell.setCellStyle(other); } else if ("variationReason".equals(clumnInfo.getPropName())) {//变动原因-离职原因 cell.setCellValue("个人发展"); cell.setCellStyle(other); } else if ("empType".equals(clumnInfo.getPropName())) {//目标用工状态 cell.setCellValue("离职员工"); cell.setCellStyle(other); } else if ("bizDate".equals(clumnInfo.getPropName())) {//离职日期 cell.setCellValue("2014-07-14"); cell.setCellStyle(other); } } }@Override
protected void addTemplateReadMe(XSSFWorkbook wb) { StringBuffer sb = new StringBuffer("模板说明:"); sb.append("\r\n").append("1、红色字体为必录项；").append("\r\n").append( "2、员工姓名存在重名时，请填写员工编码以唯一确定要变动的员工；").append("\r\n").append( "3、变动操作必须是在入职单据上配置的事务类型；").append("\r\n").append( "4、变动操作对应多个变动类型和变动原因时，请指定；").append("\r\n").append( "5、离职生效日期对应系统的生效日期;建议在使用模板前,办公人员将导入模板设置为文本格式,避免因离职日期格式为非YYYY-MM-DD格式而导入失败；").append("\r\n");XSSFSheet sheetDec = wb.createSheet("模板说明");
sheetDec.addMergedRegion(new CellRangeAddress(0, 4, 0, 4));
sheetDec.setAutobreaks(true);CellStyle autoChange = wb.createCellStyle();
autoChange.setWrapText(true); Font font = wb.createFont(); font.setFontHeightInPoints((short) 12); autoChange.setFont(font); for (int i = 0; i < 5; ++i) { Row rowDec = sheetDec.createRow(i); rowDec.setHeight((short) 800); Cell cellDec = rowDec.createCell(i); sheetDec.setColumnWidth(i, 6000); if (i == 0) { cellDec.setCellValue(sb.toString()); } cellDec.setCellStyle(autoChange); } }@Override
protected String getImportEntityName() { // TODO Auto-generated method stub return "com.kingdee.eas.hr.affair.app.ResignBizBillEntry"; } /*********************** | 数据导入校验操作 | ======================*/ @Override protected boolean validData(boolean valid, int i,Map<Integer, String> error_report) throws BOSException { boolean otherValid = true; Map columnInfoMap = getColumnInfo(); this.row = this.sheet.getRow(i); Context ctx = SHRContext.getInstance().getContext(); Map validMap = new HashMap(); for (int j = 0; j < this.colNum; ++j) { String field = (String) this.fieldMap.get(Integer.valueOf(j)); BaseColumnInfo columnInfo = (BaseColumnInfo) columnInfoMap.get(field); if (columnInfo == null){ continue; } this.cell = this.row.getCell((short) j); Object value = ExcelUtil.getCellValue(this.cell); validMap.put(columnInfo.getPropName(), value.toString());}
String errorMsg = ""; PersonResignOSDChangeImportCheck ric = new PersonResignOSDChangeImportCheck(); if (StringUtils.isEmpty(errorMsg)) { errorMsg = ric.checkPerson(ctx, validMap); }if (StringUtils.isEmpty(errorMsg)) {
errorMsg = ric.checkActionReasonAndVariation(ctx, validMap); } if (StringUtils.isEmpty(errorMsg)){ errorMsg = ric.checkEmpType(ctx, validMap); } //将检验数据的结果最终放置到这个error_report中; if (!(StringUtils.isEmpty(errorMsg))) { error_report.put(Integer.valueOf(i), errorMsg); otherValid = false; }else{ dataMaps.add(validMap); } return otherValid; }/*********************** | 数据导入操作 | ======================*/ @Override protected void submitData(ICoreBase corebase, CoreBaseInfo baseInfo)throws ParserException, EASBizException, BOSException { Context ctx = SHRContext.getInstance().getContext(); UserInfo currentUserInfo = ContextUtil.getCurrentUserInfo(ctx); UserInfo uInfo = (UserInfo) ctx.get("UserInfo"); PersonInfo pInfo = uInfo.getPerson(); List<PersonInfo> persons=new ArrayList<PersonInfo>(); Iterator iter=dataMaps.iterator(); while (iter.hasNext()) { Map<String,Object> dataMap=(Map<String, Object>) iter.next(); PersonResignOSDChangeImportCheck posdIC=new PersonResignOSDChangeImportCheck(); IObjectPK pid=posdIC.getPid(dataMap.get("empNumber").toString(), dataMap.get("empName").toString(),ctx); /** * 更新个人信息 * * 个人信息 用工关系状态 T_BD_Person.FEmployeeTypeID EXCEL中填写的目标用工状态 备注 T_BD_Person.FDescription_L2 EXCEL中填写的备注内容 最新修改人 T_BD_Person.Flastupdateuserid 当前操作用户ID 最新修改时间 T_BD_Person.FLastupdatetime EXCEL导入生效的时间 * */ PersonInfo person=PersonFactory.getLocalInstance(ctx).getPersonInfo(pid); person.setEmployeeType(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType")+"'")); person.setDescription(StringUtil.isEmpty((String) dataMap.get("description"))==true?"":dataMap.get("description").toString()); person.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); person.setLastUpdateUser(uInfo); PersonFactory.getLocalInstance(ctx).save(person); /** * 个人信息历史（针对当前最新记录） 用工关系状态 T_BD_Personhis.FEmployeeTypeID EXCEL中填写的目标用工状态 备注 T_BD_Personhis.FDescription_L2 EXCEL中填写的备注内容 最新修改人 T_BD_Personhis.Flastupdateuserid 当前操作用户ID 最新修改时间 T_BD_Personhis.FLastupdatetime EXCEL导入生效的时间 */ String sql2="select fid from t_bd_personhis where fnumber='"+dataMap.get("empNumber")+"'"; IRowSet rs2=DbUtil.executeQuery(ctx,sql2); IObjectPK phisId=null; try { if(rs2.next()){ phisId=new ObjectUuidPK(BOSUuid.read(rs2.getString(1))); } } catch (UuidException e1) { e1.printStackTrace(); } catch (SQLException e1) { e1.printStackTrace(); } PersonHisInfo personHis=PersonHisFactory.getLocalInstance(ctx).getPersonHisInfo(phisId); personHis.setEmployeeType(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType")+"'")); personHis.setDescription(StringUtil.isEmpty((String) dataMap.get("description"))==true?"":dataMap.get("description").toString()); personHis.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); personHis.setLastUpdateUser(uInfo); PersonHisFactory.getLocalInstance(ctx).save(personHis); /** * 职业信息 * * 离职日期 T_HR_PersonPosition.FLeftDate T_HR_PersonPositionhis.FLeftDate EXCEL中填写的离职日期 离职原因 T_HR_PersonPosition.FDimissionReasonid T_HR_PersonPositionhis. FDimissionReasonid EXCEL中填写的离职原因 最新修改人 T_HR_PersonPosition.Flastupdateuserid T_HR_PersonPositionhis. Flastupdateuserid 导入的操作用户ID 最新修改时间 T_HR_PersonPosition. FLastupdatetime T_HR_PersonPositionhis. FLastupdatetime EXCEL导入生效的时间 */ String sql3="select top 1 fid from t_hr_personposition where fpersonid='"+pid.toString()+"' order by flastupdatetime desc "; IRowSet rs3=DbUtil.executeQuery(ctx,sql3.toString()); IObjectPK ppid=null; try { if(rs3.next()){ ppid=new ObjectUuidPK(BOSUuid.read(rs3.getString(1))); } } catch (UuidException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } PersonPositionInfo personpositionInfo=PersonPositionFactory.getLocalInstance(ctx).getPersonPositionInfo(ppid); personpositionInfo.setLeftDate(new Date(posdIC.effect(dataMap.get("bizDate").toString()).getTime())); personpositionInfo.setDimissionReason(DimissionReasonFactory.getLocalInstance(ctx).getDimissionReasonInfo("where name='"+dataMap.get("variationReason").toString()+"'")); personpositionInfo.setLastUpdateUser(uInfo); personpositionInfo.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); PersonPositionFactory.getLocalInstance(ctx).save(personpositionInfo); /** * 职业信息历史（针对当前最新记录） */ String sql4="select top 1 fid from t_hr_personpositionhis where fpersonid='"+pid.toString()+"' order by flastupdatetime desc "; IRowSet rs4=DbUtil.executeQuery(ctx,sql4.toString()); IObjectPK pphisid=null; try { if(rs4.next()){ pphisid=new ObjectUuidPK(BOSUuid.read(rs4.getString(1))); } } catch (UuidException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } PersonPositionHisInfo personPositionHisInfo=PersonPositionHisFactory.getLocalInstance(ctx).getPersonPositionHisInfo(pphisid); personPositionHisInfo.setLeftDate(new Date(posdIC.effect(dataMap.get("bizDate").toString()).getTime())); personPositionHisInfo.setDimissionReason(DimissionReasonFactory.getLocalInstance(ctx).getDimissionReasonInfo("where name='"+dataMap.get("variationReason").toString()+"'")); personPositionHisInfo.setLastUpdateUser(uInfo); personPositionHisInfo.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); PersonPositionHisFactory.getLocalInstance(ctx).save(personPositionHisInfo); /** * 用工关系 * * 用工关系状态 T_HR_EmpLaborRelation.FLaborRelationStateID EXCEL中填写的目标用工状态 用工关系类型 T_HR_EmpLaborRelation.FEmployeeModleID EXCEL中填写的目标用工状态对应的用工关系类型 最新修改人 T_HR_EmpLaborRelation.Flastupdateuserid 导入的操作用户ID 最新修改时间 T_HR_EmpLaborRelation.FLastupdatetime EXCEL导入生效的时间 */ String sql5="select top 1 fid from T_HR_EmpLaborRelation where fpersonid='"+pid.toString()+"' order by flastupdatetime desc "; IRowSet rs5=DbUtil.executeQuery(ctx,sql5.toString()); IObjectPK elrId=null; try { if(rs5.next()){ elrId=new ObjectUuidPK(BOSUuid.read(rs5.getString(1))); } } catch (UuidException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } EmpLaborRelationInfo empLaborRelationInfo=EmpLaborRelationFactory.getLocalInstance(ctx).getEmpLaborRelationInfo(elrId); empLaborRelationInfo.setLaborRelationState(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType").toString()+"'")); empLaborRelationInfo.setEmployeeModle(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType").toString()+"'").getEmployeeModle()); empLaborRelationInfo.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); empLaborRelationInfo.setLastUpdateUser(uInfo); EmpLaborRelationFactory.getLocalInstance(ctx).save(empLaborRelationInfo); /** * 用工关系历史（针对当前最新记录） * * 用工关系状态 T_HR_EmpLaborRelationhis.FLaborRelationStateID EXCEL中填写的目标用工状态 用工关系类型 T_HR_EmpLaborRelationhis.FEmployeeModleID EXCEL中填写的目标用工状态对应的用工关系类型 最新修改人 T_HR_EmpLaborRelationhis.Flastupdateuserid 导入操作的用户ID 最新修改时间 T_HR_EmpLaborRelationhis.FLastupdatetime EXCEL导入生效的时间 */ String sql6="select top 1 fid from T_HR_EmpLaborRelationhis where fpersonid='"+pid.toString()+"' order by flastupdatetime desc "; IRowSet rs6=DbUtil.executeQuery(ctx,sql6.toString()); IObjectPK elrHisId=null; try { if(rs6.next()){ elrHisId=new ObjectUuidPK(BOSUuid.read(rs6.getString(1))); } } catch (UuidException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } EmpLaborRelationHisInfo empLaborRelationHisInfo=EmpLaborRelationHisFactory.getLocalInstance(ctx).getEmpLaborRelationHisInfo(elrHisId); empLaborRelationHisInfo.setLaborRelationState(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType").toString()+"'")); empLaborRelationHisInfo.setEmployeeModle(EmployeeTypeFactory.getLocalInstance(ctx).getEmployeeTypeInfo("where name='"+dataMap.get("empType").toString()+"'").getEmployeeModle()); empLaborRelationHisInfo.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); empLaborRelationHisInfo.setLastUpdateUser(uInfo); EmpLaborRelationHisFactory.getLocalInstance(ctx).save(empLaborRelationHisInfo); /** * 企业任职经历（针对当前最新的主要任职的记录) * * 变动操作 T_HR_EmpOrgRelation. FActionID EXCEL中填写的离职操作 变动类型 T_HR_EmpOrgRelation. FActionTypeID EXCEL中填写的离职类型 变动原因 T_HR_EmpOrgRelation. FActionReasonID EXCEL中填写的离职原因对应的变动原因 备注 T_HR_EmpOrgRelation.FRemark EXCEL中填写的备注内容 最新修改人 T_HR_EmpOrgRelation.Flastupdateuserid 导入操作的用户ID 最新修改时间 T_HR_EmpOrgRelation.FLastupdatetime EXCEL导入生效的时间 */ String sql7="select top 1 fid from T_HR_EmpOrgRelation where fpersonid='"+pid.toString()+"' order by flastupdatetime desc "; IRowSet rs7=DbUtil.executeQuery(ctx,sql7.toString()); IObjectPK eorId=null; try { if(rs7.next()){ eorId=new ObjectUuidPK(BOSUuid.read(rs7.getString(1))); } } catch (UuidException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } EmpPosOrgRelationInfo empPosOrgRelationInfo=EmpPosOrgRelationFactory.getLocalInstance(ctx).getEmpPosOrgRelationInfo(eorId); empPosOrgRelationInfo.setAction(HRBizDefineFactory.getLocalInstance(ctx).getHRBizDefineInfo("where name='"+dataMap.get("hrBizDefine").toString()+"'")); empPosOrgRelationInfo.setActionType(AffairActionReasonFactory.getLocalInstance(ctx).getAffairActionReasonInfo("where name='"+dataMap.get("affairActionReason").toString()+"'")); empPosOrgRelationInfo.setActionReason(VariationReasonFactory.getLocalInstance(ctx).getVariationReasonInfo("where name='"+dataMap.get("variationReason").toString()+"'")); empPosOrgRelationInfo.setDescription(StringUtil.isEmpty((String) dataMap.get("description"))==true?"":dataMap.get("description").toString()); empPosOrgRelationInfo.setLastUpdateUser(uInfo); empPosOrgRelationInfo.setLastUpdateTime(posdIC.effect(dataMap.get("bizDate").toString())); EmpPosOrgRelationFactory.getLocalInstance(ctx).save(empPosOrgRelationInfo); } }}