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 }