Beispiel #1
0
        public InvigilationDuty(DateTime date, string session, string venueID, string location, string categoryOfInvigilator, int duration)
        {
            MaintainExaminationControl mExamControl = new MaintainExaminationControl();

            this.date     = date;
            this.session  = session;
            this.venueID  = venueID;
            this.location = location;
            this.categoryOfInvigilator = categoryOfInvigilator;
            this.duration           = duration;
            this.timeslotID         = session + date.Date.ToString("ddMMyy");
            this.examList           = mExamControl.searchExaminationByTimeslotAndVenue(this.timeslotID, this.venueID);
            this.possibleCandidate  = new List <InvigilatorHeuristic>();
            this.constraintInvolved = 0;
            this.maxScore           = 0;
            mExamControl.shutDown();
        }
        private void writeToExcelDM(FileInfo newFile, string type)
        {
            // EPPlus library is required
            ExcelPackage package = new ExcelPackage(newFile);

            MaintainStaffControl            staffControl         = new MaintainStaffControl();
            MaintainExaminationControl      examControl          = new MaintainExaminationControl();
            MaintainInvigilationDutyControl dutyControl          = new MaintainInvigilationDutyControl();
            MaintainExemptionControl        exemptionControl     = new MaintainExemptionControl();
            MaintainPaperExaminedControl    paperExaminedControl = new MaintainPaperExaminedControl();

            //Check for CHIEF, or others
            if (type.Equals("CHIEF"))
            {
                // Add a worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);

                // Set column width
                worksheet.Column(1).Width = 4.5;
                worksheet.Column(2).Width = 45;

                //print header
                worksheet.Cells[1, 1].Value       = "No.";
                worksheet.Cells[1, 1, 3, 1].Merge = true;
                worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                worksheet.Cells[1, 2].Value       = "Name";
                worksheet.Cells[1, 2, 3, 2].Merge = true;
                worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                List <Staff> staffList = staffControl.getStaffList();
                staffControl.shutDown();
                List <string> timeslotList = examControl.getTimeslot();
                examControl.shutDown();
                List <Exemption>        exemptionList     = null;
                List <InvigilationDuty> dutyList          = null;
                List <PaperExamined>    paperExaminedList = null;

                //row and column as counter
                int    rowNoStaff = 4;
                int    rowNoDate  = 1;
                int    colNoDate  = 3;
                int    colNoTotal = 0;
                string timeslotID = "EMPTY";

                //array as counter for printing logo purpose
                List <DateTime> dateList = new List <DateTime>();

                //print timeslot
                for (int i = 0; i < timeslotList.Count; i++)
                {
                    //checking to avoid printing the same date twice
                    if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
                    {
                        timeslotID = timeslotList[i];
                        int      year  = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
                        int      month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
                        int      day   = Convert.ToInt32(timeslotList[i].Substring(2, 2));
                        DateTime dt    = new DateTime(year, month, day);
                        dateList.Add(dt);

                        worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
                        worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
                        worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
                        worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        rowNoDate = 1;
                    }
                }

                //print total header
                worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                //assign the col of "Total" for writing inv duties
                colNoTotal = colNoDate;

                //set column width
                worksheet.Column(colNoDate).Width = 5.5;
                while (colNoDate >= 4)
                {
                    worksheet.Column((colNoDate--) - 1).Width = 6.5;
                }

                //count number of inv printed
                int countInvi = 0;

                //print staffs
                for (int i = 0; i < staffList.Count; i++)
                {
                    //check and print chief invigilator name
                    if (staffList[i].IsChief == 'Y')
                    {
                        //print chief invigilator
                        worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
                        worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;

                        //check and print exemption
                        exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
                        if (exemptionList.Count > 0)
                        {
                            for (int a = 0; a < exemptionList.Count; a++)
                            {
                                //get the column number to insert
                                int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);

                                //get the column number if not AM
                                if (exemptionList[a].Session.Equals("PM"))
                                {
                                    colToInsert += 1;
                                }
                                else if (exemptionList[a].Session.Equals("EV"))
                                {
                                    colToInsert += 2;
                                }

                                //insert exemption
                                worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
                                ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
                            }
                        }

                        //count total number of duties for each inv
                        int totalDuty = 0;

                        //check and print invigilation duty
                        dutyList          = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
                        paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
                        if (dutyList.Count > 0)
                        {
                            totalDuty = 0;

                            for (int a = 0; a < dutyList.Count; a++)
                            {
                                //get timeslot Date to compare with dutyList Date
                                int      year  = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
                                int      month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
                                int      day   = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
                                DateTime dt    = new DateTime(year, month, day);

                                //get the column number to insert
                                int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);

                                //get the column number if not AM
                                if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
                                {
                                    colToInsert += 1;
                                }
                                else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
                                {
                                    colToInsert += 2;
                                }

                                //retrieve course list(exam) on given location and timeslotID
                                List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);

                                //check if the chief is also an examiner
                                var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);

                                //insert only if no exemption is inserted
                                if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
                                {
                                    totalDuty++;
                                    worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;

                                    //compare duty type
                                    if (dutyList[a].Location.Equals("Block V") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("V");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block SE") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SE");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block SD") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SD");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block SB") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SB");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block R") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block Q") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("Q");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block PA, PA7-PA12") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA7");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block PA, PA1-PA6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA1");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block M") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("M");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block L") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("L");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block KS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("KS");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block H, H7-H14") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H7");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block H, H1-H6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H1");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block H") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Dewan Utama") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DU");
                                        ert.Size = 7;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Location.Equals("Block DS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
                                        ert.Size = 7;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                }

                                //if printing last duty in a duty list, print totalDuty
                                if (a + 1 == dutyList.Count)
                                {
                                    worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
                                }
                            }
                        }
                    }
                }

                //shutdown connection
                exemptionControl.shutDown();
                dutyControl.shutDown();
                paperExaminedControl.shutDown();

                //center "No." and all the ticks
                worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            }
            else
            {
                char facultyCode = '\0';
                //compare type to get facultyCode
                if (type.Equals("CNBL"))
                {
                    facultyCode = 'E';
                }
                else if (type.Equals("FEBE"))
                {
                    facultyCode = 'T';
                }
                else if (type.Equals("FAFB"))
                {
                    facultyCode = 'B';
                }
                else if (type.Equals("FASC"))
                {
                    facultyCode = 'A';
                }

                // Add a worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);

                // Set column width
                worksheet.Column(1).Width = 4.5;
                worksheet.Column(2).Width = 45;

                //print header
                worksheet.Cells[1, 1].Value       = "No.";
                worksheet.Cells[1, 1, 3, 1].Merge = true;
                worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                worksheet.Cells[1, 2].Value       = "Name";
                worksheet.Cells[1, 2, 3, 2].Merge = true;
                worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                List <Staff> staffList = staffControl.getStaffList();
                staffControl.shutDown();
                List <string> timeslotList = examControl.getTimeslot();
                examControl.shutDown();
                List <Exemption>        exemptionList     = null;
                List <InvigilationDuty> dutyList          = null;
                List <PaperExamined>    paperExaminedList = null;

                //row and column as counter
                int    rowNoStaff = 4;
                int    rowNoDate  = 1;
                int    colNoDate  = 3;
                int    colNoTotal = 0;
                string timeslotID = "EMPTY";

                //array as counter for printing logo purpose
                List <DateTime> dateList = new List <DateTime>();

                //print timeslot
                for (int i = 0; i < timeslotList.Count; i++)
                {
                    //checking to avoid printing the same date twice
                    if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
                    {
                        timeslotID = timeslotList[i];
                        int      year  = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
                        int      month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
                        int      day   = Convert.ToInt32(timeslotList[i].Substring(2, 2));
                        DateTime dt    = new DateTime(year, month, day);
                        dateList.Add(dt);

                        worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
                        worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
                        worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
                        worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
                        worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        rowNoDate = 1;
                    }
                }

                //print total header
                worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                //assign the col of "Total" for writing inv duties
                colNoTotal = colNoDate;

                //set column width
                worksheet.Column(colNoDate).Width = 5.5;
                while (colNoDate >= 4)
                {
                    worksheet.Column((colNoDate--) - 1).Width = 6.5;
                }

                //count inv number of inv printed
                int countInvi = 0;

                //print staffs
                for (int i = 0; i < staffList.Count; i++)
                {
                    //check and print invigilator based on faculty
                    if (staffList[i].IsInvi == 'Y' && staffList[i].FacultyCode == facultyCode)
                    {
                        //print invigilator
                        worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
                        worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;

                        //check and print exemption
                        exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
                        if (exemptionList.Count > 0)
                        {
                            for (int a = 0; a < exemptionList.Count; a++)
                            {
                                //get the column number to insert
                                int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);

                                //get the column number if not AM
                                if (exemptionList[a].Session.Equals("PM"))
                                {
                                    colToInsert += 1;
                                }
                                else if (exemptionList[a].Session.Equals("EV"))
                                {
                                    colToInsert += 2;
                                }

                                //insert exemption
                                worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
                                ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
                            }
                        }

                        //count total number of duties for each inv
                        int totalDuty = 0;

                        //check and print invigilation duty
                        dutyList          = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
                        paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
                        if (dutyList.Count > 0)
                        {
                            totalDuty = 0;

                            for (int a = 0; a < dutyList.Count; a++)
                            {
                                //get timeslot Date to compare with dutyList Date
                                int      year  = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
                                int      month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
                                int      day   = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
                                DateTime dt    = new DateTime(year, month, day);

                                //get the column number to insert
                                int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);

                                //get the column number if not AM
                                if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
                                {
                                    colToInsert += 1;
                                }
                                else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
                                {
                                    colToInsert += 2;
                                }

                                //retrieve course list(exam) on given location and timeslotID
                                List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);

                                //check if the chief is also an examiner
                                var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);

                                //insert only if no exemption is inserted
                                if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
                                {
                                    totalDuty++;
                                    worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;

                                    //compare duty type
                                    if (dutyList[a].Location.Equals("Block DS"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
                                        ert.Size = 7;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].CategoryOfInvigilator.Equals("Relief"))
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;
                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
                                        ert.Size = 7;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        if (dutyList[a].Duration != 2)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Duration == 3)
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("3");
                                        ert.Size = 7;
                                    }
                                    else if (dutyList[a].Duration == 2)
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }
                                    }
                                    else if (dutyList[a].Duration == 1)
                                    {
                                        ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
                                        ert.Size = 12;

                                        if (result.ToList().Count > 0)
                                        {
                                            ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
                                            ert.Size = 7;
                                        }

                                        ert      = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("1");
                                        ert.Size = 7;
                                    }
                                }

                                //if printing last duty in a duty list, print totalDuty
                                if (a + 1 == dutyList.Count)
                                {
                                    worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
                                }
                            }
                        }
                    }
                }

                //shutdown connection
                exemptionControl.shutDown();
                dutyControl.shutDown();

                //center "No." and all the ticks
                worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            }

            //save new workbook
            package.Save();
        }
