如何实现将Excel表(含多张数据库表)导入数据库(SQLServer)

前几天，一直在网上搜索资料，想实现这个功能，都没找到我想要的结果，最后只要自己想办法实现了害怕忘记，把它贴出来，以便以后使用，也给需要这个功能的人提供点思路：大体思路是：将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;

}

出处：成都DotNet俱乐部专栏(kim)