如何把多个excel导入mysql_如何实现将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

{

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

{

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;

}