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(self): self.workbook = Workbook() self.sheet = self.workbook.get_active_sheet() if not len(self.header): raise BRException("Header not provided") if self.data and len(self.header) != len(self.data[0]): raise BRException("Header and Data must be in same format") # Write headers row = 1 col = 1 for col_header in self.header: self.sheet.cell(row=row, column=col, value=col_header) col += 1 row += 1 # Now write data for data_row in self.data: col = 1 for col_value in data_row: col_value = col_value if col_value else '' self.sheet.cell(row=row, column=col, value=col_value) col += 1 row += 1 if self.response: self.workbook.save(self.response) else: if self.file_name: self.workbook.save(self.file_name)
def generate(self): self.activate() repBook = Workbook() currentSheet = repBook.active for row in self.sheet.values: if row[0] == "Tiempo": currentSheet.append(["Tiempo", "Sensor 1" , "Sensor 2" , "Sensor 3" , "Sensor 4" ]) else: a = row[0].split()[0].split('-') print(self.fmes.get()) if int(a[0]) < int(self.fanio.get())-2000: pass elif int(a[0]) > int(self.sanio.get())-2000: break elif int(a[1]) < int(self.fmes.get()): pass elif int(a[1]) > int(self.smes.get()): break elif int(a[2]) < int(self.fdia.get()): pass elif int(a[2]) > int(self.sdia.get()): break else: currentSheet.append(row) repBook.save("Reporte.xlsx")
def createOutputExcel(outputData): wb = Workbook() ws = wb.active ws.title = 'Budget Projection' columnNames = [ "Name", "SA Rate", "Deltek Rate", "Avg Run", "Hours", "Dollars" ] for w in range(1, nTotalNumWeeks + 1): nThYear = int(w / 54) nYear = 2020 + int(nThYear) nWeek = w - (53 * nThYear) columnNames.append( "Week " + str(nWeek) + " \n[" + date.fromisocalendar(nYear, nWeek, 7).strftime("%m/%d/%y") + "]") ws.freeze_panes = 'G3' headerCnt = len(columnNames) for cells in ws.iter_rows(1, 1, 1, headerCnt): for cell in cells: cell.value = columnNames[cell.column - 1] cell.alignment = Alignment(wrapText=True, horizontal='center') rowNum = 2 rowCnt = len(outputData) + 1 for name, stats in outputData.items(): for cells in ws.iter_rows(rowNum, rowNum, 1, headerCnt): cells[0].value = name ws.column_dimensions[cells[0].column_letter].width = 25 cells[1].value = stats['SpringAhead Rate'] cells[1].number_format = '"$"#' ws.column_dimensions[cells[1].column_letter].width = 8 cells[2].value = stats['Deltek Rate'] cells[2].number_format = '"$"#' ws.column_dimensions[cells[2].column_letter].width = 8 cells[3].value = stats['Avg Run'] cells[3].number_format = '0.00' ws.column_dimensions[cells[3].column_letter].width = 7 cells[4].value = stats['Hours'] cells[4].number_format = '#,##0.00' ws.column_dimensions[cells[4].column_letter].width = 9 cells[5].value = stats['Dollars'] cells[5].number_format = '"$"#,##0.00' ws.column_dimensions[cells[5].column_letter].width = 14 for w in range(1, nTotalNumWeeks + 1): cells[w + 5].value = float(stats[w]) if rowNum > rowCnt: cells[w + 5].number_format = '"$"#,##0.00' else: cells[w + 5].number_format = '#,##0.00' ws.column_dimensions[cells[w + 5].column_letter].width = 12 ws.column_dimensions[cells[w + 5].column_letter].height = 29 rowNum = rowNum + 1 setExcelFormulas(ws, rowCnt, headerCnt) wb.save(outputFile)
def _xlsx_save(self, result: [dict], file_name: str): """data is saved in excel format""" wb = Workbook() sheet = wb.active for i, ldict in enumerate(result, 1): if i == 1: title_list: list = list(ldict.keys()) sheet.append(title_list) sheet.append(list(ldict.values())) wb.save(fr"{file_name}.xlsx")
def btnDownloadClicked(self): result_file = Workbook() sheet1 = result_file.active sheet1.title = 'result' idx = ["업무(결과값)", "수행 내용", "이름", "직급", "직급 NO."] for i in range(5): sheet1.cell(1, i+1).value = idx[i] for cell in self.result: sheet1.append(cell) result_file.save('./result' + datetime.today().strftime("%Y%m%d%H%M%S") + '.xlsx') QtWidgets.QMessageBox.about(self, "저장완료", "파일이 저장되었습니다.")
def create(cls) -> ExcelSheet: """Creates an excel worksheet from nothing Factory method to initialize an object Returns ------- excel worksheet: an opened new and empty excel worksheet object """ wb = Workbook() return cls(wb)
def checkHolds(port, driver): driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ0087_00000:_idsc00001:_idsc00003_6:_idsc00007").click() # driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:portId").send_keys(port) driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:_idsc00020").click() onHold = [] bonds = [] done = False j=2 driver.implicitly_wait(0) try: driver.find_element_by_css_selector("span[title='Page 1']").click() except: pass driver.implicitly_wait(60) while not done: cells = driver.find_elements_by_css_selector("table[class='datat']>tbody>tr>td") i=0 while i<min(1500, len(cells)): driver.execute_script("arguments[0].scrollIntoView();", cells[i]) # bonds.append(cells[i+3].text) # if not todaysDate in cells[i+10].text: if "Y" in cells[i+14].text: onHold.append(cells[i+3].text) # cells[i+3].find_element_by_tag_name("input").send_keys(port) i+=15 # else: # break driver.implicitly_wait(0) try: driver.find_element_by_css_selector("span[title='Page " +str(j)+ "']").click() j+=1 except: done=True driver.implicitly_wait(60) if len(bonds)>0: filepath = r"J:\INBOND CLOSURE TRACKING\Open Bonds - " + datetime.now().strftime("%Y-%m-%d %H-%M-%S") + ".xlsx" wb = Workbook() ws = wb.active for i in range(len(bonds)): ws.cell(row=i+1, column=1).value = bonds[i] wb.save(filepath) if len(onHold)>0: print("IN-BOND #S ON HOLD:") holdString = "IN-BOND #S ON HOLD:\n" for bond in onHold: print(bond) holdString = holdString+bond + "\n" HelperFunctions.popUpOK(holdString)
def __write_wb(self, wb_name, worksheet_data): dest_wb = Workbook(write_only=True) for ws_name, ws_data in worksheet_data.iteritems(): if ws_data == []: continue ws = dest_wb.create_sheet(title=ws_name) ws_data_transposed = zip(*ws_data) for row in ws_data_transposed: ws.append(row) dest_wb.save(os.path.join(self.output_dir, wb_name))
def store2xlsx(self): ''' select company info and store to xlsx ''' odb = OperateDB() # current date datee = datetime.datetime.now().strftime("%Y-%m-%d") fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx') wb = Workbook() sheet = wb.active # print sheet.max_row # 设置单元格所有框线 thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # 写标题 for i in range(self.__indexName.__len__()): _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i]) stockInfos = odb.selectFromCompany( MyUtil.selectCompany.replace('%s', MyUtil.indexs)) keyList = stockInfos.keys() # 按照公司代码排序 keyList.sort() # print keyList # 第一行为标题,从第二行开始为实际数据 rowNum = 2 for i in keyList: for j in range(len(stockInfos[i])): _ = sheet.cell(column=j + 1, row=rowNum, value=stockInfos[i][j]) sheet.cell(column=j + 1, row=rowNum).border = thin_border rowNum += 1 # 每100条保存一下 if int(i) % 100 == 0: wb.save(fileAbsPath) # 最后保存一下 wb.save(fileAbsPath)
def convert_to_xlxs(content: Any) -> Workbook: """Convert old workbook formats xls into xlxs""" xlsBook = xlrd.open_workbook(file_contents=content) workbook = Workbook() for i in range(0, xlsBook.nsheets): xlsSheet = xlsBook.sheet_by_index(i) sheet = workbook.active if i == 0 else workbook.create_sheet() sheet.title = xlsSheet.name for row in range(0, xlsSheet.nrows): for col in range(0, xlsSheet.ncols): sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value( row, col) return workbook
def convert_to_xlxs(fh: BinaryIO) -> Optional[Workbook]: """Convert old workbook formats xls into xlxs""" if not _HAVE_XLRD: logger.error("xlrd module not installed. Cannot convert XLS file.") return None file_content = fh.read() fh.seek(0) xlsBook = xlrd.open_workbook(file_contents=file_content) workbook = Workbook() for i in range(0, xlsBook.nsheets): xlsSheet = xlsBook.sheet_by_index(i) sheet = workbook.active if i == 0 else workbook.create_sheet() sheet.title = xlsSheet.name for row in range(0, xlsSheet.nrows): for col in range(0, xlsSheet.ncols): sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value(row, col) return workbook
def __init__(self, archive, workbook_part_name): self.archive = archive self.workbook_part_name = workbook_part_name self.wb = Workbook() self.sheets = []
# if no from_loc given, assume last sheet if from_loc is None: from_loc = len(sheets) - 1 # if no to_loc given, assume first if to_loc is None: to_loc = 0 sheet = sheets.pop(from_loc) sheets.insert(to_loc, sheet) if __name__ == "__main__": try: ato_report = filedialog.askopenfilename(title="ato_report", filetypes=[("All files", "*")]) storage_report = filedialog.askopenfilename(title="storage_report", filetypes=[("All files", "*")]) summary_workbook = Workbook() ws = summary_workbook.active ws.title = "Summary" summary_workbook.save("Summary.xlsx") ato = ATO_check(ato_report, storage_report) ato.start_to_run() input("Press <Enter>") except Exception as err: messagebox.showerror("Warning!", err) with open(os.path.join(os.getcwd(), "error.txt"), "w+") as f: traceback.print_exc(file=f) print(err) exit()
def save_trade(self, order: OrderData, grid: CtaGrid): """ 将交易费用保存于excel """ if self.get_engine_type() == EngineType.BACKTESTING: return trade_save_path = get_folder_path('data') xlsx_name = f"{self.strategy_name}_trade.xlsx" xlsx_file = str(trade_save_path.joinpath(xlsx_name)) if os.path.exists(xlsx_file): workbook = load_workbook(xlsx_file) my_sheet = workbook.get_sheet_by_name("Mysheet") row = my_sheet.max_row row += 1 else: workbook = Workbook() my_sheet = workbook.create_sheet("Mysheet", index=0) row = 1 head = [ '品种', '开仓时间', '平仓时间', '方向', '数量', '目标开仓价格', '目标平仓价格', '实际开仓价格', '实际平仓价格', '开仓手续费', '平仓手续费', '平仓收益', '净收入', '滑点' ] for i, item in enumerate(head): my_sheet.cell(row, i + 1, item) my_sheet.column_dimensions["A"].width = 20 my_sheet.column_dimensions["B"].width = 20 my_sheet.column_dimensions["C"].width = 20 row += 1 my_sheet.cell(row, 1, grid.vt_symbol) my_sheet.cell(row, 2, grid.open_time) my_sheet.cell(row, 3, datetime.datetime.now()) my_sheet.cell( row, 4, grid.direction.value if isinstance(grid.direction, Direction) else '') my_sheet.cell(row, 5, grid.volume) netpnl = grid.netpnl if order.price > grid.open_price: close_price = grid.close_price else: close_price = grid.stop_price my_sheet.cell(row, 6, grid.open_price) my_sheet.cell(row, 7, close_price) real_open_price = 0 real_open_count = 0 real_open_price_num = len(grid.open_price_list) for i in range(real_open_price_num): temp_open_price = grid.open_price_list[i] temp_open_volume = grid.open_volume_list[i] real_open_price += temp_open_price * temp_open_volume real_open_count += temp_open_volume real_open_price = real_open_price / real_open_count if real_open_price == 0: # 防止程序过渡时价格为空 real_open_price = grid.open_price real_close_price = 0 real_close_num = len(grid.close_price_list) real_close_count = 0 for i in range(real_close_num): temp_close_price = grid.close_price_list[i] temp_close_volume = grid.close_volume_list[i] real_close_price += temp_close_price * temp_close_volume real_close_count += temp_close_volume real_close_price = real_close_price / real_close_count my_sheet.cell(row, 8, real_open_price) my_sheet.cell(row, 9, real_close_price) my_sheet.cell(row, 10, grid.open_fee) my_sheet.cell(row, 11, grid.close_fee) my_sheet.cell(row, 12, netpnl) my_sheet.cell(row, 13, netpnl - grid.open_fee - grid.close_fee) slip_page = round( abs(grid.open_price - real_open_price) + abs(close_price - real_close_price), 7) my_sheet.cell(row, 14, slip_page) workbook.save(xlsx_file)
def __init__(self, name: str): self.name = name if not os.path.exists(name): save_workbook(Workbook(), name) self._wb = xl.load_workbook(name, keep_vba=True)
directoryDestination.mkdir(exist_ok = True) bigDict = {} indexDictionary = {} columnHeaders = [] isListBuilt = False wb: Workbook ws: Worksheet workbook_filename: str = 'jsonData.xlsx' try: wb = openpyxl.load_workbook(workbook_filename) ws = wb['data'] except FileNotFoundError: print('Workbook not found, creating new workbook') wb = Workbook() ws = wb.active ws.title = 'data' wb.save('jsonData.xlsx') # program options options = ['movement','pull data','push data'] for index, operation in enumerate(options): print(str(index) + ': ' + operation) # input operationIndex: str = input("Pick operation (enter digit):") print("\n") print("\n") directoryToCrawl: Path
def __init__(self): self.wb = Workbook() self.ws = self.wb.active self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"])
def write_infos(self, infos: List[Info]) -> None: 'atomically write all the infos' def clean(descrizione: str) -> str: for prefix in ('AD.COM.LE DA TR. NEL ', 'AD.REG.LE DA TR. NEL ', 'TICKET PASTO'): if descrizione.startswith(prefix): return f'{prefix}*' return descrizione # collect all details of all infos: details = list( sorted({ clean(additional_detail.descrizione) for info in infos for additional_detail in info.additional_details })) # there are 1 + len(keys)-1 + len(details) columns header: List[Tuple[E, str]] = [('month', NUMBER_FORMAT_TEXT)] for column_header in ColumnHeader: if column_header is not ColumnHeader.detail: header.append((column_header.name, NUMBER_FORMAT_TEXT)) else: for detail in details: header.append((detail, NUMBER_FORMAT_TEXT)) rows: List[List[Tuple[E, str]]] = [header] for info in infos: # group columns by column_header columns = {column.header: column for column in info.columns} # group additional_details by descrizione additional_details = { clean(additional_detail.descrizione): additional_detail for additional_detail in info.additional_details } row: List[Tuple[E, str]] = [(info.when, NUMBER_FORMAT_DATE)] for column_header in ColumnHeader: if column_header is not ColumnHeader.detail: row.append((columns[column_header].howmuch, VALUE_NUMBER_FORMAT[column_header] ) if column_header in columns else ( None, NUMBER_FORMAT_TEXT)) else: for detail in details: # cosa esportare nell'xls? row.append((additional_details[detail].competenze - additional_details[detail].trattenute, NUMBER_FORMAT_NUMBER ) if detail in additional_details else ( None, NUMBER_FORMAT_TEXT)) rows.append(row) widths: Dict[str, int] = {} for row in rows: for i, cell in enumerate(row, start=1): column_letter = get_column_letter(i) widths[column_letter] = max(widths.get(column_letter, 0), 2 * len(str(cell[0]))) workbook = Workbook(write_only=True) try: # create the main sheet sheet = workbook.create_sheet('main') # first set the width of the columns for column_letter, width in widths.items(): sheet.column_dimensions[column_letter].width = width # then add the data for row in rows: sheet.append([ self._cell(sheet, value, number_format) for value, number_format in row ]) finally: workbook.save(self.name)
for origin_file in origin_folder_glob: origin_file_name = origin_file[len(origin_folder) + 1:] input_file_name = locals()['origin_file_name'][:-4] + "_input.txt" input_file = origin_folder + "_inputs/" + input_file_name prepare_tbschema_file(origin_file, input_file) server_name = origin_file_name.split('-')[1] database_name = origin_file_name.split('-')[2][:-4] if server_name not in list(servers_dictionnary.keys()): servers_dictionnary[server_name] = Server(server_name, database_name, input_file) else: servers_dictionnary[server_name].add_database_to_dictionnary( database_name, input_file) excel_file = Workbook() ws = excel_file.active ws.title = "Summary" ws.cell(row=1, column=1, value='ACTION') ws.cell(row=1, column=2, value='COMPL.') ws.cell(row=1, column=3, value='DB') ws.cell(row=1, column=4, value='TABLE') tbl_cat_dict = {} idx_cat_dict = {} rvk_cat_dict = {} grt_cat_dict = {} server_column = 5 line_number = 2
def index(request): place = "McDonald's" state = StateProvince.objects.get(code="MG") # 1. Filtragem por precisão e duração. # Eliminar registros com precisão maior que 100m e duração maior que 90min. visits = Visit.objects.filter( precision__lt=100, total_time_in_min__lt=90, place__name=place, place__address__city__state__code=state) count_visits_total = visits.count() count_users_total = visits.values('user_id').annotate( count=Count('user_id')).count() infos = dict() series_days_of_week = [ {'name': 'Sunday', 'values':[]}, {'name': 'Monday', 'values':[]}, {'name': 'Tuesday', 'values':[]}, {'name': 'Wednesday', 'values':[]}, {'name': 'Thursday', 'values':[]}, {'name': 'Friday', 'values':[]}, {'name': 'Saturday', 'values':[]} ] series_day_periods = [ {'name': 'Manhã', 'values':[]}, {'name': 'Almoço', 'values':[]}, {'name': 'Tarde', 'values':[]}, {'name': 'Noite', 'values':[]}, {'name': 'Madrugada', 'values':[]}, ] for visit in visits: key = "{}-{}".format(visit.place, visit.place.address.zip_code) if not infos.get(key): _visits=visits.filter(place=visit.place) item = dict( visit=visit # 2. Contagem de visitas e usuários aos PDVs. ,count_visits=_visits.count() ,count_users=_visits.values( 'user_id').annotate(count=Count('user_id')).count() # 3. Criação de visualização dos dias da semana das visitas aos PDVs. ,count_days_of_week=[ {week_day[1]:_visits.filter(arrival__week_day=week_day[0]).count()} for week_day in Visit.DAYS_OF_WEEK ] # 4. Criação de visualização dos períodos do dia das visitas aos PDVs. (Manhã, Almoço, Tarde, Noite) ,count_day_periods=[ {day_period[0]:_visits.filter( arrival__time__range=(day_period[1], day_period[2])).count()} for day_period in Visit.DAY_PERIOD] # 5. Criação de visualização de visitas antes e visitas depois de visitas aos PDVs ) infos[key] = item # generate series to chart days of week if item.get('count_visits') > 100: for dayweek in item.get('count_days_of_week'): for serie in series_days_of_week: if serie['name'] in dayweek: _values = list(dayweek.items()) serie['values'].append(_values[0][1]) for dayperiod in item.get('count_day_periods'): for serie in series_day_periods: if serie['name'] in dayperiod: _values = list(dayperiod.items()) serie['values'].append(_values[0][1]) context = dict( infos=infos ,count_visits=count_visits_total ,count_users=count_users_total ,series_days_of_week=series_days_of_week ,series_day_periods=series_day_periods ) if 'reports' in request.POST: wb = Workbook() wb.remove_sheet(wb.worksheets[0]) sh = wb.create_sheet('vivists') sh.append([ '_ID' ,'EUID' ,'ARRIVAL' ,'DEPARTURE' ,'PDV' ,'STREET' ,'POSTCODE' ,'SUBURB' ,'CITY' ,'VISITS' ,'CUSTOMERS' ,'COUNT_DAY_PERIODS' ,'COUNT_DAYS_OF_WEEK' ]) for item in infos.values(): # import pdb ; pdb.set_trace() visit = item.get('visit') sh.append([ visit.code ,visit.user_id ,visit.arrival.strftime('%d/%m/%Y') ,visit.departure.strftime('%d/%m/%Y') ,visit.pdv ,visit.place.address.street or '' ,visit.place.address.zip_code or '' ,visit.place.address.suburb or '' ,visit.place.address.city.name ,item.get('count_visits') ,item.get('count_users') ,json.dumps(item.get('count_day_periods')) ,json.dumps(item.get('count_days_of_week')) ]) response = HttpResponse(save_virtual_workbook( wb), content_type="application/vnd.ms-excel") response['Content-Disposition'] = "attachment; filename=visits.xlsx" return response return render(request, 'index.html', context)
# from openpyxl import Workbook import openpyxl import os.path from openpyxl.workbook.workbook import Workbook xlsx_file = './Coin_Trading_Bot.xlsx' 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']
def __init__(self, archive): self.archive = archive self.wb = Workbook() self.sheets = [] self.rels = get_dependents(self.archive, ARC_WORKBOOK_RELS)
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()