Exemple #1
0
        public static void CombineExcelDatabase(string fileName)
        {
            FileInfo file = new FileInfo(fileName);

            using (ExcelPackage package = new ExcelPackage(file))
            {
                var dlist = new List <DiseaseHPOSummaryLibraryMappingModel>();
                //获取当前疾病和知识库的关系
                var librarySheet = package.Workbook.Worksheets[1];

                int row = librarySheet.Dimension.Rows;
                for (int i = 2; i <= row; i++)
                {
                    var mapping = new DiseaseHPOSummaryLibraryMappingModel();
                    mapping.ChineseName = librarySheet.Cells[i, 1].Value.ToString();
                    mapping.CasesCount  = librarySheet.Cells[i, 3].Value.ToString();
                    mapping.EramId      = librarySheet.Cells[i, 4].Value.ToString();
                    mapping.OMIMId      = librarySheet.Cells[i, 5].Value.ToString();
                    mapping.ORPHAId     = librarySheet.Cells[i, 6].Value.ToString();
                    dlist.Add(mapping);
                }

                ExcelWorksheet workSheet = package.Workbook.Worksheets[3];
                var            tempid    = "100050";
                row = workSheet.Dimension.Rows;
                for (int i = 2; i <= row; i++)
                {
                    var source = workSheet.Cells[i, 1].Value.ToString().Trim();
                    var id     = workSheet.Cells[i, 2].Value.ToString().Trim();

                    if (id == tempid)
                    {
                        var tempdata = dlist.FirstOrDefault(x => x.OMIMId == id && source == "OMIM");
                    }
                    var data = dlist.FirstOrDefault(x => x.OMIMId == id && source == "OMIM");
                    if (data != null)
                    {
                        workSheet.Cells[i, 3].Value = data.ChineseName;
                    }
                    data = dlist.FirstOrDefault(x => x.ORPHAId == id && source == "ORPHA");
                    if (data != null)
                    {
                        workSheet.Cells[i, 3].Value = data.ChineseName;
                    }
                }
                package.Save();
            }
        }
Exemple #2
0
        public static void CombineExcelHPO(string fileName)
        {
            FileInfo file      = new FileInfo(fileName);
            var      finalList = new List <DiseaseHPOSummaryModel>();

            using (ExcelPackage package = new ExcelPackage(file))
            {
                var dlist = new List <DiseaseHPOSummaryLibraryMappingModel>();
                //获取当前疾病和知识库的关系
                var librarySheet = package.Workbook.Worksheets[2];

                int row = librarySheet.Dimension.Rows;
                for (int i = 2; i <= row; i++)
                {
                    var mapping = new DiseaseHPOSummaryLibraryMappingModel();
                    mapping.ChineseName = librarySheet.Cells[i, 1].Value.ToString();
                    mapping.CasesCount  = librarySheet.Cells[i, 3].Value.ToString();
                    mapping.EramId      = librarySheet.Cells[i, 4].Value.ToString();
                    mapping.OMIMId      = librarySheet.Cells[i, 5].Value.ToString();
                    mapping.ORPHAId     = librarySheet.Cells[i, 6].Value.ToString();
                    dlist.Add(mapping);
                }

                //获取eram hpo
                //获取疾病所有case 和hpo
                var workSheet = package.Workbook.Worksheets[1];
                row = workSheet.Dimension.Rows;
                foreach (var item in dlist)
                {
                    var result = new DiseaseHPOSummaryModel();
                    result.HPOList = new List <DiseaseHPOSummaryHPOModel>();
                    var name = item.ChineseName;
                    for (int i = 2; i <= row; i++)
                    {
                        if (workSheet.Cells[i, 2].Value != null && workSheet.Cells[i, 2].Value.ToString().Trim() == name)
                        {
                            result.DiseaseName = name;
                            //NLP hpo
                            if (workSheet.Cells[i, 3].Value != null)
                            {
                                var arr = workSheet.Cells[i, 3].Value.ToString().Split(",");
                                foreach (var r in arr)
                                {
                                    var hpo = result.HPOList.FirstOrDefault(x => x.HPOId == r);
                                    if (hpo != null)
                                    {
                                        hpo.NlpCount++;
                                    }
                                    else
                                    {
                                        result.HPOList.Add(new DiseaseHPOSummaryHPOModel {
                                            HPOId = r, NlpCount = 1
                                        });
                                    }
                                }
                            }
                            //检验 hpo
                            if (workSheet.Cells[i, 4].Value != null)
                            {
                                var arr = workSheet.Cells[i, 4].Value.ToString().Split(",");
                                foreach (var r in arr)
                                {
                                    var hpo = result.HPOList.FirstOrDefault(x => x.HPOId == r);
                                    if (hpo != null)
                                    {
                                        hpo.ExamCount++;
                                    }
                                    else
                                    {
                                        result.HPOList.Add(new DiseaseHPOSummaryHPOModel {
                                            HPOId = r, ExamCount = 1
                                        });
                                    }
                                }
                            }
                        }
                    }
                    result.HPOList = result.HPOList.OrderByDescending(x => x.NlpCount).ThenByDescending(x => x.ExamCount).ToList();
                    finalList.Add(result);
                }
                var wordSheet = package.Workbook.Worksheets[3];
                int j         = 1;
                foreach (var disease in finalList)
                {
                    foreach (var r in disease.HPOList)
                    {
                        wordSheet.Cells[j, 1].Value = "EMR";
                        wordSheet.Cells[j, 2].Value = disease.DiseaseName;
                        wordSheet.Cells[j, 4].Value = r.HPOId;
                        if (r.NlpCount > 0)
                        {
                            wordSheet.Cells[j, 5].Value = "NLP";
                            wordSheet.Cells[j, 6].Value = r.NlpCount;
                        }
                        if (r.ExamCount > 0)
                        {
                            wordSheet.Cells[j, 5].Value = "LAB";
                            wordSheet.Cells[j, 6].Value = r.ExamCount;
                        }
                        j++;
                    }
                }
                package.Save();
            }
        }
