Excel表格导入

package com.anolesoft.erp.hr.action;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.json.JSONArray;

import org.json.JSONException;

import org.json.JSONObject;

import com.anolesoft.erp.dao.DataSource;

import com.anolesoft.erp.xyy.util.StringUtil;

import com.exedosoft.plat.ExedoException;

import com.exedosoft.plat.action.DOAbstractAction;

import com.exedosoft.plat.action.DOAction;

import com.exedosoft.plat.bo.BOInstance;

import com.exedosoft.plat.bo.DODataSource;

import com.exedosoft.plat.bo.DOService;

import com.exedosoft.plat.util.DOGlobals;

import com.samc.mdm.bd.action.ApplyForCodeAction;

public class PersonnelInformationExcelOperation extends DOAbstractAction implements DOAction {

private Log log = LogFactory.getLog(PersonnelInformationExcelOperation.class);

DOService service = null;

@Override

public String excute() throws ExedoException {

log.info("=====================人员=开始导入Execl=====================");

BOInstance paras = DOGlobals.getInstance().getSessoinContext().getFormInstance();

// String path = paras.getValue("path");//文件路径

String filename = paras.getValue("file");//文件名

String sNo = paras.getValue("sNo");//sheet表索引

String hNo = paras.getValue("hNo");//标题行索引

String headjson = paras.getValue("headjson");//标题结构JSON

int sN = Integer.parseInt(sNo);

int hN = Integer.parseInt(hNo);

String url=DOGlobals.getInstance().getServletContext().getRequest().getRealPath("/erp/upload/PersonnelInformation");

/**

* 获取数据

*/

ReadExcel re = new ReadExcel();

HSSFWorkbook hssfWorkbook =re.getWorkbook(url+"/"+filename);

List<HSSFSheet> sheets = re.getSheets(hssfWorkbook);

HSSFSheet hssfSheet = re.getSheet(sheets, sN);

String headers[] = re.getHeaders(hssfSheet, hN);

JSONArray HeadJSON = null;

List<BOInstance> heads = new ArrayList<BOInstance>();

try{

HeadJSON = new JSONArray(headjson);

if(headers.length == HeadJSON.length()){

for(int i = 0; i < HeadJSON.length(); i++){

JSONObject json = HeadJSON.getJSONObject(i);

String index = json.getString("index");

String name = json.getString("name");

String colname = json.getString("colname");

String type = json.getString("type");

String notnull = json.getString("notnull");

log.info("======"+name);

if(!name.equals(headers[i])){

log.info("========================================sheet表标题第" +i + "行不符合要求!");

return "sheet表标题第" +i + "行不符合要求";

}

BOInstance head = new BOInstance();

head.putValue("index", index);

head.putValue("name", name);

head.putValue("colname", colname);

head.putValue("type", type);

head.putValue("notnull", notnull);

heads.add(head);

}

}else{

log.info("========================================sheet表标题行数" + headers.length + "不符合用户要求!");

return "sheet表标题行数" + headers.length + "不符合用户要求";

}

}catch(JSONException e){

e.printStackTrace();

log.info("========================================读取文件时发生错误:" + e.getMessage());

return e.getMessage();

}

List<BOInstance> datas = re.getCells(hssfSheet, hN+1, heads);

log.info("========================================共获得" + datas.size() + "行数据");

if(datas.size() == 1){//只有一条数据时,判断这条数据是否读取Excel产生的错误信息

BOInstance data = datas.get(0);

String errormsg = data.getValue("errormsg");

if(errormsg != null && !"".equals(errormsg)){

log.info("========================================读取数据时发生错误:" + errormsg);

return errormsg;

}

}else if(datas.size() > 5000){

log.info("========================================读取数据条数>5000条,建议拆分后导入");

return "读取数据条数>5000条,建议拆分后导入";

}

if(datas.isEmpty()){

return "请检查Excel表格数据是否为空!";

}

/**

* 处理数据

*/

DODataSource ds = DataSource.getDataSource();

Connection conn = ds.getConnection();

log.info("==================进入处理数据===========================");

PreparedStatement pst = null;

PreparedStatement pst2= null;

PreparedStatement pst3= null;

ResultSet rs = null;

PersonnelStatic personnelStatic = new PersonnelStatic();//人员信息枚举

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

String create_user = DOGlobals.getInstance().getSessoinContext().getUser().getValue("employee_code");

String nowDate = sdf.format(new Date());

String ip = DOGlobals.getInstance().getSessoinContext().getIp();

//ip = "192.168.0.107";

StringBuilder sql = new StringBuilder();

StringBuilder sql1= new StringBuilder();

StringBuilder sql2= new StringBuilder();

ApplyForCodeAction afc = new ApplyForCodeAction();

try {

conn.setAutoCommit(false);

// String objuid =UUIDHex.getInstance().generate();

for(int i=0 ; i<datas.size() ; i++){

BOInstance data = datas.get(i);

log.info("==================进入循环===========================");

String NO = StringUtil.isNull(data.getValue("NO"));

String emp_name = StringUtil.isNull(data.getValue("emp_name"));

String sex = personnelStatic.getSex(StringUtil.isNull(data.getValue("sex")));

String nationality = personnelStatic.getNationality(StringUtil.isNull(data.getValue("nationality")));

String marriage_state =  personnelStatic.getMarriageState(StringUtil.isNull(data.getValue("marriage_state")));

String card_id = StringUtil.isNull(data.getValue("card_id"));

String household_nature = personnelStatic.getHouseholdNature(StringUtil.isNull(data.getValue("household_nature")));

String birthday = StringUtil.isNull(data.getValue("birthday"));

String contact_mode = StringUtil.isNull(data.getValue("contact_mode"));

String birth_address = StringUtil.isNull(data.getValue("birth_address"));

String now_address = StringUtil.isNull(data.getValue("now_address"));

String foreign_language = StringUtil.isNull(data.getValue("foreign_language"));

String computer_ability = StringUtil.isNull(data.getValue("computer_ability"));

String high_education = personnelStatic.getHighEducation(StringUtil.isNull(data.getValue("high_education")));

String technical_name = StringUtil.isNull(data.getValue("technical_name"));

String politics = personnelStatic.getPolitics(StringUtil.isNull(data.getValue("politics")));

String join_party_date = StringUtil.isNull(data.getValue("join_party_date"));

String interview_date = StringUtil.isNull(data.getValue("interview_date"));

String probation_period = StringUtil.isNull(data.getValue("probation_period"));

String dept_name = StringUtil.isNull(data.getValue("dept_uid"));

String post_name = StringUtil.isNull(data.getValue("post_uid"));

String headship = StringUtil.isNull(data.getValue("headship"));

String man_state = personnelStatic.getManState(StringUtil.isNull(data.getValue("man_state")));

String hiredate = StringUtil.isNull(data.getValue("hiredate"));

String regular_date = StringUtil.isNull(data.getValue("regular_date"));

String extension_number = StringUtil.isNull(data.getValue("extension_number"));

String homephone = StringUtil.isNull(data.getValue("homephone"));

String mailbox = StringUtil.isNull(data.getValue("mailbox"));

String objuid = StringUtil.isNull(data.getValue("objuid"));

String employee_code = StringUtil.isNull(data.getValue("employee_code"));

String dept_uid = "";

String post_uid = "";

sql1.append(" SELECT dept_UID, objuid ");

sql1.append(" FROM erp_hr_post ");

sql1.append(" WHERE cname = '"+post_name+"' ");

pst3 = conn.prepareStatement(sql1.toString());

rs = pst3.executeQuery();

while(rs.next()){

dept_uid = StringUtil.isNull(rs.getString("dept_UID"));

post_uid = StringUtil.isNull(rs.getString("objuid"));

}

sql1.delete(0, sql1.length());

if(!"".equals(employee_code) && employee_code.length() > 0) {

sql.append(" UPDATE erp_hr_emp ")

.append(" SET ")

.append(" emp_name = '"+emp_name+"',sex = "+StringUtil.isInt(sex)+",nationality = "+StringUtil.isInt(nationality)+",marriage_state = "+StringUtil.isInt(marriage_state)+",card_id = '"+card_id+"',birthday = "+StringUtil.isSqlDate(birthday)+",contact_mode = '"+contact_mode+"',birth_address = '"+birth_address+"', ")

.append(" now_address = '"+now_address+"',high_education = "+StringUtil.isInt(high_education)+",technical_name = '"+technical_name+"',politics = "+StringUtil.isInt(politics)+",join_party_date = "+StringUtil.isSqlDate(join_party_date)+",hiredate = "+StringUtil.isSqlDate(hiredate)+", ")

.append(" headship = '"+headship+"',man_state = "+StringUtil.isInt(man_state)+",modifier_uid = '"+create_user+"',modification_date = NOW(), ")

.append(" household_nature = "+StringUtil.isInt(household_nature)+",foreign_language = '"+foreign_language+"',computer_ability = '"+computer_ability+"',interview_date = "+StringUtil.isSqlDate(interview_date)+",probation_period = '"+probation_period+"',regular_date = "+StringUtil.isSqlDate(regular_date)+", ")

.append(" extension_number = '"+extension_number+"',dept_uid = '"+dept_uid+"',post_uid = '"+post_uid+"',homephone='"+homephone+"',mailbox='"+mailbox+"' ")

.append(" WHERE employee_code = '"+employee_code+"' ");

pst = conn.prepareStatement(sql.toString());

pst.addBatch();

pst.executeBatch();

sql.delete(0, sql.length());

}else {

employee_code = "";

employee_code = orderData(afc, create_user, ip);

sql2.append(" INSERT INTO erp_hr_emp ")

.append(" (objuid,emp_name,sex,nationality,marriage_state,card_id,birthday,contact_mode,birth_address,now_address,high_education, ")

.append(" technical_name,politics,join_party_date,hiredate,employee_code,eid,dept_uid,post_uid,headship,man_state,household_nature, ")

.append(" foreign_language,computer_ability,interview_date,probation_period,regular_date,extension_number,creater_uid,creat_date,delete_flag,homephone,mailbox) ")

.append(" VALUES ")

.append(" (REPLACE(UUID(),'-',''),'"+emp_name+"',"+StringUtil.isInt(sex)+","+StringUtil.isInt(nationality)+","+StringUtil.isInt(marriage_state)+",'"+card_id+"',"+StringUtil.isSqlDate(birthday)+",'"+contact_mode+"','"+birth_address+"','"+now_address+"',"+StringUtil.isInt(high_education)+", ")

.append(" '"+technical_name+"',"+StringUtil.isInt(politics)+","+StringUtil.isSqlDate(join_party_date)+","+StringUtil.isSqlDate(hiredate)+",'"+employee_code+"','"+employee_code+"','"+dept_uid+"','"+post_uid+"','"+headship+"',"+StringUtil.isInt(man_state)+","+StringUtil.isInt(household_nature)+", ")

.append(" '"+foreign_language+"','"+computer_ability+"',"+StringUtil.isSqlDate(interview_date)+",'"+probation_period+"',"+StringUtil.isSqlDate(regular_date)+",'"+extension_number+"','"+create_user+"',NOW(),0,'"+homephone+"','"+mailbox+"') ");

pst2 = conn.prepareStatement(sql2.toString());

pst2.addBatch();

pst2.executeBatch();

sql2.delete(0, sql2.length());

}

}

conn.commit();

return "导入数据成功";

} catch (Exception e) {

e.printStackTrace();

log.info("========================================操作失败!");

try {

if(conn!=null){

conn.rollback();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

return "操作失败,请联系管理员";

} finally {

try {

if(conn!=null){

conn.close();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

try {

if(pst!=null){

pst.close();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

try {

if(pst2!=null){

pst2.close();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

try {

if(pst3!=null){

pst3.close();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

try {

if(rs!=null){

rs.close();

}

} catch (SQLException e1) {

e1.printStackTrace();

}

}

}

public String orderData(ApplyForCodeAction afc, String eid, String ip ) throws Exception{

String Order = "{\"eid\":\""+eid+"\",\"ip\":\""+ip+"\",\"data\":{\"class\":\"综合编码\",\"composition\":\"员工\"}}";

String Order_number = afc.getCodeByJava(Order);

if(Order_number.equals("请按编码规则输入信息")||Order_number.equals("请联系管理员添加编码规则")||Order_number.equals("请输入正确信息")){

System.out.println("====================================="+Order);

throw new ExedoException("人员编码获取失败，请联系管理员");

}

return Order_number;

}

}