public ImportCheckResponse GetCheckResponse(ImportCheckRequest request) { ImportCheckResponse response = new ImportCheckResponse(); using (MemoryStream ms = new MemoryStream(request.Data)) { SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false); WorkbookPart wbPart = spreadsheet.WorkbookPart; var sheets = wbPart.Workbook.Descendants <Sheet>(); if (sheets.Count() != 2) { response.Errors.Add("Workbook must contain two spreadsheets. The first representing 'Value Sets', the second representing 'Concepts'"); return(response); } var valuesetSheet = sheets.First(); var conceptSheet = sheets.Last(); WorksheetPart valuesetWSPart = (WorksheetPart)(wbPart.GetPartById(valuesetSheet.Id)); Worksheet valuesetWorksheet = valuesetWSPart.Worksheet; SheetData valuesetSheetData = valuesetWorksheet.GetFirstChild <SheetData>(); WorksheetPart conceptWSPart = (WorksheetPart)(wbPart.GetPartById(conceptSheet.Id)); Worksheet conceptWorksheet = conceptWSPart.Worksheet; SheetData conceptSheetData = conceptWorksheet.GetFirstChild <SheetData>(); ProcessValueSetSheet(response, valuesetSheetData, wbPart, request.FirstRowIsHeader); ProcessConceptSheet(response, conceptSheetData, wbPart, request.FirstRowIsHeader); } return(response); }
public void Import(ImportCheckResponse checkResponse) { foreach (var checkValueSet in checkResponse.ValueSets) { Log.For(this).Info("Importing excel value set {0} ({1})", checkValueSet.Name, checkValueSet.Oid); ValueSet valueSet = null; if (checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.None || checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.Update) { valueSet = this.tdb.ValueSets.Single(y => y.Id == checkValueSet.Id); if (checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.Update) { valueSet.Name = checkValueSet.Name; valueSet.LastUpdate = DateTime.Now; } } else if (checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.Add) { valueSet = new ValueSet() { Name = checkValueSet.Name, LastUpdate = DateTime.Now }; ValueSetIdentifier vsIdentifier = new ValueSetIdentifier(); vsIdentifier.Identifier = checkValueSet.Oid; if (checkValueSet.Oid.StartsWith("http://") || checkValueSet.Oid.StartsWith("https://")) { vsIdentifier.Type = ValueSetIdentifierTypes.HTTP; } else if (checkValueSet.Oid.StartsWith("urn:hl7ii:")) { vsIdentifier.Type = ValueSetIdentifierTypes.HL7II; } else { vsIdentifier.Type = ValueSetIdentifierTypes.Oid; } valueSet.Identifiers.Add(vsIdentifier); this.tdb.ValueSets.Add(valueSet); } // Import concepts foreach (var checkConcept in checkValueSet.Concepts) { this.ExcelImportConcept(valueSet, checkConcept); } } this.tdb.SaveChanges(); }
private void ProcessConceptSheet(ImportCheckResponse response, SheetData sheetData, WorkbookPart wbPart, bool firstRowIsHeader) { var rows = sheetData.Descendants <Row>(); Dictionary <string, CodeSystem> cachedCodeSystems = new Dictionary <string, CodeSystem>(); SharedStringTablePart shareStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); SharedStringItem[] items = shareStringPart != null?shareStringPart.SharedStringTable.Elements <SharedStringItem>().ToArray() : null; foreach (var row in rows) { if (firstRowIsHeader && row.RowIndex.Value == 1) { continue; } var cells = row.Descendants <Cell>(); if (cells.Count() < 6) { response.Errors.Add(string.Format("Row {0} on concept sheet does not have the required number of cells (6)", row.RowIndex.Value)); continue; } Cell valuesetOidCell = cells.SingleOrDefault(y => y.CellReference == "A" + row.RowIndex.Value.ToString()); Cell codeCell = cells.SingleOrDefault(y => y.CellReference == "B" + row.RowIndex.Value.ToString()); Cell displayCell = cells.SingleOrDefault(y => y.CellReference == "C" + row.RowIndex.Value.ToString()); Cell codeSystemOidCell = cells.SingleOrDefault(y => y.CellReference == "D" + row.RowIndex.Value.ToString()); Cell statusCell = cells.SingleOrDefault(y => y.CellReference == "E" + row.RowIndex.Value.ToString()); Cell statusDateCell = cells.SingleOrDefault(y => y.CellReference == "F" + row.RowIndex.Value.ToString()); string valuesetOid = GetCellValue(valuesetOidCell, items); string code = GetCellValue(codeCell, items); string display = GetCellValue(displayCell, items); string codeSystemOid = GetCellValue(codeSystemOidCell, items); string status = GetCellValue(statusCell, items); string statusDateText = GetCellValue(statusDateCell, items); if (string.IsNullOrEmpty(valuesetOid)) { response.Errors.Add(string.Format("Row {0}'s value set identifier on concepts sheet is not identifier", row.RowIndex.Value)); continue; } if (!valuesetOid.StartsWith("http://") && !valuesetOid.StartsWith("https://") && !valuesetOid.StartsWith("urn:oid:")) { response.Errors.Add(string.Format("Row {0}'s value set identifier on concepts sheet must be correctly formatted as one of: http[s]://XXXX or urn:oid:XXXX", row.RowIndex.Value)); continue; } if (string.IsNullOrEmpty(codeSystemOid)) { response.Errors.Add(string.Format("Row {0}'s code system identifier on the concept sheet is not specified", row.RowIndex.Value)); continue; } if (!codeSystemOid.StartsWith("http://") && !codeSystemOid.StartsWith("https://") && !codeSystemOid.StartsWith("urn:oid:")) { response.Errors.Add(string.Format("Row {0}'s code system identifier on concepts sheet must be correctly formatted as one of: http[s]://XXXX or urn:oid:XXXX", row.RowIndex.Value)); continue; } var foundValueSetChange = response.ValueSets.SingleOrDefault(y => y.Oid == valuesetOid); CodeSystem foundCodeSystem = cachedCodeSystems.ContainsKey(codeSystemOid) ? cachedCodeSystems[codeSystemOid] : null; if (foundCodeSystem == null) { foundCodeSystem = this.tdb.CodeSystems.SingleOrDefault(y => y.Oid == codeSystemOid); if (foundCodeSystem == null) { response.Errors.Add(string.Format("Could not find specified code system {0} on row {1} of concept sheet.", codeSystemOid, row.RowIndex.Value)); continue; } cachedCodeSystems.Add(codeSystemOid, foundCodeSystem); } if (foundValueSetChange == null) { response.Errors.Add(string.Format("Row {0} on concept sheet does not have a valid valueset OID.", row.RowIndex.Value)); continue; } if (string.IsNullOrEmpty(code)) { response.Errors.Add(string.Format("Row {0} on concept sheet does not have a valid code.", row.RowIndex.Value)); continue; } if (string.IsNullOrEmpty(display)) { response.Errors.Add(string.Format("Row {0} on concept sheet does not have a valid display name.", row.RowIndex.Value)); continue; } if (!string.IsNullOrEmpty(status) && status.ToLower() != "active" && status.ToLower() != "inactive") { response.Errors.Add(string.Format("Row {0} on concept sheet does not have a valid status ('active' or 'inactive').", row.RowIndex.Value)); continue; } DateTime parsedStatusDate = DateTime.MinValue; int parsedStatusDateInt = 0; if (!string.IsNullOrEmpty(statusDateText) && !Int32.TryParse(statusDateText, out parsedStatusDateInt) && !DateTime.TryParse(statusDateText, out parsedStatusDate)) { response.Errors.Add(string.Format("Row {0} on concept sheet does not have a valid date (format 'mm/dd/yyyy').", row.RowIndex.Value)); continue; } DateTime?statusDate = null; if (parsedStatusDateInt > 0) { statusDate = FromExcelSerialDate(parsedStatusDateInt); } else if (parsedStatusDate != DateTime.MinValue) { statusDate = parsedStatusDate; } ImportValueSetChange.ConceptChange conceptChange = new ImportValueSetChange.ConceptChange(); ValueSetMember foundConcept = null; if (foundValueSetChange.ValueSet != null) { foundConcept = foundValueSetChange.ValueSet.Members.SingleOrDefault(y => y.Code == code && y.Status == status && y.StatusDate == statusDate); } if (foundConcept == null) { conceptChange.ChangeType = ImportValueSetChange.ChangeTypes.Add; } else { conceptChange.Id = foundConcept.Id; if (foundConcept.DisplayName != display) { conceptChange.ChangeType = ImportValueSetChange.ChangeTypes.Update; } } conceptChange.Code = code; conceptChange.DisplayName = display; conceptChange.CodeSystemOid = codeSystemOid; conceptChange.CodeSystemName = foundCodeSystem.Name; conceptChange.Status = status; conceptChange.StatusDate = statusDate; foundValueSetChange.Concepts.Add(conceptChange); } }
private void ProcessValueSetSheet(ImportCheckResponse response, SheetData sheetData, WorkbookPart wbPart, bool firstRowIsHeader) { var rows = sheetData.Descendants <Row>(); foreach (var row in rows) { if (firstRowIsHeader && row.RowIndex.Value == 1) { continue; } var cells = row.Descendants <Cell>(); if (cells.Count() < 2) { response.Errors.Add(string.Format("Row {0} on valueset sheet does not have the required number of cells (2)", row.RowIndex.Value)); continue; } Cell nameCell = cells.SingleOrDefault(y => y.CellReference == "A" + row.RowIndex.Value.ToString()); Cell oidCell = cells.SingleOrDefault(y => y.CellReference == "B" + row.RowIndex.Value.ToString()); var name = GetCellValue(nameCell, wbPart); var identifier = GetCellValue(oidCell, wbPart); if (string.IsNullOrEmpty(identifier)) { response.Errors.Add(string.Format("Row {0} on valueset sheet does not specify an identifier for the value set", row.RowIndex.Value)); continue; } if (!identifier.StartsWith("http://") && !identifier.StartsWith("https://") && !identifier.StartsWith("urn:oid:")) { response.Errors.Add(string.Format("Row {0}'s identifier on valueset sheet must be correctly formatted as one of: http[s]://XXXX or urn:oid:XXXX", row.RowIndex.Value)); continue; } ImportValueSetChange change = new ImportValueSetChange(); ValueSet foundValueSet = (from vs in this.tdb.ValueSets join vsi in this.tdb.ValueSetIdentifiers on vs.Id equals vsi.ValueSetId where vsi.Identifier.ToLower().Trim() == identifier.ToLower().Trim() select vs) .Distinct() .FirstOrDefault(); if (foundValueSet == null) { change.ChangeType = ImportValueSetChange.ChangeTypes.Add; } else { change.ValueSet = foundValueSet; change.Id = foundValueSet.Id; if (foundValueSet.Name != name) { change.ChangeType = ImportValueSetChange.ChangeTypes.Update; } } change.Oid = identifier; change.Name = name; response.ValueSets.Add(change); } }