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);
}
