帮帮忙!
帮帮忙!
try
{
OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
oleDbConnection.Open();
dataSet = new DataSet();
DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //获取excel表
string tableName1 = dataTable.Rows[0][2].ToString().Trim(); //获取Sheet1名
//[0][1]...[N]: 按名称排列的表单元素
tableName1 = "[" + tableName1.Replace("'","") + "]";
//哩两句是用来获取课程信息用的
string query1 = "SELECT * FROM " + tableName1;
OleDbDataAdapter oleAdapter1 = new OleDbDataAdapter(query1,connExcel);
oleAdapter1.Fill(dataSet,"gch_CustomerCard_Info");
dataGrid4.SetDataBinding(dataSet,"gch_CustomerCard_Info");
oleDbConnection.Close();
DataTable dataTable1 = new DataTable("gch_Customer_Info");
string getCustomerInfo = @"select customerID, bargainID, company, address, contactWay, zipcode,
linkman, linkmanBirthday, linkmanSex, linkmanPosition, linkmanFax,
linkmanPhone, linkmanMobile, linkmanEmail, principal, principalBirthday,
principalSex, principalPosition, principalFax, principalPhone, principalMobile,
principalEmail, companyProperty, companyType, companySetUpDate, businessMode,
companySize, nowService, serviceLocation, adviser, adviserLocation, adminID, recordDate
from gch_Customer_Info";
SqlDataAdapter sqlDA1 = new SqlDataAdapter(getCustomerInfo,sqlConnection1);
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);
//int count = dataSet.Tables["gch_CustomerCard_Info"].Columns.Count;
//count = count - 1;
///*
foreach(DataRow dataRow in dataSet.Tables["gch_CustomerCard_Info"].Rows)
{
//for(int i=0; i<count; i++)
//{
DataRow dataRow1 = dataTable1.NewRow();
//MessageBox.Show(dataRow[i+1].ToString());
//if(dataRow[i+1].ToString()!="")
{
if(dataRow["权益书号"].ToString()!="")
{
dataRow1["bargainID"] = dataRow["权益书号"];
}
else
{
dataRow1["bargainID"] = "0";
}
dataRow1["company"] = dataRow["公司名称"];
dataRow1["address"] = dataRow["通讯地址"];
dataRow1["contactWay"] = dataRow["通知方式"];
dataRow1["zipcode"] = dataRow["邮编"];
dataRow1["linkman"] = dataRow["联系人"];
dataRow1["linkmanBirthday"] = "1983-3-1"; //dataRow["联系人生日"];
//dataRow1["linkmanSex"] = dataRow["性别"];
switch(dataRow["性别"].ToString())
{
case "男":
dataRow1["linkmanSex"] = 1;
break;
case "女":
dataRow1["linkmanSex"] = 0;
break;
default:
dataRow1["linkmanSex"] = DBNull.Value;
break;
}
dataRow1["linkmanPosition"] = dataRow["职位"];
dataRow1["linkmanFax"] = dataRow["传真"];
dataRow1["linkmanPhone"] = dataRow["电话"];
dataRow1["linkmanMobile"] = dataRow["手机"];
dataRow1["linkmanEmail"] = dataRow["email"];
dataRow1["principal"] = dataRow["公司负责人"];
dataRow1["principalBirthday"] = "1985-7-5"; //dataRow["负责人生日"];
//dataRow1["principalSex"] = dataRow["性别1"];
switch(dataRow["性别1"].ToString())
{
case "男":
dataRow1["principalSex"] = 1;
break;
case "女":
dataRow1["principalSex"] = 0;
break;
default:
dataRow1["principalSex"] = DBNull.Value;
break;
}
dataRow1["principalPosition"] = dataRow["职位1"];
dataRow1["principalFax"] = dataRow["传真1"];
dataRow1["principalPhone"] = dataRow["电话1"];
dataRow1["principalMobile"] = dataRow["手机1"];
dataRow1["principalEmail"] = dataRow["email1"];
dataRow1["companyProperty"] = dataRow["企业性质"];
dataRow1["companyType"] = dataRow["行业类型"];
dataRow1["companySetUpDate"] = "1983-10-2"; //dataRow["成立日期"];
dataRow1["businessMode"] = dataRow["经营模式"];
dataRow1["companySize"] = dataRow["企业规模"];
dataRow1["nowService"] = dataRow["现服务人"];
dataRow1["serviceLocation"] = dataRow["区域"];
dataRow1["adviser"] = dataRow["顾问"];
dataRow1["adviserLocation"] = dataRow["分公司"];
}
//if(dataRow1[1].ToString()!="") //&& dataRow1[2].ToString()!="" && dataRow1[3].ToString()!="" && dataRow1[4].ToString()!="")
//{
dataTable1.Rows.Add(dataRow1);
//}
//}
}
//sqlDA1.Update(dataTable1); //哩句系插入数据库的关键
MessageBox.Show("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
DataSet ds = new DataSet();
ds.Tables.Add(dataTable1);
dataGrid5.SetDataBinding(ds,"gch_Customer_Info");
//*/
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
[此贴子已经被作者于2006-5-17 10:31:46编辑过]
... //前一段先读出excel文件里面的内容
SqlDataAdapter sqlDA1 = new SqlDataAdapter(getCustomerInfo,sqlConnection1);
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);
//上面三句先把原来的数据读出来
//设置一个循环 每次循环都新建一行
{
DataRow dataRow1 = dataTable1.NewRow();
... //一行一行地把excel里面的数据插入datatable
dataTable1.Rows.Add(dataRow1);
}//循环完毕
sqlDA1.Update(dataTable1); //插入数据库的关键