Beispiel #3
0
        private void writeToExcelTT(FileInfo newFile)
        {
            // EPPlus library is required
            ExcelPackage package = new ExcelPackage(newFile);

            // Add a worksheet to the empty workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inv.TT");

            // Set column width
            worksheet.Column(1).Width  = 4;
            worksheet.Column(2).Width  = 12;
            worksheet.Column(3).Width  = 0.8;
            worksheet.Column(4).Width  = 19;
            worksheet.Column(5).Width  = 0.8;
            worksheet.Column(6).Width  = 19;
            worksheet.Column(7).Width  = 11;
            worksheet.Column(8).Width  = 12;
            worksheet.Column(9).Width  = 0.8;
            worksheet.Column(10).Width = 25;

            MaintainExaminationControl      examControl   = new MaintainExaminationControl();
            MaintainInvigilationDutyControl invControl    = new MaintainInvigilationDutyControl();
            MaintainStaffControl            staffControl  = new MaintainStaffControl();
            MaintainCourseControl           courseControl = new MaintainCourseControl();

            //Get lists of examination, timeslot and invigilationDuty
            List <string>           timeslotList = examControl.getTimeslot();
            List <Examination>      examList     = null;
            List <InvigilationDuty> invDutyList  = null;

            //To keep track of the column number for A, B, D, F, G, H, J
            int[] columnNo = new int[7] {
                2, 2, 2, 2, 2, 2, 2
            };

            //Loop through examList and invDutyList with each timeslotID in timeslotList
            for (int y = 0; y < timeslotList.Count; y++)
            {
                //Get new list for every iteration with different timeslotID passed in
                examList    = examControl.searchExamination(timeslotList[y]);
                invDutyList = invControl.searchInvigilationDuty2(timeslotList[y]);

                //Get date in string
                int      year  = Convert.ToInt32("20" + timeslotList[y].Substring(6, 2));
                int      month = Convert.ToInt32(timeslotList[y].Substring(4, 2));
                int      day   = Convert.ToInt32(timeslotList[y].Substring(2, 2));
                DateTime dt    = new DateTime(year, month, day);

                //Get session in string
                string session = timeslotList[y].Substring(0, 2);
                if (session.Equals("AM"))
                {
                    session = "MORNING SESSION";
                }
                else if (session.Equals("PM"))
                {
                    session = "AFTERNOON SESSION";
                }
                else
                {
                    session = "EVENING SESSION";
                }

                // Add data into cells, Date and Sessiom
                worksheet.Cells["A" + columnNo[0].ToString()].Value = dt.DayOfWeek.ToString().ToUpper() + ", " + dt.Day + " " + dt.ToString("MMMM").ToUpper() + " " + dt.Year;
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                worksheet.Cells["A" + columnNo[0].ToString()].Value = session;
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                // New row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    if (a != 0)
                    {
                        columnNo[a] = columnNo[0] + 1;
                    }
                }
                columnNo[0]++;

                // Add data into cells, Chief and Relief Invigilator for West Campus
                worksheet.Cells["B" + columnNo[1].ToString()].Value = "West Campus";
                worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
                worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
                worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
                worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
                worksheet.Cells["H" + columnNo[5].ToString()].Value   = "West Campus";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
                worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";

                // New row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    columnNo[a] += 1;
                }

                //print Chief and Relief title
                worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
                worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";

                //print Chief and Relief invigilators for West Campus
                for (int i = 0; i < invDutyList.Count; i++)
                {
                    Staff  staff     = staffControl.getStaffName(invDutyList[i].StaffID);
                    string staffName = staff.Title + " " + staff.Name;

                    if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && !invDutyList[i].Location.Equals("Block SA") && !invDutyList[i].Location.Equals("Block SB") && !invDutyList[i].Location.Equals("Block SC") && !invDutyList[i].Location.Equals("Block SD") && !invDutyList[i].Location.Equals("Block SE") && !invDutyList[i].Location.Equals("Block SF") && !invDutyList[i].Location.Equals("Block SG"))
                    {
                        worksheet.Cells["D" + columnNo[2].ToString()].Value   = "(" + invDutyList[i].Location + ")";
                        worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
                        worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
                    }
                    else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("West Campus"))
                    {
                        worksheet.Cells["I" + columnNo[6].ToString()].Value   = "-";
                        worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
                    }
                }

                //new row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    if (a != 6)
                    {
                        columnNo[a] = columnNo[6] + 2;
                    }
                }
                columnNo[6] += 2;

                // Add data into cells, Chief and Relief Invigilator for East Campus
                worksheet.Cells["B" + columnNo[1].ToString()].Value = "East Campus";
                worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
                worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
                worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
                worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
                worksheet.Cells["H" + columnNo[5].ToString()].Value   = "East Campus";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
                worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";

                // New row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    columnNo[a] += 1;
                }

                //print Chief and Relief title
                worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
                worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";

                //print Chief and Relief invigilators for East Campus
                for (int i = 0; i < invDutyList.Count; i++)
                {
                    Staff  staff     = staffControl.getStaffName(invDutyList[i].StaffID);
                    string staffName = staff.Title + " " + staff.Name;

                    if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && (invDutyList[i].Location.Equals("Block SA") || invDutyList[i].Location.Equals("Block SB") || invDutyList[i].Location.Equals("Block SC") || invDutyList[i].Location.Equals("Block SD") || invDutyList[i].Location.Equals("Block SE") || invDutyList[i].Location.Equals("Block SF") || invDutyList[i].Location.Equals("Block SG")))
                    {
                        worksheet.Cells["D" + columnNo[2].ToString()].Value   = "(" + invDutyList[i].Location + ")";
                        worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
                        worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
                    }
                    else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("East Campus"))
                    {
                        worksheet.Cells["I" + columnNo[6].ToString()].Value   = "-";
                        worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
                    }
                }

                // New row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    if (a != 6)
                    {
                        columnNo[a] = columnNo[6] + 2;
                    }
                }
                columnNo[6] += 2;

                //print header
                worksheet.Cells["A" + columnNo[0].ToString()].Value = "Venue";
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0].ToString()].Merge = true;
                worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                worksheet.Cells["D" + columnNo[2].ToString()].Value = "Paper";
                worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2].ToString()].Merge = true;
                worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                worksheet.Cells["G" + columnNo[4].ToString()].Value = "Programme";
                worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                worksheet.Cells["H" + columnNo[5].ToString()].Value = "Duration";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                worksheet.Cells["H" + columnNo[5].ToString()].Value = "(Hours)";
                worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                worksheet.Cells["J" + columnNo[6].ToString()].Value = "Invigilators";
                worksheet.Cells["J" + columnNo[6]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                //temp var used to store cell index for adding border
                int rowFrom = 0;

                //set top and bottom border for header
                using (var range = worksheet.Cells["A" + (columnNo[0] - 2).ToString() + ":" + "J" + (columnNo[0] - 2).ToString()])
                {
                    range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    rowFrom = columnNo[0] - 2;
                }

                using (var range = worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()])
                {
                    range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                //new row
                for (int a = 0; a < columnNo.Length; a++)
                {
                    if (a != 5)
                    {
                        columnNo[a] = columnNo[5] + 1;
                    }
                }
                columnNo[5]++;

                string venueID   = "";
                string staffID   = "";
                int    examCount = 0;
                int    invCount  = 0;

                //print examinations and invigilators
                for (int i = 0; i < examList.Count(); i++)
                {
                    //To avoid same repeating VenueID
                    if (!venueID.Equals(examList[i].VenueID))
                    {
                        if (invCount > examCount)
                        {
                            //new row
                            for (int a = 0; a < columnNo.Length; a++)
                            {
                                if (a != 6)
                                {
                                    columnNo[a] = columnNo[6] + 1;
                                }
                            }
                            columnNo[6]++;
                        }
                        else
                        {
                            //new row
                            for (int a = 0; a < columnNo.Length; a++)
                            {
                                if (a != 1)
                                {
                                    columnNo[a] = columnNo[1] + 1;
                                }
                            }
                            columnNo[1]++;
                        }

                        examCount = 0;
                        invCount  = 0;
                        venueID   = examList[i].VenueID;
                        worksheet.Cells["A" + columnNo[0].ToString()].Value = venueID;
                        worksheet.Cells["A" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }

                    examCount++;

                    worksheet.Cells["B" + columnNo[1].ToString()].Value = "(" + examList[i].SitFrom + "-" + examList[i].SitTo + ")";
                    worksheet.Cells["B" + columnNo[1]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    worksheet.Cells["D" + columnNo[2]++.ToString()].Value = examList[i].CourseCode + " " + courseControl.getCourseTitle(examList[i].CourseCode);

                    if (examList[i].PaperType == 'M')
                    {
                        worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year;
                    }
                    else
                    {
                        worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].PaperType + "(" + examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year + ")";
                    }

                    worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    //Find duration in invDutyList with the same venueID
                    var value = invDutyList.First(x => x.VenueID.Equals(examList[i].VenueID));
                    worksheet.Cells["H" + columnNo[5].ToString()].Value = value.Duration;
                    worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    //Get the first staffID provided with timeslotID and venueID, to prevent printing repeated invigilator for same venueID
                    var v = invDutyList.First(x => x.TimeslotID.Equals(timeslotList[y]) && x.VenueID.Equals(venueID));
                    staffID = v.StaffID;

                    //Print invigilator
                    for (int b = 0; b < invDutyList.Count; b++)
                    {
                        if (invDutyList[b].TimeslotID.Equals(timeslotList[y]) && invDutyList[b].VenueID.Equals(examList[i].VenueID))
                        {
                            //Avoid printing repeated invigilator for same venueID
                            if (!staffID.Equals(invDutyList[b].StaffID) || invCount == 0)
                            {
                                invCount++;
                                Staff  staff     = staffControl.getStaffName(invDutyList[b].StaffID);
                                string staffName = staff.Title + " " + staff.Name;

                                //check for in-charge
                                if (invDutyList[b].CategoryOfInvigilator.Equals("In-charge"))
                                {
                                    worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName + "*";
                                }
                                else
                                {
                                    worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
                                }
                            }
                            else
                            {
                                break;
                            }
                        }
                    }
                }

                //set border
                using (var range = worksheet.Cells["A" + columnNo[1].ToString() + ":" + "J" + columnNo[1].ToString()])
                {
                    range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                using (var range = worksheet.Cells["B" + rowFrom.ToString() + ":" + "B" + columnNo[1].ToString()])
                {
                    range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                using (var range = worksheet.Cells["F" + rowFrom.ToString() + ":" + "F" + columnNo[1].ToString()])
                {
                    range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                using (var range = worksheet.Cells["G" + rowFrom.ToString() + ":" + "G" + columnNo[1].ToString()])
                {
                    range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                using (var range = worksheet.Cells["H" + rowFrom.ToString() + ":" + "H" + columnNo[1].ToString()])
                {
                    range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                }

                //leaves 20 rows between session
                for (int a = 0; a < columnNo.Length; a++)
                {
                    if (a != 1)
                    {
                        columnNo[a] = columnNo[1] + 20;
                    }
                }
                columnNo[1] += 20;
            }

            // save new workbook
            package.Save();
        }