excel导入(poi)
来源：互联网 发布：大学生网上兼职软件 编辑：程序博客网 时间：2024/05/22 10:59
package com.ultrapower.pms.probuild.probuilddataimport.manager;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;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 java.util.Set;import java.util.Map.Entry;import javax.servlet.ServletContext;import org.apache.commons.io.FileUtils;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.HSSFColor;import org.apache.struts2.ServletActionContext;import com.ultrapower.eoms.common.constants.PropertiesUtils;import com.ultrapower.eoms.common.core.component.data.DataRow;import com.ultrapower.eoms.common.core.component.data.DataTable;import com.ultrapower.eoms.common.core.component.data.QueryAdapter;import com.ultrapower.eoms.common.core.dao.IDao;import com.ultrapower.eoms.common.portal.model.UserSession;import com.ultrapower.eoms.extend.workcalendar.service.WorkTimeService;import com.ultrapower.eoms.ultrasm.model.DicItem;import com.ultrapower.eoms.ultrasm.service.DicManagerService;import com.ultrapower.pms.probuild.cooperationdep.model.CooperationDepModel;import com.ultrapower.pms.probuild.cooperationdep.service.CooperationDepService;import com.ultrapower.eoms.ultrasm.model.DepInfo;import com.ultrapower.eoms.ultrasm.model.UserInfo;import com.ultrapower.eoms.ultrasm.service.DepManagerService;import com.ultrapower.eoms.ultrasm.service.UserManagerService;import com.ultrapower.pms.common.util.PmsSpecialtyTypeUtil;import com.ultrapower.pms.designmgrm.model.DesignAccreditationModel;import com.ultrapower.pms.planmgrm.model.PlanModel;import com.ultrapower.pms.probuild.circular.model.FileUploadModel;import com.ultrapower.pms.probuild.prjdepref.model.PrjDepRefModel;import com.ultrapower.pms.probuild.probuilddataimport.model.CellValue;import com.ultrapower.pms.probuild.probuilddataimport.model.PlanInputConsts;import com.ultrapower.pms.probuild.probuilddataimport.model.PlanInputConsts.StepInfo;import com.ultrapower.pms.probuild.probuilddataimport.model.PlanInputConsts.TemplateInfo;import com.ultrapower.pms.probuild.probuilddataimport.service.IProBuildDataImportService;import com.ultrapower.pms.projectmgrm.model.ProjectModel;import com.ultrapower.pms.projectmgrm.service.ProjectService;import com.ultrapower.pms.projectmgrm.model.PrjApprovalModel;import com.ultrapower.pms.projectmgrm.model.ProjectDeptModel;import com.ultrapower.randomutil.Random15;import com.ultrapower.randomutil.RandomN;import com.ultrapower.ultrapmp.core.PlanSchedule;import com.ultrapower.ultrapmp.exception.NoSuchServiceException;import com.ultrapower.ultrapmp.exception.NoSuchStepException;import com.ultrapower.ultrapmp.exception.StepLockedException;import com.ultrapower.ultrapmp.exception.StepServiceNotClearException;import com.ultrapower.ultrapmp.facade.PlanDescriptorService;import com.ultrapower.ultrapmp.facade.PlanService;import com.ultrapower.ultrapmp.model.PlanInstance;import com.ultrapower.ultrapmp.model.StepInstance;import com.ultrapower.ultrapmp.model.TemplateInstance;import com.ultrapower.ultrapmp.plan.PlanStorage;public class ProBuildDataImportServiceImpl implements IProBuildDataImportService{ /**注入模块start**/ private CooperationDepService cooperationDepService ; private ProjectService projectService ; private IDao<FileUploadModel> fileUploadDao ; private IDao<ProjectModel> projectDao ; private IDao<ProjectDeptModel> projectDeptDao ; private IDao<PrjApprovalModel> prjApprovalDao ; private IDao<DesignAccreditationModel> designAccreditationDao; private IDao<PlanInstance> planInstanceDao ; private PlanService planService ; private PlanStorage planStorage ; private PlanDescriptorService planDescriptorService ; private DepManagerService depManagerService ; private WorkTimeService workTimeService ; private com.ultrapower.pms.planmgrm.service.PlanService planServiced; private UserManagerService userManagerService ; private DicManagerService dicManagerService ; private IDao<PrjDepRefModel> prjDepRefDao ; private IDao<StepInstance> stepInstanceDao ; private PlanSchedule planSchedule ; private PlanImport planImport ; /**注入模块end**/ private String prjid ; private String prjname ; private String prjnum ; /**数据导入块 start**/ /** * @annotation 需要注意的是errorInfoMap这个map * <br /> * 因为这个导入是导入包含所有的sheet表 * 又需要将错误信息转存至相应的行 * 所以需要这样(Map<String,Map<String,String>>)的一个map * key:错误sheet表的位置 * value:{key:错误行的位置,value:相应的错误信息} * 这样就能满足createErrorInfoFile这个方法中的excel错误信息转存 * <br /> * 不过这个导入存在的一些问题 * 1:由于上级要求这个功能有些地方写的有点死，只不过需要做一点简单的修改 * 2:导入只能导03(*.XLS)版本的 * 3:导入excel必须是第一行和第二行为头信息行的excel， * 如果要到其他样式的excel的话 就需要自己改里面的 excel行读取起始位置了 * * @author update by ChenXiaoDong 2014- 1-13 15:51:25 * @param excelFile 上传文件 * @param userSession 用户对象 * @param excelFileFileName 上传文件名 * @param dataType 上传文件类型 * @return map<String,String> * <br /> * key:信息标识 value:信息类容 * [{errorInfo:导入反馈错误信息}] * */ public Map<String, String> excelFileImprot (File excelFile, UserSession userSession, String excelFileFileName, String dataType) { String newfileName = ( new Date()).getTime()+excelFileFileName.substring(excelFileFileName.lastIndexOf(".")); //获取文件存放位置 String[] pathArr = getFilePath( "tempFile",newfileName,true); String url = pathArr[0]; File file = null; try { file = new File(url); FileUtils. copyFile(excelFile, file); } catch (IOException e) { e.printStackTrace(); } //定义默认工作表个数 int sheetNum = 0; //定义默认总行数信息(工作表总共数据行) int totalRowNumInfo = 0; //定义错误信息存储集合 Map<String, Map<String, String>> errorInfoMap = new HashMap<String, Map<String, String>>(); // 创建信息集合(非错误信息) Map<String, String> infoMap = new HashMap<String, String>(); //存入默认信息成功失败行数工作表数 infoMap.put( "sheetNum", String.valueOf(sheetNum)); //工作表 infoMap.put( "totalRowNumInfo",String.valueOf(totalRowNumInfo)); //行信息 //得到工作表个数 sheetNum = getSheetNum(url); //存入默认工作表个数 infoMap.put( "sheetNum", String.valueOf(sheetNum)); if(!checkRHeaderInfo(infoMap,sheetNum,url,dataType)){//update //文件头不匹配或数据为空 return infoMap; } //定义总行数 StringBuffer sb = new StringBuffer(); //生成错误信息存储 pid RandomN creator = new Random15(); long currentTime = System.currentTimeMillis(); String fileUploadPid = creator.getRandom(currentTime); //数据保存及验证(从第一个工作表开始) //存入总行数初始信息 sb.append( "导入完成,共" +sheetNum+"个工作表,其中"); //定义错误文件反馈 boolean flag = true; int _totalRowNum = 0; int _successRowNum = 0; int _errorRowNum = 0; for(int sn = 0; sn < sheetNum;sn ++){ //从第一个工作表开始 // 定义默认错误行数 int errorRowNum = 0; // 定义默认成功条数 int successRowNum = 0; //定义临时存储数组 0:successRowNum 1:errorRowNum int[] infoArr = {0,0}; //得到上传文件 HSSFSheet sheet = getSheet(url, sn); //得到总行数 totalRowNumInfo = sheet.getLastRowNum()-1; //从第1行算，所以取第二行值 if(totalRowNumInfo < 1){ sb.append( "第"+(sn+1)+"个工作表无数据," ); continue; } sb.append( "第"+(sn+1)+"个工作表共" +totalRowNumInfo+"行,"); //错误行存储集合 Map<String, String> rowErrorInfoMap = new HashMap<String, String>(); //数据验证 flag = dataVerify(totalRowNumInfo, infoArr, sheet,rowErrorInfoMap,infoMap,sn,userSession,fileUploadPid,dataType); //解析行信息 successRowNum = infoArr[0]; errorRowNum = infoArr[1]; errorInfoMap.put(String. valueOf(sn), rowErrorInfoMap); sb.append( "其中成功" +successRowNum+"行,失败"+errorRowNum+ "行,"); _errorRowNum += errorRowNum; _successRowNum += successRowNum; _totalRowNum += totalRowNumInfo; } infoMap.put( "rowInfo", sb.toString().substring(0,sb.lastIndexOf("," ))); //创建错误信息预存地址 String path = ""; if (!flag) { // 存在错误信息( xls),创建excel错误信息存储文件 path = createErrorInfoFile(errorInfoMap,excelFileFileName,infoMap,sheetNum,url,dataType); } int fileSize = (int) excelFile.length()/1024; //错误信息存储到数据库 saveUploadInfo(_errorRowNum, _successRowNum, _totalRowNum, fileSize, userSession,dataType,excelFileFileName,path,fileUploadPid); infoMap.put( "fileUploadPid", fileUploadPid); return infoMap; } /**数据导入块end**/ /**数据验证块start**/ private boolean dataVerify(int totalRowNumInfo, int[] infoArr, HSSFSheet sheet, Map<String, String> rowErrorInfoMap, Map<String, String> infoMap, int sn, UserSession userSession, String fileUploadPid, String dataType) { String sheetName = sheet.getSheetName().trim(); boolean flag = true; if("项目基本信息" .equals(sheetName)){//项目基本信息// flag = _1verify(totalRowNumInfo, sn,userSession, infoArr, sheet, rowErrorInfoMap,fileUploadPid,dataType); } else if ("项目节点状态" .equals(sheetName)){ flag = _2verify(totalRowNumInfo,sn,userSession, infoArr, sheet, rowErrorInfoMap,fileUploadPid,dataType); } else if ("项目关联合作商信息" .equals(sheetName)) {// flag = _3verify(totalRowNumInfo, sn,userSession, infoArr, sheet, rowErrorInfoMap,fileUploadPid,dataType); } else{ infoMap.put( "errorInfo", "工作表错误,请检查或重新下载模板..." ); return false ; } return flag; } private boolean _1verify( int totalRowNumInfo, int sn, UserSession userSession, int [] infoArr, HSSFSheet sheet, Map<String, String> rowErrorInfoMap, String fileUploadPid, String dataType) { boolean flag = true; for (int row = 2; row <= totalRowNumInfo +1; row++) { //从第三行开始验证 if(_1checkCellInfoIsNull(sheet,row,0, rowErrorInfoMap)&&_1checkData(sheet,row,0,rowErrorInfoMap)){ _1saveDataObj(sheet, sn, row, userSession.getLoginName(), userSession.getDepId(), fileUploadPid,dataType); infoArr[0]++; } else{ infoArr[1]++; flag = false; continue; } } return flag; } private boolean _1checkData(HSSFSheet sheet, int row, int cell, Map<String, String> rowErrorInfoMap) { boolean flag = true; // 创建错误信息缓冲区 StringBuffer sb = new StringBuffer(); // 读取一行数据 String[] rowData = getRowData(sheet, row); QueryAdapter qa = new QueryAdapter(); for (int i = cell; i < rowData.length; i++) { String cellValue = getCellValue(sheet.getRow(1).getCell(i),true).trim(); if("项目编号" .equals(cellValue)){ DataTable X = qa.executeQuery( "select PRJ_NAME from table where PRJ_SERIAL_NUM = ? ", rowData[i]); //项目编号 if(X != null && X.length() > 0){ sb.append(cellValue + " 已存在,请检查后重新导入...;" ); flag = false; } } else if ("工建项目经理" .equals(cellValue)){ DataTable Y = qa.executeQuery( "select loginname from table where DEPID in('39800100','39800200','39800400') and fullname= ? ", rowData[i]); if(Y == null || Y.length() < 1){ sb.append(cellValue+ " 不存在,请检查后重新导入...;" ); flag = false; } } else if ("项目专业" .equals(cellValue)){ DataTable D = qa.executeQuery( "select pid from table where SPECIALTY_TYPE_NAME= ? ", rowData[i]); if(D != null && D.length() > 0){ sb.append(cellValue+ " 已存在,请检查后重新导入...;" ); flag = false; } } else if ("项目主管" .equals(cellValue)){ DataTable Z = qa.executeQuery( "select loginname from table where DEPID in('39800100','39800200','39800400') and fullname= ? ", rowData[i]); if(Z == null || Z.length() < 1){ sb.append(cellValue+ " 不存在,请检查后重新导入...;" ); flag = false; } } else if (cellValue.contains("是否")){ if(!"是" .equals(rowData[i]) && !"否".equals(rowData[i])){ sb.append(cellValue+ " 数据填写错误,请检查规范(是/否)...;" ); flag = false; } } else if ("投资项目名称" .equals(cellValue)){ PlanModel pm = this.planServiced .getPrjName(rowData[i]); if(pm!=null ){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("投资项目编号" .equals(cellValue)){ String specialtyTypeValue = PmsSpecialtyTypeUtil.getProperty(rowData[i]); if (specialtyTypeValue==null || "".equals(specialtyTypeValue)) { sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("项目类型" .equals(cellValue)){ DataTable E = qa.executeQuery( "select pid from table where DTCODE='pmsProjectType' and DINAME= ? ", rowData[i]); if(E==null || E.length() < 1){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("计划项目经理(登陆名)" .equals(cellValue)){ DataTable F = qa.executeQuery( "select pid from table where DEPNAME='计划部' and LOGINNAME= ? ", rowData[i]); if(F == null || F.length() < 1){ sb.append(cellValue+ " 不存在,请检查后重新导入...;" ); flag = false; } } else if ("需求管理单位（部门全名）" .equals(cellValue)){//G DepInfo dep = this.depManagerService .getDepByFullname(rowData[i]); if(dep==null ){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("建设单位(部门全名)" .equals(cellValue)){ DepInfo dep = this.depManagerService .getDepByFullname(rowData[i]); if(dep==null ){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("项目优先级" .equals(cellValue)){ DataTable M = qa.executeQuery( "select pid from table where DTCODE='PmsProjectPriority' and DINAME= ? ", rowData[i]); if(M==null || M.length()<1){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("起草人(登陆名)" .equals(cellValue)){ UserInfo user = this.userManagerService .getUserByLoginName(rowData[i]); if(user == null){ sb.append(cellValue+ " 填写错误,请检查后重新导入...;" ); flag = false; } } else if ("关联预算名称" .equals(cellValue)){ DataTable W = qa.executeQuery( "select PID from table where BUDGET_NAME = ? ", rowData[i]); if(W == null || W.length()<1){ sb.append(cellValue+ " 不存在,请检查后重新导入...;" ); flag = false; } } else{} } if(!flag){ rowErrorInfoMap.put(String. valueOf(row), sb.toString()); } return flag; } private boolean _2verify(int totalRowNumInfo, int sn, UserSession userSession, int [] infoArr, HSSFSheet sheet, Map<String, String> rowErrorInfoMap, String fileUploadPid, String dataType) {// 节点验证 boolean flag = true; //预定义节点信息缓冲数组 strArr[0]:TEMPLATEID strArr[1]:PLANID String[] strArr = new String[2]; for (int row = 2; row <= totalRowNumInfo +1; row++) {//第三行 if(_2checkCellInfoIsNull(sheet,row,0, rowErrorInfoMap)&&_2checkData(sheet,row,0,rowErrorInfoMap,(row %3))){ boolean _flag = _2saveDataObj(sheet, sn, row, userSession.getLoginName(), userSession.getDepId(), fileUploadPid,dataType,strArr,rowErrorInfoMap); if(!_flag){ //存在错误行数据 infoArr[1]++; flag = false; } else{ infoArr[0]++; } } else{ infoArr[1]++; flag = false; continue; } } return flag; } private boolean _2checkData(HSSFSheet sheet, int row, int cell, Map<String, String> rowErrorInfoMap, int j) { boolean flag = true; // 创建错误信息缓冲区 StringBuffer sb = new StringBuffer(); // 读取一行数据 QueryAdapter qa = new QueryAdapter(); String[] rowData = getRowData(sheet, row); for (int i = cell; i < rowData.length; i++) { String cellValue = getCellValue(sheet.getRow(1).getCell(i),true).trim(); if("项目编号" .equals(cellValue) && j == 2){ DataTable A = qa.executeQuery( "select PRJ_NAME from table where PRJ_SERIAL_NUM = ? ", rowData[i]); //项目编号 if(A == null || A.length() < 1){ sb.append(cellValue + " 不存在,请检查后重新导入...;" ); flag = false; } } } if(!flag){ rowErrorInfoMap.put(String. valueOf(row), sb.toString()); } return flag; } private boolean _3verify( int totalRowNumInfo,int sn, UserSession userSession, int [] infoArr, HSSFSheet sheet, Map<String, String> rowErrorInfoMap,String fileUploadPid, String dataType) { boolean flag = true; for (int row = 2; row <= totalRowNumInfo +1; row++) { //从第三行开始验证 if(_3checkData(sheet,row,0, rowErrorInfoMap)){ _3saveDataObj(sheet, sn, row, userSession.getLoginName(), userSession.getDepId(), fileUploadPid,dataType); infoArr[0]++; } else{ if(row == 2){ infoArr[1]++; flag = false; break; } else{ infoArr[1]++; flag = false; continue; } } } prjid = null ; prjname = null ; prjnum = null ; return flag; } private boolean _3checkData(HSSFSheet sheet, int row, int cell, Map<String, String> rowErrorInfoMap) { boolean flag = true; // 创建错误信息缓冲区 StringBuffer sb = new StringBuffer(); // 读取一行数据 String[] rowData = getRowData(sheet, row); QueryAdapter qa = new QueryAdapter(); String deptype = null; for (int i = cell; i < rowData.length; i++) { String cellValue = getCellValue(sheet.getRow(1).getCell(i),true).trim(); if("项目编号" .equals(cellValue)) { if(row == 2 && !StringUtils.isEmpty(rowData[i])){//验证第一组的项目编号必填 if(!StringUtils.isEmpty(rowData[i])){ List<ProjectModel> prjlist = this.projectService .getProjectsByPrjNum(rowData[i]); if(prjlist != null && prjlist.size() > 0){ } else{ sb.append(cellValue +"不存在,请检查后重新导入...;" ); flag = false; } } } else if (row == 2 && StringUtils.isEmpty(rowData[i])){ sb.append( "请填写" + cellValue); flag = false; } else{ if(!StringUtils.isEmpty(rowData[i])){ List<ProjectModel> prjlist = this.projectService .getProjectsByPrjNum(rowData[i]); if(prjlist != null && prjlist.size() > 0){ } else{ sb.append(cellValue +"不存在,请检查后重新导入...;" ); flag = false; } } } } if("施工单位" .equals(cellValue)){//施工单位 if(row == 2 && !StringUtils.isEmpty(rowData[i])){//验证施工单位第一行必填 if(!StringUtils.isEmpty(rowData[i])){ CooperationDepModel workdep = this.cooperationDepService .getCooperationDepModelByName(rowData[i]); if(workdep != null){ if(!workdep.getDepType().equals("施工单位" )){ sb.append(rowData[i] +"不是施工单位,请检查后重新导入...;" ); flag = false; } } else{ sb.append(cellValue +"不存在,请检查后重新导入...;" ); flag = false; } } } else if (row == 2 && StringUtils.isEmpty(rowData[i])){ sb.append( "请填写" + cellValue); flag = false; } else{ if(!StringUtils.isEmpty(rowData[i])){ CooperationDepModel workdep = this.cooperationDepService .getCooperationDepModelByName(rowData[i]); if(workdep != null){ if(!workdep.getDepType().equals("施工单位" )){ sb.append(rowData[i] +"不是施工单位,请检查后重新导入...;" ); flag = false; } } else{ sb.append(cellValue +"不存在,请检查后重新导入...;" ); flag = false; } } } } if("其他单位名称" .equals(cellValue)){ CooperationDepModel coop = this.cooperationDepService .getCooperationDepModelByName(rowData[i]); if(coop != null){ deptype = coop.getDepType(); } else{ DataTable da = qa.executeQuery( "select PID from table where depname=? ", rowData[i]); if(da==null || da.length() < 1){ sb.append(cellValue + "不存在,请检查后重新导入...;" ); flag = false; } else{ deptype = "随工单位" ; } } } if("其他单位类型" .equals(cellValue)){ if(!deptype.trim().equals(rowData[i].trim())){ sb.append(cellValue + "不匹配,请检查后重新导入...;" ); flag = false; } } } if(!flag){ rowErrorInfoMap.put(String. valueOf(row), sb.toString()); } return flag; } /** * 验证数据是否为空 * @param sheet * @param row * @return */ private boolean _1checkCellInfoIsNull(HSSFSheet sheet, int row, int cell,Map<String, String> errorInfoMap) { boolean flag = true; // 创建错误信息缓冲区 StringBuffer sb = new StringBuffer(); // 读取一行数据 String[] rowData = getRowData(sheet, row); for (int i = cell; i < rowData.length; i++) { if (StringUtils.isEmpty(rowData[i])) { String cellValue = getCellValue(sheet.getRow(1).getCell(i),true).trim(); if(_1getCondition(cellValue)){ sb.append(cellValue + " 不能为空;" ); flag = false; } } } if(!flag){ errorInfoMap.put(String. valueOf(row), sb.toString()); } return flag; } private boolean _2checkCellInfoIsNull(HSSFSheet sheet, int row, int cell,Map<String, String> errorInfoMap) { boolean flag = true; /* // 创建错误信息缓冲区 StringBuffer sb = new StringBuffer(); // 读取一行数据 String[] rowData = getRowData(sheet, row); for ( int i = cell; i < rowData.length; i++) { if (StringUtils.isEmpty(rowData[i])) { String cellValue = getCellValue(sheet.getRow(1).getCell(i),true).trim(); if(_1getCondition(cellValue)){ sb.append(cellValue + " 不能为空;"); flag = false; } } } if(!flag){ errorInfoMap.put(String.valueOf(row), sb.toString()); } */ return flag; } private boolean _1getCondition(String cellValue) { boolean condition = !"投资项目名称" .equals(cellValue)&& ! "投资项目编号" .equals(cellValue)&& ! "需求管理单位（部门全名）" .equals(cellValue)&& ! "需求单位联系人" .equals(cellValue)&& ! "预计设计批复时间" .equals(cellValue)&& ! "预计物资到货时间" .equals(cellValue)&& ! "项目内容描述" .equals(cellValue)&& ! "立项批复金额" .equals(cellValue)&& ! "立项批复时间" .equals(cellValue)&& ! "立项批复号" .equals(cellValue)&& ! "设计批复金额" .equals(cellValue)&& ! "设计批复时间" .equals(cellValue)&& ! "设计批复号" .equals(cellValue); return condition; } /**数据验证块end**/ /**私有方法块 start**/ private StepInstance getStepInstance(String templateId,String planId,String stepdefId) {// QueryAdapter qa = new QueryAdapter();// String stepId = "";// DataTable _3dt = qa.executeQuery("SELECT STEPID,EXPECTSTARTTIME FROM table WHERE TEMPLATEID = ? and STEPDEFID = ? and PLANID = ?",templateId,stepdefId,planId);// if(_3dt != null && _3dt.length() > 0){// stepId = _3dt.getDataRow(0).getString("STEPID");// }// String sql = "SELECT * FROM table WHERE TEMPLATEID = ? and STEPDEFID = ? and PLANID = ?"; String sql = "from StepInstance where templateid = ? and stepdefid = ? and planid = ?"; StepInstance instance = stepInstanceDao.findUnique(sql, templateId,stepdefId,planId); return instance; } /** * 得到工作表个数 * @param url * @return */ private int getSheetNum(String url) { InputStream in = null; int sheetNum = 0; try { in = new FileInputStream(url); // 获取一个工作薄 HSSFWorkbook hwb = new HSSFWorkbook(in); // 得到工作表个数 sheetNum = hwb.getNumberOfSheets(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return sheetNum; } /** * @param conPath * @param newfileName * @param flag 是否在今天错误信息文件夹下 * @return pathArr[0]是绝对路径 pathArr[1]相对路径 */ private String[] getFilePath(String conPath, String newfileName,boolean flag) { ServletContext sc = ServletActionContext.getRequest().getSession().getServletContext(); //得到配置文件目录(相对WebRoot) String relative = PropertiesUtils. getProperty(conPath); //得到绝对路径 String realPath = sc.getRealPath(relative); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd" ); Date date = new Date(); String YMDTime = sdf.format(date.getTime()); File file = new File(realPath + "/" +YMDTime); if(!file.exists()){ file.mkdirs(); } String[] pathArr = new String[2]; if(flag){ //绝对路径 //realPath + today + newFileName pathArr[0] = realPath + "/"+ YMDTime + "/" +newfileName; pathArr[1] = relative + "/"+ YMDTime + "/"+newfileName; } else{ //相对路径 //realPath + newFileName pathArr[0] = realPath + "/" + newfileName; pathArr[1] = relative + "/" + newfileName; } return pathArr; } private boolean checkRHeaderInfo(Map<String, String> infoMap,int sheetNum,String url,String dataType) { boolean flag = true; String[] pathArr = null;// System.out.println("正在检查文件头信息..."); //得到模板 pathArr = getFilePath( "fileModel", "历史项目信息导入模板.xls" ,false); //得到工作表个数 int _sheetNum = getSheetNum(pathArr[0]); if(_sheetNum != sheetNum){ infoMap.put( "errorInfo", "工作表错误,请检查或重新下载模板..." ); return false ; } //存入工作表个数 infoMap.put( "sheetNum", String.valueOf(_sheetNum)); StringBuffer sb = new StringBuffer(); //定义数据行判断 int row = 0; //验证文件头 for (int i = 0; i < _sheetNum; i++) { //得到模板 HSSFSheet _sheet = getSheet(pathArr[0], i); //得到上传文件 HSSFSheet sheet = getSheet(url, i); String[] rowData = getRowData(sheet, 0); //第一行 String[] _rowData = getRowData(_sheet, 0); //第一行 if(rowData.length == _rowData.length){ for (int j = 0; j < _rowData.length; j++) { if (!rowData[j].equals(_rowData[j])) { sb.append( "'"+sheet.getSheetName()+"'、" ); flag = false; break; } } } else{ infoMap.put( "errorInfo", "工作表错误,请检查或重新下载模板..." ); return false ; } //得到当前工作表的总行数 int totalRow = sheet.getLastRowNum(); if(totalRow < 2){ row ++; continue; } } if(row == sheetNum && StringUtils.isEmpty(sb.toString())){ infoMap.put( "errorInfo", "导入数据为空,请重新导入..." ); return false ; } if(!flag){ String errorInfo = "工作表"+sb.substring(0,sb.lastIndexOf("、" ))+"文件头错误,请检查或重新下载模板..." ; infoMap.put( "errorInfo", errorInfo); return flag; } return flag; } private HSSFSheet getSheet(String url,int sheetNum){ InputStream in = null; HSSFSheet sheet = null; try { in = new FileInputStream(url); // 获取一个工作薄 HSSFWorkbook hwb = new HSSFWorkbook(in); sheet = hwb.getSheetAt(sheetNum); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return sheet; } /** * 得到一行数据 * * @param hs * @param row * @return */ private String[] getRowData(HSSFSheet hs, Integer row) { // 得到一个行对象 HSSFRow rowObj = hs.getRow(row); // 创建临时数据存储数组 String[] dataArr = new String[hs.getRow(0).getLastCellNum()]; for (int i = 0; i < dataArr.length; i++) { dataArr[i] = getCellValue(rowObj.getCell(i), true); } return dataArr; } /** * 得到Excel表中的值 add:2013年5月17日17:43:52 * * @param hssfCell Excel中的每一个格子 * @param flag 日期类型是否返回时间戳 * @return Excel中每一个格子中的值 */ private String getCellValue(HSSFCell hssfCell,boolean flag) { if (hssfCell == null) { return "" ; } if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC ) { if (HSSFDateUtil.isCellDateFormatted(hssfCell)) { double cellValue = hssfCell.getNumericCellValue(); Date javaDate = HSSFDateUtil.getJavaDate(cellValue); if(flag){ return String.valueOf(javaDate.getTime() / 1000L); } else{ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss" ); return sdf.format(javaDate.getTime()); } } // 返回数值类型的值 return String.valueOf(hssfCell.getNumericCellValue()); } else { // 返回字符串类型的值 return String.valueOf(hssfCell.getStringCellValue()); } } private String createErrorInfoFile( Map<String, Map<String, String>> errorInfoMap, String excelFileFileName, Map<String, String> infoMap, int sheetNum, String url, String dataType) { // 创建错误信息文件名称 String postfix = excelFileFileName.substring(excelFileFileName.lastIndexOf("." )); String name = excelFileFileName.substring(0, excelFileFileName.lastIndexOf("." )); String newName = name + "_error_" + (new Date()).getTime() + postfix; //得到错误文件路径 String[] pathArr = getFilePath( "errorFile", newName,true); String path = pathArr[0]; //得到返回路径 String returnPath = pathArr[1]; String[] proPathArr = getFilePath( "fileModel", "历史项目信息导入模板.xls" , false); HSSFWorkbook hwb = null; InputStream in = null; try { in = new FileInputStream(proPathArr[0]); hwb = new HSSFWorkbook(in); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } for (int sn = 0; sn < sheetNum; sn++) { //创建默认行号 int errorNum = 2; //得到模板文件 HSSFSheet _sheet = hwb.getSheetAt(sn); //得到上传文件 HSSFSheet sheet = getSheet(url, sn); int totalRow = sheet.getLastRowNum(); int _totalRow = _sheet.getLastRowNum(); for (int row = 0; row <= totalRow; row++) { if(row < _totalRow-1){ //说明是表头 String[] _rowData = getRowData(_sheet, row); HSSFRow rowObj = _sheet.getRow(row); for (int cell = 0; cell <= _rowData.length; cell++) { if(cell == _rowData.length ){ HSSFCell createCell = rowObj.createCell(cell); createCell.setCellValue( "错误信息" ); createCell.setCellStyle(getCellStyle(hwb)); } } _sheet.addMergedRegion( new CellRangeAddress(0, _totalRow-1, _rowData.length, _rowData.length) ); continue; } Set<Entry<String, Map<String, String>>> mentrySet = errorInfoMap.entrySet(); Iterator<Entry<String, Map<String, String>>> mit = mentrySet.iterator(); while(mit.hasNext()){ Entry<String, Map<String, String>> mnext = mit.next(); int mkey = Integer.parseInt(mnext.getKey());//错误工作表 if(mkey == sn){ Map<String, String> mvalue = mnext.getValue(); Set<Entry<String,String>> entrySet = mvalue.entrySet(); Iterator<Entry<String, String>> it = entrySet.iterator(); while(it.hasNext()){ Entry<String, String> next = it.next(); int key = Integer.parseInt(next.getKey()); if(key == row){ //说明当前行存在错误 String value = next.getValue(); String[] rowData = getRowData(sheet, row); HSSFRow createRow = _sheet.createRow(errorNum); errorNum++; for (int cell = 0; cell <= rowData.length; cell++) { HSSFCell createCell = createRow.createCell(cell); if(cell == rowData.length){ //说明到达最后一列 createCell.setCellValue(value); } else{ createCell.setCellValue(rowData[cell]); } } } } } } } } saveExcel(path, hwb);// System.out.println("文件导入成功..."); return returnPath; } private HSSFCellStyle getCellStyle(HSSFWorkbook hwb) { HSSFCellStyle style = hwb.createCellStyle(); style.setAlignment(HSSFCellStyle. ALIGN_CENTER);// 左右居中 style.setVerticalAlignment(HSSFCellStyle. VERTICAL_CENTER);// 上下居中 style.setFillForegroundColor(HSSFColor.GREY_80_PERCENT. index); style.setWrapText( true); return style; } private String getDivalue(String code, String dtname) { QueryAdapter qa = new QueryAdapter(); DataTable dt = qa.executeQuery( "select DIVALUE from table where DTCODE = ? and diname= ? ", code, dtname); DataRow dataRow = dt.getDataRow(0); String divalue = dataRow.getString( "DIVALUE"); return divalue; } private String getUserid(String loginname) { QueryAdapter qa = new QueryAdapter(); DataTable dt = qa.executeQuery( "select PID from table where LOGINNAME = ? ", loginname); DataRow dataRow = dt.getDataRow(0); String userId = dataRow.getString( "PID"); return userId; } private String getUserIdByFullname(String fullname) { QueryAdapter qa = new QueryAdapter(); DataTable dt = qa.executeQuery( "select PID from table where FULLNAME = ?", fullname); DataRow dataRow = dt.getDataRow(0); String pid = dataRow.getString( "PID"); return pid; } private String getBudgetId(String _23param) { QueryAdapter qa = new QueryAdapter(); DataTable dt = qa.executeQuery( "select PID from table where BUDGET_NAME=? ", _23param); DataRow dataRow = dt.getDataRow(0); String budgetId = dataRow.getString( "PID"); return budgetId; } /**私有方法块end**/ /**方法保存块start**/ private void _1saveDataObj(HSSFSheet sheet, int sn, int row, String loginName, String depId, String fileUploadPid, String dataType) { HSSFRow hssfRow = sheet.getRow(row); CellValue cellValue = getHssfCellValue(hssfRow); ProjectModel pm = new ProjectModel(); RandomN creator = new Random15(); String prjId = creator.getRandom(System.currentTimeMillis()); pm.setPid(prjId); pm.setInvestPrjName(cellValue.getA()); //投资项目名称 pm.setInvestPrjNo(cellValue.getB()); //投资项目编号 pm.setName(cellValue.getC()); //项目名称 pm.setSpecialtyType(cellValue.getD()); //项目专业 pm.setProjectType(getDivalue( "pmsProjectType", cellValue.getE()));//项目类型 pm.setPlanDeptManager(getUserid(cellValue.getF())); //计划部项目经理 DepInfo reqDep = this.depManagerService .getDepByFullname(cellValue.getG()); pm.setRequestDeptId(reqDep.getPid()); //需求单位ID pm.setRequestDeptContact(getUserIdByFullname(cellValue.getH())); //需求单位联系人ID DepInfo managerDep = this.depManagerService .getDepByFullname(cellValue.getI()); pm.setBuildDeptId(managerDep.getPid()); //建设单位ID pm.setBuildScale(cellValue.getJ()); //建设规模 pm.setPlanProjectAmount(Double. valueOf(cellValue.getK()));//计划立项金额 pm.setRealityProjectAmount(Double. valueOf(cellValue.getL()));//实际立项金额 pm.setPriority(Integer. parseInt(getDivalue("PmsProjectPriority", cellValue.getM())));//项目优先级 pm.setIsEmphasis( "是".equals(cellValue.getN())?"1" :"0" );//是否重点项目 是 pm.setPrjStartTime(Long. parseLong(cellValue.getO()));//预计启动时间 pm.setPrjCompleteTime(Long. parseLong(cellValue.getP()));//预计完工时间 pm.setPlanDesignTime(Long. parseLong(cellValue.getQ()));//预计设计批复时间 pm.setPlanSuppliesTime(Long. parseLong(cellValue.getR()));//预计设备材料到货时间 pm.setRequestAvailableTime(Long. parseLong(cellValue.getS()));//要求投入使用时间 pm.setIsExternalProject( "是".equals(cellValue.getT())?"1" :"0" );//是否外部项目 是 pm.setIsAccreditation( "是".equals(cellValue.getU())?1:0);//是否需要技术规范会审 需要 pm.setCreater(cellValue.getV()); //起草人 pm.setCreaterId(getUserid(cellValue.getV())); //起草人Id pm.setBudgetName(cellValue.getW()); //预算名称 pm.setBudgetId(getBudgetId(cellValue.getW())); //预算ID pm.setPrjNum(cellValue.getX()); //项目编号 pm.setBuildManager(getUserIdByFullname(cellValue.getY())); //建设项目经理 pm.setDesc(cellValue.getAA()); //项目内容描述 projectDao.merge(pm);//保存项目基本信息 ProjectDeptModel pdm = new ProjectDeptModel(); pdm.setPrjId(prjId); //项目ID pdm.setPid(prjId); //主键 pdm.setUserId(getUserIdByFullname(cellValue.getZ())); //用户ID pdm.setUserType( "director");//用户类型 主管 projectDeptDao.merge(pdm); PrjApprovalModel pam = new PrjApprovalModel(); pam.setPid(creator.getRandom(System. currentTimeMillis())); pam.setPrjId(prjId); //项目编号 pam.setProjectAmount(Double. valueOf(cellValue.getAB()));//立项金额 pam.setDecisionTime(Long. parseLong(cellValue.getAC()));//实际决策日期 pam.setPmApprovalNum(cellValue.getAD()); //立项批复号 prjApprovalDao.merge(pam);//保存立项批复信息 DesignAccreditationModel dam = new DesignAccreditationModel(); dam.setPid(creator.getRandom(System. currentTimeMillis())); dam.setPrjId(prjId); //项目id dam.setProjectAmount(Double. valueOf(cellValue.getAD()));//立项金额 dam.setProjectAmounts(cellValue.getAE()); //设计批复金额 dam.setApprovalTime(Long. parseLong(cellValue.getAF()));//设计批复时间 dam.setPmDesignNum(cellValue.getAG()); //设计批复号 designAccreditationDao.merge(dam);//保存设计批复信息 } private boolean _2saveDataObj(HSSFSheet sheet, int sn, int row, String loginName, String depId, String fileUploadPid, String dataType, String[] strArr, Map<String, String> rowErrorInfoMap) {//保存区域(项目节点) boolean flags = true; HSSFRow hssfRow = sheet.getRow(row); //得到一个模板 TemplateInfo templateInfo = PlanInputConsts.getTemplatemap().get( "一般土建装修项目" ); //excel节点位置 Map<String, Integer> excelatemap = PlanInputConsts.getExcelatemap(); //获取EXCEL行数据 String[] rowData = getRowData(sheet, row); Random15 creator = new Random15(); CellValue cellValue = getHssfCellValue(hssfRow); int _row = row % 3; QueryAdapter qa = new QueryAdapter(); if(_row == 2){ String cellprjnum=cellValue.getA(); //导入的excel中的项目编号 String cellusername=cellValue.getC(); //导入的excel中的项目负责人 String start_time = rowData[excelatemap.get( "开工时间" )];//启动时间 if(StringUtils.isBlank(start_time)){ rowErrorInfoMap.put(String. valueOf(row), "计划开工时间为空,请检查并重新导入数据...;" ); return false ; } DataTable _1dt = qa.executeQuery( "select t.PID, t.PRJ_NAME from tabl et where t.prj_serial_num = ?",cellprjnum.trim() ); String bizid= "",biznum="" ,bizName=""; if(_1dt != null && _1dt.length() > 0){ bizid=_1dt.getDataRow(0).getString( "pid");//业务实例ID biznum=cellprjnum.trim(); //业务实例ID bizName=_1dt.getDataRow(0).getString( "PRJ_NAME"); } else{// System.out.println("未找到对应的项目信息"); return false ; } DataTable _2dt = qa.executeQuery( "select pid,loginname,fullname from table where fullname = ?",cellusername ); String loginname = loginName; String fullname = ""; if(_2dt != null && _2dt.length() > 0){ loginname = _2dt.getDataRow(0).getString("loginname" ); fullname = _2dt.getDataRow(0).getString("fullname" ); } long startTime = workTimeService.getWorkDayByOffset("commonCalendar" , Long.parseLong(start_time), -templateInfo.getFirstDurtion()); startTime = startTime + 16 * 60 * 60; PlanInstance plan = new PlanInstance(); String planID = creator.getRandom(System.currentTimeMillis()); plan.setPid(planID); //主键 plan.setBizType( "PMS_BUILD");//业务类型 plan.setBizId(bizid); plan.setBizNum(biznum); //业务实例编号 plan.setBizName(bizName); //业务实例名称 plan.setDirectorid(loginname); //负责人id plan.setDirectorname(fullname); //负责人名称 plan.setIsEnd(0); plan.setActivatedate(startTime); plan.setCreatetime(startTime); plan.setCreateuser(loginname); plan.setIsactivated(1); planInstanceDao.save(plan); /** * 创建计划结束 */ /** * 创建模板开始 */ String templateInstanceId = ""; try { templateInstanceId = planService.creatTemplateInstance(templateInfo.getTemplateDefId(), planID, "0", loginname,fullname, "PMS_BUILD",startTime); TemplateInstance templateInstance=planDescriptorService .getTemplate(templateInstanceId); templateInstance.setStarttime(startTime); templateInstance.setStatus(TemplateInstance. STATUS_ACTIVATED); planDescriptorService.updateTemplate(templateInstance); String sql = "update StepInstance set constrainttype=2,steprule=2 where templateid ='" + templateInstanceId + "' and prestepid is not null " ; stepInstanceDao.executeUpdate(sql); String hql = "from StepInstance where templateid ='" + templateInstanceId + "' and prestepid is null "; List<StepInstance> stepInstanceList = stepInstanceDao.find(hql); if (stepInstanceList.size() > 0 && !stepInstanceList.isEmpty()) { for (StepInstance stepInstance : stepInstanceList) { stepInstance.setSteprule(2); planSchedule.arrange(stepInstance); sql = "update StepInstance set actualstarttime = expectstarttime where stepid = ?"; planInstanceDao.executeUpdate(sql, stepInstance.getStepid()); } } } catch (StepServiceNotClearException e) { e.printStackTrace(); } /** * 创建模板结束 */ strArr[0] = templateInstanceId; //TEMPLATEID strArr[1] = planID; //PLANID //节点完成时间变更 Map<String, StepInfo> stepInfoMap = templateInfo.getStepInfo(); //预定义初始测试节点 boolean flag = false; Set<Entry<String,StepInfo>> entrySet = stepInfoMap.entrySet(); Iterator<Entry<String, StepInfo>> it = entrySet.iterator(); while (it.hasNext()) { Entry<String, StepInfo> next = it.next(); String key = next.getKey(); //节点名称 StepInfo value = next.getValue(); //节点相关信息 if(!flag && "开工时间" .equals(key)){ flag = true; continue; } if(flag){ Integer cellNum = excelatemap.get(key); //获取完成时间 Long _1finishTime = 0L; if(cellNum == null || StringUtils.isBlank(rowData[cellNum])|| "无".equals(rowData[cellNum])){ _1finishTime = 0L; } else{ _1finishTime = Long.parseLong(rowData[cellNum]) + 16*60*60; } //执行修改 boolean _flag = updateStepTime(templateInstanceId,_1finishTime, planID, value.getStepDefId(),rowErrorInfoMap,row); if(!_flag){ flags = false; break; } } } } else if (_row == 0){//第二行有效数据 //定义默认实际开始时间 Long actualfinishtime = 0L; boolean flag = false; //获取节点信息map Map<String, StepInfo> stepInfo = templateInfo.getStepInfo(); Set<Entry<String,StepInfo>> entrySet = stepInfo.entrySet(); Iterator<Entry<String, StepInfo>> it = entrySet.iterator(); while(it.hasNext()){ String sql = ""; Entry<String, StepInfo> next = it.next(); String key = next.getKey(); //节点名称 StepInfo value = next.getValue(); //节点信息 //获取stepInstance实例 StepInstance stepInstance = getStepInstance(strArr[0],strArr[1],value.getStepDefId()); String stepId = stepInstance.getStepid(); //获取节点列下标 Integer cellNum = excelatemap.get(key); if(!flag && "开工时间" .equals(key)){ flag = true; } Random15 random15 = new Random15(); //table //planService.buildAndFinishService(serviceDefID, bizId, acceptingModel.getStepId());//结束服务 try { if(flag){ String[] serviceDefId = stepInfo.get(key).getServiceDefId(); if(cellNum == null){//标准模板节点多于实际模板// String bizid = random15.getRandom(System.currentTimeMillis()/1000L);// sql = "update StepInstance set actualstarttime = expectstarttime,actualfinishtime = ?,status = 9 where stepid = ?";// int i = planInstanceDao.executeUpdate(sql, actualfinishtime,stepId);// planService.buildAndFinishService(serviceDefId, bizid, stepId);//结束服务 continue; } else if(StringUtils.isBlank(rowData[cellNum])|| "无".equals(rowData[cellNum])){//节点存在 时间不存在 continue; } else{//节点存在 if(stepInstance.getStatus() == 0){ //未启动 //获取当前节点 的前置环节号 StepInfo _value = next.getValue(); String[] preStepDefId = _value.getPreStepDefId(); if(preStepDefId != null && preStepDefId.length > 0){ //存在前置环节 StringBuffer sb =new StringBuffer("from StepInstance where templateid = ? and planid = ? and status = 1 and stepdefid in("); for (String str : preStepDefId) { sb.append( "'"+str+"'," ); } sql = (sb.toString()).substring(0,(sb.toString()).lastIndexOf("," ))+")"; List<StepInstance> silist = new ArrayList<StepInstance>(); while((silist = stepInstanceDao.find(sql,strArr[0],strArr[1])).size() > 0){ for (StepInstance si : silist) { for(String servicedefid: stepInfo.get(si.getStepname()).getServiceDefId()){ String bizid = random15.getRandom(System.currentTimeMillis()/1000L);// si.setActualfinishtime(si.getActualstarttime());// si.setStatus(9);// stepname planImport.buildAndFinishService(servicedefid, bizid, si.getStepid(), si.getActualstarttime() + 16 * 60 * 60); } } } } Long _actualfinishtime = Long.parseLong(rowData[cellNum]);//实际完成时间 _actualfinishtime = _actualfinishtime + 16 * 60 * 60; for(String servicedefid: serviceDefId){ planImport.buildAndFinishService(servicedefid, (random15.getRandom(System.currentTimeMillis()/1000L)), stepId,_actualfinishtime);//结束服务 } } else{ //当前 //构建时间修改节点、状态值// planService.buildAndFinishService(serviceDefId, (random15.getRandom(System.currentTimeMillis()/1000L)), stepId);//结束服务 Long _actualfinishtime = Long.parseLong(rowData[cellNum]);//实际完成时间 _actualfinishtime = _actualfinishtime + 16 * 60 * 60; for(String servicedefid: serviceDefId){ planImport.buildAndFinishService(servicedefid, (random15.getRandom(System.currentTimeMillis()/1000L)), stepId,_actualfinishtime);//结束服务 } } } } } catch (NoSuchServiceException e) { e.printStackTrace(); } } } else if (_row == 1){ //第三行 无效数据 } else{} return flags; } private void _3saveDataObj(HSSFSheet sheet, int sn, int row, String loginName, String depId, String fileUploadPid, String dataType) { HSSFRow hssfRow = sheet.getRow(row); CellValue cellValue = getHssfCellValue(hssfRow); RandomN creator = new Random15(); boolean falg = true; String pid = creator.getRandom(System. currentTimeMillis()); PrjDepRefModel pdrm = new PrjDepRefModel(); pdrm.setPid(pid); if(!StringUtils.isEmpty(cellValue.getA())){ List<ProjectModel> prjlist = this.projectService .getProjectsByPrjNum(cellValue.getA()); if(prjlist != null && prjlist.size() > 0){ prjid = prjlist.get(0).getPid(); prjname = prjlist.get(0).getName(); prjnum = prjlist.get(0).getPrjNum(); } } pdrm.setPrjId( prjid); pdrm.setPrjName( prjname); pdrm.setPrjSerialNum( prjnum); if(!StringUtils.isEmpty(cellValue.getB())){ //施工单位 CooperationDepModel workdep = this.cooperationDepService .getCooperationDepModelByName(cellValue.getB()); if(workdep != null){ pdrm.setDepId(Long. toString(workdep.getDepid())); pdrm.setDepName(workdep.getDepName()); List<DicItem> depDic = this.dicManagerService .getDicItemByDicType("refdeptype"); if(depDic != null && depDic.size() > 0){ for(DicItem di : depDic){ if(di.getDiname().equals(workdep.getDepType().trim())){ pdrm.setDepType(di.getDivalue()); } } } } PrjDepRefModel pdrm1 = new PrjDepRefModel(); PrjDepRefModel pdrmodel = setdepinfo(cellValue); if(pdrmodel != null){ pdrm1.setDepId(pdrmodel.getDepId()); pdrm1.setDepName(pdrmodel.getDepName()); pdrm1.setDepType(pdrmodel.getDepType()); pdrm1.setPid(creator.getRandom(System. currentTimeMillis())); pdrm1.setPrjId(pdrm.getPrjId()); pdrm1.setPrjName(pdrm.getPrjName()); pdrm1.setPrjSerialNum(pdrm.getPrjSerialNum()); pdrm1.setCreateTime(System. currentTimeMillis()); pdrm1.setStatus( "1"); pdrm1.setRates(0); prjDepRefDao.merge(pdrm1); } } else{ PrjDepRefModel pdrmodel = setdepinfo(cellValue); if(pdrmodel != null){ pdrm.setDepId(pdrmodel.getDepId()); pdrm.setDepName(pdrmodel.getDepName()); pdrm.setDepType(pdrmodel.getDepType()); } else{ falg = false; } } pdrm.setStatus( "1"); pdrm.setRates(0); pdrm.setCreateTime(System. currentTimeMillis()); if(falg){ prjDepRefDao.merge(pdrm); } } /** * 其他单位和类型的数据填充 * @param cellValue * @return */ private PrjDepRefModel setdepinfo(CellValue cellValue){// HSSFRow hssfRow = sheet.getRow(row);// CellValue cellValue = getHssfCellValue(hssfRow); PrjDepRefModel pdrm = new PrjDepRefModel(); CooperationDepModel workdep = this.cooperationDepService .getCooperationDepModelByName(cellValue.getC()); if(workdep != null){//监理或设计单位 pdrm.setDepId(Long. toString(workdep.getDepid())); pdrm.setDepName(workdep.getDepName()); List<DicItem> depDic = this.dicManagerService .getDicItemByDicType("refdeptype"); if(depDic != null && depDic.size() > 0){ for(DicItem di : depDic){ if(di.getDiname().equals(workdep.getDepType().trim())){ pdrm.setDepType(di.getDivalue()); } } } } else{//随工单位 QueryAdapter qa = new QueryAdapter(); DataTable dt = qa.executeQuery( "select PID, DEPNAME from table where DEPNAME = ? ", cellValue.getC()); if(dt != null && dt.length() > 0){ DataRow datarow = dt.getDataRow(0); pdrm.setDepId(datarow.getString( "PID")); pdrm.setDepName(datarow.getString( "depName")); pdrm.setDepType( "fellowdep"); } } return pdrm; } /** * 修改时间 * @param templateInstanceId 实例id * @param finishTime excel读取完成时间 * @param planID planId * @param loginname loginName * @param fullname fullName * @param stepDefId 节点id * @param rowErrorInfoMap * @param row */ private boolean updateStepTime(String templateInstanceId, long finishTime, String planID, String stepDefId, Map<String, String> rowErrorInfoMap, int row) { boolean flag = true; if(StringUtils.isBlank(stepDefId) || StringUtils.isBlank(planID)){ return true ; } QueryAdapter qa = new QueryAdapter(); try { //获取开始时间 DataTable _3dt = qa.executeQuery( "SELECT STEPID,EXPECTSTARTTIME FROM table WHERE TEMPLATEID = ? and STEPDEFID = ? and PLANID = ?", templateInstanceId,stepDefId,planID); long expectStartTime = 1L; String stepId = ""; if(_3dt != null && _3dt.length() > 0){ expectStartTime = _3dt.getDataRow(0).getLong("EXPECTSTARTTIME" ); stepId = _3dt.getDataRow(0).getString( "STEPID"); } if(finishTime == 0){//excel节点不存在 finishTime = expectStartTime + 24 * 60 *60; } StepInstance stepObj = planStorage.getStepInstanceByID(stepId); int wordays = workTimeService.getWorkDays("commonCalendar" ,expectStartTime,finishTime);//工期 stepObj.setDuration((wordays-1) * Integer.valueOf(String.valueOf(8))); this.planDescriptorService .updateStep(stepObj); if(wordays < 0){ String sql = "update StepInstance set expectfinishtime = ? where stepid = ?"; stepInstanceDao.executeUpdate(sql, finishTime,stepId); } } catch (StepServiceNotClearException e) { e.printStackTrace(); } catch (StepLockedException e) { e.printStackTrace(); } catch (NoSuchStepException e) { e.printStackTrace(); } return flag; } private CellValue getHssfCellValue(HSSFRow hssfRow) { CellValue cv = new CellValue(); String A = getCellValue(hssfRow.getCell(0), true);//投资项目名称 String B = getCellValue(hssfRow.getCell(1), true);//投资项目编号 String C = getCellValue(hssfRow.getCell(2), true);//立项项目名称 String D = getCellValue(hssfRow.getCell(3), true);//项目专业 String E = getCellValue(hssfRow.getCell(4), true);//项目类型 String F = getCellValue(hssfRow.getCell(5), true);//计划项目经理(登陆名) String G = getCellValue(hssfRow.getCell(6), true);//需求管理单位（部门全名） String H = getCellValue(hssfRow.getCell(7), true);//需求单位联系人 String I = getCellValue(hssfRow.getCell(8), true);//建设单位(部门全名) String J = getCellValue(hssfRow.getCell(9), true);//建设规模 String K = getCellValue(hssfRow.getCell(10), true);//项目预估金额 String L = getCellValue(hssfRow.getCell(11), true);//实际立项金额 String M = getCellValue(hssfRow.getCell(12), true);//项目优先级 String N = getCellValue(hssfRow.getCell(13), true);//是否重点项目 String O = getCellValue(hssfRow.getCell(14), true);//预计启动时间 String P = getCellValue(hssfRow.getCell(15), true);//预计完工时间 String Q = getCellValue(hssfRow.getCell(16), true);//预计设计批复时间 String R = getCellValue(hssfRow.getCell(17), true);//预计物资到货时间 String S = getCellValue(hssfRow.getCell(18), true);//需求投入使用时间 String T = getCellValue(hssfRow.getCell(19), true);//是否外部项目 String U = getCellValue(hssfRow.getCell(20), true);//是否需要技术会审 String V = getCellValue(hssfRow.getCell(21), true);//起草人(登陆名) String W = getCellValue(hssfRow.getCell(22), true);//关联预算名称 String X = getCellValue(hssfRow.getCell(23), true);//项目编号 String Y = getCellValue(hssfRow.getCell(24), true);//工建项目经理 String Z = getCellValue(hssfRow.getCell(25), true);//项目主管 String AA = getCellValue(hssfRow.getCell(26), true);//项目内容描述 String AB = getCellValue(hssfRow.getCell(27), true);//立项批复金额 String AC = getCellValue(hssfRow.getCell(28), true);//立项批复时间 String AD = getCellValue(hssfRow.getCell(29), true);//立项批复号 String AE = getCellValue(hssfRow.getCell(30), true);//设计批复金额 String AF = getCellValue(hssfRow.getCell(31), true);//设计批复时间 String AG = getCellValue(hssfRow.getCell(32), true);//设计批复号 cv.setA(A);cv.setB(B);cv.setC(C); cv.setD(D);cv.setE(E);cv.setF(F); cv.setG(G);cv.setH(H);cv.setI(I); cv.setJ(J);cv.setK(K);cv.setL(L); cv.setM(M);cv.setN(N);cv.setO(O); cv.setP(P);cv.setQ(Q);cv.setR(R); cv.setS(S);cv.setT(T);cv.setU(U); cv.setV(V);cv.setW(W);cv.setX(X); cv.setY(Y);cv.setZ(Z);cv.setAA(AA); cv.setAB(AB);cv.setAC(AC);cv.setAD(AD); cv.setAE(AE);cv.setAF(AF);cv.setAG(AG); return cv; } private void saveExcel(String path, HSSFWorkbook hwb) { FileOutputStream out = null; try { out = new FileOutputStream(path); hwb.write(out); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { if (out != null) out.close(); } catch (IOException e) { e.printStackTrace(); } } } private void saveUploadInfo(int errorRowNum, int successRowNum, int totalRowNum, int fileSize, UserSession userSession, String myotype, String excelFileFileName, String path, String fileUploadPid) {// System.out.println("转存错误信息..."); FileUploadModel fum = new FileUploadModel(); fum.setFileType(myotype); fum.setFileSize(String. valueOf(fileSize)); fum.setFileName(excelFileFileName); fum.setDataSum(Double. parseDouble(String.valueOf (totalRowNum))); fum.setDataSuccess(Double. parseDouble(String.valueOf (successRowNum))); fum.setFilePath(path); fum.setPid(fileUploadPid); //主键 fum.setDataError(Double. parseDouble(String.valueOf (errorRowNum))); fum.setStoreStatus(1); long currentTime = System.currentTimeMillis(); fum.setCreateTime(currentTime/1000); fum.setLastModifyTime(fum.getCreateTime()); fum.setCreator(userSession.getLoginName()); fum.setCreateDept(userSession.getDepId()); fum.setLastModifier(userSession.getLoginName()); fum.setLastModifyDept(userSession.getDepId()); fum.setOperator(userSession.getLoginName()); fum.setOperateDept(userSession.getDepId()); // 新增 fileUploadDao.save(fum); } /**方法保存块 end**/ public CooperationDepService getCooperationDepService() { return cooperationDepService ; } public void setCooperationDepService(CooperationDepService cooperationDepService) { this.cooperationDepService = cooperationDepService; } public ProjectService getProjectService() { return projectService ; } public void setProjectService(ProjectService projectService) { this.projectService = projectService; } /**注入模块start**/ public void setFileUploadDao(IDao<FileUploadModel> fileUploadDao) { this.fileUploadDao = fileUploadDao; } public void setProjectDao(IDao<ProjectModel> projectDao) { this.projectDao = projectDao; } public void setProjectDeptDao(IDao<ProjectDeptModel> projectDeptDao) { this.projectDeptDao = projectDeptDao; } public void setPrjApprovalDao(IDao<PrjApprovalModel> prjApprovalDao) { this.prjApprovalDao = prjApprovalDao; } public void setDesignAccreditationDao( IDao<DesignAccreditationModel> designAccreditationDao) { this.designAccreditationDao = designAccreditationDao; } public void setPlanInstanceDao(IDao<PlanInstance> planInstanceDao) { this.planInstanceDao = planInstanceDao; } public void setDepManagerService(DepManagerService depManagerService) { this.depManagerService = depManagerService; } public void setWorkTimeService(WorkTimeService workTimeService) { this.workTimeService = workTimeService; } public void setPlanStorage(PlanStorage planStorage) { this.planStorage = planStorage; } public void setPlanService(PlanService planService) { this.planService = planService; } public void setPlanDescriptorService( PlanDescriptorService planDescriptorService) { this.planDescriptorService = planDescriptorService; } public void setPlanServiced( com.ultrapower.pms.planmgrm.service.PlanService planServiced) { this.planServiced = planServiced; } public void setUserManagerService(UserManagerService userManagerService) { this.userManagerService = userManagerService; } public void setDicManagerService(DicManagerService dicManagerService) { this.dicManagerService = dicManagerService; } public void setPrjDepRefDao(IDao<PrjDepRefModel> prjDepRefDao) { this.prjDepRefDao = prjDepRefDao; } public void setStepInstanceDao(IDao<StepInstance> stepInstanceDao) { this.stepInstanceDao = stepInstanceDao; } public void setPlanSchedule(PlanSchedule planSchedule) { this.planSchedule = planSchedule; } public void setPlanImport(PlanImport planImport) { this.planImport = planImport; } /**注入模块end**/}导入需要注意的一些问题已经在里面被注释，有其他问题或者更好的建议多谢提出来
(转载请标明出处)
0 0
- poi 导入excel源码
- POI导入excel出错
- 使用POI 导入excel
- POI导入Excel
- excel导入(poi)
- poi导入导出excel
- poi excel导入导出
- poi 导入 Excel
- POI实现excel导入
- java poi 导入excel
- 使用poi 导入 excel
- poi导入excel
- POI数据导入Excel
- poi之excel导入
- POI方法导入Excel
- POI导入／导出 EXCEL
- POI Excel导入导出
- Java poi+excel导入
- ShellExecuteEx函数
- ASP.NET获取两个日期之间的天数
- Impact of cpu cache lines验证
- UIViewContent
- PCA-SIFT:一个更鲜明地局部图像描述符
- excel导入(poi)
- Oracle 查询语句 all any some 含义和区别
- C++ 写开机启动项的注册表
- java 正则表达式 详解
- 数据结构之链表-多项式相加
- NO2.Java学习笔记【关键字、类名、标识符、注释、变量与常量、数据类型、进制、运算符】
- Android input处理机制（四）线程安全
- TCP/IP中的 缓冲区和窗口
- 其他监听器
