From fbba6d6e8ccf4b052735bd51fd04ff7cb5c16b78 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 19 九月 2023 17:32:30 +0800
Subject: [PATCH] 设备类型接口修改、导入问题修改、缺陷定义接口开发
---
VueWebCoreApi/Tools/ImportExcelData.cs | 395 +++++++++++++++++++++++++++-----------------------------
1 files changed, 191 insertions(+), 204 deletions(-)
diff --git a/VueWebCoreApi/Tools/ImportExcelData.cs b/VueWebCoreApi/Tools/ImportExcelData.cs
index 688d743..8642f8b 100644
--- a/VueWebCoreApi/Tools/ImportExcelData.cs
+++ b/VueWebCoreApi/Tools/ImportExcelData.cs
@@ -227,7 +227,7 @@
return Message;
}
- else if (excelTable[0].Columns.Count != 6)
+ else if (excelTable[0].Columns.Count != 4)
{
code = "301";
Message = "瑙掕壊娓呭崟妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
@@ -251,22 +251,10 @@
Message = "瑙掕壊娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�3鍒楀簲涓簕*鐘舵�亇";
return Message;
}
- else if (excelTable[0].Columns[3].ColumnName != "鏁版嵁鑼冨洿")
+ else if (excelTable[0].Columns[3].ColumnName != "鎻忚堪")
{
code = "301";
- Message = "瑙掕壊娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕鏁版嵁鑼冨洿}";
- return Message;
- }
- else if (excelTable[0].Columns[4].ColumnName != "鏁版嵁鏉冮檺")
- {
- code = "301";
- Message = "瑙掕壊娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�5鍒楀簲涓簕鏁版嵁鏉冮檺}";
- return Message;
- }
- else if (excelTable[0].Columns[5].ColumnName != "鎻忚堪")
- {
- code = "301";
- Message = "瑙掕壊娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�6鍒楀簲涓簕鎻忚堪}";
+ Message = "瑙掕壊娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕鎻忚堪}";
return Message;
}
else
@@ -464,10 +452,10 @@
Message = "搴撲綅璁剧疆妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�3鍒楀簲涓簕*鎵�灞炰粨搴撶紪鐮亇";
return Message;
}
- else if (excelTable[0].Columns[3].ColumnName != "*涓婄骇搴撲綅缂栫爜")
+ else if (excelTable[0].Columns[3].ColumnName != "涓婄骇搴撲綅缂栫爜")
{
code = "301";
- Message = "搴撲綅璁剧疆妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕*涓婄骇搴撲綅缂栫爜}";
+ Message = "搴撲綅璁剧疆妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕涓婄骇搴撲綅缂栫爜}";
return Message;
}
else if (excelTable[0].Columns[4].ColumnName != "*鐘舵��")
@@ -509,7 +497,7 @@
Message = "瀛樿揣鍒嗙被妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
return Message;
}
- else if (excelTable[1].Columns.Count != 12)
+ else if (excelTable[1].Columns.Count != 13)
{
code = "301";
Message = "瀛樿揣妗ｆ妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
@@ -521,10 +509,10 @@
Message = "瀛樿揣鍒嗙被鍨嬫ā鏉匡細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�1鍒楀簲涓簕*瀛樿揣鍒嗙被缂栧彿(鍞竴)}";
return Message;
}
- else if (excelTable[0].Columns[1].ColumnName != "*瀛樿揣鍒嗙被鍨嬪悕绉�")
+ else if (excelTable[0].Columns[1].ColumnName != "*瀛樿揣鍒嗙被鍚嶇О")
{
code = "301";
- Message = "瀛樿揣鍒嗙被鍨嬫ā鏉匡細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�2鍒楀簲涓簕*瀛樿揣鍒嗙被鍨嬪悕绉皚";
+ Message = "瀛樿揣鍒嗙被鍨嬫ā鏉匡細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�2鍒楀簲涓簕*瀛樿揣鍒嗙被鍚嶇О}";
return Message;
}
else if (excelTable[0].Columns[2].ColumnName != "涓婄骇缂栫爜")
@@ -569,10 +557,10 @@
Message = "瀛樿揣妗ｆ妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�6鍒楀簲涓簕*璁￠噺鏂瑰紡}";
return Message;
}
- else if (excelTable[1].Columns[6].ColumnName != "*璁￠噺鍗曚綅/缁勭紪鐮�")
+ else if (excelTable[1].Columns[6].ColumnName != "*璁￠噺鍗曚綅/缁勫悕绉�")
{
code = "301";
- Message = "瀛樿揣妗ｆ妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�7鍒楀簲涓簕*璁￠噺鍗曚綅/缁勭紪鐮亇";
+ Message = "瀛樿揣妗ｆ妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�7鍒楀簲涓簕*璁￠噺鍗曚綅/缁勫悕绉皚";
return Message;
}
else if (excelTable[1].Columns[7].ColumnName != "鎵�灞炰粨搴撶紪鐮�")
@@ -1626,47 +1614,39 @@
#endregion
#region銆怑xcel妯℃澘涓婁紶楠岃瘉,缂洪櫡瀹氫箟妯℃澘銆�
- public static string Eighteen(string FileCode, out string code)
+ public static string Eighteen(List<DataTable> excelTable, out string code)
{
string Message = "";
code = "";
- List<DataTable> excelTable = new List<DataTable>();
- excelTable = ImportExcel.ExcelToTableList(FileCode);
- if (excelTable.Count != 2)
+ if (excelTable.Count != 1)
{
- code = "300";
+ code = "301";
Message = "瀵煎叆妯℃澘涓嶇鍚堣鑼�,璇锋鏌heet鏁�";
return Message;
}
- else if (excelTable[0].Columns.Count != 4)
+ else if (excelTable[0].Columns.Count != 3)
{
- code = "300";
+ code = "301";
Message = "缂洪櫡瀹氫箟妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
return Message;
}
- else if (excelTable[0].Columns[0].ColumnName != "搴忓彿")
+ else if (excelTable[0].Columns[0].ColumnName != "*缂洪櫡缂栧彿(鍞竴)")
{
- code = "300";
- Message = "缂洪櫡瀹氫箟妯℃澘涓嶇鍚堣鑼冿細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�1鍒楀簲涓簕搴忓彿}";
+ code = "301";
+ Message = "缂洪櫡瀹氫箟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�1鍒楀簲涓簕*缂洪櫡缂栧彿(鍞竴)}";
return Message;
}
- else if (excelTable[0].Columns[1].ColumnName != "缂洪櫡缂栧彿(鍞竴)")
+ else if (excelTable[0].Columns[1].ColumnName != "*缂洪櫡鍚嶇О")
{
- code = "300";
- Message = "缂洪櫡瀹氫箟妯℃澘涓嶇鍚堣鑼冿細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�2鍒楀簲涓簕缂洪櫡缂栧彿(鍞竴)}";
+ code = "301";
+ Message = "缂洪櫡瀹氫箟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�2鍒楀簲涓簕*缂洪櫡鍚嶇О}";
return Message;
}
- else if (excelTable[0].Columns[2].ColumnName != "缂洪櫡鍚嶇О")
+ else if (excelTable[0].Columns[2].ColumnName != "缂洪櫡鎻忚堪")
{
- code = "300";
- Message = "缂洪櫡瀹氫箟妯℃澘涓嶇鍚堣鑼冿細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�3鍒楀簲涓簕缂洪櫡鍚嶇О}";
- return Message;
- }
- else if (excelTable[0].Columns[3].ColumnName != "缂洪櫡鎻忚堪")
- {
- code = "300";
- Message = "缂洪櫡鎻忚堪妯℃澘涓嶇鍚堣鑼冿細琛ㄥご淇℃伅涓嶇鍚堣鑼�,绗�4鍒楀簲涓簕缂洪櫡鎻忚堪}";
+ code = "301";
+ Message = "缂洪櫡瀹氫箟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�3鍒楀簲涓簕缂洪櫡鎻忚堪}";
return Message;
}
else
@@ -1988,7 +1968,7 @@
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
erro.ErrorField = "{*宀椾綅鍚嶇О}";
- erro.ErrorCont = "宀椾綅琛�:{*宀椾綅鍚嶇О}瀛楁" + excelTable[1].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
+ erro.ErrorCont = "宀椾綅琛�:{*宀椾綅鍚嶇О}瀛楁" + excelTable[0].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
list.Add(erro);
}
}
@@ -2052,7 +2032,7 @@
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
erro.ErrorField = "{*鐝粍鍚嶇О}";
- erro.ErrorCont = "鐝粍琛�:{*鐝粍鍚嶇О}瀛楁" + excelTable[1].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
+ erro.ErrorCont = "鐝粍琛�:{*鐝粍鍚嶇О}瀛楁" + excelTable[0].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
list.Add(erro);
}
}
@@ -2251,7 +2231,7 @@
DataTable dt;
List<ExcelErro> list = new List<ExcelErro>();
list = ImportExcel.InportExcelToTableListErro(excelTable); //楠岃瘉Excel鏁版嵁蹇呭～瀛楁鏄惁涓虹┖銆佸敮涓�瀛楁鏄惁閲嶅
- //鐝粍琛�
+ //瑙掕壊琛�
for (int k = 0; k < excelTable[0].Rows.Count; k++)
{
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
@@ -2278,56 +2258,56 @@
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
erro.ErrorField = "{*瑙掕壊鍚嶇О}";
- erro.ErrorCont = "瑙掕壊琛�:{*瑙掕壊鍚嶇О}瀛楁" + excelTable[1].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
+ erro.ErrorCont = "瑙掕壊琛�:{*瑙掕壊鍚嶇О}瀛楁" + excelTable[0].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
list.Add(erro);
}
}
- if (excelTable[0].Rows[k][3].ToString().Trim() != null && excelTable[0].Rows[k][3].ToString().Trim() != "")
- {
- if (excelTable[0].Rows[k][3].ToString().Trim() == "鑷畾涔�")
- {
- if (excelTable[0].Rows[k][4].ToString().Trim() == null && excelTable[0].Rows[k][4].ToString().Trim() == "")
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{鏁版嵁鏉冮檺}";
- erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鑼冨洿}瀛楁涓猴細" + excelTable[1].Rows[k][3].ToString().Trim() + "鏃�,{鏁版嵁鏉冮檺}瀛楁涓嶈兘涓虹┖";
- list.Add(erro);
- }
- else
- {
- //鍒ゆ柇鏁版嵁鏉冮檺缂栫爜鏄惁鍚堢悊
- if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
- {
- string[] torgcode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][4].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
- sql = @"select postcode from TOrganization where torg_code in @torgcode";
- dynamicParams.Add("@torgcode", torgcode);
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt != null && dt.Rows.Count > 0)
- {
- string[] values = excelTable[0].Rows[k][4].ToString().Trim().Split(',');
- var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("torg_code")));
- foreach (var value in unmatchedValues)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = (k + 1).ToString();
- erro.ErrorField = "{鏁版嵁鏉冮檺}";
- erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鏉冮檺}瀛楁" + value + " 涓嶆槸鏈夋晥鐨勭粍缁囩紪鐮�";
- list.Add(erro);
- }
- }
- else
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = (k + 1).ToString();
- erro.ErrorField = "{鏁版嵁鏉冮檺}";
- erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鏉冮檺}瀛楁" + excelTable[0].Rows[k][4].ToString().Trim() + " 涓嶆槸鏈夋晥鐨勭粍缁囩紪鐮�";
- list.Add(erro);
- }
- }
- }
- }
- }
+ //if (excelTable[0].Rows[k][3].ToString().Trim() != null && excelTable[0].Rows[k][3].ToString().Trim() != "")
+ //{
+ // if (excelTable[0].Rows[k][3].ToString().Trim() == "鑷畾涔�")
+ // {
+ // if (excelTable[0].Rows[k][4].ToString().Trim() == null && excelTable[0].Rows[k][4].ToString().Trim() == "")
+ // {
+ // ExcelErro erro = new ExcelErro();
+ // erro.RoeNumber = "/";
+ // erro.ErrorField = "{鏁版嵁鏉冮檺}";
+ // erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鑼冨洿}瀛楁涓猴細" + excelTable[1].Rows[k][3].ToString().Trim() + "鏃�,{鏁版嵁鏉冮檺}瀛楁涓嶈兘涓虹┖";
+ // list.Add(erro);
+ // }
+ // else
+ // {
+ // //鍒ゆ柇鏁版嵁鏉冮檺缂栫爜鏄惁鍚堢悊
+ // if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
+ // {
+ // string[] torgcode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][4].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ // sql = @"select postcode from TOrganization where torg_code in @torgcode";
+ // dynamicParams.Add("@torgcode", torgcode);
+ // dt = DapperHelper.selectdata(sql, dynamicParams);
+ // if (dt != null && dt.Rows.Count > 0)
+ // {
+ // string[] values = excelTable[0].Rows[k][4].ToString().Trim().Split(',');
+ // var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("torg_code")));
+ // foreach (var value in unmatchedValues)
+ // {
+ // ExcelErro erro = new ExcelErro();
+ // erro.RoeNumber = (k + 1).ToString();
+ // erro.ErrorField = "{鏁版嵁鏉冮檺}";
+ // erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鏉冮檺}瀛楁" + value + " 涓嶆槸鏈夋晥鐨勭粍缁囩紪鐮�";
+ // list.Add(erro);
+ // }
+ // }
+ // else
+ // {
+ // ExcelErro erro = new ExcelErro();
+ // erro.RoeNumber = (k + 1).ToString();
+ // erro.ErrorField = "{鏁版嵁鏉冮檺}";
+ // erro.ErrorCont = "瑙掕壊琛�:{鏁版嵁鏉冮檺}瀛楁" + excelTable[0].Rows[k][4].ToString().Trim() + " 涓嶆槸鏈夋晥鐨勭粍缁囩紪鐮�";
+ // list.Add(erro);
+ // }
+ // }
+ // }
+ // }
+ //}
}
if (list.Count > 0)
{
@@ -2392,24 +2372,21 @@
list.Add(erro);
}
}
- if (excelTable[0].Rows[j][2].ToString().Trim() != null && excelTable[0].Rows[j][2].ToString().Trim() != "")
- {
- // 浣跨敤Lambda琛ㄨ揪寮忓垽鏂埗绾D鏄惁鍚堢悊
- bool isParentIdValid = excelTable[0].AsEnumerable()
- .Any(r => Convert.ToString(r["*寰�鏉ュ崟浣嶇被鍨嬬紪鍙�(鍞竴)"]) == excelTable[0].Rows[j][2].ToString().Trim()); // 鍋囪ID鍒楀悕涓篒d
-
- // 濡傛灉鐖剁骇ID涓嶅悎鐞嗭紝鍒欒緭鍑�
- if (!isParentIdValid)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{涓婄骇缂栫爜}";
- erro.ErrorCont = "寰�鏉ュ崟浣嶇被鍨嬭〃:{涓婄骇缂栫爜}瀛楁锛�" + excelTable[0].Rows[j][2].ToString().Trim() + " 涓嶅悎鐞�";
- list.Add(erro);
- }
- }
}
+ var invalidRows = excelTable[0].AsEnumerable().Where(row =>
+ row.Field<string>("涓婄骇缂栫爜") == row.Field<string>("*寰�鏉ュ崟浣嶇被鍨嬬紪鍙�(鍞竴)") ||
+ (row.Field<string>("涓婄骇缂栫爜") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*寰�鏉ュ崟浣嶇被鍨嬬紪鍙�(鍞竴)") == row.Field<string>("涓婄骇缂栫爜")))
+ );
+ // 杈撳嚭涓嶅悎鐞嗙殑琛�
+ foreach (var row in invalidRows)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{涓婄骇缂栫爜}";
+ erro.ErrorCont = $"寰�鏉ュ崟浣嶇被鍨嬭〃:*寰�鏉ュ崟浣嶇被鍨嬬紪鍙�(鍞竴)锛� {row.Field<string>("*寰�鏉ュ崟浣嶇被鍨嬬紪鍙�(鍞竴)")} 鐨勪笂绾х紪鐮侊細{row.Field<string>("涓婄骇缂栫爜")}涓嶅悎鐞�";
+ list.Add(erro);
+ }
//寰�鏉ュ崟浣嶈〃
for (int k = 0; k < excelTable[1].Rows.Count; k++)
@@ -2600,23 +2577,22 @@
list.Add(erro);
}
}
- if (excelTable[0].Rows[k][3].ToString().Trim() != null && excelTable[0].Rows[k][3].ToString().Trim() != "")
- {
- // 浣跨敤Lambda琛ㄨ揪寮忓垽鏂埗绾D鏄惁鍚堢悊
- bool isParentIdValid = excelTable[0].AsEnumerable()
- .Any(r => Convert.ToString(r["*搴撲綅缂栧彿(鍞竴)"]) == excelTable[0].Rows[k][3].ToString().Trim()); // 鍋囪ID鍒楀悕涓篒d
-
- // 濡傛灉鐖剁骇ID涓嶅悎鐞嗭紝鍒欒緭鍑�
- if (!isParentIdValid)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{涓婄骇搴撲綅缂栫爜}";
- erro.ErrorCont = "搴撲綅琛�:{涓婄骇搴撲綅缂栫爜}瀛楁锛�" + excelTable[0].Rows[k][3].ToString().Trim() + " 涓嶅悎鐞�";
- list.Add(erro);
- }
- }
}
+ var invalidRows = excelTable[0].AsEnumerable().Where(row =>
+ row.Field<string>("涓婄骇搴撲綅缂栫爜") == row.Field<string>("*搴撲綅缂栧彿(鍞竴)") ||
+ (row.Field<string>("涓婄骇搴撲綅缂栫爜") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*搴撲綅缂栧彿(鍞竴)") == row.Field<string>("涓婄骇搴撲綅缂栫爜")))
+ );
+
+ // 杈撳嚭涓嶅悎鐞嗙殑琛�
+ foreach (var row in invalidRows)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{涓婄骇搴撲綅缂栫爜}";
+ erro.ErrorCont = $"搴撲綅琛�:*搴撲綅缂栧彿(鍞竴)锛� {row.Field<string>("*搴撲綅缂栧彿(鍞竴)")} 鐨勪笂绾у簱浣嶇紪鐮侊細{row.Field<string>("涓婄骇搴撲綅缂栫爜")}涓嶅悎鐞�";
+ list.Add(erro);
+ }
+
if (list.Count > 0)
{
int index = 0;
@@ -2680,24 +2656,21 @@
list.Add(erro);
}
}
- if (excelTable[0].Rows[j][2].ToString().Trim() != null && excelTable[0].Rows[j][2].ToString().Trim() != "")
- {
- // 浣跨敤Lambda琛ㄨ揪寮忓垽鏂埗绾D鏄惁鍚堢悊
- bool isParentIdValid = excelTable[0].AsEnumerable()
- .Any(r => Convert.ToString(r["*瀛樿揣鍒嗙被缂栧彿(鍞竴)"]) == excelTable[0].Rows[j][2].ToString().Trim()); // 鍋囪ID鍒楀悕涓篒d
-
- // 濡傛灉鐖剁骇ID涓嶅悎鐞嗭紝鍒欒緭鍑�
- if (!isParentIdValid)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{涓婄骇缂栫爜}";
- erro.ErrorCont = "瀛樿揣鍒嗙被琛�:{涓婄骇缂栫爜}瀛楁锛�" + excelTable[0].Rows[j][2].ToString().Trim() + " 涓嶅悎鐞�";
- list.Add(erro);
- }
- }
}
+ var invalidRows = excelTable[0].AsEnumerable().Where(row =>
+ row.Field<string>("涓婄骇缂栫爜") == row.Field<string>("*瀛樿揣鍒嗙被缂栧彿(鍞竴)") ||
+ (row.Field<string>("涓婄骇缂栫爜") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*瀛樿揣鍒嗙被缂栧彿(鍞竴)") == row.Field<string>("涓婄骇缂栫爜")))
+ );
+ // 杈撳嚭涓嶅悎鐞嗙殑琛�
+ foreach (var row in invalidRows)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{涓婄骇缂栫爜}";
+ erro.ErrorCont = $"瀛樿揣绫诲瀷琛�:*瀛樿揣鍒嗙被缂栧彿(鍞竴)锛� {row.Field<string>("**瀛樿揣鍒嗙被缂栧彿(鍞竴)")} 鐨勪笂绾х紪鐮侊細{row.Field<string>("涓婄骇缂栫爜")}涓嶅悎鐞�";
+ list.Add(erro);
+ }
//瀛樿揣琛�
for (int k = 0; k < excelTable[1].Rows.Count; k++)
@@ -2730,7 +2703,7 @@
list.Add(erro);
}
}
- if (excelTable[1].Rows[k][5].ToString().Trim() != null && excelTable[5].Rows[k][1].ToString().Trim() != "")
+ if (excelTable[1].Rows[k][5].ToString().Trim() != null && excelTable[1].Rows[k][5].ToString().Trim() != "")
{
switch (excelTable[1].Rows[k][5].ToString().Trim())
{
@@ -2738,15 +2711,15 @@
//鍒ゆ柇璁￠噺鍗曚綅鏄惁涓哄崟璁￠噺
if (excelTable[1].Rows[k][6].ToString().Trim() != null && excelTable[1].Rows[k][6].ToString().Trim() != "")
{
- sql = @"select * from TUnit where code=@code and isSingleUnit='1'";
- dynamicParams.Add("@code", excelTable[1].Rows[k][6].ToString().Trim());
+ sql = @"select * from TUnit where name=@name and isSingleUnit='1'";
+ dynamicParams.Add("@name", excelTable[1].Rows[k][6].ToString().Trim());
dt = DapperHelper.selectdata(sql, dynamicParams);
if (dt.Rows.Count <= 0)
{
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
- erro.ErrorField = "{*璁￠噺鍗曚綅/缁勭紪鐮亇";
- erro.ErrorCont = "瀛樿揣妗ｆ琛�:{*璁￠噺鍗曚綅/缁勭紪鐮亇瀛楁" + excelTable[1].Rows[k][6].ToString().Trim() + "涓嶆槸鏈夋晥鐨勫崟璁￠噺鍗曚綅";
+ erro.ErrorField = "{*璁￠噺鍗曚綅/缁勫悕瀛梷";
+ erro.ErrorCont = "瀛樿揣妗ｆ琛�:{*璁￠噺鍗曚綅/缁勫悕瀛梷瀛楁" + excelTable[1].Rows[k][6].ToString().Trim() + "涓嶆槸鏈夋晥鐨勫崟璁￠噺鍗曚綅";
list.Add(erro);
}
}
@@ -2755,15 +2728,15 @@
//鍒ゆ柇璁￠噺鍗曚綅鏄惁涓哄璁￠噺
if (excelTable[1].Rows[k][6].ToString().Trim() != null && excelTable[1].Rows[k][6].ToString().Trim() != "")
{
- sql = @"select * from TUnitGroup where code=@code";
+ sql = @"select * from TUnitGroup where name=@name";
dynamicParams.Add("@code", excelTable[1].Rows[k][6].ToString().Trim());
dt = DapperHelper.selectdata(sql, dynamicParams);
if (dt.Rows.Count <= 0)
{
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
- erro.ErrorField = "{*璁￠噺鍗曚綅/缁勭紪鐮亇";
- erro.ErrorCont = "瀛樿揣妗ｆ琛�:{*璁￠噺鍗曚綅/缁勭紪鐮亇瀛楁" + excelTable[1].Rows[k][6].ToString().Trim() + "涓嶆槸鏈夋晥鐨勫璁￠噺鍗曚綅缁�";
+ erro.ErrorField = "{*璁￠噺鍗曚綅/缁勫悕绉皚";
+ erro.ErrorCont = "瀛樿揣妗ｆ琛�:{*璁￠噺鍗曚綅/缁勫悕绉皚瀛楁" + excelTable[1].Rows[k][6].ToString().Trim() + "涓嶆槸鏈夋晥鐨勫璁￠噺鍗曚綅缁�";
list.Add(erro);
}
}
@@ -3538,32 +3511,44 @@
#endregion
#region銆怑xcel涓婁紶鏁版嵁楠岃瘉,缂洪櫡瀹氫箟銆�
- public static List<ExcelErro> EighteenData(string FileCode, out string StuCode, out string message, out int count)
+ public static List<ExcelErro> EighteenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
{
message = "";
StuCode = "";
count = 0;
string sql = "";
- DataTable dt;
var dynamicParams = new DynamicParameters();
+ DataTable dt;
List<ExcelErro> list = new List<ExcelErro>();
- DataTable excelTable = new DataTable();
- list = ImportExcel.ExcelToTableErro(FileCode); //楠岃瘉Excel鏁版嵁蹇呭～瀛楁鏄惁涓虹┖銆佸敮涓�瀛楁鏄惁閲嶅
- excelTable = ImportExcel.ExcelToTable(FileCode); //鑾峰彇Excel鏁版嵁
- for (int i = 0; i < excelTable.Rows.Count; i++)
+ list = ImportExcel.InportExcelToTableListErro(excelTable); //楠岃瘉Excel鏁版嵁蹇呭～瀛楁鏄惁涓虹┖銆佸敮涓�瀛楁鏄惁閲嶅
+ //缂洪櫡琛�
+ for (int k = 0; k < excelTable[0].Rows.Count; k++)
{
- if (excelTable.Rows[i][1].ToString().Trim() != null && excelTable.Rows[i][1].ToString().Trim() != "")
+ if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
{
- sql = @"select code from TDefect where code=@code";
-
- dynamicParams.Add("@code", excelTable.Rows[i][1].ToString().Trim());
+ sql = @"select * from TDefect where code=@code";
+ dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
dt = DapperHelper.selectdata(sql, dynamicParams);
if (dt.Rows.Count > 0)
{
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
- erro.ErrorField = "{缂洪櫡瀹氫箟缂栧彿(鍞竴)}";
- erro.ErrorCont = "缂洪櫡瀹氫箟琛�:{缂洪櫡瀹氫箟缂栧彿(鍞竴)}瀛楁" + excelTable.Rows[i][1].ToString().Trim() + "宸插瓨鍦�";
+ erro.ErrorField = "{*缂洪櫡缂栧彿(鍞竴)}";
+ erro.ErrorCont = "缂洪櫡:{*缂洪櫡缂栧彿(鍞竴)}瀛楁" + excelTable[0].Rows[k][0].ToString().Trim() + "宸插瓨鍦�";
+ list.Add(erro);
+ }
+ }
+ if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
+ {
+ sql = @"select * from TDefect where name=@name";
+ dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
+ dt = DapperHelper.selectdata(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{*缂洪櫡鍚嶇О}";
+ erro.ErrorCont = "缂洪櫡琛�:{*缂洪櫡鍚嶇О}瀛楁" + excelTable[1].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
list.Add(erro);
}
}
@@ -3583,7 +3568,7 @@
{
StuCode = "200";
message = "鏁版嵁楠岃瘉鎴愬姛";
- count = excelTable.Rows.Count;
+ count = excelTable[0].Rows.Count;
}
return list;
}
@@ -4042,28 +4027,28 @@
//瀵煎叆瑙掕壊
for (int i = 0; i < excelTable[0].Rows.Count; i++)
{
- if (excelTable[0].Rows[i][3].ToString().Trim() != null && excelTable[0].Rows[i][3].ToString().Trim() != "")
- {
- //鏁版嵁鑼冨洿
- switch (excelTable[0].Rows[i][3].ToString().Trim())
- {
- case "鍏ㄩ儴":
- datarange = "ALL";
- break;
- case "鏈骇":
- datarange = "LEVEL";
- break;
- case "鏈汉":
- datarange = "PERSON";
- break;
- case "鑷畾涔�":
- datarange = "CUSTOM";
- datapermissions = excelTable[0].Rows[i][4].ToString().Trim();
- break;
- default:
- break;
- }
- }
+ //if (excelTable[0].Rows[i][3].ToString().Trim() != null && excelTable[0].Rows[i][3].ToString().Trim() != "")
+ //{
+ // //鏁版嵁鑼冨洿
+ // switch (excelTable[0].Rows[i][3].ToString().Trim())
+ // {
+ // case "鍏ㄩ儴":
+ // datarange = "ALL";
+ // break;
+ // case "鏈骇":
+ // datarange = "LEVEL";
+ // break;
+ // case "鏈汉":
+ // datarange = "PERSON";
+ // break;
+ // case "鑷畾涔�":
+ // datarange = "CUSTOM";
+ // datapermissions = excelTable[0].Rows[i][4].ToString().Trim();
+ // break;
+ // default:
+ // break;
+ // }
+ //}
sql = @"insert into TRole(rolecode,rolename,status,datarange,datapermissions,identifying,description,lm_user,lm_date)
values(@rolecode,@rolename,@status,@datarange,@datapermissions,@identifying,@description,@lm_user,@lm_date)";
@@ -4075,9 +4060,10 @@
rolecode = excelTable[0].Rows[i][0].ToString().Trim(),
rolename = excelTable[0].Rows[i][1].ToString().Trim(),
status = excelTable[0].Rows[i][2].ToString().Trim(),
- datarange = datarange,
+ datarange = "ALL",
datapermissions = datapermissions,
- description = excelTable[0].Rows[i][5].ToString().Trim(),
+ identifying="2",
+ description = excelTable[0].Rows[i][3].ToString().Trim(),
lm_user = us.usercode,
lm_date = DateTime.Now.ToString()
}
@@ -4323,16 +4309,19 @@
if (excelTable[1].Rows[i][5].ToString().Trim() == "S")
{
isSingleUnit = 1;
- idunit = excelTable[1].Rows[i][6].ToString().Trim();
+ sql = @"select * from TUnit where name=@name and isSingleUnit='1'";
+ dynamicParams.Add("@name", excelTable[1].Rows[i][6].ToString().Trim());
+ var dt = DapperHelper.selectdata(sql, dynamicParams);
+ idunit = dt.Rows[0]["code"].ToString();
}
if (excelTable[1].Rows[i][5].ToString().Trim() == "M")
{
sql = @"select T.code,T.name,T.isMainUnit from TUnitGroup G
inner join TUnit T on G.code=T.idunitgroup
- where G.code=@code";
- dynamicParams.Add("@code", excelTable[1].Rows[i][6].ToString().Trim());
+ where G.name=@name";
+ dynamicParams.Add("@name", excelTable[1].Rows[i][6].ToString().Trim());
var dt = DapperHelper.selectdata(sql, dynamicParams);
- idunitgroup = excelTable[1].Rows[i][6].ToString().Trim();
+ idunitgroup = dt.Rows[0]["groupcode"].ToString();
idunit = dt.AsEnumerable().Where(row => row.Field<string>("isMainUnit") =="1").Select(row => row.Field<string>("code")).FirstOrDefault();
idnounit = dt.AsEnumerable().Where(row => row.Field<string>("isMainUnit") == "0").Select(row => row.Field<string>("code")).FirstOrDefault();
}
@@ -5086,34 +5075,32 @@
#endregion
#region銆怑xcel鏁版嵁涓婁紶,缂洪櫡瀹氫箟銆�
- public static string EighteenSubmit(string FileCode, string User, out string StuCode)
+ public static string EighteenSubmit(List<DataTable> excelTable, User us, out string StuCode)
{
string message = "";
StuCode = "";
string sql = "";
- DataTable dt;
List<object> list = new List<object>();
var dynamicParams = new DynamicParameters();
try
{
list.Clear();
- DataTable excelTable = new DataTable();
- excelTable = ImportExcel.ExcelToTable(FileCode);
- //瀵煎叆寰�鏉ュ崟浣嶈〃
- for (int i = 0; i < excelTable.Rows.Count; i++)
+ //瀵煎叆缂洪櫡
+ for (int i = 0; i < excelTable[0].Rows.Count; i++)
{
- sql = @"insert into TDefect(code,name,descr,lm_user,lm_date)
- values(@code,@name,@descr,@Operator,@CreateDate)";
+ sql = @"insert into TDefect(code,name,description,is_step,lm_user,lm_date)
+ values(@code,@name,@description,@is_step,@lm_user,@lm_date)";
list.Add(new
{
str = sql,
parm = new
{
- code = excelTable.Rows[i][1].ToString().Trim(),
- name = excelTable.Rows[i][2].ToString().Trim(),
- descr = excelTable.Rows[i][3].ToString().Trim(),
- Operator = User,
- CreateDate = DateTime.Now.ToString()
+ code = excelTable[0].Rows[i][0].ToString().Trim(),
+ name = excelTable[0].Rows[i][1].ToString().Trim(),
+ description = excelTable[0].Rows[i][2].ToString().Trim(),
+ is_step="N",
+ lm_user = us.usercode,
+ lm_date = DateTime.Now.ToString()
}
});
}
--
Gitblit v1.9.3