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 validate_sheet_names( *, wb: Workbook, filename: Union[Path, str], sheet_name: str, sheet_names: List[str], start_time: float, original_stdout: io.TextIOWrapper, output_url: str ) -> Workbook: """ Parameters ---------- wb : Workbook A workbook. filename : Union[Path, str] The file containing the workbook. sheet_name : str A sheet name in the workbook. sheet_names : List[str] The sheet names in the workbook. start_time : float The start time of the script. original_stdout : io.TextIOWrapper The buffered text stream for the html output. output_url : str The html filename. Returns ------- wb : Workbook The workbook with a revised sheetname. Example ------- >>> import datasense as ds >>> wb = validate_sheet_names( >>> wb=wb, >>> filename=filename, >>> sheet_name=sheet_name, >>> sheet_names=sheet_names, >>> start_time=start_time, >>> original_stdout=original_stdout, >>> output_url=output_url >>> ) """ if sheet_name not in sheet_names and len(sheet_names) != 1: print('Manually rename one of these sheets:') print(wb.sheetnames) print('Then re-run script') print('XXX File NOT OK XXX') stop_time = time.time() report_summary( start_time=start_time, stop_time=stop_time ) exit_script( original_stdout=original_stdout, output_url=output_url ) elif sheet_name not in sheet_names and len(sheet_names) == 1: print('One sheet found and it was re-named.') ws = wb.active ws.title = sheet_name wb.save(filename=filename) elif sheet_name in sheet_names and len(sheet_names) != 1: sheet_names_removed = [x for x in sheet_names if x != sheet_name] for sheet in sheet_names_removed: wb.remove(worksheet=wb[sheet]) print('Sheet names removed:') print(sheet_names_removed) wb.save(filename=filename) return wb
sheet = 'trading_bot_revenue' # workbook 생성 (덮어쓰기) # write_wb = Workbook() if os.path.isfile(xlsx_file): write_wb = openpyxl.load_workbook(xlsx_file) else: write_wb = Workbook() write_ws = write_wb.create_sheet(sheet) # Set write worksheet sheet_list = write_wb.sheetnames flag = 0 for sheet_name in sheet_list: if sheet_name == 'Sheet': # 기본 생성 시트 제거 write_wb.remove(write_wb[sheet_name]) if sheet_name == sheet: flag = 1 break if flag == 0: write_ws = write_wb.create_sheet('trading_bot_revenue') else: write_ws = write_wb['trading_bot_revenue'] # 작업할 workbook 내 sheet 활성화 #write_ws = write_ws.active write_ws['A1'] = '날짜' write_ws['B1'] = '티커' # 암호화폐 구분 write_ws['C1'] = '구분' # 매도, 매수 구분 write_ws['D1'] = '매수금액' # 매수 금액
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()