110,532
社区成员
发帖
与我相关
我的任务
分享
dtExcel.Columns.Add("数据是否正确");
dtExcel.Columns.Add("省ID");
dtExcel.Columns.Add("市ID");
dtExcel.Columns.Add("区ID");
//获取省市区数据
DataTable dtRegion = Customer.BlldtRegion();
if (dtExcel.Rows.Count > 0)
{
#region
foreach (DataRow dr in dtExcel.Rows)
{
string strEror = string.Empty;
if (string.IsNullOrEmpty(dr["姓名"].ToString()))
{
strEror += "姓名不能为空" + ",";
}
if (!string.IsNullOrEmpty(dr["手机"].ToString()))
{
//验证手机格式
string reg = @"^1[3|4|5|8][0-9]\d{4,8}$";
bool isMobile = Regex.IsMatch(dr["手机"].ToString(), reg);
if (!isMobile)
{
strEror += "手机号码格式不对" + ",";
}
}
if (!string.IsNullOrEmpty(dr["电话"].ToString()))
{
//验证电话格式
string reg = @"^(0[0-9]{2,3}-)?([2-9][0-9]{6,7})+(-[0-9]{1,4})?$";
string regMobile = @"^1[3|4|5|8][0-9]\d{4,8}$";
bool isTelephone = Regex.IsMatch(dr["电话"].ToString(), reg);
bool isMobile = Regex.IsMatch(dr["电话"].ToString(), regMobile);
if (!isTelephone && !isMobile)
{
strEror += "电话号码格式不对" + ",";
}
}
if (string.IsNullOrEmpty(dr["手机"].ToString()) && string.IsNullOrEmpty(dr["电话"].ToString()))
{
strEror += "手机或电话不能为空" + ",";
}
else
{
Model_DX_Customer IsHaveCustomer = new Model_DX_Customer();
IsHaveCustomer.Mobile = dr["手机"].ToString();
IsHaveCustomer.Telephone = dr["电话"].ToString();
bool isHave = Customer.BllIsHave(IsHaveCustomer);
if (isHave)
{
strEror += "该客户已存在" + ",";
}
else
{
if (!string.IsNullOrEmpty(dr["手机"].ToString()))
{
DataRow[] drExcel = dtExcel.Select("手机='" + dr["手机"].ToString() + "' or 电话='" + dr["手机"].ToString() + "'");
if (drExcel.Length > 1)
{
strEror += "该客户在Excel中出现重复" + ",";
}
}
if (!string.IsNullOrEmpty(dr["电话"].ToString()) && !strEror.Contains("Excel"))
{
DataRow[] drExcel = dtExcel.Select("手机='" + dr["电话"].ToString() + "' or 电话='" + dr["电话"].ToString() + "'");
if (drExcel.Length > 1)
{
strEror += "该客户在Excel中出现重复" + ",";
}
}
}
}
if (!string.IsNullOrEmpty(dr["邮箱"].ToString()))
{
//验证电话格式
string reg = @"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$";
bool isEmail = Regex.IsMatch(dr["邮箱"].ToString(), reg);
if (!isEmail)
{
strEror += "邮箱号码格式不对" + ",";
}
}
if (!string.IsNullOrEmpty(dr["区"].ToString()))
{
DataRow[] drRegion = dtRegion.Select("Name='" + dr["区"].ToString() + "'");
if (drRegion.Length > 0)
{
dr["区ID"] = drRegion[0]["ID"].ToString();
DataRow[] drDistrictID = dtRegion.Select("ID=" + drRegion[0]["FID"].ToString());
dr["市ID"] = drDistrictID[0]["ID"].ToString();
DataRow[] drProvinceID = dtRegion.Select("ID=" + drDistrictID[0]["FID"].ToString());
dr["省ID"] = drProvinceID[0]["ID"].ToString();
}
else
{
strEror += "系统中不存在该区,";
}
}
if (!string.IsNullOrEmpty(dr["市"].ToString()))
{
DataRow[] drRegion = dtRegion.Select("Name='" + dr["市"].ToString() + "'");
if (drRegion.Length > 0)
{
dr["市ID"] = drRegion[0]["ID"].ToString();
DataRow[] drProvinceID = dtRegion.Select("ID=" + drRegion[0]["FID"].ToString());
dr["省ID"] = drProvinceID[0]["ID"].ToString();
}
else
{
strEror += "系统中不存在该市,";
}
}
if (!string.IsNullOrEmpty(dr["省"].ToString()))
{
DataRow[] drRegion = dtRegion.Select("Name='" + dr["省"].ToString() + "'");
if (drRegion.Length > 0)
{
dr["省ID"] = drRegion[0]["ID"].ToString();
}
else
{
strEror += "系统中不存在该省,";
}
}
dtRegion.Dispose();
if (string.IsNullOrEmpty(strEror))
{
dr["数据是否正确"] = "数据正确";
//判断是否是保存
if (isSave)
{
Model_DX_Customer ModelCustomer = new Model_DX_Customer();
ModelCustomer.CustomerName = dr["姓名"].ToString();
ModelCustomer.Sex = dr["性别"].ToString();
ModelCustomer.Province = string.IsNullOrEmpty(dr["省ID"].ToString()) ? 0 : Convert.ToInt32(dr["省ID"].ToString());
ModelCustomer.City = string.IsNullOrEmpty(dr["市ID"].ToString()) ? 0 : Convert.ToInt32(dr["市ID"].ToString());
ModelCustomer.District = string.IsNullOrEmpty(dr["区ID"].ToString()) ? 0 : Convert.ToInt32(dr["区ID"].ToString());
ModelCustomer.Address = dr["地址"].ToString();
ModelCustomer.Company = dr["单位"].ToString();
ModelCustomer.Mobile = dr["手机"].ToString();
ModelCustomer.Telephone = dr["电话"].ToString();
ModelCustomer.Email = dr["邮箱"].ToString();
ModelCustomer.Flag = -1;
ModelCustomer.IsHignSeas = 0;
lisCustomer.Add(ModelCustomer);
}
}
else
{
dr["数据是否正确"] = strEror.Substring(0, strEror.Length - 1);
}
}
#endregion
BaoxianList.DataSource = dtExcel;
BaoxianList.DataBind();
operate.Visible = true;
Table.Visible = false;
tablePutInSource.Visible = true;
}
}
catch (Exception ex)
{
WebUility.alert(ex.Message);
}
}
/// <summary>
/// 将Excel的数据显示到Repeater中
/// </summary>
/// <param name="filename"></param>
/// <param name="isSave">是否为保存</param>
public void GetData(string filename, bool isSave)
{
try
{
//连接Excel字符串
OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../ITMAN_DianXiao/Excel/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"");
_ExcelConn.Open();
DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
OleDbDataAdapter adp = new OleDbDataAdapter("select * from [Sheet1$]", _ExcelConn);
DataSet dsExcel = new DataSet();
adp.Fill(dsExcel);
_ExcelConn.Close();
if (_Table.Rows[0]["TABLE_NAME"].ToString().Equals("Sheet1"))
{
WebUility.alert("Excel中没有Sheet1页签,请下载模板!");
return;
}
DataTable dtExcelImport = dsExcel.Tables[0];
#region 判断是否存在所需的列
string strIsColumns = string.Empty;
if (!dtExcelImport.Columns.Contains("姓名"))
{
strIsColumns += "该Excel中没有姓名列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("性别"))
{
strIsColumns += "该Excel中没有性别列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("省"))
{
strIsColumns += "该Excel中没有省列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("市"))
{
strIsColumns += "该Excel中没有市列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("区"))
{
strIsColumns += "该Excel中没有区列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("地址"))
{
strIsColumns += "该Excel中没有地址列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("单位"))
{
strIsColumns += "该Excel中没有单位列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("手机"))
{
strIsColumns += "该Excel中没有手机列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("电话"))
{
strIsColumns += "该Excel中没有电话列\\r\\n";
}
if (!dtExcelImport.Columns.Contains("邮箱"))
{
strIsColumns += "该Excel中没有邮箱列\\r\\n";
}
if (!string.IsNullOrEmpty(strIsColumns))
{
strIsColumns += "请下载模板";
WebUility.alert(strIsColumns);
return;
}
#endregion
#region
DataTable dtExcel = dtExcelImport.Clone();
dtExcel.Columns["手机"].DataType = typeof(string);
dtExcel.Columns["电话"].DataType = typeof(string);
foreach (DataRow drExcelImport in dtExcelImport.Rows)
{
DataRow drExcel = dtExcel.NewRow();
drExcel["姓名"] = drExcelImport["姓名"].ToString();
drExcel["性别"] = drExcelImport["性别"].ToString();
drExcel["省"] = drExcelImport["省"].ToString();
drExcel["市"] = drExcelImport["市"].ToString();
drExcel["区"] = drExcelImport["区"].ToString();
drExcel["地址"] = drExcelImport["地址"].ToString();
drExcel["单位"] = drExcelImport["单位"].ToString();
drExcel["手机"] = drExcelImport["手机"].ToString();
drExcel["电话"] = drExcelImport["电话"].ToString();
drExcel["邮箱"] = drExcelImport["邮箱"].ToString();
dtExcel.Rows.Add(drExcel);
}
#endregion