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 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_data_about_match_in_xlsx_file(url: url_type) -> None: """Write data about match in Match_Statistic.xlsx with styles.""" match_statistic_xlsx: Workbook = openpyxl.load_workbook( 'Match_Statistic.xlsx') match_statistic: Worksheet = match_statistic_xlsx['Match statistic'] for link_on_match in get_list_of_links_to_matches(url): data = get_data_about_match(link_on_match) data['Название матча'], link_on_match = data['Название матча'] data['Название лиги'], link_on_league = data['Название лиги'] data_values = list(data.values()) match_statistic.append(data_values) match_statistic[ f'C{match_statistic.max_row}'].hyperlink = link_on_match match_statistic[ f'D{match_statistic.max_row}'].hyperlink = link_on_league for coefficient in data_values[11:-1]: if coefficient != '-': if float(coefficient) == float( match_statistic[f'AG{match_statistic.max_row}'].value): match_statistic[( f'{get_column_letter(list(data.values()).index(coefficient) + 1)}' f'{match_statistic.max_row}')].fill = PatternFill( 'solid', bgColor=Color('FFFF00'), fgColor=Color('FFFF00')) match_statistic_xlsx.save('Match_Statistic.xlsx') print( f'Данные о матче по ссылке {link_on_match} записаны в Match_Statistic.xlsx файл' )
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 test_hashable(self, GradientFill): gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4, stop=[BLACK, WHITE]) assert hash(gf) == hash( ('linear', 90, 1.0, 2.0, 3.0, 4.0, (Color(BLACK), Color(WHITE))))
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 create_file(query, questions, host): wb = Workbook() stream = None ws = wb.active # HEADER header, questions_id = create_header(questions) ws.append(header) row = ws.row_dimensions[1] c = Color(indexed=22) f = PatternFill(fill_type='darkGrid', start_color=c, end_color=c) a = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=2) row.fill = f row.alignment = a row.height = 50 # end_cell = Cell(worksheet=ws, column=len(header), row=5) # tab = Table(displayName="Table1", ref="A1:" + end_cell.coordinate) # style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True, # showLastColumn=True) # tab.tableStyleInfo = style # ws.add_table(tab) # BODY body = get_data_from_query_as_lists(query, questions_id, host) for data in body: ws.append(data) with NamedTemporaryFile() as tmp: wb.save(tmp.name) tmp.seek(0) stream = tmp.read() return stream
def create_font(self, markers): """ Returns a font suitable for rich text that has been tagged with the set of ``markers``. """ if len(markers) == 0: return None return Font( name="Consolas" if "c" in markers else "Calibri", sz=11, family=3 if "c" in markers else 2, b=True if "b" in markers else False, i=True if "i" in markers else False, color=Color(rgb="FF382DA0") if "l" in markers else Color(theme=1), u='single' if "l" in markers else None, strike=True if "s" in markers else None)
def _setup_ws_header(ws, ws_type=None): # noqa: CCR001 if not ws_type: ws_type = 'items' color = Color('d3d3d3') fill = PatternFill('solid', color) cels = ws['A1':'{cell}1'.format(cell=get_col_limit_by_ws_type(ws_type), )] col_headers = get_col_headers_by_ws_type(ws_type) if ws_type == '_attributes': col_headers = { c.column_letter: c.value for c in next(ws.iter_rows(min_row=1, max_row=1)) } for cel in cels[0]: ws.column_dimensions[cel.column_letter].width = 25 ws.column_dimensions[cel.column_letter].auto_size = True cel.fill = fill cel.value = col_headers[cel.column_letter] if ws_type == 'params' and cel.value == 'JSON Properties': ws.column_dimensions[cel.column_letter].width = 100 elif ws_type == 'capabilities' and cel.value == 'Capability': ws.column_dimensions[cel.column_letter].width = 50 elif ws_type == 'static_links' and cel.value == 'Url': ws.column_dimensions[cel.column_letter].width = 100 elif ws_type == 'templates': if cel.value == 'Content': ws.column_dimensions[cel.column_letter].width = 100 if cel.value == 'Title': ws.column_dimensions[cel.column_letter].width = 50 elif ws_type == '_attributes': if cel.column_letter in ['A', 'B', 'D']: ws.column_dimensions[cel.column_letter].width = 100
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 make_excel(self, path): img = Image.open(path) pixels = img.load() width, higth = img.size excel_name, kind = path.split('\\')[-1].split('.') wb = Workbook() ws = wb['Sheet'] patterns = [] for row in range(higth): for col in range(width): if re.search('png', kind, re.I): r, g, b, s = pixels[col, row] else: r, g, b = pixels[col, row] pre_process = map(lambda x: hex(x), [r, g, b]) hex_pixel = list( map(lambda x: x.split('x')[-1].zfill(2), pre_process)) hex_pixel.insert(0, '00') hex_value = ''.join(hex_pixel) #print('color', r, g, b, hex_value) #sys.exit() base_color = Color(rgb=hex_value) color_pattern = PatternFill(patternType='solid', fgColor=base_color, bgColor=base_color) patterns.append(color_pattern) position = 0 for row in ws.iter_rows(min_row=1, min_col=1, max_row=higth, max_col=width): for cell in row: cell.fill = patterns[position] position += 1 wb.save('d:\\%s.xlsx' % excel_name)
def parse_pattern_fill(self, node): fill = dict(node.attrib) for child in safe_iterator(node): if child is not node: tag = localname(child) fill[tag] = Color(**dict(child.attrib)) return PatternFill(**fill)
def test_create(self, GradientFill): src = """ <fill> <gradientFill xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" degree="90"> <stop position="0"> <color theme="0"/> </stop> <stop position="1"> <color theme="4"/> </stop> </gradientFill> </fill> """ xml = fromstring(src) fill = GradientFill.from_tree(xml) assert fill.stop == (Color(theme=0), Color(theme=4))
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 ColorScaleRule(start_type=None, start_value=None, start_color=None, mid_type=None, mid_value=None, mid_color=None, end_type=None, end_value=None, end_color=None): """Backwards compatibility""" formats = [] if start_type is not None: formats.append(FormatObject(type=start_type, val=start_value)) if mid_type is not None: formats.append(FormatObject(type=mid_type, val=mid_value)) if end_type is not None: formats.append(FormatObject(type=end_type, val=end_value)) colors = [] for v in (start_color, mid_color, end_color): if v is not None: if not isinstance(v, Color): v = Color(v) colors.append(v) cs = ColorScale(cfvo=formats, color=colors) rule = Rule(type="colorScale", colorScale=cs) return rule
def write_row(self, sheet_name, row, col, data, style=CellStyle.NONE, header_links: List[str] = None, column_size: Dict[str, int] = None): header_links: List[str] = header_links or [] column_size: Dict[str, int] = column_size or {} self.add_named_style(style) i = 0 sheet = self.get_sheet(sheet_name) for cell in (sheet.cell(row + 1, col + 1 + idx) for idx in range(len(data))): cell.value = data[i] cell.style = style.name column_size[cell.column] = max( (column_size.get(cell.column, 0), len(str(cell.value)))) if header_links is not None and len(header_links) > i and len( header_links[i]) > 0: cell.value = f'=HYPERLINK("{header_links[i]}", "{cell.value}")' cell.font = Font(name="Meiryo UI", size=10, underline="single", color=Color(rgb=None, indexed=None, auto=None, theme=10, tint=0.0, type="theme")) i += 1
def write_xlsx(out_xlsx_file, table): wb = openpyxl.Workbook() ws = wb.active ws.title = "Таблица прокладки кабеля" for row in table: ws.append(list(map(str, row))) rows = ws.max_row cols = ws.max_column # tab = openpyxl.worksheet.table.Table(displayName="Table1", ref=f"A1:{COLUMN_NAMES[cols - 1]}{rows}") # style = TableStyleInfo(name="TableStyleMedium9", # showFirstColumn=False, # showLastColumn=False, # showRowStripes=True, # showColumnStripes=True) # tab.tableStyleInfo = style # ws.add_table(tab) ws.column_dimensions["A"].width = 25 ws.column_dimensions["B"].width = 15 ws.column_dimensions["C"].width = 15 ws.column_dimensions["D"].width = 15 ws.column_dimensions["E"].width = 15 ws.column_dimensions["F"].width = 15 ws.column_dimensions["G"].width = 15 ws.column_dimensions["H"].width = 15 ws.column_dimensions["I"].width = 10 ws.row_dimensions[1].height = 50 for column in range(1, cols + 1): cell = ws.cell(row=1, column=column) cell.font = Font(name="Calibri", bold=True) cell.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="6495ed")) for row in range(1, rows + 1): for column in range(1, cols + 1): cell = ws.cell(row=row, column=column) bd = Side(style="thin", color="000000") cell.border = Border(left=bd, top=bd, right=bd, bottom=bd) if row != 1 and column != 1: cell.alignment = Alignment(horizontal="center", vertical="center") for column in range(1, cols + 1): cell = ws.cell(row=rows, column=column) cell.font = Font(name="Calibri", bold=True) try: wb.save(out_xlsx_file) except PermissionError: print( f"Открыт файл: '{out_xlsx_file}'. Заккройте файл и повторите операцию." )
def setValueCell(cell, value, fontRGB=None, bold=False): """ Sets the value of a cell. Style will be copied, the font color will be green to indicate the machine generation of the value.""" cell.value = value if fontRGB: fnt = cell.font.copy(color=Color(rgb=fontRGB), bold=bold) cell.font = fnt fll = cell.fill.copy() cell.fill = fll
def __init__(self): self.visible = False self.blurRadius = 6 self.distance = 2 self.direction = 0 self.alignment = self.SHADOW_BOTTOM_RIGHT self.color = Color() self.alpha = 50
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 set_style(self, cell): if self.color is not None: cell.fill = PatternFill(patternType='solid', fgColor=Color('FF%06x' % self.color)) if self.borders: kwa = { self.border_names[b]: Side(style='thick') for b in self.borders } cell.border = Border(**kwa)
def test_write_gradient_fill(): fill = GradientFill(degree=90, stop=[Color(theme=0), Color(theme=4)]) writer = StyleWriter(DummyWorkbook()) writer._write_gradient_fill(writer._root, fill) xml = get_xml(writer._root) expected = """<?xml version="1.0" ?> <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <gradientFill degree="90" type="linear"> <stop position="0"> <color theme="0"/> </stop> <stop position="1"> <color theme="4"/> </stop> </gradientFill> </styleSheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def _fill_sheet(self, content_dict, target_worksheet, content_color): for wellkey, welldata in content_dict.items(): _row, _column = return_plate_index(wellkey) for n, label in enumerate(self.annotations): row_offset = n * (self.Nrows + self.row_gap) cell_ = target_worksheet.cell( row = row_offset + self.origin[0] + _row, column = self.origin[1] + _column ) cell_.value = welldata[label.lower()] fill_color = Color(rgb=content_color[wellkey]) cell_.fill = PatternFill("solid", fill_color)
def parse_border(self, border_node): """Read individual border""" border = dict(border_node.attrib) for side in ('left', 'right', 'top', 'bottom', 'diagonal'): node = border_node.find('{%s}%s' % (SHEET_MAIN_NS, side)) if node is not None: bside = dict(node.attrib) color = node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: bside['color'] = Color(**dict(color.attrib)) border[side] = Side(**bside) return Border(**border)
def test_serialise(self, GradientFill): gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4, stop=[Color(BLACK), Color(WHITE)]) xml = tostring(gf.to_tree()) expected = """ <fill> <gradientFill bottom="4" degree="90" left="1" right="2" top="3" type="linear"> <stop position="0"> <color rgb="00000000"></color> </stop> <stop position="1"> <color rgb="00FFFFFF"></color> </stop> </gradientFill> </fill> """ diff = compare_xml(xml, expected) assert diff is None, diff
def main(arguments): inputs = arguments["<FILE>"] output = arguments["<EXCELFILE>"] print("inputs: %s" % inputs) print("output: %s" % output) wb = Workbook() height_in_points = 10.0 width_in_charwidth = 1.6 wss = [] for fidx, f in enumerate(inputs): if fidx == 0: ws = wb.active ws.title = os.path.basename(f) else: ws = wb.create_sheet(os.path.basename(f)) with Image.open(f) as im: width, height = im.size print("%s: format=%s, size=%s" % (f, im.format, im.size)) resized = im.size rgb, rgbmap, num_c = get_image_color_dict(im, resized) if num_c > 65535: rgb, num_c = recreate_image(rgb, w=resized[0], h=resized[1]) rgbmap = None fo = Font(name='Calibri', size=1, color="FFFFFFFF") bytes_written = 0 for x in tqdm(range(width)): for y in range(height): color = ctuple2cstr( rgb[y * width + x], dic=rgbmap) c = ws.cell(column=(x + 1), row=(y + 1), value=("#%s" % color)) bytes_written += 1 co = Color(color) c.font = fo c.fill = PatternFill(fgColor=co, fill_type="solid") for y in range(height): ws.row_dimensions[y + 1].height = height_in_points for x in range(width): ws.column_dimensions[get_column_letter( x + 1)].width = width_in_charwidth wb.save(output)
def _prepare_worksheet(ws): color = Color('d3d3d3') fill = PatternFill('solid', color) cels = ws['A1': 'T1'] for cel in cels[0]: if cel.column_letter in ['J', 'K', 'L']: ws.column_dimensions[cel.column_letter].width = 50 elif cel.column_letter in ['B', 'D', 'E', 'F']: ws.column_dimensions[cel.column_letter].width = 15 else: ws.column_dimensions[cel.column_letter].width = 20 ws.column_dimensions[cel.column_letter].auto_size = True cel.fill = fill cel.value = COL_HEADERS[cel.column_letter]
def parse_font(self, font_node): """Read individual font""" font = {} for child in safe_iterator(font_node): if child is not font_node: tag = localname(child) font[tag] = child.get("val", True) underline = font_node.find('{%s}u' % SHEET_MAIN_NS) if underline is not None: font['u'] = underline.get('val', 'single') color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: font['color'] = Color(**dict(color.attrib)) return Font(**font)
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 set_top_bot_borders( cell: openpyxl.cell.cell.Cell) -> openpyxl.cell.cell.Cell: ''' Sets the top and bottom borders of a given cell ''' if not isinstance(cell, openpyxl.cell.cell.Cell): raise ValueError('Argument given is not a cell object type') side_properties = Side(style='medium', color=Color(rgb='00000000', auto=True, type='auto')) border_properties = Border(top=side_properties, bottom=side_properties) cell.border = border_properties return cell
def test_validation(self): c = Color() with pytest.raises(TypeError): c.value = 4