Ejemplo n.º 1
0
        /// <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);
        }