/// <summary>
/// Consider using getCellValue
/// </summary>
/// <param name="xlRange"></param>
/// <param name="dataElement"></param>
/// <param name="indicatorid"></param>
/// <param name="rowId"></param>
/// <param name="colmnId"></param>
/// <param name="counter"></param>
/// <param name="sex"></param>
/// <param name="builder"></param>
/// <returns></returns>
protected DataValue GetDataValue(Range xlRange, ProgramAreaDefinition dataElement, string indicatorid, int rowId, int colmnId, int counter, string sex, StringBuilder builder = null)
{
var i = rowId;
var j = colmnId;
var value = getCellValue(xlRange, i, j);
double asDouble;
DataValue dataValue = null;
if (customHandledIndicators.Contains(indicatorid))
{
return(null);
}
try
{
asDouble = value.ToDouble();
if (asDouble == -2146826273 || asDouble == -2146826281)
{
ShowErrorAndAbort(value, indicatorid, dataElement.ProgramArea, i, j);
return(null);
}
}
catch
{
ShowErrorAndAbort(value, indicatorid, dataElement.ProgramArea, i, j);
return(null);
}
if (asDouble != Constants.NOVALUE)
{
if (value == null)
{
ShowValueNullErrorAndAbort(indicatorid, dataElement.ProgramArea, i, j);
}
dataValue = new DataValue()
{
IndicatorValue = asDouble,
IndicatorId = indicatorid,
ProgramArea = dataElement.ProgramArea,
AgeGroup = dataElement.AgeDisaggregations[counter],
Sex = sex
};
if (builder != null)
{
LogCsvOutput(string.Format("{0}\t", value));
}
}
else
{
if (builder != null)
{
LogCsvOutput(string.Format("{0}\t", "x"));
//builder.AppendFormat("{0}\t", "x");
}
}
return(dataValue);
}
protected static RowColmnPair GetFirstAgeGroupCell(ProgramAreaDefinition dataElement, Range xlrange, bool isNonDod)
{
int colCount = xlrange.Columns.Count;
colCount = colCount > 15 ? 12 : colCount;
int row = -1, colmn = -1, colmn2 = -1;
var matchfound = false;
var maxDepthSearchRows = 8;
var cleanAgeDisaggs = dataElement.getCleanAgeDisaggregations();
for (var rowId = 1; rowId <= maxDepthSearchRows; rowId++)
{
for (var colmnId = 1; colmnId <= colCount; colmnId++)
{
var rawAgeValue = getCellValue(xlrange, rowId, colmnId);
if (string.IsNullOrWhiteSpace(rawAgeValue) || rawAgeValue.Length > 40)
{
continue;
}
var cleanAgeValue = rawAgeValue.toCleanAge();
if (!PageController.Instance.AlternateAgegroups.ContainsKey(cleanAgeValue))
{
continue;
}
//if (dataElement.AgeDisaggregations.Contains(value))
if (cleanAgeDisaggs.Contains(cleanAgeValue) &&
cleanAgeDisaggs.IndexOf(cleanAgeValue) == 0)
{
//we've found our column, time to find where the data begibs, lets find the corresponding indicator
//we'll scan for columns from rowid to perhaps 5 places, and starting from column 0
row = rowId;
colmn = colmnId;
matchfound = true;
//if (dataElement.Gender.ToLowerInvariant() == "both")
//{
// //we continue and find the next occurrence of this value
// colmn2 = findNextOccurence(dataElement, xlrange, colCount, rowId, colmnId + 1, cleanAgeValue);
//}
break;
}
}
if (matchfound)
{
break;
}
}
return(new RowColmnPair(row, colmn, colmn2));
}
static int findNextOccurence(ProgramAreaDefinition dataElement, Range xlrange, int colCount, int rowId, int startColmnIndex, string valueToFind)
{
//if(dataElement.ProgramArea =="PEP")
int colmnIndex = -1;
for (var colmnId = startColmnIndex; colmnId <= colCount; colmnId++)
{
var value = getCellValue(xlrange, rowId, colmnId);
if (value != valueToFind)
{
continue;
}
colmnIndex = colmnId;
break;
}
return(colmnIndex);
}
protected static int findNextOccurence(ProgramAreaDefinition dataElement, Range xlrange, int colCount, int rowId, int startColmnIndex, string valueToFind)
{
//if(dataElement.ProgramArea =="PEP")
int colmnIndex = -1;
for (var colmnId = startColmnIndex; colmnId <= colCount; colmnId++)
{
var value = getCellValue(xlrange, rowId, colmnId, string.Empty);
if (string.IsNullOrWhiteSpace(value) || value.toCleanAge() != valueToFind)
{
continue;
}
colmnIndex = colmnId;
break;
}
return(colmnIndex);
}
private static FirstAgeGroupOccurence GetFirstAgeGroupCell(ProgramAreaDefinition dataElement, Range xlrange, bool isNonDod)
{
int colCount = xlrange.Columns.Count;
int row = -1, colmn = -1, colmn2 = -1;
var matchfound = false;
var maxDepthSearchRows = isNonDod ? 8 : 3;
for (var rowId = 1; rowId <= maxDepthSearchRows; rowId++)
{
for (var colmnId = 1; colmnId <= colCount; colmnId++)
{
var value = getCellValue(xlrange, rowId, colmnId);
if (string.IsNullOrWhiteSpace(value) || value.Length > 20)
{
continue;
}
if (dataElement.AgeDisaggregations.Contains(value))
{
//we've found our row
row = rowId;
colmn = colmnId;
matchfound = true;
if (dataElement.Gender.ToLowerInvariant() == "both")
{
//we continue and find the next occurrence of this value
colmn2 = findNextOccurence(dataElement, xlrange, colCount, rowId, colmnId + 1, value);
}
break;
}
}
if (matchfound)
{
break;
}
}
return(new FirstAgeGroupOccurence(row, colmn, colmn2));
}
protected DataValue getCellValue(ProgramAreaDefinition dataElement, string indicatorId, Range xlrange, KeyValuePair <string, RowColmnPair> rowObject, KeyValuePair <string, List <RowColmnPair> > indicatorAgeGroupCells, RowColmnPair indicatorAgeGroupCell)
{
var value = getCellValue(xlrange, rowObject.Value.Row, indicatorAgeGroupCell.Column, Constants.NULLVALUE);
if (value == Constants.NULLVALUE)
{
return(null);
}
var knownProblematicValues =
new List <string>()
{
"iud", "jaddel", "hiv negative", "hiv positive", "hiv status unknown"
};
//value can be Constants.NULLVALUE for cells that are merged and return a null
if (knownProblematicValues.Contains(value.ToLowerInvariant()))
{
//we continue
//jaddel is FP4 Male Total and IUD is FP4 Female Total
//HIV Negative
//HIV Positive
//HIV Status Unknown
return(null);
}
var asDouble = 0d;
try
{
asDouble = value.ToDouble();
//if (asDouble == 0)
// return null;
if (asDouble == -2146826273 || asDouble == -2146826281)
{
ShowErrorAndAbort(value, rowObject.Key, dataElement.ProgramArea, rowObject.Value.Row, indicatorAgeGroupCell.Column);
//return null;
}
}
catch
{
ShowErrorAndAbort(value, rowObject.Key, dataElement.ProgramArea, rowObject.Value.Row, indicatorAgeGroupCell.Column);
//return null;
}
if (asDouble == Constants.NOVALUE)
{
return(null);
}
var sex = dataElement.Gender;
if (dataElement.Gender == "both")
{
if (indicatorAgeGroupCell.Index == 1)
{
sex = "Male";
}
else
{
sex = "Female";
}
}
var dataValue = new DataValue()
{
IndicatorValue = asDouble,
IndicatorId = indicatorId, //rowObject.Key,
ProgramArea = dataElement.ProgramArea,
AgeGroup = indicatorAgeGroupCells.Key,
Sex = sex,
};
return(dataValue);
}
private string UpdateIndicatorDefinitionsByProgramArea(Microsoft.Office.Interop.Excel.Application excelApp)
{
var res = string.Empty;
//http://stackoverflow.com/questions/16213255/how-to-read-cell-values-from-existing-excel-file
var relativePath = "staticdata//sampleTemplate.xlsx";
var filePath = string.Empty;
if (File.Exists(relativePath))
{
filePath = Path.GetFullPath(relativePath);
}
else
{
throw new FileNotFoundException("Couldn't find the file " + relativePath);
}
var wb = excelApp.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
var shts = wb.Sheets;
var sheetsToSkip = new[] { "Narrative", "Appendix 1", "Cover1" };
var programAreaDefinitions = new List <ProgramAreaDefinition>();
foreach (Worksheet sht in shts)
{
var usedRange = sht.UsedRange;
var rows = usedRange.Rows.Count;
var colmns = usedRange.Columns.Count;
var programAreaName = sht.Name.Trim();
if (sheetsToSkip.Contains(programAreaName))
{
continue;
}
var programAreaDefinition = new ProgramAreaDefinition()
{
ProgramArea = programAreaName
};
programAreaDefinitions.Add(programAreaDefinition);
if (maleFemaleIndicators.Contains(programAreaName))
{
programAreaDefinition.Gender = "both";
}
else
{
var gender = string.Empty;
if (singleGenderIndicators.TryGetValue(programAreaName, out gender))
{
programAreaDefinition.Gender = gender;
}
else
{
throw new ArgumentOutOfRangeException(string.Format("Error. Gender categorization of {0} not defined", programAreaName));
}
}
//if (programAreaName == "Family Planning")
//{
// programAreaDefinition.DefaultHandler = "Custom";
//}
//Prevention - PWP programAreaName=="Prevention - PWP"
//we get the indicator codes and names. These are in the first and second columns starting from the second row
for (var i = 2; i <= rows; i++)
{
var indicatorCode = GetValuesFromReport.getCellValue(usedRange, i, 1);
var indicatorName = GetValuesFromReport.getCellValue(usedRange, i, 2);
if (string.IsNullOrWhiteSpace(indicatorCode) || string.IsNullOrWhiteSpace(indicatorName))
{
continue;
}
programAreaDefinition.Indicators.Add(new ProgramIndicator()
{
IndicatorId = indicatorCode,
Indicator = indicatorName
});
}
//we get the agegroup categories
for (var j = 3; j <= colmns; j++)
{
var ageGroupLabel = GetValuesFromReport.getCellValue(usedRange, 2, j);
if (string.IsNullOrWhiteSpace(ageGroupLabel))
{
continue;
}
programAreaDefinition.AgeDisaggregations.Add(ageGroupLabel);
}
}
//we save the data
if (File.Exists("ProgramAreaDefinitions.json"))
{
File.Delete("ProgramAreaDefinitions.json");
}
var builder = new StringBuilder();
programAreaDefinitions.ForEach(t => builder.AppendLine(Newtonsoft.Json.JsonConvert.SerializeObject(t)));
res = builder.ToString();
File.AppendAllText("ProgramAreaDefinitions.json", res);
if (File.Exists("IndicatorDefinitions.csv"))
{
File.Delete("IndicatorDefinitions.csv");
}
var fields = new List <string>()
{
"ProgramArea", "IndicatorCode", "Indicator"
};
using (var writer = new StreamWriter("IndicatorDefinitions.csv", false))
{
writer.WriteLine(string.Format("{0},{1},{2}", "ProgramArea", "IndicatorCode", "Indicator"));
programAreaDefinitions.ForEach(programArea =>
{
programArea.Indicators.ForEach(
indicator =>
writer.WriteLine(string.Format("{0},{1},{2}",
programArea.ProgramArea.csvDelim(),
indicator.IndicatorId.csvDelim(),
indicator.Indicator.csvDelim()))
);
});
}
MessageBox.Show("Done");
return(res);
}
