public List <SamplingPointModel> GetExcelSamplePointData(string path) { var xlWorkSheet = Worksheet(path); var rngHeader = xlWorkSheet.Rows[1] as Excel.Range; var samplingPointIdIndex = rngHeader.Find(What: "SamplingPointId", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var nameIndex = rngHeader.Find(What: "Name", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var descriptionIndex = rngHeader.Find(What: "Description", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var instructionIndex = rngHeader.Find(What: "Instruction", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var groupIdIndex = rngHeader.Find(What: "GroupId", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var fieldWorkOfficeIndex = rngHeader.Find(What: "FieldworkOffice", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var stratumIndex = rngHeader.Find(What: "Stratum", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); var PreferenceIndex = rngHeader.Find(What: "Preference", LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns); range = xlWorkSheet.UsedRange; var sampleDataItems = new List <SamplingPointModel>(); for (var spRowCnt = 2; spRowCnt <= range.Rows.Count; spRowCnt++) { var samplingPointId = samplingPointIdIndex != null ? xlWorkSheet.Cells[spRowCnt, samplingPointIdIndex.Column] as Excel.Range : null; var name = nameIndex != null ? xlWorkSheet.Cells[spRowCnt, nameIndex.Column] as Excel.Range : null; var description = descriptionIndex != null ? xlWorkSheet.Cells[spRowCnt, descriptionIndex.Column] as Excel.Range : null; var instruction = instructionIndex != null ? xlWorkSheet.Cells[spRowCnt, instructionIndex.Column] as Excel.Range : null; var groupId = groupIdIndex != null ? xlWorkSheet.Cells[spRowCnt, groupIdIndex.Column] as Excel.Range : null; var fieldWorkOffice = fieldWorkOfficeIndex != null ? xlWorkSheet.Cells[spRowCnt, fieldWorkOfficeIndex.Column] as Excel.Range : null; var stratum = stratumIndex != null ? xlWorkSheet.Cells[spRowCnt, stratumIndex.Column] as Excel.Range : null; var preference = PreferenceIndex != null ? xlWorkSheet.Cells[spRowCnt, PreferenceIndex.Column] as Excel.Range : null; var knd = preference != null?preference.Value?.ToString() : ""; var kind = SamplingPointKind.Regular; if (knd != null) { switch (knd.ToLower()) { case "spare": kind = SamplingPointKind.Spare; break; case "spareactive": kind = SamplingPointKind.SpareActive; break; case "replaced": kind = SamplingPointKind.Replaced; break; default: kind = SamplingPointKind.Regular; break; } } var samplingPoint = new SamplingPointModel { SamplingPointId = samplingPointId != null?samplingPointId.Value?.ToString() : "", Name = name != null?name.Value?.ToString() : "", Description = description != null?description.Value?.ToString() : "", Instruction = instruction != null?instruction.Value?.ToString() : "", GroupId = groupId != null?groupId.Value?.ToString() : "", FieldworkOfficeId = fieldWorkOffice != null?fieldWorkOffice.Value?.ToString() : "", Stratum = stratum != null?stratum.Value?.ToString() : "", Kind = kind }; sampleDataItems.Add(samplingPoint); } CloseExcel(); return(sampleDataItems); }
private async Task UploadSamplingPoints(AccessToken token, FileData file) { var url = $"{ServerUrl}/v1/Surveys/{SurveyId}/SamplingPoints"; var officesUrl = $"{ServerUrl}/v1/Offices"; var offices = await GetFieldWorkOfficeIdAsync(officesUrl, token); using (var memoryStream = new MemoryStream(file.DataArray)) { using (var reader = new StreamReader(memoryStream)) { var csvFile = await reader.ReadToEndAsync(); var columnsLine = new StringReader(csvFile).ReadLine(); var delim = new[] { ',', ';' }; var columns = columnsLine.Split(delim); var csvData = csvFile.Split('\n').Skip(1); foreach (var row in csvData) { try { if (string.IsNullOrEmpty(row)) { continue; } var columnData = row.Split(delim); var kind = SamplingPointKind.Regular; switch (columnData[columns.IndexOf("Preference")].ToLower()) { case "spare": kind = SamplingPointKind.Spare; break; case "spareactive": kind = SamplingPointKind.SpareActive; break; case "replaced": kind = SamplingPointKind.Replaced; break; } var officeId = string.Empty; if (columns.IndexOf("FieldworkOffice") != -1) { officeId = offices.FirstOrDefault(x => x.OfficeName == columnData[columns.IndexOf("FieldworkOffice")])?.OfficeId; } var sample = new SamplingPointModel { SamplingPointId = columns.IndexOf("SamplingPointId") != -1 ? columnData[columns.IndexOf("SamplingPointId")] : "", Name = columns.IndexOf("Name") != -1 ? columnData[columns.IndexOf("Name")] : "", Description = columns.IndexOf("Description") != -1 ? columnData[columns.IndexOf("Description")] : "", Instruction = columns.IndexOf("Instruction") != -1 ? columnData[columns.IndexOf("Instruction")] : "", FieldworkOfficeId = officeId, GroupId = columns.IndexOf("GroupId") != -1 ? columnData[columns.IndexOf("GroupId")] : null, Stratum = columns.IndexOf("Stratum") != -1 ? columnData[columns.IndexOf("Stratum")] : null, Kind = kind }; var samplingData = JsonConvert.SerializeObject(sample); await PostSamplingPoint(samplingData, url, token); } catch (System.Exception e) { IsLoading = false; throw; } } var samplingUrl = $"{ServerUrl}/v1/Surveys/{SurveyId}/SamplingPoints"; SamplingPoints = await GetSamplingPointsAsync(samplingUrl, token); } } }