def createExcelFile(Path, sheetName): try: wb = Workbook(Path) ws = wb[sheetName] wb.close() except: wb = Workbook() ws = wb.create_sheet(sheetName) wb.save(Path) wb = openpyxl.load_workbook(Path) wb.remove(wb['Sheet']) wb.save(Path) wb.close()
def write_xlsx(self, filename: str) -> None: """ Writes the solution to an Excel XLSX file (and its problem, for data safety). Args: filename: Name of file to write. """ log.info(f"Writing output to: {filename}") # Not this way -- we can't then set column widths. # wb = Workbook(write_only=True) # doesn't create default sheet # Instead: wb = Workbook() wb.remove(wb.worksheets[0]) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Allocations, by student # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ss = wb.create_sheet(SheetNames.STUDENT_ALLOCATIONS) ss.append([ "Student", "Project", "Supervisor", "Student's rank of allocated project (dissatisfaction score)", ]) for student, project in self._gen_student_project_pairs(): ss.append([ student.name, project.title, project.supervisor_name(), student.dissatisfaction(project), ]) autosize_openpyxl_worksheet_columns(ss) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Allocations, by project # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ps = wb.create_sheet(SheetNames.PROJECT_ALLOCATIONS) ps.append([ "Project", "Supervisor", "Student(s)", "Students' rank(s) of allocated project (dissatisfaction score)", "Project supervisor's rank(s) of allocated student(s) (dissatisfaction score)", # noqa ]) for project in self.problem.sorted_projects(): student_names = [] # type: List[str] supervisor_dissatisfactions = [] # type: List[float] student_dissatisfactions = [] # type: List[float] for student in self.allocated_students(project): student_names.append(student.name) supervisor_dissatisfactions.append( project.dissatisfaction(student)) student_dissatisfactions.append( student.dissatisfaction(project)) ps.append([ project.title, project.supervisor_name(), ", ".join(student_names), ", ".join(str(x) for x in student_dissatisfactions), ", ".join(str(x) for x in supervisor_dissatisfactions), ]) autosize_openpyxl_worksheet_columns(ps) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Popularity of projects # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ pp = wb.create_sheet(SheetNames.PROJECT_POPULARITY) pp.append([ "Project", "Supervisor", "Total dissatisfaction score from all students", "Allocated student(s)", "Number of students expressing a preference", "Students expressing a preference", ]) proj_to_unpop = {} # type: Dict[Project, float] for project in self.problem.projects: unpopularity = 0 for student in self.problem.students: unpopularity += student.dissatisfaction(project) proj_to_unpop[project] = unpopularity for project, unpopularity in sorted(proj_to_unpop.items(), key=operator.itemgetter(1, 0)): allocated_students = ", ".join( student.name for student in self.allocated_students(project)) student_prefs = {} # type: Dict[Student, float] for student in self.problem.students: if student.preferences.actively_expressed_preference_for( project): student_prefs[student] = student.preferences.preference( project) student_details = [] # type: List[str] for student, studpref in sorted(student_prefs.items(), key=operator.itemgetter(1, 0)): student_details.append(f"{student.name} ({studpref})") pp.append([ project.title, project.supervisor_name(), unpopularity, allocated_students, len(student_details), ", ".join(student_details), ]) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Software, settings, and summary information # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ zs = wb.create_sheet(SheetNames.INFORMATION) is_stable, instability_reason = self.stability() zs_rows = [ ["SOFTWARE DETAILS"], [], ["Software", "pdn_project_allocation"], ["Version", VERSION], ["Version date", VERSION_DATE], [ "Source code", "https://github.com/RudolfCardinal/pdn_project_allocation" ], ["Author", "Rudolf Cardinal ([email protected])"], [], ["RUN INFORMATION"], [], ["Date/time", datetime.datetime.now()], [ "Overall weight given to student preferences", 1 - self.problem.config.supervisor_weight ], [ "Overall weight given to supervisor preferences", self.problem.config.supervisor_weight ], ["Command-line parameters", cmdline_quote(sys.argv)], ["Config", str(self.problem.config)], [], ["SUMMARY STATISTICS"], [], [ "Student dissatisfaction median", self.student_dissatisfaction_median() ], [ "Student dissatisfaction mean", self.student_dissatisfaction_mean() ], [ "Student dissatisfaction variance", self.student_dissatisfaction_variance() ], [ "Student dissatisfaction minimum", self.student_dissatisfaction_min() ], [ "Student dissatisfaction minimum", self.student_dissatisfaction_max() ], [], [ "Supervisor dissatisfaction (with each student) median", self.supervisor_dissatisfaction_median() ], [ "Supervisor dissatisfaction (with each student) mean", self.supervisor_dissatisfaction_mean() ], [ "Supervisor dissatisfaction (with each student) variance", self.supervisor_dissatisfaction_variance() ], [ "Supervisor dissatisfaction (with each student) minimum", self.supervisor_dissatisfaction_min() ], [ "Supervisor dissatisfaction (with each student) maximum", self.supervisor_dissatisfaction_max() ], [], ["Stable marriages?", str(is_stable)], ["If unstable, reason:", instability_reason] ] for row in zs_rows: zs.append(row) autosize_openpyxl_column(zs, 0) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # Problem definition # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ self.problem.write_to_xlsx_workbook(wb) wb.save(filename) wb.close()