/// <summary>
/// Takes a file path as a string as input and returns a DataTable containing the extracted data.
/// Designed to work together with the DBHandler to import the data into the database.
/// </summary>
/// <param name="filePath">The file path of the Excel-file containing the relevant data.</param>
internal static ICollection <Tuple <Student, string> > GetStudentDataTableFromExcelFile(string filePath)
{
if (string.IsNullOrWhiteSpace(filePath))
{
throw new ArgumentException("File path is null, empty or consist of only white-space characters.");
}
ICollection <Tuple <Student, string> > extractedStudents = new List <Tuple <Student, string> >();
_Application excelInstance = null;
_Workbook workbook = null;
try
{
excelInstance = new Application();
workbook = excelInstance.Workbooks.Open(filePath);
_Worksheet worksheet = workbook.Worksheets[1];
ValidateHeaderRow(worksheet);
//import content
//todo: handle half-empty rows correctly! (Error message and removal from DataTable, so it's not imported)
int rowIdx = 2;
bool rowIsEmpty = false;
while (!rowIsEmpty)
{
ICollection <string> row = worksheet.GetRow(rowIdx, 5);
rowIsEmpty = row.All(string.IsNullOrWhiteSpace);
if (!rowIsEmpty)
{
string surname = row.ElementAt(0);
string forename = row.ElementAt(1);
string courseName = row.ElementAt(2);
string rawSex = row.ElementAt(3);
string rawYearOfBirth = row.ElementAt(4);
Sex sex;
if (SexDictionary.TryGetValue(rawSex, out sex))
{
short yearOfBirth = Convert.ToInt16(rawYearOfBirth);
if (IsValidYear(yearOfBirth))
{
Student student = new Student
{
Surname = surname,
Forename = forename,
Sex = sex,
YearOfBirth = yearOfBirth
};
extractedStudents.Add(new Tuple <Student, string>(student, courseName));
}
}
}
rowIdx++;
}
}
finally
{
workbook?.Close();
excelInstance?.Quit();
SafelyReleaseComObject(workbook);
SafelyReleaseComObject(excelInstance);
}
return(extractedStudents);
}