public void BuildMdmCodeSet(string excelPath, string sheetName, string dbConnName) { Workbook workbook = null; IList <code_set_excel_entity> codesystemCels = new List <code_set_excel_entity>(); IList <code_set_excel_entity> codesetexcels1 = new List <code_set_excel_entity>(); using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { workbook = new Workbook(fs); } //新版MDMExcel 的说明 if (string.IsNullOrEmpty(sheetName)) { sheetName = "代码系统"; } Worksheet sheet = workbook.Worksheets[sheetName]; var cells = sheet.Cells; #region 加载代码系统,包含代码集的代码系统 for (int i = 1; i <= cells.MaxDataRow; i++) { var row = cells.CheckRow(i); if (row == null || row.FirstCell == null) { continue; } else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")) == null) { continue; } else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue.Contains("代码")) { continue; } else { var hasCodeSet = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P")).StringValue; if (string.IsNullOrWhiteSpace(hasCodeSet)) { continue; } var aaa = new code_set_excel_entity(); aaa.code_sys_code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("G")).StringValue; aaa.code_sys_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("H")).StringValue; var ser = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P")); aaa.memo = sheet.Hyperlinks.FirstOrDefault(p => (p.Area.StartRow == ser.Row && p.Area.StartColumn == ser.Column)).Address; if (!string.IsNullOrWhiteSpace(aaa.memo)) { aaa.show_name = aaa.memo.Split('!')[0];//存放对应的sheet的名称 } codesystemCels.Add(aaa); } } #endregion #region 循环处理所有的代码系统,并提取代码集 foreach (var item in codesystemCels) //所有的代码系统 { string sheetNameCodeSet = item.code_sys_code; Cells codeSetCells = workbook.Worksheets[item.show_name]?.Cells; if (codeSetCells != null) { //读取worksheet codeset for (int k = 3; k <= codeSetCells.MaxDataRow; k++) //单个codeset 从第二行开始 { var row = codeSetCells.CheckRow(k); if (row == null || row.FirstCell == null) { continue; } else if (row.GetCellOrNull(0).StringValue.StartsWith("代码", StringComparison.OrdinalIgnoreCase)) { continue; } else { var aaa = new code_set_excel_entity(); aaa.code_sys_code = item.code_sys_code; aaa.code_sys_name = item.code_sys_name; aaa.code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue; //第一列 aaa.name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("B")).StringValue; //第二列 aaa.show_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("C")).StringValue; //第三列 codesetexcels1.Add(aaa); } } } }//循环所有的代码系统 #endregion #region 插入数据 var connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ConnectionString; using (NpgsqlConnection conn = new NpgsqlConnection(connstr)) { foreach (var item in codesystemCels) { var code_sys_id_list = conn.Query <int>($"select code_sys_id from mdm.code_system where code_sys_code = '{item.code_sys_code}' ").ToList(); int code_sys_id = -1; if (code_sys_id_list == null || code_sys_id_list.Count == 0) { Serilog.Log.Warning($"{item.code_sys_code}{'\t'}{item.code_sys_name}"); } else { code_sys_id = code_sys_id_list.FirstOrDefault(); } var codesets = codesetexcels1.Where(p => p.code_sys_code == item.code_sys_code); int sortno = 0; foreach (var codeset in codesets) { var codeistm = new code_set_entity { //code_id = "879 ", code = codeset.code, name = codeset.name, show_name = codeset.show_name, spell_code = "", wb_code = "", code_sys_id = code_sys_id, is_std = true, std_class_id = 0, unit_id = 0, unit_name = null, value_type = 0, range = null, range_low = null, range_high = null, note = item.show_name, quote_code_id = 0, state = 1, sort_no = ++sortno, oper_id = 3, oper_time = DateTime.Now, etl_time = DateTime.Now, tenant_id = 0, is_valid = true }; conn.Insert <code_set_entity>(codeistm); } } #region 拼音码、五笔码 //生成拼音、五笔码 conn.Execute("update mdm.code_set set spell_code = substring(get_pym_pg(name) from 0 for 20) ,wb_code = substring(get_wbm(name) from 0 for 20) ", commandTimeout: 300); conn.Execute("update mdm.code_domain set spell_code = substring(get_pym_pg(domain_name) from 0 for 20) ,wb_code = substring(get_wbm(domain_name) from 0 for 20)", commandTimeout: 300); conn.Execute("update mdm.code_system set spell_code = substring(get_pym_pg(code_sys_name) from 0 for 20),wb_code = substring(get_wbm(code_sys_name) from 0 for 20) ", commandTimeout: 300); #endregion } #endregion }
public void InsertCodeSet(string mdmexcelpath, string dbConnName, string sheetName) { Workbook workbook = null; IList <code_set_excel_entity> codesetexcels = new List <code_set_excel_entity>(); using (FileStream fs = new FileStream(mdmexcelpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { workbook = new Workbook(fs); } if (string.IsNullOrEmpty(sheetName)) { sheetName = "代码明细"; } Worksheet sheet = workbook.Worksheets[sheetName]; var cells = sheet.Cells; for (int i = 0; i < cells.MaxDataRow; i++) { var row = cells.CheckRow(i); if (row == null || row.FirstCell == null) { continue; } if (row.GetCellOrNull(0).StringValue == "代码系统编码") { continue; } else { var aaa = new code_set_excel_entity(); aaa.code_sys_code = row.GetCellOrNull(0).StringValue; aaa.code_sys_name = row.GetCellOrNull(1).StringValue; aaa.code = row.GetCellOrNull(2).StringValue; aaa.name = row.GetCellOrNull(3).StringValue; aaa.memo = row.GetCellOrNull(4).StringValue; codesetexcels.Add(aaa); } } if (string.IsNullOrEmpty(dbConnName)) { dbConnName = "hdr"; } var connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ConnectionString; //select setval(pg_get_serial_sequence('mdm.code_set', 'code_id'), max(code_id), true) from mdm.code_set 设置序列最大值 //删除数据 //delete from mdm.code_set where etl_time > current_TIMESTAMP - interval '1 days' using (NpgsqlConnection conn = new NpgsqlConnection(connstr)) { foreach (var item in codesetexcels) { var code_sys_id = conn.Query <int>($"select code_sys_id from mdm.code_system where code_sys_code = '{item.code_sys_code}' and oper_time > current_TIMESTAMP - interval '1 days'").FirstOrDefault(); var codeistm = new code_set_entity { //code_id = "879 ", code = item.code, name = item.name, show_name = item.name, spell_code = "", wb_code = "", code_sys_id = code_sys_id, is_std = true, std_class_id = 0, unit_id = 0, unit_name = null, value_type = 0, range = null, range_low = null, range_high = null, note = item.memo, quote_code_id = 0, state = 1, sort_no = 1, oper_id = 3, oper_time = DateTime.Now, etl_time = DateTime.Now, tenant_id = 0, is_valid = true }; conn.Insert <code_set_entity>(codeistm); } } }