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