java 实现excel导入

/**
 * output package name
 */
package com.kingdee.eas.guiliumuye.duckbreeding.client;

import java.awt.Component;
import java.awt.Frame;
import java.awt.event.ActionEvent;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

import javax.swing.JFileChooser;
import javax.swing.JLabel;
import javax.swing.SwingUtilities;
import javax.swing.filechooser.FileNameExtensionFilter;

import org.apache.log4j.Logger;
import com.kingdee.bos.ctrl.kdf.kds.KDSBook;
import com.kingdee.bos.ctrl.kdf.kds.KDSSheet;
import com.kingdee.bos.ctrl.kdf.read.POIXlsReader;
import com.kingdee.bos.ctrl.kdf.read.POIXlsxReader;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.bos.dao.query.SQLExecutorFactory;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.bos.ui.face.CoreUIObject;
import com.kingdee.eas.base.permission.client.longtime.ILongTimeTask;
import com.kingdee.eas.base.permission.client.longtime.LongTimeDialog;
import com.kingdee.eas.basedata.master.cssp.CustomerFactory;
import com.kingdee.eas.basedata.master.cssp.CustomerInfo;
import com.kingdee.eas.basedata.master.cssp.SupplierFactory;
import com.kingdee.eas.basedata.master.cssp.SupplierInfo;
import com.kingdee.eas.common.client.SysContext;
import com.kingdee.eas.guiliumuye.BillStatus;
import com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitInfo;
import com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitCollection;
import com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitE1Info;
import com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitFactory;
import com.kingdee.eas.guiliumuye.duckbreeding.IFreightUnit;
import com.kingdee.eas.tools.datatask.core.TaskExternalException;
import com.kingdee.eas.util.SysUtil;
import com.kingdee.eas.util.client.MsgBox;
import com.kingdee.jdbc.rowset.IRowSet;


/**
 * output class name
 */
public class FreightUnitListUI extends AbstractFreightUnitListUI
{
    private static final Logger logger = CoreUIObject.getLogger(FreightUnitListUI.class);
    
