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 );

 

 

 

转载于:https://www.cnblogs.com/Tonyyang/archive/2010/08/09/1795823.html