private static void CreateKpOrPractice(Worksheet sheetTamplate, Worksheet sheet, Group group, Subject subject,
            Semestr semestr, int pivricha)
        {
            Log.Info(LoggerConstants.ENTER);
            sheet.Cells.PasteSpecial(sheetTamplate.Cells.Copy());

            sheet.Cells[13, "E"].Value = group.TrainingDirection.Equals("Програмна інженерія")
                ? "Програмної інженерії"
                : "Метрології та інформаційно-вимірювальної технології";
            sheet.Cells[15, "F"].Value = group.Speciality;
            sheet.Cells[17, "D"].Value = group.Course;
            sheet.Cells[17, "G"].Value = group.Name;
            sheet.Cells[19, "I"].Value = group.Year + "-" + (int.Parse(group.Year.Trim()) + 1);
            sheet.Cells[26, "F"].Value = subject.Name;
            sheet.Cells[28, "D"].Value = pivricha == 1
                ? group.FirstRomeSemestr
                : ArabToRome(FromRomeToArab(group.FirstRomeSemestr) + 1);
            sheet.Cells[22, "M"].Value = CreateNumberOfOblic(subject.NumberOfOlic,
                pivricha == 1 ? group.Year : int.Parse(@group.Year.Trim()) + 1 + "");
            sheet.Cells[30, "Q"].Value = semestr.CountOfHours;
            sheet.Cells[30, "F"].Value = FormaZdachi(semestr);
//            sheet.Cells[32, "K"].Value = subject.Teacher + "_____";
//            sheet.Cells[100, "N"].Value = subject.Teacher;

            var n = 45;
            foreach (var student in @group.Students)
            {
                sheet.Cells[n, "C"].Value = string.IsNullOrWhiteSpace(student.PibChanged)
                    ? student.Pib
                    : student.PibChanged;

                sheet.Cells[n, "H"].Value = student.NumberOfBook;
                n++;
            }
            if (n != 75)
                sheet.Range["B" + n, "Q" + 74].Delete();
            Log.Info(LoggerConstants.EXIT);
        }
        private static void MovePracticeAndStateExam()
        {
            Log.Info(LoggerConstants.ENTER);
            foreach (var group in Manager.Groups)
            {
                group.FirstRomeSemestr = ArabNormalize(group.FirstRomeSemestr);
                if (group.Practice != null)
                    foreach (var practice in @group.Practice)
                    {
                        practice.Semestr = ArabNormalize(practice.Semestr);

                        var subject = new Subject
                        {
                            Name = practice.Name,
                            NumberOfOlic = practice.NumberOfOlic,
                            Teacher = practice.Teacher.Aggregate("", (current, s) => current + s)
                        };
                        var semestr = new Semestr
                        {
                            CountOfHours = practice.CountOfHours,
                            PracticeFormOfControl = practice.FormOfControl
                        };

                        if (practice.Semestr.Equals(group.FirstRomeSemestr))
                            subject.FirstSemestr = semestr;
                        else subject.SecondSemestr = semestr;

                        group.Subjects.Add(subject);
                    }

                if (group.StateExamination != null)
                    foreach (var stateExamination in @group.StateExamination)
                    {
                        stateExamination.Semestr = ArabNormalize(stateExamination.Semestr);
                        foreach (var subject in @group.Subjects)
                        {
                            if (CustomEquals(subject.Name, stateExamination.Name))
                            {
                                if (subject.FirstSemestr != null &&
                                    group.FirstRomeSemestr.Equals(stateExamination.Semestr))
                                {
                                    subject.FirstSemestr.StateExamination = subject.FirstSemestr.Isput;
                                    subject.FirstSemestr.Isput = 0;
                                }
                                else if (subject.SecondSemestr != null)
                                {
                                    subject.SecondSemestr.StateExamination = subject.SecondSemestr.Isput;
                                    subject.SecondSemestr.Isput = 0;
                                }
                            }
                        }
                    }
            }
            Log.Info(LoggerConstants.EXIT);
        }
        private static string FormaZdachi(Semestr semestr)
        {
            Log.Info(LoggerConstants.ENTER);
            var s = "";

            if (semestr.CursovaRobota > 0) s = ConstantExcel.KursovyiProekt;
            else if (semestr.DyfZalik > 0) s = ConstantExcel.DyfZalik;
            else if (semestr.Isput > 0) s = ConstantExcel.Examen;
            else if (!string.IsNullOrWhiteSpace(semestr.PracticeFormOfControl)) s = semestr.PracticeFormOfControl;
            else if (semestr.Zalic > 0) s = ConstantExcel.Zalik;
            else if (semestr.StateExamination > 0) s = ConstantExcel.Protokol;

            Log.Info(LoggerConstants.EXIT);
            return s;
        }
        private static List<Subject> ReadSubject(Worksheet sheet)
        {
            Log.Info(LoggerConstants.ENTER);
            var subjects = new List<Subject>();
            //[0] - Hours; [1] - Cursova; [2] - Ispyt (Examen) [3] - DyfZalikOrZalic; [4] - DyfZalik (if exist)
            string[]
                firstSemestr = {"Y", "AK", "AO", "AQ", "AR"},
                secondSemestr = {"AS", "BE", "BI", "BK", "BL"};

            var dyfZalikOrNot = false;
            var ifDufZalicIsExist = true;

            // check the cells
            var s = sheet.Cells[15, "C"].Value;
            if (s == null || string.IsNullOrWhiteSpace(s.ToString()) ||
                !s.Trim().ToLower().Equals("назви навчальних  дисциплін"))
            {
                sheet.Cells[15, "C"].Interior.Color =
                    ColorTranslator.ToOle(Color.Red);
                Log.Error("Expekted at the `C15` value 'Назви навчальних  дисциплін'");
                return new List<Subject>();
            }

            // check the cells
            s = sheet.Cells[18, "AR"].Value;
            if (!string.IsNullOrWhiteSpace(s) && s.Trim().ToLower().Equals("диф  залік"))
                ifDufZalicIsExist = false;

            // check the cells
            s = sheet.Cells[18, "AQ"].Value;
            if (!string.IsNullOrWhiteSpace(s) && s.Trim().ToLower().Equals("диф  залік"))
                dyfZalikOrNot = true;

            var n = 14;

            while (true)
            {
                try
                {
                    if (n == 100)
                    {
                        Log.Warn("Exit with bad parameter");
                        break;
                    }

                    n++;

                    var subjectName = sheet.Cells[n, "C"].Value;
                    if (subjectName == null || string.IsNullOrWhiteSpace(subjectName.ToString()) || n == 15)
                        continue;
                    if (subjectName.ToString().Trim().ToLower().Equals("разом"))
                        break;

                    string teacher = sheet.Cells[n, "BN"].Value;
                    if (string.IsNullOrWhiteSpace(teacher))
                        teacher = "";

                    var subject = new Subject
                    {
                        Name = RemoveSymbolFromSubjectName(subjectName.ToString().Trim()),
                        Teacher = teacher
                    };
                    var addToList = false;

                    for (var i = 0; i < 2; i++)
                    {
                        var list = i == 0 ? firstSemestr : secondSemestr;
                        var ss = sheet.Cells[n, list[0]].Value;

                        // if cursova robota have same of the pas the not continue
                        var bl = true;
                        var kp = sheet.Cells[n, list[1]].Value;
                        if (kp != null && !string.IsNullOrWhiteSpace(kp.ToString()))
                            bl = false;

                        if ((ss == null || string.IsNullOrWhiteSpace(ss.ToString())) && bl)
                            continue;

                        addToList = true;
                        var semestr = new Semestr();

                        if (ss == null || string.IsNullOrWhiteSpace(ss.ToString()))
                            semestr.CountOfHours = 0;
                        else semestr.CountOfHours = ss;

                        ss = sheet.Cells[n, list[1]].Value;
                        if (ss != null && (!string.IsNullOrWhiteSpace(ss.ToString()) || !ss.ToString().Equals("0")))
                            semestr.CursovaRobota = ss;

                        ss = sheet.Cells[n, list[2]].Value;
                        if (ss != null && (!string.IsNullOrWhiteSpace(ss.ToString()) || !ss.ToString().Equals("0")))
                            semestr.Isput = ss;

                        ss = sheet.Cells[n, list[3]].Value;
                        if (ss != null && (!string.IsNullOrWhiteSpace(ss.ToString()) || !ss.ToString().Equals("0")))
                        {
                            if (ifDufZalicIsExist) semestr.DyfZalik = ss;
                            else semestr.Zalic = ss;
                        }

                        if (!dyfZalikOrNot)
                        {
                            ss = sheet.Cells[n, list[4]].Value;
                            if (ss != null && (!string.IsNullOrWhiteSpace(ss.ToString()) || !ss.ToString().Equals("0")))
                                semestr.DyfZalik = ss;
                        }

                        if (i == 0) subject.FirstSemestr = semestr;
                        else subject.SecondSemestr = semestr;
                    }
                    if (addToList) subjects.Add(subject);
                }
                catch (Exception exception)
                {
                    Log.Warn("Something wrong", exception);
                }
            }
            Log.Info(LoggerConstants.EXIT);
            return subjects;
        }
        private static void CreateStateExamen(Worksheet sheetTamplate, Worksheet sheet, Group group, Subject subject,
            Semestr semestr, int pivricha)
        {
            Log.Info(LoggerConstants.ENTER);
            sheet.Cells.PasteSpecial(sheetTamplate.Cells.Copy());
            sheet.Cells[4, "H"].Value = subject.Name;
            sheet.Cells[9, "C"].Value = group.Name;
            sheet.Cells[20, "G"].Value = subject.Teacher + "_________________________________";
            sheet.Cells[84, "H"].Value = subject.Teacher + "__";

            var n = 46;
            foreach (var student in @group.Students)
            {
                sheet.Cells[n, "C"].Value = string.IsNullOrWhiteSpace(student.PibChanged)
                    ? student.Pib
                    : student.PibChanged;

                n++;
            }

            if (n != 76)
                sheet.Range["B" + n, "Q" + 75].Delete();

            // Count of students in group
            sheet.Cells[12, "G"] = "__" + (n - 46) + "__";
            Log.Info(LoggerConstants.EXIT);
        }