public ImportCheckResponse CheckExcelImport(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 ExcelImport(ImportCheckResponse checkResponse) { using (IObjectRepository auditedTdb = DBContext.CreateAuditable(CheckPoint.Instance.UserName, CheckPoint.Instance.HostAddress)) { ExcelImporter importer = new ExcelImporter(auditedTdb); importer.Import(checkResponse); } }
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 oid = GetCellValue(oidCell, wbPart); ImportValueSetChange change = new ImportValueSetChange(); ValueSet foundValueSet = this.tdb.ValueSets.SingleOrDefault(y => y.Oid == oid); if (foundValueSet == null) { change.ChangeType = ImportValueSetChange.ChangeTypes.Add; } else { change.Id = foundValueSet.Id; if (foundValueSet.Name != name) { change.ChangeType = ImportValueSetChange.ChangeTypes.Update; } } change.Oid = oid; change.Name = name; response.ValueSets.Add(change); } }
public void ExcelImport(ImportCheckResponse checkResponse) { using (IObjectRepository auditedTdb = DBContext.Create()) { auditedTdb.AuditChanges(CheckPoint.Instance.UserName, CheckPoint.Instance.OrganizationName, CheckPoint.Instance.HostAddress); foreach (var checkValueSet in checkResponse.ValueSets) { ValueSet valueSet = null; if (checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.None || checkValueSet.ChangeType == ImportValueSetChange.ChangeTypes.Update) { valueSet = auditedTdb.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() { Oid = checkValueSet.Oid, Name = checkValueSet.Name, LastUpdate = DateTime.Now }; auditedTdb.ValueSets.AddObject(valueSet); } // Import concepts foreach (var checkConcept in checkValueSet.Concepts) { ExcelImportConcept(auditedTdb, valueSet, checkConcept); } } auditedTdb.SaveChanges(); } }
private void ProcessConceptSheet(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() < 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, wbPart); string code = GetCellValue(codeCell, wbPart); string display = GetCellValue(displayCell, wbPart); string codeSystemOid = GetCellValue(codeSystemOidCell, wbPart); string status = GetCellValue(statusCell, wbPart); string statusDateText = GetCellValue(statusDateCell, wbPart); var foundValuesetChange = response.ValueSets.SingleOrDefault(y => y.Oid == valuesetOid); var foundCodeSystem = this.tdb.CodeSystems.SingleOrDefault(y => y.Oid == codeSystemOid); 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 (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; } 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(); var foundConcept = foundValuesetChange.ChangeType != ImportValueSetChange.ChangeTypes.Add ? this.tdb.ValueSetMembers.SingleOrDefault(y => y.ValueSet.Oid == foundValuesetChange.Oid && y.Code == code && y.Status == status && y.StatusDate == statusDate) : null; 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); } }