//将Execl数据放入DataSet
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, table);
return ds;
}
//导入按钮事件
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string strpath = FileUpload1.PostedFile.FileName.ToString(); //获取Execle文件路径
string filename = FileUpload1.FileName; //获取Execle文件名
DataSet ds = ExecleDs(strpath, filename);
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
int count = dr.Length;
int execCount = 0;
int yiCunZaiCount = 0;
for (int i = 0; i < dr.Length; i++)
{
string customerCode = dr[i][0].ToString();
string customerName = dr[i][1].ToString();
//
string cityId = dr[i]["所在城市"].ToString();
string address = dr[i]["联系地址"].ToString();
string phoneAreaCode = dr[i]["电话区号"].ToString();
string telPhone = dr[i]["电话"].ToString();
string telFenJi = dr[i]["电话分机号"].ToString();
string fax = dr[i]["传真区号"].ToString();
string faxFenJi = dr[i]["传真分机号"].ToString();
string webUrl = dr[i]["客户网址"].ToString();
//
string source = dr[i]["客户来源"].ToString();
string status = dr[i]["客户状态"].ToString();
string stype = dr[i]["客户类型"].ToString();
string level = dr[i]["客户等级"].ToString();
string industry = dr[i]["客户所属行业"].ToString();
string soucreId = string.Empty;
string statusId = string.Empty;
string stypeId = string.Empty;
string levelId = string.Empty;
string industryId = string.Empty;
//核对客户来源是否存在
soucreId = checkSource(source);
//核对客户状态是否存在
statusId = checkStatus(status);
//核对客户类型是否存在
stypeId = checkType(stype);
//核对客户等级是否存在
levelId = checkLevel(level);
//核对客户所属行业是否存在
industryId = checkIndustry(industry);
string[] param ={customerCode,customerName,cityId,address,phoneAreaCode, telPhone, telFenJi,fax,faxFenJi,webUrl,
soucreId,statusId,stypeId,levelId,industryId,ClsCommon.getUserInfo().USER_ID
};
string insertSqlStr = @"insert into T_Customer
(CustomerCode,CustomerName,CityID,Address,TelAreaCode,
Telephone,TelExtNo,FaxAreaCode,Fax,WebSiteUrl,
SourceId,StatusId,TypeId,LevelId,IndustryId,CreateUserId)
values ('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},{11},{12},{13},{14},'{15}' ";
insertSqlStr = string.Format(insertSqlStr, param);
insertSqlStr += @")";
string sqlcheck = "select * from T_Customer where CustomerName='" + customerName + "'";
DataSet dss = DbHelperSQL.Query(sqlcheck);
if (dss.Tables[0].Rows.Count <= 0)
{
try
{
int scuess = DbHelperSQL.ExecuteSql(insertSqlStr);
execCount++;
}
catch (Exception ex)
{
}
}
else
{
yiCunZaiCount++;
}
}
Response.Write("<script> alert('Excle表导入成功!！,导入" + execCount + "条新客户信息；" + yiCunZaiCount + "条老客户信息');</script>");
}
}
//核对客户来源是否存在
public string checkSource(string sourceName)
{
string sql = " select * from dbo.T_Customer_Source where SourceName='" + sourceName+"'";
string i = ClsDbAccess.ExecuteScalar(sql);
string j = string.Empty;
if (!string.IsNullOrEmpty(i))
{
return i;
}
else
{
string inseSql = "insert into dbo.T_Customer_Source (SourceName) values ('" + sourceName + "')";
j = ClsDbAccess.ExecuteScalar(inseSql);
return j;
}
}
//核对客户状态是否存在
public string checkStatus(string status)
{
string sql = " select * from T_Customer_Status where StatusName='" + status + "'";
string i = ClsDbAccess.ExecuteScalar(sql);
string j = string.Empty;
if (!string.IsNullOrEmpty(i))
{
return i;
}
else
{
string inseSql = "insert into T_Customer_Status (StatusName) values ('" + status + "')";
j = ClsDbAccess.ExecuteScalar(inseSql);
return j;
}
}
//核对客户类型是否存在
public string checkType(string type)
{
string sql = " select * from dbo.T_Customer_Type where TypeName='" + type+"'";
string i = ClsDbAccess.ExecuteScalar(sql);
string j = string.Empty;
if (!string.IsNullOrEmpty(i))
{
return i;
}
else
{
string inseSql = "insert into dbo.T_Customer_Type (TypeName) values ('" + type + "')";
j = ClsDbAccess.ExecuteScalar(inseSql);
return j;
}
}
//核对客户级别是否存在
public string checkLevel(string level)
{
string sql = " select * from dbo.T_Customer_Level where LevelName='" + level + "'";
string i = ClsDbAccess.ExecuteScalar(sql);
string j = string.Empty;
if (!string.IsNullOrEmpty(i))
{
return i;
}
else
{
string inseSql = "insert into T_Customer_Level (LevelName) values ('" + level + "')";
j = ClsDbAccess.ExecuteScalar(inseSql);
return j;
}
}
//核对客户所属行业是否存在
public string checkIndustry(string industry)
{
string sql = " select * from dbo.T_Customer_Industry where IndustryName='" + industry + "'";
string i = ClsDbAccess.ExecuteScalar(sql);
string j = string.Empty;
if (!string.IsNullOrEmpty(i))
{
return i;
}
else
{
string inseSql = "insert into T_Customer_Industry (IndustryName) values ('" + industry + "')";
j = ClsDbAccess.ExecuteScalar(inseSql);
return j;
}
}