15、NC导入excel

**

15、NC导入excel

**

如下是我新增一个按钮的代码：

package nc.ui.hfhr.hfhr_emptrainfile.action;

import java.awt.Container;
import java.awt.event.ActionEvent;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import nc.bs.framework.common.NCLocator;
import nc.bs.logging.Logger;
import nc.itf.hfdm.IHfdm_accountsMaintain;
import nc.itf.hfdm.IHfdm_dormcostimpMaintain;
import nc.itf.uap.IUAPQueryBS;
import nc.itf.uap.IVOPersistence;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.BeanProcessor;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.ui.hf.pub.utils.excel.FileFromLocalUtils;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.bill.BillCardPanel;
import nc.ui.pub.tools.BannerDialog;
import nc.ui.pubapp.uif2app.model.BillManageModel;
import nc.ui.pubapp.uif2app.query2.QueryConditionDLGDelegator;
import nc.ui.pubapp.uif2app.view.ShowUpableBillForm;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.ShowStatusBarMsgUtil;
import nc.vo.bd.psn.PsndocVO;
import nc.vo.hfhr.hfhr_emptrainfile.AggHfhr_emptrainfile;
import nc.vo.hfhr.hfhr_emptrainfile.Hfhr_emptrainfile;
import nc.vo.hfhr.hfhr_emptrainfile.Hfhr_emptrainfile_b;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pub.lang.UFTime;
import nc.vo.pub.pf.BillStatusEnum;
import nc.vo.pubapp.AppContext;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class inPutAction extends NCAction {
	
	private static final long serialVersionUID = 1L;
	private ShowUpableBillForm editor;
	private BillManageModel model;
	private QueryConditionDLGDelegator queryAreaDelegator;
	public inPutAction(){
		setCode("ExcelImpPXDA");
		setBtnName("导入数据");
	}
	
	@Override
	public void doAction(ActionEvent e) throws Exception {
		
		IHfdm_accountsMaintain server = NCLocator.getInstance().lookup(IHfdm_accountsMaintain.class);
		final IUAPQueryBS qrysrv = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		final BillCardPanel card = getEditor().getBillCardPanel();
		new Thread(new Runnable() {
			public void run(){
				BannerDialog dialog = new BannerDialog(getEditor());
				dialog.setTitle("导入员工培训资料");
				dialog.setStartText("正在从excel导入信息，请稍等......");
				try {
					dialog.start();
					final List list1=new ArrayList<>();
					boolean flag = onImport(card,list1);
					if(list1.size()>0){
						if(flag){ MessageDialog.showHintDlg(getEditor(), "提示", "导入成功,档案编号为: "+list1.get(0)); }
						String sqlfile = "select * from hfhr_emptrainfile a where a.filenumber = '"+list1.get(0)+"' and dr=0";
						AggHfhr_emptrainfile aggvo = new AggHfhr_emptrainfile();
						Hfhr_emptrainfile headvo = (Hfhr_emptrainfile) qrysrv.executeQuery(sqlfile, new BeanProcessor(Hfhr_emptrainfile.class));
						aggvo.setParentVO(headvo);
						String sqlfile2 = "select * from hfhr_emptrainfile_b a where a.pk_hfhr_emptrainfile = '"+headvo.getPk_hfhr_emptrainfile()+"' and dr=0";
						List<Hfhr_emptrainfile_b> bodyvos = (List<Hfhr_emptrainfile_b>) qrysrv.executeQuery(sqlfile2, new BeanListProcessor(Hfhr_emptrainfile_b.class));
						aggvo.setChildrenVO(bodyvos.toArray(new Hfhr_emptrainfile_b[]{}));
						model.initModel(aggvo);
					}
					
					//AggHfhr_emptrainfileMeta aggvo = new AggHfhr_emptrainfileMeta();
					//IUAPQueryBS server = NCLocator.getInstance().lookup(IUAPQueryBS.class);
					//String sql ="select * from hfhr_emptrainfile where filenumber = '"+list1.get(0)+"'";
					//Hfhr_emptrainfile hfhr_emptrainfile =(Hfhr_emptrainfile) server.executeQuery(sql, new BeanProcessor(Hfhr_emptrainfile.class));
					//aggvo.setAttrnameMetaRelation("Hfhr_emptrainfile",  );
					//IBusinessEntity be = getBillTempletVO().getHeadVO().getBillMetaDataBusinessEntity();
					// DASFacade.newInstanceWithContainedObject(tabvos[0].getBillMetaDataBusinessEntity(), vos[i]);
					//nc.ui.pub.bill.BillListData.setHeaderValueObjectByMetaData  1028
					//nc.ui.pub.bill.BillModel.setBodyObjectByMetaData   3402
					//BillModel nc.ui.pub.bill.BillListData.getHeadBillModel()
					//private BillTempletVO billTempletVO = null;
					//getHeadBillModel().setBodyObjectByMetaData(ncos);
				} catch (Exception e) {
					Logger.error(e.getMessage(), e);
					ShowStatusBarMsgUtil.showErrorMsg("导入员工培训资料失败!原因："+e.getMessage(),"导入员工培训资料失败!原因："+e.getMessage(),getModel().getContext());
				}finally{
					dialog.end();
				}
			}
		}).start();
		
	}
	
	public boolean onImport(BillCardPanel card,List list1) throws Exception {
		String pkuser = AppContext.getInstance().getPkUser();    // 获取当前登录用户
		IVOPersistence inSrv = NCLocator.getInstance().lookup(IVOPersistence.class);
		IUAPQueryBS server = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		IHfdm_dormcostimpMaintain insertsrv = NCLocator.getInstance().lookup(IHfdm_dormcostimpMaintain.class);
		Sheet sheet = getSheetFromExcel(card, "导入员工培训资料", "", "", 0);
		if(null == sheet){
			return false;
		}
		List<String> Mes = getMesFromSheet(sheet);
		for (String string : Mes) {
			if(string==null){
				throw new Exception("请正确填写<培训讲师><培训主题><培训地点><培训日期><开始时间><结束时间>等字段值,不允许为空！");
				
			}
		}
		Hfhr_emptrainfile hfhr_emptrainfile = new Hfhr_emptrainfile();
	    hfhr_emptrainfile.setTrainsqperker(Mes.get(0));
	    hfhr_emptrainfile.setTraintheme(Mes.get(1));
	    hfhr_emptrainfile.setTrainplace(Mes.get(2));
	    hfhr_emptrainfile.setTraindate(new UFDate(Mes.get(3)));
	    hfhr_emptrainfile.setBegintime(new UFTime(Mes.get(4)));
	    hfhr_emptrainfile.setEndtime(new UFTime(Mes.get(5)));
	    hfhr_emptrainfile.setCurhour(getOvertimeCount(new UFTime(Mes.get(4)),new UFTime(Mes.get(5))));
	    hfhr_emptrainfile.setFiledate(new UFDate(new UFDate().toString().substring(0, 10)));
	    hfhr_emptrainfile.setFilenumber(getfilenumber());
	    list1.add(hfhr_emptrainfile.getFilenumber());
	    hfhr_emptrainfile.setVbilldate(new UFDate());
	    hfhr_emptrainfile.setPk_group("0001A110000000000L9C");
	    hfhr_emptrainfile.setPk_org("GLOBLE00000000000000");
	    hfhr_emptrainfile.setCreator(pkuser);
	    hfhr_emptrainfile.setBillmaker(pkuser);
	    hfhr_emptrainfile.setCreationtime(new UFDateTime());
	    hfhr_emptrainfile.setDr(new Integer(0));
	    hfhr_emptrainfile.setApprovestatus(-1);
	  String pk_hfhr_emptrainfile = inSrv.insertVO(hfhr_emptrainfile);
	  if(pk_hfhr_emptrainfile!=null){
		List<Hfhr_emptrainfile_b> list = getBodyVOsFromSheet(pk_hfhr_emptrainfile, sheet, 3, 0, 0, 0);
		inSrv.insertVOList(list);
		//String sql = "select code from hfdm_dormarea where nvl(dr,0) = 0 and pk_hfdm_dormarea = '" + pk_hfdm_dormarea + "'";
		//String code = (String) server.executeQuery(sql, new ColumnProcessor());
		
		//insertsrv.insertBodys(list.toArray(new Hfdm_dormcostimp_b[]{}), pk_hfdm_dormcostimp);
	  }
	 
		//自动刷新设置
		/*new ModelDataRefresher(getModel()).refreshData();
		List<AggHfdm_dormcostimp> retlist = getModel().getData();
		int row = 0;
		for(int i = 0; i < retlist.size(); i++){
			Hfdm_dormcostimp headvo = retlist.get(i).getParentVO();
			if(pk_hfhr_emptrainfile.equals(headvo.getPk_hfdm_dormcostimp())){
				row = i;
				break;
			}
		}
		getModel().setSelectedRow(row);*/
		
		return true;
	 }
	/**
	 * 
	 * 获取两个时间差
	 * 
	 * @param ufTime
	 * @param ufTime2
	 * @return
	 */
	 private UFDouble getOvertimeCount(UFTime ufTime,UFTime ufTime2){
			String sbegintime = ufTime.toString();
			String sendtime = ufTime2.toString();
			DateFormat df = new SimpleDateFormat("HH:mm:ss");   
			try {
				Date d1 = df.parse(sbegintime);
				Date d2 = df.parse(sendtime);
					if(ufTime2.after(ufTime)){ //加班结束时间>加班开始时间；
						long diff = d2.getTime() - d1.getTime() ;   
						double hours = (diff / (1000 * 60 * 60 * 1.00)) / 1.00 ; //将两个时间段相减得到的毫秒数转换为小时
						BigDecimal bd = new BigDecimal(hours);
						bd = bd.setScale(1, BigDecimal.ROUND_HALF_UP);
						String hourcount = bd.toString();
						//向下取整按0.5算
						String hour1 = hourcount.substring(0,hourcount.indexOf("."));
						String hour2 = hourcount.substring(hourcount.indexOf(".") + 1);
						if("0".equals(hour2) || "1".equals(hour2) || "2".equals(hour2) || "3".equals(hour2) || "4".equals(hour2)){
							hour2 = "0";
						}else if("5".equals(hour2) || "6".equals(hour2) || "7".equals(hour2) || "8".equals(hour2) || "9".equals(hour2)){
							hour2 = "5";
						}
						hourcount = hour1+"."+hour2;
						return new UFDouble(hourcount);
					}else{
						long diff = d1.getTime() - d2.getTime() ;   
						double hours = (diff / (1000 * 60 * 60 * 1.00)) / 1.00 ; //将两个时间段相减得到的毫秒数转换为小时
						BigDecimal bd = new BigDecimal(hours);
						bd = bd.setScale(1, BigDecimal.ROUND_HALF_UP);
						String hourcount = bd.toString();
						//向下取整按0.5算
						String hour1 = hourcount.substring(0,hourcount.indexOf("."));
						String hour2 = hourcount.substring(hourcount.indexOf(".") + 1);
						if("0".equals(hour2) || "1".equals(hour2) || "2".equals(hour2) || "3".equals(hour2) || "4".equals(hour2)){
							hour2 = "0";
						}else if("5".equals(hour2) || "6".equals(hour2) || "7".equals(hour2) || "8".equals(hour2) || "9".equals(hour2)){
							hour2 = "5";
						}
						hourcount = hour1 + "." + hour2;
						return new UFDouble(24.0 - Double.parseDouble(hourcount));
					}
				
				
			} catch (ParseException e) {
				Logger.error(e);
			}   
			   
			return new UFDouble(0);
		}

	/*
	 * 获取随机数
	 * 
	 * */
	public static String  getfilenumber(){
		SimpleDateFormat  simpleDateFormat = new SimpleDateFormat("yyyyMMdd");  
        Date date = new Date();  
        String str = simpleDateFormat.format(date);  
        Random random = new Random();  
        int rannum = (int) (random.nextDouble() * (99999 - 10000 + 1)) + 10000;// 获取5位随机数  
        String kk="EPDA"+str+""+rannum;
        return kk;
	}
	/**
	 * 获取年份月份区域
	 * @param sheet
	 * @return
	 */
	public static List<String> getMesFromSheet(Sheet sheet) throws Exception{
		List<String> list = new ArrayList<String>();
		Object speaker = sheet.getRow(1).getCell(0);
		Object theme = sheet.getRow(1).getCell(1);
		Object place = sheet.getRow(1).getCell(2);
		Object data = sheet.getRow(1).getCell(3);
		Object btime = sheet.getRow(1).getCell(4);
		Object etime = sheet.getRow(1).getCell(5);
		
		list.add(null == speaker ? null : speaker.toString());
		list.add(null == theme ? null : theme.toString());
		list.add(null == place ? null : place.toString());
		list.add(null == data ? null : data.toString());
		list.add(null == btime ? null : btime.toString());
		list.add(null == etime ? "" : etime.toString());
		return list;
	}
	/**
	 * 从EXCEL获取数据
	 * @param parent   
	 * @param dialogName
	 * @param pathKey
	 * @param sheetName
	 * @param sheetNum
	 * @param startrow   开始行
	 * @param endrow     倒数第几行
	 * @param startcell  开始列
	 * @param endcell    倒数第几列
	 * @return
	 * @throws Exception
	 */
	public static List<Hfhr_emptrainfile_b> getBodyVOsFromSheet(String pk_hfhr_emptrainfile, Sheet sheet, Integer startrow, Integer endrow, Integer startcell, Integer endcell) throws Exception{
		IVOPersistence srv = NCLocator.getInstance().lookup(IVOPersistence.class);
		IUAPQueryBS server = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		//返回的集合
		List<Hfhr_emptrainfile_b> list = new ArrayList<Hfhr_emptrainfile_b>();
		//人员是否重复
		List<String> codelist = new ArrayList<String>();
		Set<String> codeset = new HashSet<String>();
		//列名的列索引位置
		Map<String, Integer> columnMap = new HashMap<String, Integer>();
		
		if(null == sheet){
			return null;
		}
        int maxRowIx = sheet.getLastRowNum();//获取表格的最后一行
        int temp = sheet.getRow(startrow - 1).getLastCellNum();
        for(int i = 0; i < temp; i++){
        	columnMap.put(sheet.getRow(startrow - 1).getCell(i).toString(), i);
        }
        StringBuffer sb =new StringBuffer();
        for (int rowIx = startrow; rowIx <= maxRowIx - endrow; rowIx++) {  
        	Hfhr_emptrainfile_b Hfhr_emptrainfile_b = new Hfhr_emptrainfile_b();
            Row row = sheet.getRow(rowIx); 
            //null == pk_hfdm_dormdoc ? null : pk_hfdm_dormdoc
            if(row.getCell(columnMap.get("参训人工号"))!=null){
            	String trainercode = row.getCell(columnMap.get("参训人工号")).toString();
                codelist.add(trainercode);
                codeset.add(trainercode);
                String sql = "select * from bd_psndoc where nvl(dr,0) = 0 and code = '" + trainercode + "'";
                PsndocVO  PsndocVO  = (PsndocVO) server.executeQuery(sql, new BeanProcessor(PsndocVO.class));
                if(PsndocVO==null){
                	sb.append(","+(rowIx+1));
                }else{
                	 Hfhr_emptrainfile_b.setPk_trainpsndoc(PsndocVO.getPk_psndoc());
                     Hfhr_emptrainfile_b.setTrainname(PsndocVO.getName());
                     String postsql = "select om_post.postname from hi_psnjob hi_psnjob left join om_post om_post on hi_psnjob.pk_post=om_post.pk_post where hi_psnjob.ismainjob='Y' and hi_psnjob.lastflag='Y'  and hi_psnjob.trnsevent<>5 and hi_psnjob.psntype=0 and hi_psnjob.dr=0 and om_post.dr=0 and hi_psnjob.clerkcode='"+trainercode+"'";
                     String postname = (String)server.executeQuery(postsql.toString(), new ColumnProcessor());
                     Hfhr_emptrainfile_b.setTrainerpost(postname);
                     Hfhr_emptrainfile_b.setDr(0);
                     Hfhr_emptrainfile_b.setPk_hfhr_emptrainfile(pk_hfhr_emptrainfile);
                     if(row.getCell(columnMap.get("考核情况"))!=null){
                    	 Hfhr_emptrainfile_b.setAssessment(row.getCell(columnMap.get("考核情况")).toString());
                     }else{
                    	 Hfhr_emptrainfile_b.setAssessment(null);
                     }
                     if(row.getCell(columnMap.get("备注"))!=null){
                    	 Hfhr_emptrainfile_b.setMeno(row.getCell(columnMap.get("备注")).toString());
                     }else{
                    	 Hfhr_emptrainfile_b.setMeno(null);
                     }
                    
                     list.add(Hfhr_emptrainfile_b);
                }
               
            }else{
            	sb.append(","+(rowIx+1));
            }
           
        }
        if(sb!=null&&sb.length()!=0){
        	String sql = "select * from hfhr_emptrainfile where pk_hfhr_emptrainfile='"+pk_hfhr_emptrainfile+"' and dr=0";
        	Hfhr_emptrainfile  hfhr_emptrainfile  = (Hfhr_emptrainfile) server.executeQuery(sql, new BeanProcessor(Hfhr_emptrainfile.class));
        	srv.deleteVO(hfhr_emptrainfile);
        	throw new Exception("Excel第( "+getname(sb)+" )行人员工号不存在,或该字段单元格格式不是文本,或输入的工号有误！请检查对应行的数据是否正常！");
        }
        if(codelist.size() != codeset.size()){
        	String sql = "select * from hfhr_emptrainfile where pk_hfhr_emptrainfile='"+pk_hfhr_emptrainfile+"' and dr=0";
        	Hfhr_emptrainfile  hfhr_emptrainfile  = (Hfhr_emptrainfile) server.executeQuery(sql, new BeanProcessor(Hfhr_emptrainfile.class));
        	srv.deleteVO(hfhr_emptrainfile);
        	throw new Exception("存在重复的人员！请检查表格数据，删除重复人员数据或错误数据！");
        }
        
        return list;
		
	}
	/**
	 * 获取容器里面的数据
	 * @param sb
	 * @return
	 */
	public static String  getname(StringBuffer sb){
		return sb.toString().substring(1);
		
	}
	@Override
	protected boolean isActionEnable() {
		boolean isEnable = super.isActionEnable();
		if ((isEnable) && (getModel().getSelectedData() != null)) {
			nc.md.data.access.NCObject obj = nc.md.data.access.NCObject.newInstance(getModel().getSelectedData());
			if (obj != null) {
				Integer fstatusflag = nc.bs.pubapp.pf.util.ApproveFlowUtil.getBillStatus(obj);
				if (tryMakeFlow(fstatusflag)) {
					return true;
				}
			}
		}
		return false;
	}
	public static Sheet getSheetFromExcel(Container parent, String dialogName, String pathKey, String sheetName, Integer sheetNum) throws Exception{
		//选择的EXCEL返回的数据
		Sheet sheet = getSheetFromSheetNameOrNum(parent, dialogName, pathKey, sheetName, sheetNum);
		return sheet;
	}
	public static Sheet getSheetFromSheetNameOrNum(Container parent, String dialogName, String pathKey, String sheetName, Integer sheetNum) throws Exception{
		Workbook workbook = getWorkbookFromExcel(parent, dialogName, pathKey);
		if(!"".equals(sheetName)){
			return workbook == null ? null : workbook.getSheet(sheetName);
		}else{
			return workbook == null ? null : workbook.getSheetAt(sheetNum);
		}
	}
	/**
	 * 导入excel文件的方法
	 */
	public static Workbook getWorkbookFromExcel(Container parent, String dialogName, String pathKey) throws Exception{
	     File file = FileFromLocalUtils.getFileByPathCachekey(parent, dialogName, pathKey);
	     if(file != null){
	    	 InputStream input = new FileInputStream(file.getPath());
	    	 Workbook rwb = null;
	    	 if(file.getName().endsWith("xlsx")){//2007
	    		 rwb = new XSSFWorkbook(input);
	    	 }else if(file.getName().endsWith("xls")){//2003
	    		 rwb = new HSSFWorkbook(input);
	    	 }else{
	    		 MessageDialog.showErrorDlg(parent, "提示框", "请选择EXCEL文件导入！");
	    	 }
	    	 return rwb;
	     }
		return null;
	}
	private boolean tryMakeFlow(Integer fstatusflag) {
		return (fstatusflag == null) || (BillStatusEnum.FREE.equalsValue(fstatusflag));
	}

	public ShowUpableBillForm getEditor() {
		return editor;
	}

	public void setEditor(ShowUpableBillForm editor) {
		this.editor = editor;
	}

	public BillManageModel getModel() {
		return model;
	}

	public void setModel(BillManageModel model) {
		this.model = model;
	}

	public QueryConditionDLGDelegator getQueryAreaDelegator() {
		return queryAreaDelegator;
	}

	public void setQueryAreaDelegator(QueryConditionDLGDelegator queryAreaDelegator) {
		this.queryAreaDelegator = queryAreaDelegator;
	}}