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))
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 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 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 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
# 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 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)
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)
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()