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