Exemple #3
0
        public static void CombineExcelDatabaseEram(string fileName)
        {
            FileInfo file = new FileInfo(fileName);

            var eramlIst = new List <DatabaseHpo>();

            using (ExcelPackage package = new ExcelPackage(file))
            {
                var dlist = new List <DiseaseHPOSummaryLibraryMappingModel>();
                //获取当前疾病和知识库的关系
                var librarySheet = package.Workbook.Worksheets[1];

                int row = librarySheet.Dimension.Rows;
                for (int i = 2; i <= row; i++)
                {
                    var mapping = new DiseaseHPOSummaryLibraryMappingModel();
                    mapping.ChineseName = librarySheet.Cells[i, 1].Value.ToString();
                    mapping.CasesCount  = librarySheet.Cells[i, 3].Value.ToString();
                    mapping.EramId      = librarySheet.Cells[i, 4].Value.ToString();
                    mapping.OMIMId      = librarySheet.Cells[i, 5].Value.ToString();
                    mapping.ORPHAId     = librarySheet.Cells[i, 6].Value.ToString();
                    dlist.Add(mapping);
                }

                //获取eram hpo
                var eramSheet = package.Workbook.Worksheets[2];
                row = eramSheet.Dimension.Rows;
                for (int i = 2; i <= row; i++)
                {
                    var name = "";
                    var data = dlist.FirstOrDefault(x => x.EramId == eramSheet.Cells[i, 1].Value.ToString().Trim());
                    if (data != null)
                    {
                        name = data.ChineseName;
                    }

                    var hpoStr = eramSheet.Cells[i, 3].Value.ToString();
                    if (!string.IsNullOrEmpty(hpoStr))
                    {
                        var array = hpoStr.Split(";");
                        foreach (var arr in array)
                        {
                            var item = new DatabaseHpo();
                            item.Name   = name;
                            item.Id     = eramSheet.Cells[i, 1].Value.ToString().Trim();
                            item.HpoId  = arr.Split("|")[0];
                            item.Source = "eRam";
                            eramlIst.Add(item);
                        }
                    }
                }

                var wordSheet = package.Workbook.Worksheets[4];
                for (int i = 0; i < eramlIst.Count; i++)
                {
                    var data = eramlIst[i];
                    int j    = i + 1;
                    wordSheet.Cells[j, 1].Value = data.Source;
                    wordSheet.Cells[j, 2].Value = data.Id;
                    wordSheet.Cells[j, 3].Value = data.Name;
                    wordSheet.Cells[j, 4].Value = data.HpoId;
                }
                package.Save();
            }
        }