private List <DataValue> ImportData(Microsoft.Office.Interop.Excel.Application excelApp, ProjectName projectName)
{
PerformProgressStep("Please wait, initialising");
var _loadAllProgramDataElements = new GetProgramAreaIndicators().GetAllProgramDataElements();
PerformProgressStep("Please wait, Opening Excel document");
//for all available program areas, we read the values into an array
var workbook = excelApp.Workbooks.Open(fileName, 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);
//we get the other data
var worksheetCount = workbook.Sheets.Count;
var worksheetNames = new Dictionary <string, string>();
for (var indx = 1; indx <= worksheetCount; indx++)
{
var worksheetName = ((Worksheet)(workbook.Sheets[indx])).Name;
worksheetNames.Add(worksheetName.Trim(), worksheetName);
}
//we get the facility codes
//LocationDetail locationDetail = null;
switch (projectName)
{
case ProjectName.DOD:
{
Worksheet coverWorksheet = null;
const string coverWorksheetName = "Cover1";
try
{
coverWorksheet = (Worksheet)workbook.Sheets[coverWorksheetName];
}
catch
{
ShowMissingWorksheet(coverWorksheetName);
return(null);
}
locationDetail = GetReportLocationDetails(coverWorksheet, ShowErrorAndAbort);
break;
}
case ProjectName.IHP_VMMC:
{
//then its VMMC, we remove the other DOD program areas and just leave one
if (!worksheetNames.ContainsKey(GetProgramAreaIndicators.IhpVmmcProgramAreaName))
{
throw new ArgumentNullException("Invalid file selected");
}
var vmmcProgramArea = _loadAllProgramDataElements.FirstOrDefault(t => t.ProgramArea == GetProgramAreaIndicators.dodVmmcProgramAreaName);
vmmcProgramArea.ProgramArea = GetProgramAreaIndicators.IhpVmmcProgramAreaName;
_loadAllProgramDataElements.Clear();
_loadAllProgramDataElements.Add(vmmcProgramArea);
locationDetail = GetIhpReportLocationDetails(workbook, ShowErrorAndAbort);
break;
}
case ProjectName.IHP_Capacity_Building_and_Training:
{
break;
}
}
if (locationDetail == null)
{
return(null);
}
var datavalues = new List <DataValue>();
MarkStartOfMultipleSteps(_loadAllProgramDataElements.Count + 2);
foreach (var dataElement in _loadAllProgramDataElements)
{
var programAreaName = dataElement.ProgramArea;
var worksheetName = programAreaName;
//if (isIhpVmmc && programAreaName != GetProgramAreaIndicators.dodVmmcProgramAreaName)
// continue;
//if (projectName == ProjectName.IHP_VMMC)
//{
// worksheetName = GetProgramAreaIndicators.IhpVmmcProgramAreaName;
// programAreaName = GetProgramAreaIndicators.IhpVmmcProgramAreaName;
//}
PerformProgressStep("Please wait, Processing worksheet: " + programAreaName);
ResetSubProgressIndicator(dataElement.Indicators.Count + 1);
var xlrange = ((Worksheet)workbook.Sheets[worksheetNames[worksheetName]]).UsedRange;
//we scan the first 3 rows for the row with the age groups specified for this program area
var rowCount = xlrange.Rows.Count;
var colCount = xlrange.Columns.Count;
//we have the column indexes of the first age category options, and other occurrences of the same
var firstAgeGroupCell = GetFirstAgeGroupCell(dataElement, xlrange, ProjectName.IHP_VMMC == projectName);
//Now we find the row indexes of the program indicators
//ProgramIndicator currentRowMatchingIndicator = null;
var firstIndcatorRowIndex = -1;
for (var rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
var value = getCellValue(xlrange, rowIndex, 1);
if (string.IsNullOrWhiteSpace(value))
{
continue;
}
var matchingDataElementByIndicatorId = dataElement.Indicators.FirstOrDefault(t => t.IndicatorId == value);
if (matchingDataElementByIndicatorId != null)
{
firstIndcatorRowIndex = rowIndex;
//currentRowMatchingIndicator = matchingDataElementByIndicatorId;
break;
}
}
//now we know that AgeGroups start from e.g. colmn 7 of row 4
//we also know that indicators start from Row X of column 1
//we can now start reading these values into an array for each indicator vs age group
//we start reading the values from cell [firstIndcatorRowIndex, firstAgeGroupCell.Colmn1]
LogCsvOutput("Processing: " + programAreaName);
var testBuilder = new StringBuilder();
testBuilder.AppendLine();
testBuilder.AppendLine(programAreaName);
var countdown = dataElement.Indicators.Count;
var i = firstIndcatorRowIndex;
PerformSubProgressStep();
do
{
var indicatorid = getCellValue(xlrange, i, 1);
if (string.IsNullOrWhiteSpace(indicatorid))
{
if (ProjectName.IHP_VMMC == projectName)
{
countdown--;
continue;
}
throw new ArgumentNullException(string.Format("Expected a value in Cell ( A{0}) for sheet {1}", i, programAreaName));
}
var j = firstAgeGroupCell.Column;
var counter = 0;
//or we can get the corresponding data element and see what indicators it reports under
while (counter < dataElement.AgeDisaggregations.Count)
{
while (counter < dataElement.AgeDisaggregations.Count)
{
var dataValue = GetDataValue(
xlRange: xlrange,
dataElement: dataElement,
indicatorid: indicatorid,
rowId: i,
colmnId: j,
counter: counter,
sex: getGenderText(dataElement.Gender),
builder: testBuilder
);
if (dataValue != null)
{
datavalues.Add(dataValue);
}
j++;
counter++;
}
j++;
counter++;
}
if (dataElement.Gender == "both")
{
j = firstAgeGroupCell.Column2;
counter = 0;
while (counter < dataElement.AgeDisaggregations.Count)
{
var dataValue = GetDataValue(
xlRange: xlrange,
dataElement: dataElement, indicatorid: indicatorid,
rowId: i, colmnId: j,
counter: counter,
sex: getGenderText("Female"),
builder: testBuilder
);
if (dataValue != null)
{
datavalues.Add(dataValue);
}
j++;
counter++;
}
}
PerformSubProgressStep();
testBuilder.AppendLine();
countdown--;
i++;
} while (countdown > 0);
LogCsvOutput(testBuilder.ToString());
//Console.WriteLine("Done - "+ dataElement.ProgramArea);
}
PerformProgressStep("Please wait, finalizing");
datavalues.ForEach(t =>
{
t.ReportYear = locationDetail.ReportYear;
t.ReportMonth = locationDetail.ReportMonth;
t.FacilityName = locationDetail.FacilityName;
});
PerformProgressStep("Please wait, Preparing to display results");
//convert to dataset
return(datavalues);
}
private List <DataValue> ImportData(Microsoft.Office.Interop.Excel.Application excelApp)
{
//we analyse if our field dictionary has unique indicatorids
PerformProgressStep("Please wait, initialising");
//we have twwo spreadsheets for finance:
var _loadAllProgramDataElements = new
GetProgramAreaIndicators()
.GetDodDataElements();
//.GetAllProgramDataElements();
foreach (var element in _loadAllProgramDataElements)
{
var allIndicIds = (from t in element.Indicators
select t.IndicatorId).Count();
var distinctIndicIds = (from t in element.Indicators
select t.IndicatorId).ToList().Distinct().Count();
if (allIndicIds != distinctIndicIds)
{
throw new ArgumentOutOfRangeException(
"Duplicate indicatorids for program area " + element.ProgramArea);
}
}
PerformProgressStep("Please wait, Opening Excel document");
//for all available program areas, we read the values into an array
var workbook = excelApp.Workbooks.Open(fileName, 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);
//we get the other data
var worksheetCount = workbook.Sheets.Count;
var worksheetNames = new Dictionary <string, string>();
for (var indx = 1; indx <= worksheetCount; indx++)
{
var worksheetName = ((Worksheet)(workbook.Sheets[indx])).Name;
worksheetNames.Add(worksheetName.Trim().ToLowerInvariant(), worksheetName);
}
if (!worksheetNames.ContainsKey(CoverSheetName.ToLowerInvariant()))
{
MessageBox.Show(
string.Format(
"Please ensure that the file \r'{0}'\r contains a Cover sheet called '{1}'", fileName, CoverSheetName)
, "Missing worksheet", MessageBoxButton.OK);
return(null);
}
var coverWorksheet = (Worksheet)workbook.Sheets[worksheetNames[CoverSheetName.ToLowerInvariant()]];
locationDetail = GetReportLocationDetails(coverWorksheet, ShowErrorAndAbort);
var datavalues = new List <DataValue>();
MarkStartOfMultipleSteps(_loadAllProgramDataElements.Count + 2);
foreach (var dataElement in _loadAllProgramDataElements)
{
var programAreaName = dataElement.ProgramArea;
PerformProgressStep("Please wait, Processing worksheet: " + programAreaName);
ResetSubProgressIndicator(dataElement.Indicators.Count + 1);
worksheetName = programAreaName.ToLowerInvariant();
if (!worksheetNames.ContainsKey(worksheetName))
{
MessageBox.Show(
string.Format(
"Please ensure that the file \r'{0}'\r contains a sheet called '{1}'", fileName, programAreaName)
, "Missing worksheet", MessageBoxButton.OK);
var choice = MessageBox.Show(string.Format("Do you still want to proceed and import the data without {0} data", programAreaName), "Proceed", MessageBoxButton.YesNo);
if (choice != MessageBoxResult.Yes)
{
return(null);
}
else
{
continue;
}
}
var xlrange = ((Worksheet)workbook.Sheets[worksheetNames[worksheetName.ToLowerInvariant()]]).UsedRange;
//we scan the first 3 rows for the row with the age groups specified for this program area
var rowCount = xlrange.Rows.Count;
var colCount = xlrange.Columns.Count;
//we have the column indexes of the first age category options, and other occurrences of the same
var firstAgeGroupCell = GetFirstAgeGroupCell(dataElement, xlrange, false);
if (firstAgeGroupCell.Column == -1 && firstAgeGroupCell.Row == -1)
{
MessageBox.Show(
string.Format(
"File \r'{0}'\r is not well formed. Please upload a file that has the correct structure", fileName)
, "Incorrectly formatted file", MessageBoxButton.OK);
return(null);
}
var ageGroupCells = GetMatchedCellsInRow(excelRange: xlrange,
searchTerms: dataElement.AgeDisaggregations,
endColumnIndex: colCount, startColumnIndex: firstAgeGroupCell.Column,
rowIndex: firstAgeGroupCell.Row
);
//Now we find the row indexes of the program indicators
var indicatorList = (from t in dataElement.Indicators select t.Indicator).ToList();
var firstIndicatorCell = GetFirstMatchedCellByRow(excelRange: xlrange,
searchTerms: indicatorList,
startColumnIndex: 1,
endColumnIndex: firstAgeGroupCell.Column - 1,
maxRows: rowCount,
statRowIndex: firstAgeGroupCell.Row + 1
);
var indicatorCells = GetCellsInColumnContaining(
excelRange: xlrange,
columnIndex: firstIndicatorCell.Column,
searchTerms: indicatorList,
startRowIndex: firstIndicatorCell.Row,
maxRows: rowCount
);
foreach (var rowObject in indicatorCells)
{
//we convert the indicator to an indicatorId
var matchingIndicator = dataElement.Indicators.Where(t => t.Indicator == rowObject.Key).FirstOrDefault();
if (matchingIndicator == null)
{
throw new ArgumentOutOfRangeException("Could not match indicator " + rowObject.Key);
}
foreach (var indicatorAgeGroupCells in ageGroupCells)
{
foreach (var indicatorAgeGroupCell in indicatorAgeGroupCells.Value)
{
var dataValue = getCellValue(dataElement, matchingIndicator.IndicatorId, xlrange, rowObject, indicatorAgeGroupCells, indicatorAgeGroupCell);
if (dataValue == null)
{
continue;
}
datavalues.Add(dataValue);
}
}
}
}
return(datavalues);
}
