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