private IList <code_set_excel_entity> ParseExcelCodeSetItem(string code_sys_code, string code_sys_name, Cells codeSetCells) { IList <code_set_excel_entity> codeSetItem = new List <code_set_excel_entity>(); if (codeSetCells == null) { return(codeSetItem); } //读取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 = code_sys_code; aaa.code_sys_name = 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; //第三列 codeSetItem.Add(aaa); } } return(codeSetItem); }
/// <summary> /// 读取hdr表说明 转换成list 导入数据库 与数据库中的列 进行比较 判断excel与表结构是否一致 /// </summary> /// <param name="cells"></param> /// <returns></returns> public IList <ExcelColumn> ProcessSD(Cells cells) { List <ExcelColumn> list = new List <ExcelColumn>(); string TableName = string.Empty; //表名 (organization(机构信息)) string Schema = string.Empty; //业务域(mdm(主数据)) string TableComment = string.Empty; //表说明 (机构信息,如:医疗机构、科研机构等) for (int row = 0; row <= cells.MaxDataRow; row++) { Row current_row = cells.CheckRow(row); if (current_row == null || current_row.FirstDataCell == null) { continue; } //表名 Cell cel = current_row[CellsHelper.ColumnNameToIndex("A")]; if (cel.IsMerged) { //业务域 及 表说明 var range = cel.GetMergedRange(); if (range.CellCount > 3) {//表的第一行 TableName = cel.GetStringValue(CellValueFormatStrategy.None); } if (range.CellCount == 2) {//第二行 Schema = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); TableComment = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); } continue; } else if (cel.GetStringValue(CellValueFormatStrategy.None) == "序号") //列头 { continue; } else //表列 { ExcelColumn ec = new ExcelColumn(); var tableNamearray = TableName.Split(new string[4] { "(", ")", "(", ")" }, StringSplitOptions.None); ec.TableName = tableNamearray[0];//表名 (organization(机构信息)) ec.TableNameCh = string.Join("", tableNamearray.Where(p => p != tableNamearray[0]).ToArray()); var schemaarray = Schema.Split(new string[4] { "(", ")", "(", ")" }, StringSplitOptions.None); ec.Schema = schemaarray[0]; //业务域(mdm(主数据)) ec.SchemaCh = string.Join("", schemaarray.Where(p => p != schemaarray[0]).ToArray()); //业务域(mdm(主数据)) ec.TableComment = TableComment; //表说明 (机构信息,如:医疗机构、科研机构等) ec.SerialNumber = cel.GetStringValue(CellValueFormatStrategy.None); //序号 ec.ColumnName = current_row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None); //列名 ec.ColumnComment = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); //中文名 ec.DataType = current_row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None); //字段类型 ec.IsNulled = current_row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None); //允许空 ec.ForeignKey = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); //外键(关联字段) ec.CodeSystem = current_row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None); //字典系统(值域) ec.IsStandard = ""; //字典表标准化 无 ec.Description = current_row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None); //说明 list.Add(ec); continue; } } return(list); }
/// <summary> /// 解析excel生成实体 /// </summary> /// <param name="excelPath"></param> /// <param name="sheetName"></param> /// <returns></returns> public IList <mdmExcelRawEntity> ParseExcelToEntity(string excelPath, string sheetName) { Workbook workbook = null; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { workbook = new Workbook(fs); } if (string.IsNullOrEmpty(sheetName)) { sheetName = "代码系统"; } Worksheet worksheet = workbook.Worksheets[sheetName]; Cells cells = worksheet.Cells; IList <mdmExcelRawEntity> codsyslist = new List <mdmExcelRawEntity>(); #region excel解析 for (int i = 0; i <= cells.MaxDataRow; i++) { var row = cells.CheckRow(i); if (row == null || row.FirstCell == null) { continue; } Cell cel = row[CellsHelper.ColumnNameToIndex("A")]; if (row[CellsHelper.ColumnNameToIndex("A")] == null || cel.Value == null) { continue; } if (cel.IsMerged) { continue; } else { if (cel.GetStringValue(CellValueFormatStrategy.None) == "一级域代码") { //表头 } else { mdmExcelRawEntity aa = new mdmExcelRawEntity(); aa.一级域代码 = row[CellsHelper.ColumnNameToIndex("A")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); // 一级域代码 aa.一级域 = row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //一级域 aa.二级域代码 = row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //二级域代码 aa.二级域 = row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //二级域 aa.级域代码 = row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //三级域代码 aa.级域 = row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //三级域 aa.代码系统 = row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //代码系统 aa.代码系统名称 = row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //代码系统名称 aa.标准来源 = row[CellsHelper.ColumnNameToIndex("I")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //标准来源 aa.标准级别 = row[CellsHelper.ColumnNameToIndex("J")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //标准级别 aa.标准完整性 = row[CellsHelper.ColumnNameToIndex("K")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //标准完整性 aa.值集合 = row[CellsHelper.ColumnNameToIndex("L")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //"值集合" aa.ETL转换 = row[CellsHelper.ColumnNameToIndex("M")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //"ETL转换" aa.应用转换 = row[CellsHelper.ColumnNameToIndex("N")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //"应用转换" aa.示例数据 = row[CellsHelper.ColumnNameToIndex("O")].GetStringValue(CellValueFormatStrategy.None)?.Trim(); //示例数据 var celcodeset = row[CellsHelper.ColumnNameToIndex("P")]; aa.代码集 = celcodeset.GetStringValue(CellValueFormatStrategy.None); //代码集 var codesetsheetaddress = celcodeset.Worksheet.Hyperlinks.FirstOrDefault(p => (p.Area.StartRow == celcodeset.Row && p.Area.StartColumn == celcodeset.Column))?.Address; if (!string.IsNullOrWhiteSpace(codesetsheetaddress)) { var codesetsheetname = codesetsheetaddress.Split('!')[0];//存放对应的sheet的名称 var codesetsheetcells = workbook.Worksheets[codesetsheetname]?.Cells; aa.代码集明细 = ParseExcelCodeSetItem(aa.代码系统, aa.代码系统名称, codesetsheetcells); } codsyslist.Add(aa); } } } #endregion return(codsyslist); }
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 }
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 }
/// <summary> /// 解析excel生成实体 /// </summary> /// <param name="excelPath"></param> /// <param name="sheetName"></param> /// <returns></returns> private IList <mdmExcelRawEntity> ParseExcelToEntity(string excelPath, string sheetName) { Workbook workbook = null; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { workbook = new Workbook(fs); } if (string.IsNullOrEmpty(sheetName)) { sheetName = "代码系统"; } Worksheet worksheet = workbook.Worksheets[sheetName]; Cells cells = worksheet.Cells; IList <mdmExcelRawEntity> codsyslist = new List <mdmExcelRawEntity>(); #region excel解析 for (int i = 0; i <= cells.MaxDataRow; i++) { var row = cells.CheckRow(i); if (row == null || row.FirstCell == null) { continue; } Cell cel = row[CellsHelper.ColumnNameToIndex("A")]; if (cel == null || cel.Value == null) { continue; } if (cel.IsMerged) { continue; } else { if (cel.GetStringValue(CellValueFormatStrategy.None) == "一级域代码") { //表头 } else { mdmExcelRawEntity aa = new mdmExcelRawEntity(); aa.一级域代码 = row[CellsHelper.ColumnNameToIndex("A")].GetStringValue(CellValueFormatStrategy.None); // 一级域代码 aa.一级域 = row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None); //一级域 aa.二级域代码 = row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); //二级域代码 aa.二级域 = row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None); //二级域 aa.级域代码 = row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None); //三级域代码 aa.级域 = row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); //三级域 aa.代码系统 = row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None); //代码系统 aa.代码系统名称 = row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None); //代码系统名称 aa.标准来源 = row[CellsHelper.ColumnNameToIndex("I")].GetStringValue(CellValueFormatStrategy.None); //标准来源 aa.标准级别 = row[CellsHelper.ColumnNameToIndex("J")].GetStringValue(CellValueFormatStrategy.None); //标准级别 aa.标准完整性 = row[CellsHelper.ColumnNameToIndex("K")].GetStringValue(CellValueFormatStrategy.None); //标准完整性 aa.值集合 = row[CellsHelper.ColumnNameToIndex("L")].GetStringValue(CellValueFormatStrategy.None); //"值集合" aa.ETL转换 = row[CellsHelper.ColumnNameToIndex("M")].GetStringValue(CellValueFormatStrategy.None); //"ETL转换" aa.应用转换 = row[CellsHelper.ColumnNameToIndex("N")].GetStringValue(CellValueFormatStrategy.None); //"应用转换" aa.示例数据 = row[CellsHelper.ColumnNameToIndex("O")].GetStringValue(CellValueFormatStrategy.None); //示例数据 codsyslist.Add(aa); } } } #endregion return(codsyslist); }