def write(c,book): sheet = book.add_sheet("ProgramTotals") freezePanes(sheet,1) informationStartRow = 1 columnWidth(sheet, 6) c.execute("SELECT DISTINCT course_id FROM courses;") courseList = c.fetchall() c.execute("SELECT DISTINCT program FROM students;") programsList = c.fetchall() courseNameStr = 'Course Name' termNameStr = 'Term' enrollmentNameStr = 'Enrollments' fYrArtsStr = 'Arts (1st)' #these help differentiate between 1st year artsci HONOURS and upper year artsci HONOURS fYrSciStr = 'Science (1st)' upYrArtsStr = 'Arts Hon (2-4)' upYrSciStr = 'Science Hon (2-4)' columns = { courseNameStr : 0, termNameStr : 1, enrollmentNameStr : 2, fYrArtsStr : 3, fYrSciStr : 4, upYrArtsStr : 5, upYrSciStr : 6, } #Creating all the headings (Course Name, BA, BAH, BSCH...) and hard code 1st year arts and sci for columnName in columns: #write hardcoded column headings (Name, total enrollment, etc) sheet.write(0, columns[columnName], columnName) hardColumns = len(columns) for program in programsList: #Writing all the column headings to the excel sheet columns[program] = programsList.index(program) + hardColumns sheet.write(0, columns[program], program) count = informationStartRow for course in courseList: #Outputs the course codes in column 0 (ANAT 215, 216) course = course[0] #unpack the tuple courseName = data.grabCourseName(c, course) sheet.write(count, columns[courseNameStr], courseName) term = data.grabCourseTerm(c,course) sheet.write(count, columns[termNameStr], term) enrollments = data.grabEnrollmentNumber(c,course) sheet.write(count, columns[enrollmentNameStr], enrollments) for program in programsList: #Outputs enrollments for all programs (except 1st year Arts Sci) studCount = data.grabStudentEnrollment(c, program[0], course) sheet.write(count, columns[program], studCount) #Breaking down between 1st year and upper year Arts and Sci HONOURS firstYrArts = data.grabStudentProgYearEnroll(c, "BAH", 1, course) firstYrSci = data.grabStudentProgYearEnroll(c, "BSCH",1, course) upYrArts = data.grabStudentEnrollment(c, "BAH", course) - firstYrArts upYrSci = data.grabStudentEnrollment(c, "BSCH", course) - firstYrSci sheet.write(count, columns[fYrArtsStr], firstYrArts) sheet.write(count, columns[fYrSciStr], firstYrSci) sheet.write(count, columns[upYrArtsStr], upYrArts) sheet.write(count, columns[upYrSciStr], upYrSci) count = count + 1 return True
def write(c, book): ''' Tried to imitate what Dan Adams had for his "Enrollment by Program" Sheet For each program, it will have a column for # of weighted enrollments, and also percentage of total enrollments ''' sheet = book.add_sheet("Weighted Program Enroll") twoDecimalStyle = xlwt.XFStyle() twoDecimalStyle.num_format_str = '0.00' freezePanes(sheet,1) informationStartRow = 1 columnWidth(sheet, 9) c.execute("SELECT DISTINCT course_id FROM courses;") courseList = c.fetchall() c.execute("SELECT DISTINCT program FROM students;") programsList = c.fetchall() courseNameStr = 'Course Name' termNameStr = 'Term' enrollmentNameStr = 'W. Enrollments' DBMSNameStr = "W. DBMS" DBMSPerNameStr = "% DBMS" #List of all the DBMS plans. This excludes the minors. DBMSPlans = ['LISC-G-BSC', 'LISC-M-BSH', 'LISC-P-BSH', 'LISC-Z-3', 'BCHM-G-BSC', 'BCHM-M-BSH', 'BCHM-P-BSH', 'BCHM-Z-3', ] ArtSciNameStr = "W. ArtSci" #all other plans with degree program BA, BAH, BSC... ArtSciPerNameStr = "% ArtSci" NursNameStr = "W. NURS" #all those with BNURS NursPerNameStr = "% NURS" EngNameStr = "W. ENG" #all those with BSCE, , etc EngPerNameStr = "% ENG" CommNameStr = "W. COMM" CommPerNameStr = "% COMM" columns = { courseNameStr : 0, termNameStr : 1, enrollmentNameStr : 2, DBMSNameStr : 3, DBMSPerNameStr: 4, ArtSciNameStr : 5, ArtSciPerNameStr : 6, NursNameStr : 7, NursPerNameStr : 8, EngNameStr : 9, EngPerNameStr : 10, CommNameStr : 11, CommPerNameStr : 12, } for columnName in columns: sheet.write(0, columns[columnName], columnName) count = informationStartRow courseWeightedTotal = 0 for course in courseList: course = course[0] courseWeight = data.grabCourseCredits(c, course) weighting = courseWeight / 3.0 #the weighting adjusts the number of Full Time Enrollments (FTE) courseName = data.grabCourseName(c, course) sheet.write(count, columns[courseNameStr], courseName) term = data.grabCourseTerm(c, course) sheet.write(count, columns[termNameStr], term) enrollments = data.grabEnrollmentNumber(c, course) enrollments = enrollments * weighting #total weighted sheet.write(count, columns[enrollmentNameStr], enrollments) courseWeightedTotal = courseWeightedTotal + enrollments DBMSEnrollments = 0 for plan in DBMSPlans: DBMSEnrollments = DBMSEnrollments + data.grabStudentPlanEnroll(c,plan,course) #DBMS Columns DBMSEnrollments = DBMSEnrollments * weighting #number of all those in DBMS majors sheet.write(count,columns[DBMSNameStr],DBMSEnrollments) DBMSPerEnrollments = DBMSEnrollments / enrollments #Percentage of all those in DBMS majors sheet.write(count, columns[DBMSPerNameStr], DBMSPerEnrollments * 100, twoDecimalStyle) #Nursing Columns NursEnrollments = data.grabStudentEnrollment(c, "BNSC", course) sheet.write(count, columns[NursNameStr], NursEnrollments) NursPerEnrollments = NursEnrollments / enrollments sheet.write(count, columns[NursPerNameStr], NursPerEnrollments * 100, twoDecimalStyle) #Engineering Columns EngEnrollments = data.grabStudentEnrollment(c, "BSCE", course) EngEnrollments = EngEnrollments + data.grabStudentEnrollment(c, "UENG", course) sheet.write(count, columns[EngNameStr], EngEnrollments) EngPerEnrollments = EngEnrollments / enrollments sheet.write(count, columns[EngPerNameStr], EngPerEnrollments * 100, twoDecimalStyle) #Commerce Columns CommEnrollments = data.grabStudentEnrollment(c, "BCOM", course) sheet.write(count, columns[CommNameStr], CommEnrollments) CommPerEnrollments = CommEnrollments / enrollments sheet.write(count, columns[CommPerNameStr], CommPerEnrollments * 100, twoDecimalStyle) #ArtSci Columns ArtSciEnrollments = enrollments - DBMSEnrollments - NursEnrollments - EngEnrollments - CommEnrollments sheet.write(count, columns[ArtSciNameStr], ArtSciEnrollments) ArtSciPerEnrollments = ArtSciEnrollments / enrollments sheet.write(count, columns[ArtSciPerNameStr], ArtSciPerEnrollments * 100, twoDecimalStyle) count = count + 1 return True