def __init__(self): self.titles = [ "基金代码", "基金全称", "基金简称", "成立日期", "资产规模/亿元", "跟踪标的", "跟踪误差率", "基金公司", "基金公司规模/亿元", "管理费率/%(年)", "托管费率/%(年)", ] self.font_title = Font(name='Arial', size=14, color=colors.BLACK, bold=True) self.font_content = Font(name='Arial', size=12, color=colors.BLACK, bold=False) self.alignment_title = Alignment(horizontal='center', vertical='center') self.alignment_content = Alignment(horizontal='left', vertical='center')
def test_alignment_rotation(self): self.worksheet.cell('A1').style = Style( alignment=Alignment(vertical='center', text_rotation=90)) self.worksheet.cell('A2').style = Style( alignment=Alignment(vertical='center', text_rotation=135)) self.worksheet.cell('A3').style = Style(alignment=Alignment( text_rotation=-34)) w = StyleWriter(self.workbook) nft = w._write_number_formats() w._write_cell_xfs(nft, {}, {}, {}) xml = get_xml(w._root) assert 'textRotation="90"' in xml assert 'textRotation="135"' in xml assert 'textRotation="124"' in xml
def test_alignment_indent(self): self.worksheet.cell('A1').style = Style(alignment=Alignment(indent=1)) self.worksheet.cell('A2').style = Style(alignment=Alignment(indent=4)) self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=0)) self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=-1)) w = StyleWriter(self.workbook) nft = w._write_number_formats() w._write_cell_xfs(nft, {}, {}, {}) xml = get_xml(w._root) assert 'indent="1"' in xml assert 'indent="4"' in xml #Indents not greater than zero are ignored when writing assert 'indent="0"' not in xml assert 'indent="-1"' not in xml
def excel_write(sheet): #find column with IP addresses for cols in sheet.iter_cols(min_row=1, max_col=10, max_row=1): if cols[0].value == "IP": ip_not_found = False col_num = cols[0].col_idx if cols[0].value == 'Status': status_not_found = False stat_col = cols[0].col_idx #Notify if IP column not found if ip_not_found == True: print('Could not locate IP title in first row') if status_not_found == True: print('Could not locate Status title in first row') #iterate through IPs and insert value for rows in sheet.iter_rows(min_col=col_num, min_row=2, max_col=col_num, max_row=50): edit_ip = re.sub(r'[\n\t\s\r]+', '', str(rows[0].value)) if edit_ip in new_dict: c = sheet.cell(row=rows[0].row, column=stat_col) c.value = new_dict[edit_ip] c.alignment = Alignment(horizontal='center')
def MakeFormat(self): self.wb = openpyxl.Workbook() self.ws = self.wb.active side = Side(style='thin', color='000000') border = Border(bottom=side) width = [15, 7, 15, 15] # 一行目の背景色と罫線の作成 for rows in self.ws['A1':'D1']: i = 0 for cell in rows: cell.fill = openpyxl.styles.PatternFill(patternType='solid', fgColor='9FD9F6', bgColor='9FD9F6') cell.border = border cell.value = self.header[i] cell.alignment = Alignment(horizontal='center') self.ws.column_dimensions[chr(i + 1 + 64)].width = width[i] i = i + 1 # 二行目から五行目の背景色 for rows in self.ws['A2':'D5']: for cell in rows: cell.fill = openpyxl.styles.PatternFill(patternType='solid', fgColor='D3EDFB', bgColor='D3EDFB')
def set_word_wrap_across_columns( worksheet: openpyxl.worksheet.worksheet.Worksheet, min_col: int, max_col: int ): for row in worksheet.iter_cols(min_col=min_col, max_col=max_col): for cell in row: cell.alignment = Alignment(wrapText=True) return worksheet
def set_horiz_vert_center_across_columns( worksheet: openpyxl.worksheet.worksheet.Worksheet, min_col: int, max_col: int ): for row in worksheet.iter_cols(min_col=min_col, max_col=max_col, min_row=4): for cell in row: cell.alignment = Alignment(horizontal="center", vertical="center") return worksheet
def _setup_styles(self): """Bootstrap styles""" from openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.styles.colors import COLOR_INDEX from openpyxl.styles.named_styles import NamedStyleList self._fonts = IndexedList() self._fonts.add(DEFAULT_FONT) self._alignments = IndexedList([Alignment()]) self._borders = IndexedList() self._borders.add(DEFAULT_BORDER) self._fills = IndexedList() self._fills.add(DEFAULT_EMPTY_FILL) self._fills.add(DEFAULT_GRAY_FILL) self._number_formats = IndexedList() self._protections = IndexedList([Protection()]) self._colors = COLOR_INDEX self._cell_styles = IndexedList([StyleArray()]) self._named_styles = NamedStyleList() self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0))
def _setup_styles(self): """Bootstrap styles""" self._fonts = IndexedList() self._fonts.add(DEFAULT_FONT) self._alignments = IndexedList([Alignment()]) self._borders = IndexedList() self._borders.add(DEFAULT_BORDER) self._fills = IndexedList() self._fills.add(DEFAULT_EMPTY_FILL) self._fills.add(DEFAULT_GRAY_FILL) self._number_formats = IndexedList() self._date_formats = {} self._protections = IndexedList([Protection()]) self._colors = COLOR_INDEX self._cell_styles = IndexedList([StyleArray()]) self._named_styles = NamedStyleList() self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0)) self._table_styles = TableStyleList() self._differential_styles = DifferentialStyleList()
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 config_xlsx(data, ll, sheetname, ws): ws['A1'] = sheetname ws['A1'].font = openpyxl.styles.Font(bold=True, size=16) ws['A2'] = '' ws['A3'] = now ws['A3'].alignment = Alignment(horizontal='left') ws['A4'] = '' ws.append(data) # sort column if sheetname != 'IP': for i in sorted(ll): ws.append(i) else: j = 6 for i in ll: ws['C' + str(j)] = i j = j + 1 # adjust column dimension max_len = 0 for i in ws.columns: column = i[0].coordinate[:-1] for j in i: if len(str(j.value)) > max_len: max_len = len(str(j.value)) adj_width = (max_len) * 1.1 ws.column_dimensions[column].width = adj_width max_len = 0
def setAlignment(self, range, vertical='top', horizon='left', wrapText=False): self.sheet[range].alignment = Alignment(vertical=vertical, horizontal=horizon, wrapText=wrapText)
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 add_ind_to_cell( active_cell: openpyxl.cell.cell.Cell) -> openpyxl.cell.cell.Cell: """ If the attribute is an indicator (such as chemo_ind, persistent note), this will add the value and format the cells appropriately """ active_cell.value = "x" active_cell.alignment = Alignment(horizontal="center") return active_cell
def export_data(ws, students_dict, term, export_all=False): # Initialise sheet headers col_headers = [ "First Names", "Last Name", "zID", "College", "Type", "Program", "Term", "Code", "Course", "Mark", "Grade", "WAM" ] for row in ws.iter_rows(min_row=1, max_col=len(col_headers), max_row=1): for i, cell in enumerate(row): cell.value = col_headers[i] cell.font = Font(bold=True) r = 2 # print each student's data for zid, student in students_dict.items(): # start = r min_rows = 3 for c, (prop, val) in enumerate(student.__dict__.items(), 1): # print courses if prop == "terms": if term in val.keys(): # print Term ws.cell(row=r, column=c, value=term.title()) # print WAM wam = student.wams[term] if wam: wam = float(student.wams[term]) ws.cell(row=r, column=c + 5, value=wam) # print each course from course.__dict__ courses = val[term] for course in courses: for i, (c_prop, c_val) in enumerate(course.__dict__.items(), 1): if c_prop == 'grade' and c_val: c_val = float(c_val) if not c_val: c_val = '-' ws.cell(row=r, column=c + i, value=c_val).alignment = Alignment( horizontal='left') r += 1 min_rows -= 1 break #print other data else: ws.cell(row=r, column=c, value=val) # pad rows while min_rows > 0: r += 1 min_rows -= 1 if not export_all: r += 1 # Format columns for i, (_, w) in enumerate(col_widths.items(), 1): ws.column_dimensions[get_column_letter(i)].width = w
def test_alignment(self): st = Style(alignment=Alignment(horizontal='center', vertical='center')) self.worksheet.cell('A1').style = st w = StyleWriter(self.workbook) nft = w._write_number_formats() w._write_cell_xfs(nft, {}, {}, {}) xml = get_xml(w._root) assert 'applyAlignment="1"' in xml assert 'horizontal="center"' in xml assert 'vertical="center"' in xml
def setAlignmentMulti(self, range, vertical='top', horizon='left', wrapText=False): startRange = range.split(':')[0] endRange = range.split(':')[1] for rows in self.sheet[startRange:endRange]: for cell in rows: self.sheet[cell.coordinate].alignment = Alignment( vertical=vertical, horizontal=horizon, wrapText=wrapText)
def FillInfo(self): i = 0 for info in self.data: for j in range(4): self.ws.cell(row=i + 2, column=j + 1).value = info[j] self.ws.cell(row=i + 2, column=j + 1).alignment = Alignment(horizontal='center') self.ws.cell(row=i + 2, column=3).hyperlink = info[4] i = i + 1 self.wb.save(self.TITLE) os.chmod(self.TITLE, 0o666)
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()
def formattingCells(self, rowNo: int): self.firstRow = 16 while rowNo <= (self.firstRow + self.service_len + 1) or rowNo == 16 or rowNo == 17: print(rowNo) self.activeWorkSheet.merge_cells('B' + str(rowNo) + ':' + 'D' + str(rowNo)) self.activeWorkSheet.cell( row=rowNo, column=1).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) self.activeWorkSheet.cell( row=rowNo, column=2).alignment = Alignment(horizontal='left', vertical='top', wrap_text=True) self.activeWorkSheet.cell( row=rowNo, column=5).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) self.activeWorkSheet.cell( row=rowNo, column=7).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) self.activeWorkSheet.cell( row=rowNo, column=8).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) # Increasing the size of height of row if (len(str(self.activeWorkSheet.cell(row=rowNo, column=2).value))) > 36: self.activeWorkSheet.row_dimensions[rowNo].height = 31.50 # fixed rowNo = rowNo + 1 self.chargesQtyCell = 'E' + str(rowNo + 2) self.visitTypeCell = 'B' + str(rowNo + 2) self.visitRatecell = 'G' + str(rowNo + 2)
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 add_frame(self, all_sheet): side = Side(style='thin', color='000000') border = Border(top=side, bottom=side, left=side, right=side) pass_A = re.compile(r'A[0-9]') #A列のみ省きたいので for sheet in all_sheet: #シートごとに枠線付け for row in sheet: for cell in row: #セルごとに回す if sheet[cell. coordinate].value != None: #該当のセルがNone以外==何かしらある cell.alignment = Alignment( horizontal='center', #全てを中央揃えに vertical='center', wrap_text=False) if pass_A.match(cell.coordinate): #A行には枠線をつけない continue sheet[cell.coordinate].border = border #枠線つけ
def close_spider(self, spider: scrapy.Spider): print('close_spider', self.ws.max_row, self.ws.max_column) for row in range(1, self.ws.max_row + 1): for col in range(1, self.ws.max_column + 1): try: # print(col) cell_index = '%s%d' % (self.num_to_letter(col), row) print(cell_index) self.ws[cell_index].font = Font( size=12 if row > 1 else 16, color=colors.BLACK, ) self.ws[cell_index].alignment = Alignment( horizontal='left' if row > 1 else 'center', vertical='center', ) except Exception as e: print("Exception", e) self.wb.save('csi_500.xlsx')
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 optimize(excel): """ 测试报告优化,包括文字居中和边框设置 :param excel: :return: """ if not os.path.isfile(excel): logging.warning(f'optimize excel fail , file : {excel}') return wwb = openpyxl.load_workbook(excel) side = Side(style='thin', color='FF000000') for sheet in wwb.worksheets: for row in sheet.iter_rows(): for cell in row: cell.border = Border(left=side, right=side, top=side, bottom=side) cell.alignment = Alignment(horizontal='left', vertical='center', wrapText=True) wwb.save(excel)
def style_range(worksheet, cell_range, bg_color=None, align=None): """ Apply styles to a range of cells https://openpyxl.readthedocs.io/en/default/styles.html#applying-styles :param worksheet: Excel worksheet instance :param cell_range: An excel range to style (e.g. A1:F20) :param bg_color: Background color in hex (e.g. ffffff or d7e4bc) :param align: An openpyxl Alignment object :return: None """ bg_color = bg_color or 'ffffff' align = align or Alignment( horizontal='left', vertical='center', wrapText=True) bd = Side(style='thin', color='000000') cell_border = Border(left=bd, right=bd, top=bd, bottom=bd) for row in worksheet[cell_range]: fill_color = PatternFill('solid', fgColor=bg_color) for cell in row: cell.border = cell_border cell.fill = fill_color cell.alignment = align
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)
pays["A1"] = "Days" pays["A2"] = "Date\\Type" # Merge cells and set names for i in range(1, 8): # Find two side columns to merge curr_col = i * 3 - 1 next_col = i * 3 + 1 pays.merge_cells(start_row=1, end_row=1, start_column=curr_col, end_column=next_col) curr_cell = pays.cell(row=1, column=curr_col, value=days[i - 1]) curr_cell.alignment = Alignment(horizontal='center') curr_cell.fill = PatternFill(bgColor=colors[i - 1], fill_type="gray0625") # Give them spec for i in range(1, 8): for j in range(3): curr_cell = pays.cell(row=2, column=i * 3 - 1 + j, value=spec[j]) curr_cell.alignment = Alignment(horizontal='center') curr_cell.fill = PatternFill(bgColor=colors[i - 1], fill_type="gray0625") correct_table(pays)
encoding="cp932", names=("JANcode", "商品名", "分析値", "構成比", "累計構成比", "判定")) #dfを降順に並び替え df_s = df.sort_values("分析値", ascending=False) #エクセルファイルを作成・データフレームの書き込み df_s.to_excel("ABCanalysis.xlsx", sheet_name="ABCanalysis", index=False) wb = px.load_workbook("ABCanalysis.xlsx") ws = wb["ABCanalysis"] #最終行を取得 max_row = ws.max_row #最終行の次の行に合計・合計値(数式)を記入 ws.cell(row=max_row + 1, column=2).value = "合計" ws.cell(row=max_row + 1, column=2).alignment = Alignment(horizontal='center', vertical='bottom') ws.cell(row=max_row + 1, column=3).value = "=SUM(C{}:C{})".format(2, max_row) for row in range(2, max_row + 2): #書式設定を変更 ws.cell(row=row, column=1).number_format = "0" ws.cell(row=row, column=1).alignment = Alignment(horizontal='center') ws.cell(row=row, column=3).number_format = "#,##0" ws.cell(row=row, column=6).alignment = Alignment(horizontal='center') #構成比の数式の記入・書式設定(%) ws.cell(row=row, column=4).value = "=C{}/$C${}".format(row, max_row + 1) ws.cell(row=row, column=4).number_format = "0.00%" ws.cell(row=row, column=5).number_format = "0.00%" #累計構成比の書式設定 #累計構成比を記入
excelbook="geoparam.xlsx" wb = Workbook() ws = wb.create_sheet(title=version) font = Font(name="Arial", size=12) maxchar = [0,0,0,0] header = ["Category", "Varname", "value(cm)", "Meaning"] for ic in range(0, len(header)): ws.cell(row=1, column=ic+1).value=header[ic] ws.cell(row=1, column=ic+1).font=font maxchar[ic] = len(header[ic]) for ir in range(0, len(comments)): for ic in range(0, len(header)): ws.cell(row=ir+2, column=ic+1).value = comments[ir][ic] ws.cell(row=ir+2, column=ic+1).font = font if len(comments[ir][ic]) > maxchar[ic]: maxchar[ic] = len(comments[ir][ic]) if ic == 2: ws.cell(row=1, column=ic+1).alignment = Alignment(horizontal="right") colname=["A","B","C","D"] for ic in range(0, len(header)): ws.column_dimensions[colname[ic]].width = int(maxchar[ic]*1.4) wb.save(excelbook) print "Geometry parameters are written in "+excelbook