    /**
     * output class constructor
     */
    public FreightUnitListUI() throws Exception
    {
        super();
    }

    
    @Override
	public void actionMyImport_actionPerformed(ActionEvent e) throws Exception {

		// 创建文件选择器对象
		JFileChooser file = new JFileChooser();
		// 创建文件名过滤器对象，只显示xls,xlsx格式文件
		FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel文件",
				"xls", "xlsx");
		// 从可用文件过滤器的列表中移除 AcceptAll文件过滤器
		file.setAcceptAllFileFilterUsed(false);
		// 将filter设置为文件过滤器
		file.setFileFilter(filter);
		// 弹出Open File对话框如果选择的不是确认则直接终止
		if (file.showOpenDialog(this) != JFileChooser.APPROVE_OPTION)
			return;
		// 获得用户选择的文件的路径
		final String fileName = file.getSelectedFile().getPath();
		// 长时间对话框对象
		LongTimeDialog dialog = new LongTimeDialog((Frame) SwingUtilities
				.getWindowAncestor(this));
		// 为长时间对话框对象设置长时间任务,直接创建ILongTimeTask的局部内部类
		dialog.setLongTimeTask(new ILongTimeTask() {

			//@Override
			public Object exec() throws Exception {
				try {
					// 创建KDSBook的引用
					KDSBook book = null;
					// 根据FreightUnitUI的对象获得文件名
					// String fileName = FreightUnitListUI.this.fileName;
					// 如果选择的这个文件的后缀是xls,则利用POIXlsReader解析这个文件
					if (fileName.toLowerCase().endsWith("xls"))
						book = POIXlsReader.parse(fileName);
					else if (fileName.toLowerCase().endsWith("xlsx")) // 后缀为xlsx同上
						book = POIXlsxReader.parse(fileName);
					else { // 不符合EXCEL文件格式显示提示
						return "不支持的文件格式。";
					}
					// 如果book没有被赋上解析文件后产生的对象
					if (book == null) {
						return "不支持的文件格式。";
					}

					// 清除目前的记录(防止再次读取文件时叠加)
					// kdtCash.removeRows();

					//========================导入start===========================
					// 利用解析后的book对象获得sheet对象(这里为0则是第一个sheet)
					KDSSheet sheet = book.getSheet(new Integer(0));
					// 创建FreightUnitCollection对象(根据实体自动生成)
					FreightUnitCollection freightUnitCollection = new FreightUnitCollection();
					// 循环工作表取出数据
					// 创建FreightUnitInfo对象
					FreightUnitInfo freightUnitinfo =new FreightUnitInfo();  
					for (int i = 3; i < sheet.getRowCount(); i++) {
					
						FreightUnitE1Info freightUnitE1Info = new FreightUnitE1Info();
						
						// 单据编码
						String number = sheet.getCell(i, 0, true).getText();
						if (number == null || "".equals(number.trim())) {
							throw new TaskExternalException("编码不能为空！");
						}
						FilterInfo filter = new FilterInfo();
						IFreightUnit isum = null;
					 
							isum = FreightUnitFactory.getRemoteInstance();
							filter.getFilterItems().add(new FilterItemInfo("number",number
				,CompareType.EQUALS));
							if (isum.exists(filter)){
								throw new TaskExternalException("编码不能重复！"+number);
							}else{isum.delete(filter);}
						 

						
					    //判断单据编码是否和前一行excel相同
						if(i>3){
							String number0 = sheet.getCell(i-1, 0, true).getText();
							if(!number0.equals(number)){
								freightUnitinfo = new FreightUnitInfo();  
							}
						}
					 
						// 分录_养殖户编码
						String customer = sheet.getCell(i, 1, true).getText();
						//分录_供应商编码
						String supplayNumber = sheet.getCell(i, 2, true).getText();
						//分录_运费单价
						String price = sheet.getCell(i, 3, true).getText();
						//分录_备注
						String remark= sheet.getCell(i, 4, true).getText();
						
						// 当前工作表中单据编码空则判定为已经没有值了,结束从工作表读取数据
						if ("".equals(number)&&("".equals(customer))&&("".equals(supplayNumber))&&("".equals(price))) {
							break;
						}

						// 养殖户编码
					    customer = sheet.getCell(i, 1, true).getText();
						if (customer == null || customer.length() < 1) {
							return "导入失败,运费单价表第" + (i + 1) + "行养殖户编码不得为空";
						}
						// 供应商编码
						supplayNumber = sheet.getCell(i, 2, true).getText();
						if (supplayNumber == null || supplayNumber.length() < 1) {
							return "导入失败,运费单价表第" + (i + 1) + "行供应商编码不得为空";
						}
						customer = customer.replaceAll(" ", ""); // 去除空格
						supplayNumber = supplayNumber.replaceAll(" ", "");  
						
						String CUSTOMERID = null;
						//这里不捕捉异常，rowset0没有值就会被导入的try捕捉异常报错
						try {
							String sql0="  /*dialect*/ select   FCUSTOMERID   CUSTOMERID  from T_BD_CustomerSaleInfo  where  FSALEORGID='"+SysContext.getSysContext().getCurrentSaleUnit().getId()+"' and FCUSTOMERID=(  select  fid  from T_BD_Customer where  FNUMBER='"+customer+"')   ";
							System.out.println(sql0);
							IRowSet rowset0 = SQLExecutorFactory.getRemoteInstance(sql0).executeSQL();
							 while (rowset0.next()){
								 CUSTOMERID = rowset0.getString("CUSTOMERID");
							}
						} catch (Exception e) {
							e.printStackTrace();
						} 
						if(null==CUSTOMERID||"".equals(CUSTOMERID)){
							return "导入失败,运费单价表第" + (i + 1) + "行当前公司下养户查询不到!";
						}
						 
						String sql="   select FSupplierID   from T_BD_SupplierCompanyInfo  where  FComOrgID='"+SysContext.getSysContext().getCurrentSaleUnit().getId()+"' and FSupplierID=(  select  fid  from T_BD_Supplier where  FNUMBER='"+supplayNumber+"') ";
						IRowSet rowset = null;
						try {
							rowset = SQLExecutorFactory.getRemoteInstance(sql.toString()).executeSQL();
						} catch (Exception e) {
							e.printStackTrace();
						}
						 if (rowset.next()){
							
						}else{
							return "导入失败,运费单价表第" + (i + 1) + "行当前公司下供应商查询不到!";
						}
						
 
						freightUnitinfo.setAPPCompany(SysContext.getSysContext().getCurrentFIUnit());
						freightUnitinfo.setCU(SysContext.getSysContext().getCurrentCtrlUnit());
						freightUnitinfo.setCreator(SysContext.getSysContext().getCurrentUserInfo());
						freightUnitinfo.setBizDate(new Date());//业务日期
						freightUnitinfo.setNumber(number);
						freightUnitinfo.setBillStatus(BillStatus.no);
 						CustomerInfo customerInfo = CustomerFactory.getRemoteInstance().getCustomerInfo(  "select *  where Number = '"
						+ customer + "'" );
 						freightUnitE1Info.setRemarks(remark);
						freightUnitE1Info.setCustomer(customerInfo);
						freightUnitE1Info.setCustomerName(customerInfo.getName());
						SupplierInfo supplierInfo = SupplierFactory.getRemoteInstance().getSupplierInfo("select *  where Number = '"
								+ supplayNumber + "'"  );
						freightUnitE1Info.setSupplyNumber(supplierInfo);	
						freightUnitE1Info.setSupplyName(supplierInfo.getName());
						freightUnitE1Info.setPrice(new BigDecimal(price));
						
						freightUnitinfo.getE1().add(freightUnitE1Info);
						// 导入对应的容器对象中
						if(!freightUnitCollection.contains(freightUnitinfo)){
							 freightUnitCollection.add(freightUnitinfo);
						}
						
						  
					}
				 
					// ========================导入end============================
					// ========================整体插入数据库start==========================
					// 将查询到的数据存入map中
					HashMap<Integer, FreightUnitInfo> map = new HashMap<Integer, FreightUnitInfo>();
					for (int i = 0; i < freightUnitCollection.size(); i++) {
						map.put(i, freightUnitCollection.get(i));
					}
					// 根据map的key进行排序
					Integer[] items = new Integer[map.size()];
					Set<Integer> keyset = map.keySet();
					keyset.toArray(items);
					Arrays.sort(items);
					// 循环所有数据回显到UI中
					for (Integer item : items) {
						FreightUnitInfo FreightUnitInfo = map.get(item);
						FreightUnitFactory.getRemoteInstance().save(FreightUnitInfo) ;
					}
					// ========================整体插入数据库end==========================
					  refreshList();
					return "导入成功。";
				} catch (Exception ex) {
					ex.printStackTrace();
					String message = ex.getMessage();
					return message == null ? "导入失败，请查看日志。" : message;
				}
			}

		private void FreightUnitE1Info() {
				// TODO Auto-generated method stub
				
			}

		//	@Override
			public void afterExec(Object result) throws Exception {
				if (result != null) {
					com.kingdee.eas.util.client.MsgBox.showInfo(result
							.toString());
				}
			
			}
		});

