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