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 updateScriptFileAndCaseResult(self, workB: Workbook, testCaseResult: TestCaseResult, caseResult): workB.save(self.CurrentScriptPath) logging.info('save scenario file: "{0}" successfully'.format( self.CurrentScriptPath)) testCaseResult.CaseResult = caseResult testCaseResult.EndTime = Tool.CurrentTime() self.testPlanResult.TestCaseResultCollection.append(testCaseResult)
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 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 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_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))
class ExcelPipeline(object): def __init__(self): self.wb = Workbook() self.ws = self.wb.active self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"]) def process_item(self, item, spider): line = [ item.get("type"), item.get("asequence"), item.get("pname"), item.get("munit"), item.get("mperson"), item.get("anumber"), item.get("awardtype"), item.get("rank") ] self.ws.append(line) self.wb.save("landandresourceaward.xlsx") return item
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 build_excel_response(field_names,data,filename): try: wb = Workbook(write_only=True) ws = wb.create_sheet() ws.append(field_names) for item in data: ws.append(item.values()) s = StringIO() wb.save(s) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = u'attachment; filename={}.xlsx'.format(filename) response['Content-Length'] = s.len response.write(s.getvalue()) s.close() return response except Exception, exc: return build_exception_response()
def write_xlsx(self, file: Union[str, BinaryIO]) -> None: """ Write the contents in XLSX (Excel) format to a file. Args: file: filename or file-like object """ if XLSX_VIA_PYEXCEL: # use pyexcel_xlsx data = self._get_pyexcel_data(convert_for_openpyxl) pyexcel_xlsx.save_data(file, data) else: # use openpyxl # Marginal performance gain with write_only. Does not automatically # add a blank sheet wb = XLWorkbook(write_only=True) valid_name_dict = self.get_pages_with_valid_sheet_names() for page, title in valid_name_dict.items(): ws = wb.create_sheet(title=title) page.write_to_openpyxl_xlsx_worksheet(ws) wb.save(file)
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)
class ExcelFileWriter(Writter): def __init__(self, data, header=[], file_path=None, *args, **kwargs): super(ExcelFileWriter, self).__init__(data=data, header=header, file_path=file_path, *args, **kwargs) 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 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()
# 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()
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 if operationIndex == "0": k = float(input("Enter a decimal multiplier for movespeed:"))
except: print _u html = etree.HTML(content) _as = html.xpath("//table[@id='detail']//a") for _a in _as: title = _a.xpath('string(.)').strip() if title: _href = ''.join(_a.xpath('@href')) _store.append((title, _href, str(i) + '_'.encode('utf-8') + _u.encode('utf-8'))) print len(_store) wb = Workbook() sheet = wb.active sheet.cell(row=1, column=1, value='标题') sheet.cell(row=1, column=2, value='链接') sheet.cell(row=1, column=3, value='出处') _titles = map(lambda x: x[0], _store) _links = map(lambda x: urljoin('http://admission.ucas.ac.cn/', x[1]), _store) for i in range(len(_titles)): sheet.cell(row=i + 2, column=1, value=_titles[i]) sheet.cell(row=i + 2, column=2, value=_links[i]) sheet.cell(row=i + 2, column=3, value=_store[i][2].encode('utf-8')) wb.save('kejidaxue3.xlsx')
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
driver.find_element(By.XPATH, ".//input[contains(@id,'search')]").send_keys("iphone") driver.find_element(By.XPATH, ".//input[contains(@value,'Go')]").click() sleep(3) phones = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-color-base a-text-normal')]") prices = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-price-whole')]") phone_names = [] phone_prices = [] for phone in phones: phone_names.append(phone.text) for price in prices: phone_prices.append(price.text) final_list = zip(phone_names, phone_prices) wb = Workbook() wb['Sheet'].title = 'iPhone Data' sheet = wb.active sheet.append(["Phone Name", "Price"]) for i in list(final_list): sheet.append(i) wb.save("iPhonesList.xlsx") driver.quit()
# If found, I add my info into the good cell else: # I check into my tbl_cat_dict if I've the same table same_cat = False num_cat = -1 for category in list(grt_cat_dict.keys()): cat = grt_cat_dict.get(category) num_cat += 1 if cat.__eq__(grt): grt_cat_dict_key = str( num_cat ) + "-" + db.database_name + "-" + grt.privilege_granted + "-" + grt.user_granted same_cat = True break if not same_cat: grt_cat_dict_key = str( len(list(grt_cat_dict.keys())) ) + "-" + db.database_name + "-" + grt.privilege_granted + "-" + grt.user_granted grt_cat_dict[grt_cat_dict_key] = grt ws.cell(row=line_number_found, column=server_column, value=list( grt_cat_dict.keys()).index(grt_cat_dict_key)) server_column += 1 excel_file.save('resul.xlsx')
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)
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)