def setupDefaultStrings(): print("Setup sheet for default strings ...") global SHEET SHEET.cell(ROW_HEADING, COL_SLNO).value = "Sl.No" SHEET.cell(ROW_HEADING, COL_KEY).value = "String Key" SHEET.cell(ROW_HEADING, COL_DEFAULT).value = "Default" SHEET.cell(ROW_HEADING, COL_SLNO).font = Font(bold=True, size="13") SHEET.cell(ROW_HEADING, COL_KEY).font = Font(bold=True, size="13") SHEET.cell(ROW_HEADING, COL_DEFAULT).font = Font(bold=True, size="13") SHEET.column_dimensions["A"].width = 6 SHEET.column_dimensions["B"].width = 25 SHEET.column_dimensions["C"].width = 75
def changeCellFrontColorMulti(self, range, color): startRange = range.split(':')[0] endRange = range.split(':')[1] font = Font(color=color) for rows in self.sheet[startRange:endRange]: for cell in rows: self.sheet[cell.coordinate].font = font
def add_phase_properties( phase_dict: dict, worksheet: openpyxl.worksheet.worksheet.Worksheet ): attribute_label_dict = OrderedDict( { "Phase Description": phase_dict.get("description"), "Reference Text": phase_dict.get(""), "Evidence Link": phase_dict.get(""), "Related Results": phase_dict.get(""), "Check Alerts on Planning": util.bool_to_str( phase_dict.get("check_alerts_on_planning_ind") ), "Check Alerts on Plan Updates": util.bool_to_str( phase_dict.get("check_alerts_on_plan_updt_ind") ), "Treatment Schedule": "Yes" if phase_dict.get("treatment_sched") else "No", "Route for Review": phase_dict.get("route_for_review"), # TODO: Add fields here to the query, "Classification": "Oncology", "Document Reschedule Reason": phase_dict.get(""), "Build Linked Components Group": phase_dict.get(""), } ) row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet) for k, v in attribute_label_dict.items(): active_cell = worksheet.cell(row=row, column=1) active_cell.value = k active_cell.font = Font(bold=True) active_cell.offset(column=1).value = v row += 1 return worksheet
def add_phase_row(phase_name: str, worksheet: openpyxl.worksheet.worksheet.Worksheet): row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet) worksheet.row_dimensions[row].height = 21.75 bg_fill = GradientFill( type="linear", degree=90, stop=[ Stop(position=0, color=openpyxl.styles.colors.Color(rgb="FF344756")), Stop(position=1, color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")), ], ) bg_font = Font( name="Calibri", sz=16, family=2.0, b=True, color=Color(rgb="FFFFFFFF") ) phase_row_value = "Phase - {}".format(phase_name) for col in range(1, 17): # Non-hyperlinked cells active_cell = worksheet.cell(row=row, column=col) active_cell.font = bg_font active_cell.fill = bg_fill if col == 1: util_borders.set_top_bot_l_borders(active_cell) active_cell.value = phase_row_value elif col == 16: util_borders.set_top_bot_r_borders(active_cell) else: util_borders.set_top_bot_borders(active_cell) return worksheet
def format_components_tab_row_3(workbook: openpyxl.Workbook): ''' Formats row 2 in the Components tab of the DCW ''' sheet_name = 'Components' util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name) worksheet = workbook[sheet_name] field_bg_fill = PatternFill(patternType='solid', fgColor=Color(rgb='FF48545D', type='rgb'), bgColor=Color(indexed=64, type='indexed', theme=0)) field_font = Font(name='Calibri', family=2, sz=11, b=True, scheme='minor', vertAlign=None, color=Color(theme=0, type='theme')) for col in range(1, 17): active_cell = worksheet.cell(row=3, column=col) active_cell.fill = field_bg_fill active_cell.font = field_font active_cell.alignment = Alignment(wrapText=True) util_borders.set_outside_borders(active_cell) return workbook
def __write(self, ws, key: str, value, fill: PatternFill = PatternFill(), border=Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000')), font_size: int = 9): """ワークシートの指定したキーにデータを書き込む Arguments: ws {} -- 対象のワークシート key {str} -- キー value {} -- 書き込む内容 Keyword Arguments: fill {PatternFill} -- 色の指定 (default: {PatternFill()}) border {Border} -- 罫線情報(default: {Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'))}) font_size {int} -- フォントサイズ (default: {9}) """ ws[key] = value ws[key].fill = fill ws[key].border = border ws[key].font = Font(size=font_size)
def add_phase_label(phase_name: str, row: int, worksheet: openpyxl.worksheet.worksheet.Worksheet): phase_bg_fill = PatternFill( patternType="solid", fgColor=Color(type="rgb", rgb="FF305496"), bgColor=Color(type="indexed", indexed=64), ) phase_font = Font( name="Calibri", family=2, sz=11, scheme="minor", vertAlign=None, color=Color(theme=0, type="theme"), ) worksheet.cell(row=row, column=1).fill = phase_bg_fill worksheet.cell(row=row, column=1).font = phase_font worksheet.cell(row=row, column=1).value = phase_name for x in range(1, 17): active_cell = worksheet.cell(row=row, column=x) active_cell.fill = phase_bg_fill return worksheet
def add_links_on_toc_tab(workbook: openpyxl.Workbook, output_file: str = None): """ Adds Excel links to appropriate cells on the Table of Contents tab """ if output_file is None: output_file = "test_output.xlsx" sheet_name = "Table of Contents" util.check_toc_tab_exists(workbook, sheet_name=sheet_name) worksheet = workbook["Table of Contents"] linked_cell_values = [ "Components", "Treatment Schedules", "Plan Attributes" ] for row in range(9, 12): active_cell = worksheet.cell(row=row, column=1) if active_cell.value in linked_cell_values: active_cell.hyperlink = "{}#'{}'!A1".format( output_file, active_cell.value) # TODO: Hyperlink is dependent on output file name active_cell.style = "Hyperlink" active_cell.font = Font( name="Calibri", family=2, sz=14, u="single", color=Color(theme=10, type="theme"), ) return workbook
def store_xml(data, first_row, sheet_name, strenth_col=None, strenth_color='#ff0000'): wb = Workbook() ws = wb.create_sheet(sheet_name, 0) ws.append(first_row) for onr in data: ws.append(onr) # 关键标红 if strenth_col: col = chr(64 + strenth_col) if col < 'A' or col > 'Z': raise Exception("不支持标红列数多于26") colB = ws[col] for cell in colB: # cell.value = 'sb' cell.font = Font(color="ff0000") name = uuid.uuid4() file_name = str(name) + ".xlsx" date_dir = get_cur_date() file_dir = os.path.join(STATIC_ROOT, date_dir) if not os.path.exists(file_dir): os.makedirs(file_dir, exist_ok=True) path = os.path.join(file_dir, file_name) wb.save(path) url = PROTOCOL_IP_OR_DOMAIN + STATIC_URL + "/" + date_dir + "/" + file_name return url
def add_worksheet_header(worksheet: openpyxl.worksheet.worksheet.Worksheet): """ Adds a PowerPlan header for any tab on the PowerPlan DCW. This only adds the PowerPlan header and not the links to the different tabs """ bg_fill = GradientFill(type='linear', degree=90, stop=[ Stop(position=0, color=Color(rgb='FF2A92D0')), Stop(position=1, color=Color(rgb='FF0F6EB4')) ]) bg_font = Font(name='Segoe UI Light', sz=20, family=2.0, b=True, color=Color(rgb='FFFFFFFF')) for col in range(1, 17): active_cell = worksheet.cell(row=1, column=col) active_cell.font = bg_font active_cell.fill = bg_fill # PowerPlan name is already denoted on the Table of Contents tab, so # refer to that tab for the name worksheet['A1'].value = '=\'Table of Contents\'!B2' return worksheet
def resize_image_to_cell(self, img, cell): ratio = 1 width = img.width height = img.height font = Font( name='Arial', sz=11, ) cell.font = font #self.wb._named_styles["標準"].font = font cellHeight = self.get_cell_height_px(cell) cellWidth = self.get_cell_width_px(cell) self.logger.info("target cell width, height: {}, {}".format( cellWidth, cellHeight)) if width > cellWidth: ratio = cellWidth / width width *= ratio height *= ratio if height > cellHeight: ratio = cellHeight / height height *= ratio width *= ratio self.logger.info("needs resize cell width, height: {}, {}".format( width, height)) return self.resize_image(img, round(width), round(height))
def test_style(): from openpyxl.styles import Font, Style wb = Workbook(optimized_write=True) ws = wb.create_sheet() new_style = Style(font=Font(bold=True)) ws.append([{'value': 'hello', 'style': new_style}]) assert new_style in wb.shared_styles
def add_parent_plan_properties_rows( plan_dict: dict, worksheet: openpyxl.worksheet.worksheet.Worksheet ): for phase_dict in plan_dict.get("phases").values(): if phase_dict.get("phase_offset_qty"): phase_offset_dict = phase_dict break if "phase_offset_dict" not in locals(): phase_offset_dict = {} attribute_label_dict = OrderedDict( { "Plan Name/Display Description": plan_dict.get("display_description"), "Description": plan_dict.get("description"), "Plan Type": plan_dict.get("plan_type"), "Plan Display Method": plan_dict.get("display_method"), "Active": "Yes", "Version": 1, "Begin Effective Date": "", "End Effective Date": "", "PowerPlan Status": "Production", "Cross Encounter": util.bool_to_str(plan_dict.get("cross_encounter_ind")), "Evidence Link": plan_dict.get("evidence_link"), "Allow Diagnosis Propagation": util.bool_to_str( plan_dict.get("allow_diagnosis_propagation_ind") ), "Hide Flexed Components": util.bool_to_str( plan_dict.get("hide_flexed_components") ), "Use Cycle Numbers": plan_dict.get("use_cycle_numbers_ind"), "Standard": "Yes" if plan_dict.get("cycle_std_nbr") else "No", "Standard number of cycles": plan_dict.get("cycle_std_nbr"), "Range": plan_dict.get(""), "Range Begin Value": plan_dict.get(""), "Range End Value": plan_dict.get(""), "Cycle Increment": plan_dict.get(""), "Display Standard Number/End Value": plan_dict.get(""), "Restrict ability to modify Standard/End Value": plan_dict.get(""), "Cycle Value Display": plan_dict.get("cycle_disp_val"), "Prompt for Ordering Physician": util.bool_to_str( plan_dict.get("prompt_for_ordering_physician_ind") ), "Copy Forward": util.bool_to_str(plan_dict.get("copy_forward_ind")), "Phase Offsets": plan_dict.get(""), "Lab Phase": plan_dict.get(""), "Offset": "{} {}".format(phase_offset_dict.get("phase_offset_qty"), phase_offset_dict.get("phase_offset_unit")), "Anchor": phase_offset_dict.get("anchor_phase"), "Plan Ordering Defaults": plan_dict.get(""), } ) row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet) for k, v in attribute_label_dict.items(): active_cell = worksheet.cell(row=row, column=1) active_cell.value = k active_cell.font = Font(b=True) active_cell.offset(column=1).value = v row += 1 return worksheet
def write_data(sheet, row, data_list): font = Font(name='Times New Roman', size=10) coordinates = 'K{}:CF{}'.format(row, row) cells = sheet[coordinates][0] for i, cell in enumerate(cells): cell.value = data_list[i] cell.font = font
def format_toc_tab(workbook: openpyxl.Workbook): """ Formats the table of contents tab for PowerPlan DCWs """ sheet_name = "Table of Contents" util.check_toc_tab_exists(workbook, sheet_name=sheet_name) worksheet = workbook[sheet_name] # Column/row dimensions worksheet.column_dimensions["A"].width = 45 worksheet.column_dimensions["B"].width = 101.42578125 worksheet.row_dimensions[1].height = 28.5 # Background color bg_fill = GradientFill( type="linear", degree=90, stop=[ Stop(position=0, color=openpyxl.styles.colors.Color(rgb="FF344756")), Stop(position=1, color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")), ], ) worksheet["A1"].fill = bg_fill worksheet["B1"].fill = bg_fill # Font style worksheet["A1"].font = Font( name="Calibri", family=2.0, b=True, sz=22, color=openpyxl.styles.colors.Color(rgb="FFFFFFFF"), ) worksheet["A1"] = sheet_name worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2) for row in range(2, 9): active_cell = worksheet.cell(row=row, column=1) active_cell.font = Font(name="Calibri", sz=11, b=True) return workbook
def test_dump_with_font(): from openpyxl.writer.dump_worksheet import WriteOnlyCell test_filename = _get_test_filename() wb = Workbook(optimized_write=True) ws = wb.create_sheet() user_style = Style(font=Font(name='Courrier', size=36)) cell = WriteOnlyCell(ws, value='hello') cell.style = Style(font=Font(name='Courrier', size=36)) ws.append([cell, 3.14, None]) assert user_style in wb.shared_styles wb.save(test_filename) wb2 = load_workbook(test_filename) ws2 = wb2[ws.title] assert ws2['A1'].style == user_style
def format_components_tab(workbook: openpyxl.Workbook, output_file: str=None): ''' Formats the Components tab of the DCW ''' sheet_name = 'Components' util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name) worksheet = workbook[sheet_name] # Column dimensions: worksheet.column_dimensions['A'].width = 15.85546875 worksheet.column_dimensions['B'].width = 44.0 worksheet.column_dimensions['C'].width = 21.28515625 worksheet.column_dimensions['D'].width = 40 worksheet.column_dimensions['E'].width = 40 worksheet.column_dimensions['F'].width = 9.140625 worksheet.column_dimensions['G'].width = 8.7109375 worksheet.column_dimensions['H'].width = 8.7109375 worksheet.column_dimensions['I'].width = 9.42578125 worksheet.column_dimensions['J'].width = 15.85546875 worksheet.column_dimensions['K'].width = 15.0 worksheet.column_dimensions['L'].width = 11.28515625 worksheet.column_dimensions['M'].width = 9.42578125 # Row dimensions worksheet.row_dimensions[1].height = 33.75 worksheet.row_dimensions[2].height = 21.75 worksheet.row_dimensions[3].height = 45 # Row 1 formatting bg_fill = GradientFill(type='linear', degree=90, stop=[ Stop( position=0, color=openpyxl.styles.colors.Color(rgb='FF2A92D0') ), Stop( position=1, color=openpyxl.styles.colors.Color(rgb='FF0F6EB4') ) ]) bg_font = Font(name='Segoe UI Light', sz=20, family=2.0, b=True, color=Color(rgb='FFFFFFFF')) for col in range(1, 17): # Non-hyperlinked cells active_cell = worksheet.cell(row=1, column=col) active_cell.font = bg_font active_cell.fill = bg_fill worksheet = add_links_header_to_components_tab(worksheet, output_file=output_file) return workbook
def __export_to_xlsx_steps(work_book, features): """stylborder son los bordes de la tablita""" styleborder = Style(font=Font(bold=False), border=Border(top=Side(border_style='thin', color=colors.BLACK), left=Side(border_style='thin', color=colors.BLACK), bottom=Side(border_style='thin', color=colors.BLACK), right=Side(border_style='thin', color=colors.BLACK))) #tomamos los steps del .feature y demas steps = _gather_steps(features) #nos paramos en la pestana activa work_sheet = Workbook.get_active_sheet(work_book) # y le ponemos el titulo work_sheet.title = 'Execution Steps' #nos paramos en la primer celda row_index = 1 work_sheet['A1'].style = styleborder work_sheet['A1'].value = 'Step' work_sheet['B1'].style = styleborder work_sheet['B1'].value = 'Ocurrencias' work_sheet['C1'].style = styleborder work_sheet['C1'].value = 'Ejecuciones' work_sheet['D1'].style = styleborder work_sheet['D1'].value = 'Tiempo promedio' work_sheet['E1'].style = styleborder work_sheet['E1'].value = 'Tiempo total' work_sheet['F1'].style = styleborder work_sheet['F1'].value = 'Scenarios' #por cada columna le agrega los datos y steps, etc for step in sorted(steps): cell = work_sheet.cell(row=row_index + 1, column=1) cell.value = step cell.style = styleborder cell.offset(column=1).value = steps[step]['appearances'] cell.offset(column=1).style = styleborder cell.offset(column=2).value = steps[step]['quantity'] cell.offset(column=2).style = styleborder cell.offset(column=3).value = '%.2fs' % \ (steps[step]['total_duration'] / (steps[step]['quantity'] or 1)) cell.offset(column=3).style = styleborder cell.offset(column=4).value = '%.2fs' % steps[step]['total_duration'] # cell.offset(column=5).value = steps[step]['scenario'] # cell.offset(column=5).style = styleborder cell.offset(column=4).style = styleborder if len(step) > work_sheet.column_dimensions['A'].width: work_sheet.column_dimensions['A'].width = len(step) work_sheet.column_dimensions['B'].width = 10 work_sheet.column_dimensions['C'].width = 10 work_sheet.column_dimensions['D'].width = 10 work_sheet.column_dimensions['E'].width = 10 # work_sheet.column_dimensions['F'].width = 20 row_index += 1
def test_ctor(self): f = Font() assert f.name == 'Calibri' assert f.size == 11 assert f.bold is False assert f.italic is False assert f.underline == 'none' assert f.strikethrough is False assert f.color.value == '00000000' assert f.color.type == 'rgb' assert f.vertAlign is None assert f.charset is None
def test_dump_with_font(): test_filename = _get_test_filename() wb = Workbook(optimized_write=True) ws = wb.create_sheet() user_style = Style(font=Font(name='Courrier', size=36)) complex_cell = {'value': 'hello', 'style': user_style} ws.append([complex_cell, 3.14, None]) wb.save(test_filename) wb2 = load_workbook(test_filename) ws2 = wb2[ws.title] assert ws2['A1'].style == user_style
def make_excel(): # 1) 기본데이터 serial_data = [] num = [] for i in range(0, 20): serial_number = e[i] serial_data.append(serial_number.get()) num.append(i) selected_comapny = company_name_cmb.get() title = selected_comapny + " 재셋팅 제품" # 제목 resetting_data = pd.DataFrame({'재셋팅 필요한 제품 S/N': serial_data}, index=num) # 시리얼정보 데이터 today = str(date.today()) tday = today[2:4] + today[5:7] + today[8:] # 시트명 및 파일명 만들기 : ex)220105 file_dir = './' + tday + '_' + selected_comapny + '.xlsx' # 2) 엑셀파일 만들기 resetting_data.to_excel(file_dir, sheet_name=tday, startrow=6, startcol=1) wb = openpyxl.load_workbook(file_dir) sheet = wb[tday] sheet.cell(2, 2).value = title sheet['B2'].font = Font(size=16, bold=True, name='HY엽서M') sheet.cell(4, 2).value = '업체명' sheet['B4'].font = Font(size=11, bold=True, name='HY엽서L') sheet.cell(4, 3).value = selected_comapny sheet.cell(5, 2).value = '발주번호' sheet['B5'].font = Font(size=11, bold=True, name='HY엽서L') sheet.cell(5, 3).value = psno_input_txt.get() sheet.cell(7, 2).value = 'No' sheet['B7'].font = Font(size=11, bold=True) sheet['C7'].font = Font(size=11, bold=True) wb.save(file_dir) msgbox.showinfo('완료!', '입력완료!') print('파일생성 / 데이터입력 완료')
def applyFmt(tblStyle, trStyle, tdStyle, cell, ws): # resolve all the styles finalStyle = deepcopy(tblStyle) if finalStyle == None: finalStyle = {} for s in [trStyle, tdStyle]: if s == None: continue for k, v in s.iteritems(): if v == False: continue finalStyle[k] = v font = Font() for k, v in finalStyle.iteritems(): if k == "italic" and v != False: font.i = True if k == "underline" and v != False: font.u = Font.UNDERLINE_SINGLE if k == "line-through" and v != False: font.strikethrough = True if k == "font_name" and v != False: font.name = v if k == "bold" and v == True: font.bold = True if k == 'width' and v != "" and v != False: c, r = coordinate_from_string(cell.coordinate) m = re.match("([\d\.]+)(\D+)", v) if m != None: w = m.group(1) units = m.group(2) if units == "in": w = float(w) * 12 ws.column_dimensions[c].width = w if k == "color" and v != False: if v[1] == "#": font.color = v[1:] else: try: hxcol = webcolors.name_to_hex(v) font.color = hxcol[1:] except: pass if k == "background-color" and v != False: c = Color(v[1:]) fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=c) cell.fill = fill cell.font = font
def add_links_header(link: str, col_start: int, col_end: int, worksheet: openpyxl.worksheet.worksheet.Worksheet, output_file: str = None): """ Adds linked cells to the header for any tab on the PowerPlan DCW. The output_file is needed so that the links will point to the different tabs of the same file, and it needs the file name for some reason """ if output_file is None: output_file = 'test_output.xlsx' link_bg_fill = PatternFill(patternType='solid', fgColor=Color(tint=-0.25, type='theme', theme=0), bgColor=Color(indexed=64, type='indexed', theme=0)) link_font = Font(name='Calibri', family=2, sz=11, u='single', scheme='minor', vertAlign=None, color=Color(theme=10, type='theme')) link_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) initial_cell = worksheet.cell(row=1, column=col_start) initial_cell.value = link initial_cell.hyperlink = '{}#\'{}\'!A1'.format(output_file, link) initial_cell.style = 'Hyperlink' for x in range(col_start, col_end + 1): active_cell = worksheet.cell(row=1, column=x) active_cell.font = link_font active_cell.alignment = link_alignment active_cell.fill = link_bg_fill active_cell = util_borders.set_outside_borders(active_cell) worksheet.merge_cells(start_row=1, start_column=col_start, end_row=1, end_column=col_end) return worksheet
def edit_font(self, name='MS Pゴシック', size=11, bold=False, italic=False, underline='none', strike=False, color='FF000000'): self.cell.font = Font(name=name, size=size, bold=bold, italic=italic, underline=underline, strike=strike, color=color)
def export_file(self, groups, file, file_type): if file_type == "Comma Seperated Values (*.csv)": with open(file, 'w', newline='') as f: writer = csv.writer(f, delimiter=',') for group in groups: people = "" for person in group[1]: people += person + ", " row = str(group[0]) + ", " + people row = row.split(",") writer.writerow(row) elif file_type == "Microsoft Excel Spreadsheets (*.xls *.xlsx)": # Setup the workbook and sheet wb = openpyxl.Workbook() sheet = wb['Sheet'] sheet.title = "Generated Groups" # Setup the header # TODO: Centre the headers header_font = Font(bold=True) people_per_group = len(groups[0][1]) + 1 sheet.cell(1, 1).font = header_font sheet.cell(1, 1).value = "#" sheet.merge_cells(start_row=1, start_column=2, end_row=1, end_column=people_per_group) sheet.cell(1, 2).font = header_font sheet.cell(1, 2).value = "Groups" # Put the data in the cells below the header for group in groups: people = "" for person in group[1]: people += person + ", " row = str(group[0]) + ", " + people row = row.split(",") row[0] = int(row[0]) for column in row: sheet.cell(group[0] + 1, row.index(column) + 1).value = column wb.save(file) else: pass
def a(): wb = openpyxl.Workbook() ws = wb.active ws['A1'] = 'You should see three logos below' ws['A2'] = '中国' ws['B1'] = 'You' ws['B2'] = '伤害' ws.sheet_properties.tabColor = "1072BA" # ws._fonts.add(Font(size=8)) # ws.add_named_style(NamedStyle(font=Font(size=8), builtinId=0)) for row in ws.rows: for cell in row: print(cell) # Font(name='等线', size=24, italic=True, color=colors.RED, bold=True) cell.font = Font(size=8) wb.save('group.xlsx')
def createFile(filePath): ''' create an excel file in the filePath ''' wb = Workbook() ws = wb.create_sheet('Report', 0) header = ['P/N', 'Description', 'Storage-Qty', 'Quickbook-Qty', 'Real-Qty'] j = 1 for i in header: ws.cell(row=1, column=j, value=i).font = Font(color=colors.BLACK, sz=13, bold=True) j = j + 1 wb.save(filePath) return wb
def write_operations_to_cell(excelfilename, sheettitle, cell_num, operation, weapTextopt=True, fill_line_color=None, fix_size=13, bold=False): wb = load_workbook(excelfilename) ws = wb.active ws.title = sheettitle ws[cell_num].alignment = Alignment(wrapText=weapTextopt, vertical='center') ws[cell_num] = operation if fill_line_color: for i in range(12): color_fill_cellnum = "".join([chr(65 + i), cell_num[1:]]) ws[color_fill_cellnum].fill = PatternFill(fill_type='solid', fgColor=fill_line_color) ws[cell_num].font = Font(size=fix_size, bold=bold) wb.save(filename=excelfilename)
def write_day(day, xl_parser): """Setup day to table cells""" sheet = xl_parser.work_book.get_active_sheet() # Count summary rows in the day sum_rows = 0 for it in day.notes + day.kvants: sum_rows += len(it.teachers) # Shift the pointer to setup the table without splitting days while xl_parser.cur_row % SHIFT_ROW == 0 or ( xl_parser.cur_row % SHIFT_ROW) + sum_rows > SHIFT_ROW: xl_parser.cur_row += 1 # Merge cells to setup the date sheet.merge_cells(start_column=COLUMNS['title'], end_column=COLUMNS['class'], start_row=xl_parser.cur_row, end_row=xl_parser.cur_row) # Setup the date of the day at the center and bold it cell = sheet.cell(row=xl_parser.cur_row, column=COLUMNS['title']) cell.value = day.date cell.alignment = Alignment(horizontal='center') cell.font = Font(bold=True) # Setup title's borders for column in range(COLUMNS['title'], COLUMNS['class'] + 1): sheet.cell(row=xl_parser.cur_row, column=column).border = border('medium') # Iterate rows of the table xl_parser.cur_row += 1 for kvant in day.kvants: write_note(kvant, xl_parser) for note in day.notes: write_note(note, xl_parser)
def create_pattern_xlsx(): """This function to create template xlsx file with style. This file is called Match_Statistic.xlsx.""" bold_side: Side = Side('hair', color=Color()) bold_font: Font = Font('Roboto', bold=True) bold_border: Border = Border(left=bold_side, right=bold_side, top=bold_side, bottom=bold_side) column_number: int = 1 match_statistic_xlsx: Workbook = openpyxl.Workbook() match_statistic_xlsx.remove(match_statistic_xlsx['Sheet']) match_statistic: Worksheet = match_statistic_xlsx.create_sheet( 'Match statistic') match_statistic.merge_cells('H1:J2') match_statistic.merge_cells('L1:AF2') match_statistic['H1'].font = bold_font match_statistic['L1'].font = bold_font match_statistic['H1'].alignment = Alignment('center') match_statistic['L1'].alignment = Alignment('center') match_statistic['H1'].border = bold_border match_statistic['L1'].border = bold_border match_statistic['H1'].value = 'Average odds' match_statistic[ 'L1'].value = 'Названия букмекерских контор и коэффициенты на победу фаворита' for col_name in COLUMNS: match_statistic[ f'{get_column_letter(column_number)}3'].font = bold_font match_statistic[ f'{get_column_letter(column_number)}3'].border = bold_border match_statistic[f'{get_column_letter(column_number)}3'] = col_name column_number += 1 match_statistic_xlsx.save('Match_Statistic.xlsx') match_statistic_xlsx.close()