C#- Excel表导入到sql数据库

请参考:
       前几天，一直在网上搜索资料，想实现这个功能，找到有存储过程的。都没找到我想要的结果，最后只要自己想办法实现了。
        大体思路是：将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;

    }