转一文章请参考: 前几天,一直在网上搜索资料,想实现这个功能,都没找到我想要的结果,最后只要自己想办法实现了。 大体思路是:将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; } |
asp.net怎么将Excel表导入到sql数据库
最新推荐文章于 2021-01-31 21:28:45 发布