/// <summary>
/// XLS文件导入到数据库中
/// </summary>
/// <param name="fu">文件流</param>
/// <param name="tableName"></param>
/// <param name="msgList"></param>
/// <param name="errMsg"></param>
public bool ImportXls2Db(FileUpload fu, string tableName, ref string msgList, ref string errMsg)
{
//1.将文件上传到服务器
string filePath = string.Empty;
if (Trans2ServerPath(fu, ref filePath, ref errMsg))
{
//2.分析服务器端的文件是否符合规范，如扩展名、文件大小、是否已经被打开等等。
if (!CheckFileValid(fu, ref errMsg))
{
return(false);
}
//3.将文件导入内存中的DT中
DataSet ds = GetExcel(filePath, fu, ref errMsg);
if (!string.IsNullOrEmpty(errMsg))
{
errMsg = (string.Format("DialogboxShow(\"{0}\")", errMsg));
return(false);
}
if (ds != null && ds.Tables.Count > 0)
{
DataTable dataTable = ds.Tables[0];
int i = dataTable.Rows.Count * dataTable.Columns.Count;
//4.在内存中对数据做Check
var errInfos = new List <ErrInfo>(i);
var rule = new ImportItemsRule();
//不需要导入部分是否包含该字段
DataTable inImportFileButNotImportDT = rule.GetImportItemsDTByColType(tableName, ((int)PageConst.ImportItemTypeEnum.InImportFileButNotImport).ToString());
//从导入文件中获取数据的字段列表
DataTable fromImportItemsDT = rule.GetImportItemsDTByColType(tableName, ((int)PageConst.ImportItemTypeEnum.FromImportFile).ToString());
//从默认值中获取数据的字段列表
DataTable fromDefaulValueItemsDT = rule.GetImportItemsDTByColType(tableName, ((int)PageConst.ImportItemTypeEnum.FromDefaultValue).ToString());
bool s = CheckDataValid(ds, tableName, ref errInfos, ref errMsg, ref fromImportItemsDT, ref fromDefaulValueItemsDT, ref inImportFileButNotImportDT);
if (s)
{
if (!string.IsNullOrEmpty(errMsg))
{
errMsg = (string.Format("DialogboxShow(\"{0}\")", errMsg));
return(false);
}
s = ImportDate(tableName, dataTable, fromImportItemsDT, fromDefaulValueItemsDT, ref errInfos);
}
if (errInfos.Count > 0)
{
var scriptSerializer = new JavaScriptSerializer();
msgList = scriptSerializer.Serialize(errInfos);
}
return(s);
}
}
else
{
if (!string.IsNullOrEmpty(errMsg))
{
errMsg = (string.Format("DialogboxShow(\"{0}\")", errMsg));
return(false);
}
}
return(false);
}
/// <summary>
/// 检测数据是否合法
/// </summary>
/// <param name="csvDataDR"></param>
/// <param name="tableName"></param>
/// <param name="fromCsvItemsDT"></param>
/// <param name="fromDefaulValueItemsDT"></param>
/// <param name="rowNo"></param>
/// <param name="listErrInfo"></param>
/// <param name="msg"></param>zzzz
/// <returns></returns>
public static bool CheckRowData(DataRow csvDataDR, string tableName, DataTable fromCsvItemsDT, DataTable fromDefaulValueItemsDT, string rowNo, ref List <ErrInfo> listErrInfo, ref string msg)
{
bool retVal = true;
#region 数据重复Check
DataTable dt = new ImportItemsRule().GetImportItemsDtbyPk(fromCsvItemsDT.Rows[0]["ObjName"].ToString());
string sql = string.Format("select * from {0} where ", tableName);
bool sqlError = false;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["ColType"].ToString().Equals(((int)PageConst.ImportItemTypeEnum.FromImportFile).ToString()))
{
//导入文件中的数据
sql += string.Format("{0}='{1}' and ", dr["xColumn"].ToString().Trim(), csvDataDR[dr["CName"].ToString().Trim()]);
}
else
{
//默认值数据
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(dr["DataSource"].ToString().Replace("\"", "'"), csvDataDR);
try
{
DataTable pDT = DBAccess.DbHelperSQL.QueryBySQL(pDataSource);
if (pDT.Rows.Count > 0)
{
sql += string.Format("{0}='{1}' and ", dr["xColumn"].ToString().Trim(), pDT.Rows[0][0].ToString().Trim());
}
else
{
sqlError = true;
}
}
catch (Exception ex)
{
sqlError = true;
msg = ex.Message;
}
}
}
if (sqlError == false)
{
dt = DBAccess.DbHelperSQL.QueryBySQL(sql.Trim().Substring(0, sql.Length - 4));
if (dt.Rows.Count > 0)
{
var paras = new List <string>(1);
//主键重复
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataHasExist,
ErrMsg = GetMessage("700002", paras),
ErrData = GetImportFileRowData(csvDataDR)
};
listErrInfo.Add(er);
return(false);
}
}
else
{
//SQL执行错误
var paras = new List <string>(1);
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataIsValid,
ErrMsg = GetMessage("700010", paras),
ErrData = GetImportFileRowData(csvDataDR)
};
listErrInfo.Add(er);
retVal = false;
}
}
#endregion
//检测导入文件中的字段是否符合规范
foreach (DataRow dr in fromCsvItemsDT.Rows)
{
#region 模板文件中不存在该列
if (!CheckItemExist(dr["CName"].ToString(), csvDataDR))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.ItemNotExist,
ErrMsg = GetMessage("700011", paras),
ErrData = dr["CName"].ToString()
};
listErrInfo.Add(er);
return(false);
}
#endregion
#region 找不到符合条件的数据，一般出现在数据替换的时候
if (!string.IsNullOrEmpty(dr["DataSource"].ToString()))
{
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(dr["DataSource"].ToString().Replace("\"", "'"), csvDataDR);
DataTable dt2 = DBAccess.DbHelperSQL.QueryBySQL(pDataSource);
if (dt2.Rows.Count <= 0)
{
var paras = new List <string>(2)
{
csvDataDR[dr["CName"].ToString()].ToString(),
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.KeyDataNotExist,
ErrMsg = GetMessage("700012", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
}
}
#endregion
#region 检测数据类型是否正确
if (dr["DataType"].ToString().ToLower().StartsWith("varchar") ||
dr["DataType"].ToString().ToLower().StartsWith("char") ||
dr["DataType"].ToString().ToLower().StartsWith("nvarchar"))
{
//获取长度
int left = dr["DataType"].ToString().ToLower().IndexOf('(') + 1;
int right = dr["DataType"].ToString().ToLower().IndexOf(')');
int length = Convert.ToInt32(dr["DataType"].ToString().ToLower().Substring(left, right - left));
#region Check字符窜长度是否超出范围
if (PageHelper.CheckCharIsLong(csvDataDR[dr["CName"].ToString()].ToString(), length))
{
var paras = new List <string>(2)
{
dr["CName"].ToString(), length.ToString()
};
int halfLength = length / 2;
paras.Add(halfLength.ToString());
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataIsTooLarge,
ErrMsg = GetMessage("700004", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("int"))
{
#region Check整数
if (!PageHelper.CheckIsInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
//不是整数时
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700005", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("decimal"))
{
//Check高精度浮点数
string subStr = dr["DataType"].ToString().ToLower().Substring(dr["DataType"].ToString().ToLower().IndexOf("(") + 1).TrimEnd(')');
string[] strs = subStr.Split(',');
int decimalPart = Convert.ToInt32(strs[1]);
int integerPart = Convert.ToInt32(strs[0]) - decimalPart;
#region Check是否为Decimal类型
if (!PageHelper.CheckIsDecimal(csvDataDR[dr["CName"].ToString()].ToString()))
{
//不是高精度浮点数类型
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700007", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
#region Check高精度型的位数是否正确
else
{
//是高精度浮点数，但超出范围
if (PageHelper.CheckDecimalIsBigger(csvDataDR[dr["CName"].ToString()].ToString(), integerPart, decimalPart))
{
//不是高精度浮点数类型
var paras = new List <string>(3)
{
dr["CName"].ToString(),
integerPart.ToString(),
decimalPart.ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700008", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("datetime"))
{
#region Check日期格式
if (!PageHelper.CheckIsDateTime(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700009", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
#endregion
#region 检测是否符合某些验证规范
if (!String.IsNullOrEmpty(dr["CheckMethod"].ToString()))
{
string[] methods = dr["CheckMethod"].ToString().Split(';');
bool hasChecked = false;
foreach (string method in methods)
{
if (hasChecked)
{
break;
}
switch (method)
{
#region 检测是否不为空
case "CheckIsNotEmpty":
if (!PageHelper.CheckIsNotEmpty(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700014", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为整数
case "CheckIsInteger":
if (!PageHelper.CheckIsInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700005", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为数字类型
case "CheckIsDecimal":
if (!PageHelper.CheckIsDecimal(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700007", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为字符类型
case "CheckIsChar":
if (!PageHelper.CheckIsChar(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700015", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为数字字母类型
case "CheckIsNumChar":
if (!PageHelper.CheckIsNumChar(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700016", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为邮件类型
case "CheckIsEmail":
if (!PageHelper.CheckIsEmail(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700017", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为日期类型
case "CheckIsDate":
if (!PageHelper.CheckIsDateTime(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700009", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为有效的身份证号码
case "CheckIsIdCardNo":
if (!PageHelper.CheckIsIdCardNo(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700018", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为正数
case "ChechIsSignless":
if (!PageHelper.ChechIsSignless(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700019", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为有效的手机号码
case "CheckIsMobile":
if (!PageHelper.CheckIsMobile(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700020", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为正整数类型
case "CheckIsSignlessInteger":
if (!PageHelper.CheckIsSignlessInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700021", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为中文类型
case "isChinese":
if (!PageHelper.IsChinese(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700023", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为非负数
case "CheckIsNonnegativeInteger":
if (!PageHelper.CheckIsNonnegativeInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700022", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
}
}
}
#endregion
}
return(retVal);
}