Пример #1
0
        public void CreateClassForUser(string filePath, string userId)
        {
            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

            //Choose one of either 1 or 2
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            //Choose one of either 3, 4, or 5
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader.AsDataSet();

            //4. DataSet - Create column names from first row
            //excelReader.IsFirstRowAsColumnNames = true;
            //DataSet result = excelReader.AsDataSet();

            //5. Data Reader methods
            var clases = new List <ClassDomain>();

            for (int sheetIndex = 0; sheetIndex < result.Tables.Count; sheetIndex++)
            {
                var @class = new ClassDomain
                {
                    Name           = result.Tables[sheetIndex].TableName,
                    ClassTeacherId = userId,
                    // TODO works only for Blagoev
                    SchoolId = 1
                };

                var sheet = result.Tables[sheetIndex];

                // skip first rows
                for (var row = 2; row < sheet.Rows.Count; row++)
                {
                    if (sheet.Rows[row].ItemArray[0].ToString() == "")
                    {
                        break;
                    }

                    var student = new StudentDomain
                    {
                        Number     = int.Parse(sheet.Rows[row].ItemArray[0].ToString()),
                        FirstName  = sheet.Rows[row].ItemArray[1].ToString(),
                        MiddleName = sheet.Rows[row].ItemArray[2].ToString(),
                        LastName   = sheet.Rows[row].ItemArray[3].ToString()
                    };

                    var monthId = 1;

                    // start from 4 because in exel file absenses starts from 4 column
                    for (var col = 4; col <= sheet.Rows[row].ItemArray.Length; col += 2)
                    {
                        if (sheet.Rows[row].ItemArray[col].ToString() == "" && sheet.Rows[row].ItemArray[col + 1].ToString() == "")
                        {
                            break;
                        }

                        var absence = new AbsenceDomain();

                        var    excusedAbsenceAsString = sheet.Rows[row].ItemArray[col].ToString();
                        double excusedAbsence;
                        absence.Excused = double.TryParse(excusedAbsenceAsString, out excusedAbsence) ? excusedAbsence : 0;

                        var    notExcusedAbsenceAsString = sheet.Rows[row].ItemArray[col + 1].ToString();
                        double notExcusedAbsence;
                        absence.NotExcused = double.TryParse(notExcusedAbsenceAsString, out notExcusedAbsence) ? notExcusedAbsence : 0;

                        absence.MonthId = monthId;

                        student.Absences.Add(absence);
                        monthId++;
                    }

                    @class.Students.Add(student);
                }

                clases.Add(@class);
            }

            _classService.AddRange(clases);

            excelReader.Close();
        }