XAF 如何批量汇入数据从Excel中

1.首先用ExcelReader类实现读取Excel数据

2.定义一个泛型方法负责根据不同的BO汇入数据

3.实现代码

  1>ExcelReaer类

/// <summary>
    /// 操作Excel幫助類
    /// 2010-08-02
    /// </summary>
    public class ExcelReader : IDisposable
    {
        #region Variables
        private int[] _PKCol;
        private string _strExcelFilename;
        private bool _blnMixedData=true;
        private bool _blnHeaders=false;        
        private string _strSheetName;
        private string _strSheetRange;
        private bool _blnKeepConnectionOpen=false;
        private OleDbConnection _oleConn; 
        private OleDbCommand _oleCmdSelect;
        private OleDbCommand _oleCmdUpdate;
        #endregion

        #region properties

        private string _StrWhere;
        /// <summary>
        /// 匯入條件: where 之後條件
        /// </summary>
        public string StrWhere
        {
            get
            {
                return _StrWhere;
            }
            set
            {
                _StrWhere = value;
            }
        } 

        public int[] PKCols
        {
            get {return _PKCol;}
            set {_PKCol=value;}
        }

        public string ColName(int intCol)
        {
            string sColName="";
            if (intCol<26)
                sColName= Convert.ToString(Convert.ToChar((Convert.ToByte((char) 'A')+intCol)) );
            else
            {
                int intFirst = ((int) intCol / 26);
                int intSecond = ((int) intCol % 26);
                sColName= Convert.ToString(Convert.ToByte((char) 'A')+intFirst);
                sColName += Convert.ToString(Convert.ToByte((char) 'A')+intSecond);
            }
            return sColName;
        }

        public int ColNumber(string strCol)
        {
            strCol = strCol.ToUpper(); 
            int intColNumber=0;
            if (strCol.Length>1) 
            {
                intColNumber = Convert.ToInt16(Convert.ToByte(strCol[1])-65);  
                intColNumber += Convert.ToInt16(Convert.ToByte(strCol[1])-64)*26; 
            }
            else
                intColNumber = Convert.ToInt16(Convert.ToByte(strCol[0])-65);  
            return intColNumber;
        }

        public String[] GetExcelSheetNames(){
            System.Data.DataTable dt = null;
            try{
                if (_oleConn ==null) Open();
                // Get the data table containing the schema
                dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if(dt == null){
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                // Add the sheet name to the string array.
                foreach(DataRow row in dt.Rows){
                    string strSheetTableName = row["TABLE_NAME"].ToString();
                    excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1); 
                    i++;
                }
                return excelSheets;
            }
            catch(Exception){
                return null;
            }
            finally{
                // Clean up.
                if(this.KeepConnectionOpen==false){
                    this.Close();
                }
                if(dt != null){
                    dt.Dispose();
                    dt=null;
                }
            }
        }
                                                            
        public string ExcelFilename
        {
            get { return _strExcelFilename;}
            set { _strExcelFilename=value;}
        }
        public string SheetName
        {
            get { return _strSheetName;}
            set { _strSheetName=value;}
        }
        public string SheetRange
        {
            get {return _strSheetRange;}
            set 
            {
                if (value.IndexOf(":")==-1) throw new Exception("Invalid range length"); 
                _strSheetRange=value;}
        }        
        public bool KeepConnectionOpen
        {
            get { return _blnKeepConnectionOpen;}
            set {_blnKeepConnectionOpen=value;}
        }
        public bool Headers
        {
            get { return _blnHeaders;}
            set { _blnHeaders=value;}
        }
        public bool MixedData
        {
            get {return _blnMixedData;}
            set {_blnMixedData=value;}
        }
        #endregion

        #region Methods
        
        #region Excel Connection
        private string ExcelConnectionOptions()
        {
            string strOpts="";
            if (this.MixedData ==true)
                strOpts += "Imex=2;";
            if (this.Headers==true)
                strOpts += "HDR=Yes;";
            else    
                strOpts += "HDR=No;";
            return strOpts;
        }
        
        private string ExcelConnection()
        {
            return
                @"Provider=Microsoft.Jet.OLEDB.4.0;" + 
                @"Data Source=" + _strExcelFilename  + ";" + 
                @"Extended Properties=" + Convert.ToChar(34).ToString() + 
                @"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString(); 
        }
        #endregion


        #region Open / Close
        public void Open()
        {
            try
            {
                if (_oleConn !=null)
                {
                    if (_oleConn.State==ConnectionState.Open)
                    {
                        _oleConn.Close();
                    }
                    _oleConn=null;
                }

                //Server.MapPath("MyWebSite");

                

                //MapPath("XX");

                if (System.IO.File.Exists(_strExcelFilename)==false)
                {
                    throw new Exception("Excel file " + _strExcelFilename +  "could not be found.");
                }
                _oleConn = new OleDbConnection(ExcelConnection());  
                _oleConn.Open();                   
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Close()
        {
            if (_oleConn !=null)
            {
                if (_oleConn.State != ConnectionState.Closed) 
                    _oleConn.Close(); 
                _oleConn.Dispose();
                _oleConn=null;
            }
        }
        #endregion

        #region Command Select
        private bool SetSheetQuerySelect()
        {
            try
            {
                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed."); 
                }

                if (_strSheetName.Length ==0)
                    throw new Exception("Sheetname was not assigned."); 

                _oleCmdSelect =new OleDbCommand(
                    @"SELECT * FROM [" 
                    + _strSheetName + "$"
                 
                    + _strSheetRange
                    + "]" + _StrWhere , _oleConn);   
            
                return true;
            }            
            catch (Exception ex)
            {
                throw ex;
            }
            

        }
        #endregion

        #region simple utilities
        private string AddWithComma(string strSource,string strAdd)
        {
            if (strSource !="") strSource = strSource += ", ";
            return strSource + strAdd;
        }

        private string AddWithAnd(string strSource,string strAdd)
        {
            if (strSource !="") strSource = strSource += " and ";
            return strSource + strAdd;
        }
        #endregion

        private OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)
        {
            // Deleting in Excel workbook is not possible
            //So this command is not defined
            try
            {
                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed."); 
                }


                if (_strSheetName.Length ==0)
                    throw new Exception("Sheetname was not assigned."); 
                
                if (PKCols == null)
                    throw new Exception("Cannot update excel sheet with no primarykey set."); 
                if (PKCols.Length<1) 
                    throw new Exception("Cannot update excel sheet with no primarykey set."); 
                    
                OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);                 
                string strUpdate="";
                string strInsertPar="";
                string strInsert="";
                string strWhere="";
                
                
                for (int iPK=0;iPK<PKCols.Length;iPK++)
                {
                    strWhere = AddWithAnd(strWhere,dt.Columns[iPK].ColumnName +  "=?"); 
                }
                strWhere =" Where "+strWhere;

                for (int iCol=0;iCol<dt.Columns.Count;iCol++)
                {
                    strInsert= AddWithComma(strInsert,dt.Columns[iCol].ColumnName);
                    strInsertPar= AddWithComma(strInsertPar,"?");
                    strUpdate= AddWithComma(strUpdate,dt.Columns[iCol].ColumnName)+"=?";
                }

                string strTable = "["+ this.SheetName + "$" + this.SheetRange + "]";  
                strInsert = "INSERT INTO "+ strTable + "(" + strInsert +") Values (" + strInsertPar + ")";
                strUpdate = "Update " + strTable + " Set " + strUpdate + strWhere;
                
                
                oleda.InsertCommand = new OleDbCommand(strInsert,_oleConn);
                oleda.UpdateCommand = new OleDbCommand(strUpdate,_oleConn); 
                OleDbParameter oleParIns = null;
                OleDbParameter oleParUpd = null;
                for (int iCol=0;iCol<dt.Columns.Count;iCol++)
                {
                    oleParIns = new OleDbParameter("?",dt.Columns[iCol].DataType.ToString());
                    oleParUpd = new OleDbParameter("?",dt.Columns[iCol].DataType.ToString());
                    oleParIns.SourceColumn =dt.Columns[iCol].ColumnName;
                    oleParUpd.SourceColumn =dt.Columns[iCol].ColumnName;
                    oleda.InsertCommand.Parameters.Add(oleParIns);
                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
                    oleParIns=null;
                    oleParUpd=null;
                }

                for (int iPK=0;iPK<PKCols.Length;iPK++)
                {
                    oleParUpd = new OleDbParameter("?",dt.Columns[iPK].DataType.ToString());
                    oleParUpd.SourceColumn =dt.Columns[iPK].ColumnName;
                    oleParUpd.SourceVersion = DataRowVersion.Original;
                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
                }
                return oleda;
            }            
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        #region command Singe Value Update
        private bool SetSheetQuerySingelValUpdate(string strVal)
        {
            try
            {
                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed."); 
                }

                if (_strSheetName.Length ==0)
                    throw new Exception("Sheetname was not assigned."); 

                _oleCmdUpdate =new OleDbCommand(
                    @" Update [" 
                    + _strSheetName 
                    + "$" + _strSheetRange
                    + "] set F1=" + strVal, _oleConn);   
                return true;
            }            
            catch (Exception ex)
            {
                throw ex;
            }
            

        }
        #endregion

        

        public void SetPrimaryKey(int intCol)
        {
            _PKCol = new int[1] { intCol };            
        }

        

        private void SetPrimaryKey(DataTable dt)
        {
            try
            {
                if (PKCols!=null)
                {
                    //set the primary key
                    if (PKCols.Length>0)
                    {
                        DataColumn[] dc;
                        dc = new DataColumn[PKCols.Length];
                        for (int i=0;i<PKCols.Length;i++)
                        {
                            dc[i] =dt.Columns[PKCols[i]]; 
                        }
                
                        
                        dt.PrimaryKey = dc;

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

         public DataTable GetTable()
        {
            return GetTable("ExcelTable");
        }
        public DataTable GetTable(string strTableName)
        
        {
            try
            {
                //Open and query
                if (_oleConn ==null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection cannot open error."); 
                if (SetSheetQuerySelect()==false) return null;

                //Fill table
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();   
                oleAdapter.SelectCommand = _oleCmdSelect;   
                DataTable dt = new DataTable(strTableName+"1");
                oleAdapter.FillSchema(dt, SchemaType.Source);  
                oleAdapter.Fill(dt);
                if (this.Headers ==false)
                {
                    if (_strSheetRange.IndexOf(":")>0)
                    {
                        string FirstCol = _strSheetRange.Substring(0,_strSheetRange.IndexOf(":")-1); 
                        int intCol = this.ColNumber(FirstCol);
                        for (int intI=0;intI<dt.Columns.Count;intI++)
                        {
                            dt.Columns[intI].Caption =ColName(intCol+intI);
                        }
                    }
                }
                SetPrimaryKey(dt);
                //Cannot delete rows in Excel workbook
                dt.DefaultView.AllowDelete =false;
            
                //Clean up
                _oleCmdSelect.Dispose();
                _oleCmdSelect=null;
                oleAdapter.Dispose();
                oleAdapter=null;
                if (KeepConnectionOpen==false) Close();
                return dt;            

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CheckPKExists(DataTable dt)
        {
            if (dt.PrimaryKey.Length==0) 
                if (this.PKCols !=null)
                {
                    SetPrimaryKey(dt);
                }
                else
                    throw new Exception("Provide an primary key to the datatable"); 
        }
        public DataTable SetTable(DataTable dt)
        {
            try
            {
                DataTable dtChanges = dt.GetChanges();
                if (dtChanges == null) throw new Exception("There are no changes to be saved!"); 
                CheckPKExists(dt);
                //Open and query
                if (_oleConn ==null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection cannot open error."); 
                if (SetSheetQuerySelect()==false) return null;

                //Fill table
                OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(dtChanges);        
                
                oleAdapter.Update(dtChanges); 
                //Clean up
                _oleCmdSelect.Dispose();
                _oleCmdSelect=null;
                oleAdapter.Dispose();
                oleAdapter=null;
                if (KeepConnectionOpen==false) Close();
                return dt;            
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region Get/Set Single Value

        public void SetSingleCellRange(string strCell)
        {
            _strSheetRange = strCell + ":" + strCell;
        }

        public object GetValue(string strCell)
        {
            SetSingleCellRange(strCell);
            object objValue=null;
            //Open and query
            if (_oleConn ==null) Open();
            if (_oleConn.State != ConnectionState.Open)
                throw new Exception("Connection is not open error."); 

            if (SetSheetQuerySelect()==false) return null;
            objValue = _oleCmdSelect.ExecuteScalar();

            _oleCmdSelect.Dispose();
            _oleCmdSelect=null;    
            if (KeepConnectionOpen==false) Close();
            return objValue;
        }

        public void SetValue(string strCell,object objValue)
        {
            
            try
            {

                SetSingleCellRange(strCell);
                //Open and query
                if (_oleConn ==null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection is not open error."); 
                
                if (SetSheetQuerySingelValUpdate(objValue.ToString())==false) return;
                objValue = _oleCmdUpdate.ExecuteNonQuery(); 

                _oleCmdUpdate.Dispose();
                _oleCmdUpdate=null;    
                if (KeepConnectionOpen==false) Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_oleCmdUpdate != null)
                {
                    _oleCmdUpdate.Dispose();
                    _oleCmdUpdate=null;
                }
            }
            
        }
        #endregion
        

        #endregion

        #region Dispose / Destructor
        public     void Dispose()
        {
            if (_oleConn !=null)
            {
                _oleConn.Dispose();
                _oleConn=null;
            }
            if (_oleCmdSelect!=null)
            {
                _oleCmdSelect.Dispose(); 
                    _oleCmdSelect=null;
            }
            // Dispose of remaining objects.
        }
#endregion
    
        #region CTOR
        public ExcelReader()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        /// <summary>
        /// 默認有表頭
        /// </summary>
        /// <param name="filName"></param>
        /// <param name="sheetName"></param>
        public ExcelReader(string fileName, string sheetName)
        {
            if (string.IsNullOrEmpty(fileName) == true)
            { throw new ArgumentNullException("文件名不能為空！"); }
            if (string.IsNullOrEmpty(sheetName) == true) { throw new ArgumentNullException("Excel文件中Sheet Name不能為空！");
            }
            this.ExcelFilename = fileName;
            this.SheetName = sheetName;
            //this.Headers = true;
        }
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="filName">表名稱和路徑</param>
        /// <param name="sheetName">Excel中Sheet name</param>
        /// <param name="isHeaders">是否有表頭,默認是false</param>
        public ExcelReader(string fileName,string sheetName,bool isHeaders)
        {
            if (string.IsNullOrEmpty(fileName) == true)
            { throw new ArgumentNullException("文件名不能為空！"); }
            if (string.IsNullOrEmpty(sheetName) == true)
            {
                throw new ArgumentNullException("Excel文件中Sheet Name不能為空！");
            }
            
            this.ExcelFilename = fileName;
            this.SheetName     = sheetName;
            this.Headers       = isHeaders;
        }
        #endregion
    }

     2>汇入数据的业务逻辑类

 /// <summary>
    /// 匯入數據業務邏輯
    /// 2010-08-01
    /// </summary>
    public class ImportDataLogic
    {
       
       #region 匯入MRF單
        
        public static ImportDataDelegate<MP_MRF> CreateMP_MRFImportDataDelegate()
        {
            return delegate (Session session, object [] args) 
            {
                DataRow row;
                User currentUser;
                Init(session, args, out row, out currentUser);

                MP_MRF mrf = new MP_MRF(session);
                mrf.OrderNO = row["PRODUCTIONNO"].ToString();
                mrf.Plnt = row["PLANT"].ToString();
                mrf.Material = row["MATERIAL"].ToString();
                mrf.Description = row["MATERIAL_DESCRIPTION"].ToString();
                mrf.Order_Qty = int.Parse(row["ORDER_QTY"].ToString());
                mrf.QtyOfOuter = int.Parse(row["QTY/OUTER"].ToString());
                mrf.Unit = row["BASE_UNIT"].ToString();
                mrf.COMPONENT = row["COMPONENT"].ToString();
                mrf.COMPONENT_DESCRIPTION = row["COMPONENT_DESCRIPTIO"].ToString();
                mrf.COMPONENT_QTY = int.Parse(row["COMPONENT_QTY"].ToString());
                mrf.COMPONENT_SCRAP = decimal.Parse(row["COMPONENT_SCRAP"].ToString());
                mrf.COMPONT_UNIT = row["COMPONT_UNIT"].ToString();
                mrf.Start_Date = DateTime.Now;
                mrf.Finish_Date =DateTime.Now;
                mrf.Write_Date = DateTime.Now;
                mrf.Create_Date = DateTime.Now;
                mrf.Create_Uid = currentUser;
                mrf.Write_Uid = currentUser; //mrf.Save();
                
                return mrf;
            };
        }

        public static ValidateDataDelegate<MP_MRF> CreateMP_MRFValidateDataDelegate()
        {
            return (mrf, args) =>
            {
                if(mrf != null)
                   return mrf.Plnt.Length <= 4 ;//在此写入验证逻辑
                return false;
            };
        }
        #endregion
        #region Mothed
       
     

        /// <summary>
        /// 初始化匯入對象帶masterObject
        /// </summary>
        /// <param name="session"></param>
        /// <param name="args"></param>
        /// <param name="masterObject"></param>
        /// <param name="currentUser"></param>
        /// <param name="row"></param>
        private static void Init(Session session, object[] args, out object masterObject, out User currentUser, out DataRow row)
        {
            masterObject = null;
            currentUser = null;
            row = null;
            if (args != null)
            {
                masterObject = args[0];
                row = (DataRow)args[1];
                currentUser = session.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
            }
            else { throw new ArgumentNullException("args"); }
        }
        /// <summary>
        /// 初始化匯入對象
        /// </summary>
        /// <param name="session"></param>
        /// <param name="args"></param>
        /// <param name="row"></param>
        /// <param name="currentUser"></param>
        private static void Init(Session session, object[] args, out DataRow row, out User currentUser)
        {
            if (args == null)
            { throw new ArgumentNullException("args"); }
            //object masterObject = args[0];
            row = (DataRow)args[1];
            currentUser = session.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
        }
        
        /// <summary>
        /// 將對象轉換為字符且截取兩端空格
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static  string ToStingTrim(object obj)
        {
            if (obj == null)
            { return ""; }
            return obj.ToString ().Trim();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static int ToInt(object obj)
        {
            if (obj == null) { 
                //throw new  ArgumentNullException("Object");
                return 0;
            }
            return int.Parse(obj.ToString());
        } 
        #endregion
    }

       3>汇入数据管理类

　　　

 /// <summary>
    /// 匯入數據代理
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sessin"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public delegate  T ImportDataDelegate<T>(Session sessin, params object[] args);

    /// <summary>
    /// 驗證要匯入數據代理
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="data"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public delegate bool ValidateDataDelegate<T>(T data, params object[] args);

    /// <summary>
    /// 
    /// </summary>
    public class ImportDataManager
    {
        /// <summary>
        /// 匯入對象到ListView
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt">DataTable</param>
        /// <param name="importDataDelegate"></param>
        /// <param name="customValidateDataDelegate"></param>
        /// <param name="listView"></param>
        /// <returns></returns>
        public static int ImportData<T>(DataTable dt,ImportDataDelegate<T> importDataDelegate, ValidateDataDelegate<T> customValidateDataDelegate, ListView listView) where T : IXPSimpleObject
        {
            if (listView == null)
            {
                throw new ArgumentNullException("listView");
            }
            UnitOfWork uow = null;
            int countOK = 0;
            try
            {
                if (listView.ObjectSpace == null)
                {
                    throw new ArgumentNullException("listView.ObjecSpace");
                }
                uow = new UnitOfWork(listView.ObjectSpace.Session.DataLayer);//
                using (NestedUnitOfWork nestedUOW = uow.BeginNestedUnitOfWork()) {
                    PropertyCollectionSource pcs = listView.CollectionSource as PropertyCollectionSource;
                    object masterObject = pcs != null ? nestedUOW.GetObjectByKey(pcs.MasterObjectType, nestedUOW.GetKeyValue(pcs.MasterObject)) : null;
                    foreach (DataRow row in dt.Rows){
                         T obj = importDataDelegate(nestedUOW, masterObject, row);
                        #region Validate Object
                         if (obj != null)
                         {
                             bool isValid = false;
                             try
                             {
                                 RuleSetValidationResult validationResult = Validator.RuleSet.ValidateTarget(obj,new ContextIdentifiers(DefaultContexts.Save.ToString()));
                                 isValid = validationResult.State != ValidationState.Invalid && (customValidateDataDelegate != null ? customValidateDataDelegate(obj, masterObject) : true);
                                 if (isValid)
                                 {
                                     nestedUOW.CommitChanges();
                                 }
                             }
                             catch (Exception )
                             {
                                 isValid = false;
                             }
                             finally
                             {
                                 if (isValid)
                                 {
                                     countOK++;
                                 }
                             }
                         }
                        #endregion
                    }
                }
                uow.CommitChanges();
            }
            catch (Exception commitException){
                try {
                    uow.RollbackTransaction();
                }
                catch (Exception rollBackException){
                    throw new Exception(String.Format("異常類型 {0} 撤銷匯入數據 {1} type.\n錯誤信息:{2}\n堆棧跟蹤:{3}", rollBackException.GetType(), typeof(T), rollBackException.Message, rollBackException.StackTrace), rollBackException);
                }
                throw new UserFriendlyException(new Exception(String.Format("匯入失敗!\n異常類型 {0} 當匯入數據 {1} 類型.\n錯誤信息 = {2}\n堆棧跟蹤:{3}\n沒有記錄匯入.", commitException.GetType(), typeof(T), commitException.Message, commitException.StackTrace)));
            }
            finally{
                uow.Dispose();
                uow = null;
            }
            listView.ObjectSpace.Refresh();
            return countOK;
        }

        public static void ImportData<T>(DataTable dt,ImportDataDelegate<T> importDataDelegate, ValidateDataDelegate<T> customValidateDataDelegate, ListView listView,bool notifyAboutResults)  where  T : IXPSimpleObject
        {
            int countOK = ImportData<T>(dt,importDataDelegate,customValidateDataDelegate,listView);
            if(notifyAboutResults == true)
            {
                throw new UserFriendlyException(new Exception(string.Format("已經匯入{0}條記錄！", countOK)));
            }
        }
    }

4.调用方法：

     

string fileName = "";
string sheetName = "Sheet1";
bool isHeader = true;
ExcelReader er = null;
DataTable dt = null;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 文件|*.xls";
ofd.ShowDialog();
fileName = ofd.FileName;
if (string.IsNullOrEmpty(fileName) == true) return;
er = new ExcelReader(fileName, sheetName, isHeader);
er.MixedData = true;
er.KeepConnectionOpen = true;

dt = er.GetTable();
ImportDataManager.ImportData<WH_IL>(dt, ImportDataLogic.CreateDummyWH_ILImportDataDelegate(),
                            ImportDataLogic.CreateWH_ILValidateDataDelegate(), listView, true);