<script type="text/JavaScript"> alimama_pid="mm_10249644_1605763_4929893"; alimama_titlecolor="0000FF"; alimama_descolor ="000000"; alimama_bgcolor="FFFFFF"; alimama_bordercolor="E6E6E6"; alimama_linkcolor="008000"; alimama_bottomcolor="FFFFFF"; alimama_anglesize="0"; alimama_bgpic="0"; alimama_icon="0"; alimama_sizecode="16"; alimama_width=658; alimama_height=60; alimama_type=2; </script> <script src="http://a.alimama.cn/inf.js" type=text/javascript> </script>
前几天,一直在网上搜索资料,想实现这个功能,都没找到我想要的结果,最后只要自己想办法实现了,以便以后使用,也给需要这个功能的人提供点思路:大体思路是:将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;
- }
- <script type="text/JavaScript"> alimama_pid="mm_10249644_1605763_4930558"; alimama_type="f"; alimama_sizecode ="tl_1x5_8"; alimama_fontsize=12; alimama_bordercolor="FFFFFF"; alimama_bgcolor="FFFFFF"; alimama_titlecolor="0000FF"; alimama_underline=0; alimama_height=22; alimama_width=512; </script> <script src="http://a.alimama.cn/inf.js" type=text/javascript> </script>