private string GetLinkValue(SqlConnection conn,
ExcelLoadColumnConfiguration xlcc,
string str)
{
using (SqlCommand cmd1 = new SqlCommand(string.Format("select " +
xlcc.LinkSelColName +
" from " + xlcc.LinkTblName +
" where " + xlcc.LinkWhColName + "={0}", str), conn))
{
object obj = cmd1.ExecuteScalar();
if (obj == null)
{
throw new DXInfo.Models.BusinessException("导入的文件中：" + str + xlcc.XlsColName + "不存在，请先添加该" + xlcc.XlsColName);
}
return(obj.ToString());
}
}
private void CheckTbl(DataTable table,
ExcelLoadColumnConfiguration prixlcc)
{
//判断条码重复
var groupedData = (from b in table.AsEnumerable()
group b by b.Field <string>(prixlcc.XlsColName) into g
select new
{
Code = g.Key,
Count = g.Count(),
}).ToList().Where(w => w.Count > 1).Select(s => s.Code).ToList();
if (groupedData.Count > 0)
{
string retstr = groupedData.Aggregate(delegate(string str1, string str2) { return(str1 + str2); });
throw new DXInfo.Models.BusinessException("以下" + prixlcc.XlsColName + "重复：" + retstr);
}
}
public void InitConf(string path)
{
XmlSerializer xmlSerializer = new System.Xml.Serialization.XmlSerializer(typeof(DXInfo.Models.ExcelLoadConfiguration));
ExcelLoadConfiguration xlc = new ExcelLoadConfiguration();
xlc.lTable = new List <ExcelLoadTableConfiguration>();
ExcelLoadTableConfiguration xltc = new ExcelLoadTableConfiguration();
xltc.lColumn = new List <ExcelLoadColumnConfiguration>();
ExcelLoadColumnConfiguration xlcc = new ExcelLoadColumnConfiguration();
xltc.lColumn.Add(xlcc);
xlc.lTable.Add(xltc);
using (FileStream fs = new FileStream(path, FileMode.Create))
{
xmlSerializer.Serialize(fs, xlc);
}
}
private int GetCount(SqlConnection conn,
ExcelLoadColumnConfiguration prixlcc,
string privalue)
{
using (SqlCommand cmd2 = new SqlCommand("select count(1) from " +
prixlcc.DbColName + "=" + privalue, conn))
{
object obj2 = cmd2.ExecuteScalar();
int count = 0;
if (obj2 != null)
{
count = Convert.ToInt32(obj2);
if (count > 1)
{
throw new BusinessException(prixlcc.XlsColName + "为：" + privalue + "记录重复");
}
}
return(count);
}
}
public void LoadExcel(string connStr,
string savePath,
string defaultValue,
string configurationPath,
string tableName)
{
XmlSerializer xmlSerializer = new System.Xml.Serialization.XmlSerializer(typeof(DXInfo.Models.ExcelLoadConfiguration));
ExcelLoadConfiguration xlc;
using (FileStream fs = new FileStream(configurationPath, FileMode.Open))
{
xlc = (ExcelLoadConfiguration)xmlSerializer.Deserialize(fs);
}
if (xlc == null || xlc.lTable == null || xlc.lTable.Count == 0)
{
throw new DXInfo.Models.BusinessException("无配置文件");
}
List <ExcelLoadTableConfiguration> lTable = xlc.lTable.Where(w => w.DbTblName == tableName).ToList();
if (lTable == null || lTable.Count != 1)
{
throw new DXInfo.Models.BusinessException("表设置只能有且必须有一个");
}
ExcelLoadTableConfiguration xltc = lTable[0];
if (xltc == null || xltc.lColumn == null || xltc.lColumn.Count == 0)
{
throw new DXInfo.Models.BusinessException("无表或者列设置");
}
List <ExcelLoadColumnConfiguration> lPriColumn = xltc.lColumn.Where(w => w.IsPrimary).ToList();
if (lPriColumn == null || lPriColumn.Count != 1)
{
throw new DXInfo.Models.BusinessException("主键设置不正确，主键只能有一个且必须一个");
}
ExcelLoadColumnConfiguration prixlcc = lPriColumn[0];
DataTable table = GetXlsData(savePath, xltc.XlsTblName);
CheckTbl(table, prixlcc);
try
{
List <string> lSql = new List <string>();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
foreach (DataRow dr in table.Rows)
{
string sql = GetSql(conn, dr, xltc, prixlcc, defaultValue);
if (!string.IsNullOrEmpty(sql))
{
lSql.Add(sql);
}
}
}
if (lSql.Count > 0)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
SqlTransaction trans = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
foreach (string sql in lSql)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
throw new BusinessException(ex.InnerException.InnerException.Message);
}
throw new BusinessException(ex.InnerException.Message);
}
throw new BusinessException(ex.Message);
}
}
private string GetSql(SqlConnection conn,
DataRow dr,
ExcelLoadTableConfiguration xltc,
ExcelLoadColumnConfiguration prixlcc,
string defaultValue)
{
string privalue = dr[prixlcc.XlsColName].ToString();
if (string.IsNullOrEmpty(privalue))
{
throw new Exception("请录入" + prixlcc.XlsColName);
}
int count = GetCount(conn, prixlcc, privalue);
string sql1 = string.Empty,
sql2 = string.Empty,
sql3 = string.Empty,
sql4 = string.Empty,
sql5 = string.Empty,
sql = string.Empty;
if (count == 1)
{
//更新
sql1 = "update " + xltc.DbTblName + "set ";
if (!prixlcc.IsChar)
{
sql3 = string.Format("where {0}={1}", prixlcc.DbColName, privalue);
}
else
{
sql3 = string.Format("where {0}='{1}'", prixlcc.DbColName, privalue);
}
foreach (ExcelLoadColumnConfiguration xlcc in xltc.lColumn)
{
//if (!string.IsNullOrEmpty(xlcc.DefaultValueDbCol))
//{
// defaultValue = xltc.lColumn.Where(w => w.DbColName == xlcc.DefaultValueDbCol).Select(s => s.Value).FirstOrDefault();
//}
string value = CheckCol(dr, conn, prixlcc, xlcc, defaultValue);
//xlcc.Value = value;
if (!xlcc.IsChar)
{
sql2 += string.Format("{0}={1},", xlcc.DbColName, value);
}
else
{
sql2 += string.Format("{0}='{1}',", xlcc.DbColName, value);
}
//inv.InvType = 2;
}
sql = sql1 + sql2 + sql3;
}
else
{
//插入
sql1 = "insert into " + xltc.DbTblName + "(";
sql2 = "";
sql3 = ")values(";
sql4 = "";
sql5 = ")";
foreach (ExcelLoadColumnConfiguration xlcc in xltc.lColumn)
{
sql2 += xlcc.DbColName + ",";
string value = CheckCol(dr, conn, prixlcc, xlcc, defaultValue);
if (!xlcc.IsChar)
{
sql4 += value + ",";
}
else
{
sql4 += string.Format("'{0}',", value);
}
//inv.InvType = 2;
}
sql = sql1 + sql2 + sql3 + sql4 + sql5;
}
return(sql);
}
private string CheckCol(DataRow dr,
SqlConnection conn,
ExcelLoadColumnConfiguration prixlcc,
ExcelLoadColumnConfiguration xlcc,
string defaultValue)
{
string value = "";
string str = "";
if (!string.IsNullOrEmpty(xlcc.XlsColName))
{
str = dr[xlcc.XlsColName].ToString();
}
if (xlcc.IsLinkCol)
{
if (!string.IsNullOrEmpty(str))
{
value = GetLinkValue(conn, xlcc, str);
}
}
else
{
//value = str;
if (xlcc.IsDefaultValue)
{
if (string.IsNullOrEmpty(str))
{
if (!string.IsNullOrEmpty(xlcc.DefaultValueXlsCol))
{
str = dr[xlcc.DefaultValueXlsCol].ToString() + xlcc.DefaultValue;
}
else
{
str = xlcc.DefaultValue;
}
}
}
if (xlcc.IsBoolean)
{
if (string.IsNullOrEmpty(str))
{
str = "0";
}
else
{
str = str == xlcc.TrueValue ? "1" : "0";
}
}
value = string.Format(str, defaultValue);
string myreg = "";
switch (myreg)
{
case "ImageFileName":
myreg = DXInfo.Models.MyReg.ImageFileName;
break;
case "PlusNumber":
myreg = DXInfo.Models.MyReg.PlusNumber;
break;
}
if (!Regex.IsMatch(value, myreg))
{
throw new DXInfo.Models.BusinessException(prixlcc.XlsColName + "为：" + value + "的【" + xlcc.XlsColName + "】的格式不正确。");
}
}
return(value);
}