	   Component[] components = dialog.getContentPane().getComponents();
		for (Component cp : components) {
			if (cp instanceof JLabel) {
				((JLabel) cp).setText("执行中，请稍后.......");
			}
		}
		dialog.show();
	}







	/**
     * output storeFields method
     */
    public void storeFields()
    {
        super.storeFields();
    }

    /**
     * output tblMain_tableClicked method
     */
    protected void tblMain_tableClicked(com.kingdee.bos.ctrl.kdf.table.event.KDTMouseEvent e) throws Exception
    {
        super.tblMain_tableClicked(e);
    }

    /**
     * output tblMain_tableSelectChanged method
     */
    protected void tblMain_tableSelectChanged(com.kingdee.bos.ctrl.kdf.table.event.KDTSelectEvent e) throws Exception
    {
        super.tblMain_tableSelectChanged(e);
    }

    /**
     * output menuItemImportData_actionPerformed method
     */
    protected void menuItemImportData_actionPerformed(java.awt.event.ActionEvent e) throws Exception
    {
        super.menuItemImportData_actionPerformed(e);
    }

    /**
     * output menuItemPCVoucher_actionPerformed method
     */
    protected void menuItemPCVoucher_actionPerformed(java.awt.event.ActionEvent e) throws Exception
    {
            }

    /**
     * output actionPageSetup_actionPerformed
     */
    public void actionPageSetup_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPageSetup_actionPerformed(e);
    }

    /**
     * output actionExitCurrent_actionPerformed
     */
    public void actionExitCurrent_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExitCurrent_actionPerformed(e);
    }

    /**
     * output actionHelp_actionPerformed
     */
    public void actionHelp_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHelp_actionPerformed(e);
    }

    /**
     * output actionAbout_actionPerformed
     */
    public void actionAbout_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAbout_actionPerformed(e);
    }

    /**
     * output actionOnLoad_actionPerformed
     */
    public void actionOnLoad_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionOnLoad_actionPerformed(e);
    }

    /**
     * output actionSendMessage_actionPerformed
     */
    public void actionSendMessage_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionSendMessage_actionPerformed(e);
    }

    /**
     * output actionCalculator_actionPerformed
     */
    public void actionCalculator_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCalculator_actionPerformed(e);
    }

    /**
     * output actionExport_actionPerformed
     */
    public void actionExport_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExport_actionPerformed(e);
    }

    /**
     * output actionExportSelected_actionPerformed
     */
    public void actionExportSelected_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSelected_actionPerformed(e);
    }

    /**
     * output actionRegProduct_actionPerformed
     */
    public void actionRegProduct_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionRegProduct_actionPerformed(e);
    }

    /**
     * output actionPersonalSite_actionPerformed
     */
    public void actionPersonalSite_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPersonalSite_actionPerformed(e);
    }

    /**
     * output actionProcductVal_actionPerformed
     */
    public void actionProcductVal_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionProcductVal_actionPerformed(e);
    }

    /**
     * output actionExportSave_actionPerformed
     */
    public void actionExportSave_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSave_actionPerformed(e);
    }

    /**
     * output actionExportSelectedSave_actionPerformed
     */
    public void actionExportSelectedSave_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSelectedSave_actionPerformed(e);
    }

    /**
     * output actionKnowStore_actionPerformed
     */
    public void actionKnowStore_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionKnowStore_actionPerformed(e);
    }

    /**
     * output actionAnswer_actionPerformed
     */
    public void actionAnswer_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAnswer_actionPerformed(e);
    }

    /**
     * output actionRemoteAssist_actionPerformed
     */
    public void actionRemoteAssist_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionRemoteAssist_actionPerformed(e);
    }

    /**
     * output actionPopupCopy_actionPerformed
     */
    public void actionPopupCopy_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPopupCopy_actionPerformed(e);
    }

    /**
     * output actionHTMLForMail_actionPerformed
     */
    public void actionHTMLForMail_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHTMLForMail_actionPerformed(e);
    }

    /**
     * output actionExcelForMail_actionPerformed
     */
    public void actionExcelForMail_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExcelForMail_actionPerformed(e);
    }

    /**
     * output actionHTMLForRpt_actionPerformed
     */
    public void actionHTMLForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHTMLForRpt_actionPerformed(e);
    }

    /**
     * output actionExcelForRpt_actionPerformed
     */
    public void actionExcelForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExcelForRpt_actionPerformed(e);
    }

    /**
     * output actionLinkForRpt_actionPerformed
     */
    public void actionLinkForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionLinkForRpt_actionPerformed(e);
    }

    /**
     * output actionPopupPaste_actionPerformed
     */
    public void actionPopupPaste_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPopupPaste_actionPerformed(e);
    }

    /**
     * output actionToolBarCustom_actionPerformed
     */
    public void actionToolBarCustom_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionToolBarCustom_actionPerformed(e);
    }

    /**
     * output actionCloudFeed_actionPerformed
     */
    public void actionCloudFeed_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudFeed_actionPerformed(e);
    }

    /**
     * output actionCloudShare_actionPerformed
     */
    public void actionCloudShare_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudShare_actionPerformed(e);
    }

    /**
     * output actionCloudScreen_actionPerformed
     */
    public void actionCloudScreen_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudScreen_actionPerformed(e);
    }

    /**
     * output actionXunTongFeed_actionPerformed
     */
    public void actionXunTongFeed_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionXunTongFeed_actionPerformed(e);
    }

    /**
     * output actionAddNew_actionPerformed
     */
    public void actionAddNew_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAddNew_actionPerformed(e);
    }

    /**
     * output actionView_actionPerformed
     */
    public void actionView_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionView_actionPerformed(e);
    }

    /**
     * output actionEdit_actionPerformed
     */
    public void actionEdit_actionPerformed(ActionEvent e) throws Exception
    {
        List ids = getSelectedIdValues();
        if ((ids != null) && (ids.size() > 0)) {
          for (int i = 0; i < ids.size(); i++)
          {
            String id = (String)ids.get(i);
            FreightUnitInfo info = 
              FreightUnitFactory.getRemoteInstance().getFreightUnitInfo(
              new ObjectUuidPK(id));
            if ((info != null) && (
              (info.getBillStatus().equals(BillStatus.processed)) || 
              (info.getBillStatus().equals(BillStatus.approved))))
            {
              MsgBox.showInfo(
                "单据编号为" + info.getNumber() + "的单据已审核，不允许修改！");
              SysUtil.abort();
            }
          }
        }
        super.actionEdit_actionPerformed(e);
    }

    /**
     * output actionRemove_actionPerformed
     */
    public void actionRemove_actionPerformed(ActionEvent e) throws Exception
    {        List ids = getSelectedIdValues();
    if ((ids != null) && (ids.size() > 0)) {
        for (int i = 0; i < ids.size(); i++)
        {
          String id = (String)ids.get(i);
          FreightUnitInfo info = 
            FreightUnitFactory.getRemoteInstance().getFreightUnitInfo(
            new ObjectUuidPK(id));
          if ((info != null) && (
            (info.getBillStatus().equals(BillStatus.processed)) || 
            (info.getBillStatus().equals(BillStatus.approved))))
          {
            MsgBox.showInfo(
              "单据编号为" + info.getNumber() + "的单据已审核，不允许删除！");
            SysUtil.abort();
          }
        }
      }
        super.actionRemove_actionPerformed(e);
    }

 
    

    /**
     * output actionApproved_actionPerformed
     */
    public void actionApproved_actionPerformed(ActionEvent e) throws Exception
    {    
    	List ids = getSelectedIdValues();
        if ((ids != null) && (ids.size() > 0)) {
          for (int i = 0; i < ids.size(); i++)
          {
            String id = (String)ids.get(i);
            FreightUnitInfo info = FreightUnitFactory.getRemoteInstance()
            .getFreightUnitInfo(new ObjectUuidPK(id));
             FreightUnitFactory.getRemoteInstance().approved(info);
          }
        }
        refresh(e);
        super.actionApproved_actionPerformed(e);
    }

 

    /**
     * output actionNo_actionPerformed
     */
    public void actionNo_actionPerformed(ActionEvent e) throws Exception
    {      
    	List ids = getSelectedIdValues();
        if ((ids != null) && (ids.size() > 0)) {
          for (int i = 0; i < ids.size(); i++)
          {
            String id = (String)ids.get(i);
            FreightUnitInfo info = FreightUnitFactory.getRemoteInstance()
            .getFreightUnitInfo(new ObjectUuidPK(id));
             FreightUnitFactory.getRemoteInstance().no(info);
          }
        }
        refresh(e);
       
        super.actionNo_actionPerformed(e);
    }

    /**
     * output getBizInterface method
     */
    protected com.kingdee.eas.framework.ICoreBase getBizInterface() throws Exception
    {
        return com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitFactory.getRemoteInstance();
    }

    /**
     * output createNewData method
     */
    protected com.kingdee.bos.dao.IObjectValue createNewData()
    {
        com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitInfo objectValue = new com.kingdee.eas.guiliumuye.duckbreeding.FreightUnitInfo();
		
        return objectValue;
    }

}