将excel 导入数据库

 

private void Button1_Click(object sender, System.EventArgs e)    {    //选择文件     ofdSelectExcel.Filter = "Excel Files(*.xls)|*.xls";     ofdSelectExcel.RestoreDirectory = true;     if( ofdSelectExcel.ShowDialog() == DialogResult.OK )     {      if ( ofdSelectExcel.FileName.Trim().Length == 0)      {       MessageBox.Show(this,"Please select a excel file first!");       return;      }      else      {       ImportExcelToSqlServer(ofdSelectExcel.FileName.Trim());      }              }    }    //********************************************************    //提取数据    public   void ImportExcelToSqlServer(string fileName)     {      if (fileName == null)      {       throw new ArgumentNullException("filename string is null!");      }      if (fileName.Length == 0)      {       throw new ArgumentException("filename string is empty!");      }      string oleDBConnString = String.Empty;      oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";      oleDBConnString += "Data Source=";      oleDBConnString += fileName;      oleDBConnString += ";Extended Properties=Excel 8.0;";      OleDbConnection oleDBConn = null;      OleDbDataAdapter oleAdMaster = null;      DataTable m_tableName=new DataTable();;      DataSet ds=new DataSet();      try      {       oleDBConn = new OleDbConnection(oleDBConnString);       oleDBConn.Open();       m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);           if (m_tableName != null && m_tableName.Rows.Count > 0)       {        m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();       }       string sqlMaster;       sqlMaster=" SELECT *   FROM ["+m_tableName+"]";       oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);       oleAdMaster.Fill(ds,"m_tableName");            MailRebateManager manger=new MailRebateManager();       bool isSucess=manger.AddExceLGmailRebate(ds.Tables["m_tableName"],ApplicationVariable.HomeCompanyID);       if(isSucess)       {        MessageBox.Show("Manipulate Succs!");       }       else       {        MessageBox.Show("Manipulate Failed");       }      }      catch(Exception ex)      {       MessageBox.Show(ex.Message);       SimpleLogger.Log(ex);       try       {       }       catch (OleDbException e)       {        SimpleLogger.Log(e);        MessageBox.Show("An exception of type " + e.GetType() );        }      }          }    //*****************************************    //将数据进行处理分别插入主表和从表     public bool AddExceLGmailRebate(DataTable tb,string homeCompanyID)     {      bool ret=false;      SqlConnection con=null;                 DataTable table=new DataTable();      table=tb;          string sConn = PublicManager.GetDBConnectionString(homeCompanyID);      con=new SqlConnection();      con.ConnectionString=sConn;          SqlTransaction tran=null;      SqlCommand com=null;      SqlCommand comm=null;         try      {       con.Open();       tran = con.BeginTransaction();           if (table != null && table.Rows.Count > 0)       {              for(int i=1;i<table.Rows.Count;i++)        {                string m_PromoCode=Convert.ToString(table.Rows[i][0]);         if(m_PromoCode=="")         {          m_PromoCode=Convert.ToString(table.Rows[i-1][0]);         }         if(m_PromoCode.Length>50 )         {          m_PromoCode=m_PromoCode.Substring(0,50);         }         string m_ItemDescription=Convert.ToString(table.DefaultView[i][1]);         if(m_ItemDescription.IndexOf("(")>0)         {          int num=m_ItemDescription.IndexOf("(");          m_ItemDescription=m_ItemDescription.Substring(0,num);          if(m_ItemDescription.Length>50)          {           m_ItemDescription=m_ItemDescription.Substring(0,50);          }                 }         if(m_ItemDescription.Length>50)         {          m_ItemDescription=m_ItemDescription.Substring(0,50);         }         string begin=Convert.ToString(table.DefaultView[i][2]);         string m_BeginPromoPeriodDate;         string m_EndPromoPeriodEndDate;         if(begin=="")         {          continue;         }         else         {          string beginTime=begin.Substring(0,8);          beginTime=beginTime.Replace("/","-");          m_BeginPromoPeriodDate=beginTime;          string endTime=begin.Substring(begin.Length-8);          endTime=endTime.Replace("/","-");          m_EndPromoPeriodEndDate=endTime;         }         string m_RebateAmountStr=Convert.ToString(table.DefaultView[i][3]);         if(m_RebateAmountStr.Length >9)         {          m_RebateAmountStr=m_RebateAmountStr.Substring(0,9);         }         decimal m_RebateAmount;         if(m_RebateAmountStr=="")         {          m_RebateAmount=0;         }         else         {          m_RebateAmount= Convert.ToDecimal(m_RebateAmountStr);         }         string m_TotalSoldStr=Convert.ToString(table.DefaultView[i][7]);         if(m_TotalSoldStr.Length >4)         {          m_TotalSoldStr=m_TotalSoldStr.Substring(0,4);         }         int m_TotalSold;         if(m_TotalSoldStr=="")         {          m_TotalSold=0;         }         else         {          m_TotalSold=Convert.ToInt32(m_TotalSoldStr);         }         string m_RebateReserveStr=Convert.ToString(table.DefaultView[i][8]);         if(m_RebateReserveStr.Length>9)         {          m_RebateReserveStr=m_RebateReserveStr.Substring(0,9);         }         decimal m_RebateReserve;         if(m_RebateReserveStr=="")         {          m_RebateReserve=0;         }         else         {          m_RebateReserve=Convert.ToDecimal(m_RebateReserveStr);                    }         string m_RedeemedStr=Convert.ToString(table.DefaultView[i][17]);         if(m_RedeemedStr.Length >8)         {          m_RedeemedStr=m_RedeemedStr.Substring(0,8);         }         decimal m_Redeemed;         if(m_RedeemedStr=="")         {          m_Redeemed=0;         }         else         {          m_Redeemed=Convert.ToDecimal(m_RedeemedStr);         }         string m_PromoItem=Convert.ToString(table.DefaultView[i][23]);         if(m_PromoItem.Length >50)         {          m_PromoItem=m_PromoItem.Substring(0,50);         }         DateTime m_InDate;         m_InDate=DateTime.Now;                 string m_sqlMaster="INSERT INTO act.dbo.Newegg_GMailRebate(PromoCode,PromoItem,RebateAmount,ItemDescription,BeginPromoPeriodDate,EndPromoPeriodEndDate,PostMaskDate,TotalSold,RebateReserve,Redeemed,InDate)VALUES('"+m_PromoCode+"','"+m_PromoItem+"','"+m_RebateAmount+"', '"+m_ItemDescription+"','"+m_BeginPromoPeriodDate+"','"+m_EndPromoPeriodEndDate+"', '"+m_InDate+"','"+m_TotalSold+"','"+m_RebateReserve+"','"+m_Redeemed+"','"+m_InDate+"')";            comm=new SqlCommand(m_sqlMaster,con);         comm.Transaction =tran;         comm.ExecuteNonQuery ();         comm.Dispose();               }                     for(int i=1;i<table.Rows.Count;i++)        {                string m_PromoCode=Convert.ToString(table.Rows[i][0]);         if(m_PromoCode=="")         {          continue;         }         int   m_PromoCodeTransactionID=GetMasterTransactionID(m_PromoCode,homeCompanyID);                string m_InvoiceNumber=Convert.ToString(table.Rows[i][4]);         if(m_InvoiceNumber.Length >30)         {          m_InvoiceNumber=m_InvoiceNumber.Substring(0,30);         }         if(m_InvoiceNumber=="")         {          m_InvoiceNumber="0";         }         string m_InvoiceDate=Convert.ToString(table.DefaultView[i][6]);         if(m_InvoiceDate=="")         {          DateTime m_InDate;          m_InDate=DateTime.Now;          m_InvoiceDate=Convert.ToString(m_InDate);         }         string m_serialNumberStr=Convert.ToString(table.Rows[i][5]);         if(m_serialNumberStr.Length >4)         {          m_serialNumberStr=m_serialNumberStr.Substring(0,4);         }         if(m_serialNumberStr=="")         {          m_serialNumberStr="0";         }         int   m_serialNumber=Convert.ToInt32(m_serialNumberStr);                       string m_TotalValidStr=Convert.ToString( table.DefaultView[i][9]);         if(m_TotalValidStr          .Length >4)         {          m_TotalValidStr=m_TotalValidStr.Substring(0,4);             }         if(m_TotalValidStr=="")         {          m_TotalValidStr="0";         }         int m_TotalValid=Convert.ToInt32 (m_TotalValidStr);         string m_TotalInValidStr=Convert.ToString( table.DefaultView[i][10]);         if(m_TotalInValidStr.Length >4)         {          m_TotalInValidStr=m_TotalInValidStr.Substring(0,4);         }         if(m_TotalInValidStr=="")         {          m_TotalInValidStr="0";         }         int m_TotalInValid=Convert.ToInt32(m_TotalInValidStr);                string m_ProcessFeeStr=Convert.ToString(table.DefaultView[i][13]);         if(m_ProcessFeeStr.Length >9)         {          m_ProcessFeeStr=m_ProcessFeeStr.Substring(0,9);         }         if(m_ProcessFeeStr=="")         {          m_ProcessFeeStr="0";         }         decimal m_ProcessFee=Convert.ToDecimal(m_ProcessFeeStr);         string m_sqlDetails="INSERT INTO act.dbo.Newegg_GMailExcelMaster(PromoCodeTransactionID,InvoiceNumber,InvoiceDate,SerialNumber,TotalValid,TotalInvalid,ProcessFee)VALUES('"+m_PromoCodeTransactionID+"','"+m_InvoiceNumber+"','"+m_InvoiceDate+"', '"+m_serialNumber+"','"+m_TotalValid+"','"+m_TotalInValid+"','"+m_ProcessFee+"')";         com=new SqlCommand(m_sqlDetails,con);         com.CommandTimeout=60;         com.Transaction =tran;         com.ExecuteNonQuery();         com.Dispose();         m++;        }        tran.Commit();        ret=true;       }          }      catch(Exception ex)      {            try       {        tran.Rollback();       }       catch (SqlException e)       {        if (tran.Connection != null)        {         MessageBox.Show("An exception of type " + e.GetType() );         }       }       MessageBox.Show("Error come up row number:"+m.ToString());       MessageBox.Show("Error details:"+ex.Message);       SimpleLogger.Log(ex);       ret=false;      }      finally      {       con.Close();       con.Dispose();           }      return ret;    }