Python openpyxl границы ячеек

Примените границы ко всем ячейкам в диапазоне с помощью openpyxl

У меня есть script, который принимает фреймворк pandas и разбивает его на несколько сотен кусков и сохраняет каждый фрагмент как отдельный файл excel. Каждый кусок будет иметь одинаковое количество столбцов, но количество строк будет различным. Я выяснил, как применить все остальные необходимые форматирования к этим файлам с помощью openpyxl, но я еще не определил самый быстрый способ применения границ. Кроме того, я думаю, что я просто не применяю границы правильно, потому что код ниже (который, как я подозреваю, не должен зацикливаться на каждой ячейке отдельно) не применяет никаких границ.

from openpyxl.style import Border wb = load_workbook(filename = _fname) ws = wb.worksheets[0] for _row in ws.range('A1:L'+str(ws.get_highest_row() ) ): for _cell in _row: _cell.style.borders.left.border_style = Border.BORDER_THIN _cell.style.borders.right.border_style = Border.BORDER_THIN _cell.style.borders.top.border_style = Border.BORDER_THIN _cell.style.borders.bottom.border_style = Border.BORDER_THIN wb.save(_fname) 

Итак, этот код работает, но он не применяется к границе, которую я ожидаю (граница по умолчанию в excel), и для этого требуется намного больше шагов, чем я бы предпочел. Я ожидаю, что смогу сделать что-то вроде этого:

from openpyxl.style import Border wb = load_workbook(filename = _fname) ws = wb.worksheets[0] _range = ws.some_range_func('A1:L'+str(ws.get_highest_row() ) ): _range.style.borders.all_borders = Borders.BORDER_THIN 

Существует ли эта функциональность? Если нет, может кто-то, пожалуйста, так любезны, чтобы хотя бы объяснить, как применять стиль границы по умолчанию, а не эту немного более толстую границу? Ни один из Border.BORDER_THICK, Border.BORDER_MEDIUM, Border.BORDER_THIN или Border.BORDER_HAIR не кажется правильным. Спасибо!

Источник

Working with styles¶

Styles are used to change the look of your data while displayed on screen. They are also used to determine the formatting for numbers.

Styles can be applied to the following aspects:

• font to set font size, color, underlining, etc.
• fill to set a pattern or color gradient
• border to set borders on a cell
• cell alignment
• protection

The following are the default values

>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font >>> font = Font(name='Calibri', . size=11, . bold=False, . italic=False, . vertAlign=None, . underline='none', . strike=False, . color='FF000000') >>> fill = PatternFill(fill_type=None, . start_color='FFFFFFFF', . end_color='FF000000') >>> border = Border(left=Side(border_style=None, . color='FF000000'), . right=Side(border_style=None, . color='FF000000'), . top=Side(border_style=None, . color='FF000000'), . bottom=Side(border_style=None, . color='FF000000'), . diagonal=Side(border_style=None, . color='FF000000'), . diagonal_direction=0, . outline=Side(border_style=None, . color='FF000000'), . vertical=Side(border_style=None, . color='FF000000'), . horizontal=Side(border_style=None, . color='FF000000') . ) >>> alignment=Alignment(horizontal='general', . vertical='bottom', . text_rotation=0, . wrap_text=False, . shrink_to_fit=False, . indent=0) >>> number_format = 'General' >>> protection = Protection(locked=True, . hidden=False) >>> 

Cell Styles and Named Styles¶

There are two types of styles: cell styles and named styles, also known as style templates.

Cell Styles¶

Cell styles are shared between objects and once they have been assigned they cannot be changed. This stops unwanted side-effects such as changing the style for lots of cells when only one changes.

>>> from openpyxl.styles import colors >>> from openpyxl.styles import Font, Color >>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active >>> >>> a1 = ws['A1'] >>> d4 = ws['D4'] >>> ft = Font(color="FF0000") >>> a1.font = ft >>> d4.font = ft >>> >>> a1.font.italic = True # is not allowed # doctest: +SKIP >>> >>> # If you want to change the color of a Font, you need to reassign it:: >>> >>> a1.font = Font(color="FF0000", italic=True) # the change only affects A1 

Copying styles¶

Styles can also be copied

>>> from openpyxl.styles import Font >>> from copy import copy >>> >>> ft1 = Font(name='Arial', size=14) >>> ft2 = copy(ft1) >>> ft2.name = "Tahoma" >>> ft1.name 'Arial' >>> ft2.name 'Tahoma' >>> ft2.size # copied from the 14.0 

Colours¶

Colours for fonts, backgrounds, borders, etc. can be set in three ways: indexed, aRGB or theme. Indexed colours are the legacy implementation and the colours themselves depend upon the index provided with the workbook or with the application default. Theme colours are useful for complementary shades of colours but also depend upon the theme being present in the workbook. It is, therefore, advisable to use aRGB colours.

aRGB colours¶

RGB colours are set using hexadecimal values for red, green and blue.

>>> from openpyxl.styles import Font >>> font = Font(color="FF0000") 

The alpha value refers in theory to the transparency of the colour but this is not relevant for cell styles. The default of 00 will prepended to any simple RGB value:

>>> from openpyxl.styles import Font >>> font = Font(color="00FF00") >>> font.color.rgb '0000FF00' 

There is also support for legacy indexed colours as well as themes and tints.

>>> from openpyxl.styles.colors import Color >>> c = Color(indexed=32) >>> c = Color(theme=6, tint=0.5) 

Indexed Colours¶

Standard Colours

Index
0-4	00000000	00FFFFFF	00FF0000	0000FF00	000000FF
	5-9	00FFFF00	00FF00FF	0000FFFF	00000000	00FFFFFF
10-14		00FF0000	0000FF00	000000FF	00FFFF00	00FF00FF
	15-19	0000FFFF	00800000	00008000	00000080	00808000
20-24		00800080	00008080	00C0C0C0	00808080	009999FF
	25-29	00993366	00FFFFCC	00CCFFFF	00660066	00FF8080
30-34		000066CC	00CCCCFF	00000080	00FF00FF	00FFFF00
	35-39	0000FFFF	00800080	00800000	00008080	000000FF
40-44		0000CCFF	00CCFFFF	00CCFFCC	00FFFF99	0099CCFF
	45-49	00FF99CC	00CC99FF	00FFCC99	003366FF	0033CCCC
50-54		0099CC00	00FFCC00	00FF9900	00FF6600	00666699
	55-60	00969696	00003366	00339966	00003300	00333300
60-63		00993300	00993366	00333399	00333333

Источник

Add border to range of cells in openpyxl Python

This tutorial is based on the task to add borders to a range of cells in openpyxl in Python. For this first of all, you need to make sure you have openpyxl installed on your system or not. To install run the below command in your terminal-

Now the next step is to import this openpyxl module you have just installed on your system,, you guessed it right- just write import openpyxl and then import border, side, from openpyxl.styles module and Workbook.

The code for the above two lines

from openpyxl import Workbook import openpyxl from openpyxl.styles import Border,Side

The next step is to load the workbook, we will do it using-

wb=openpyxl.load_workbook("Border.xlsx") ws=wb['Sheet1']

top=Side(border_style='thick',color="A52A2A") bottom=Side(border_style='thick', color="A52A2A") border=Border(top=top,bottom=bottom)

We have now defined the border for the top and bottom, we can also do it in the same way for the left and right sides of the cell.

Now its time to implement it to a range of cells, this can be done using a for loop –

range=ws['B6':'B12'] for cell in range: for x in cell: x.border=border

We have completed our task to add a border to the range of cells, we just need to save it and start our excel file to see the changes, we will do this by-

wb.save("Border.xlsx") startfile("Border.xlsx")

This will show changes to your file, you can modify it according to your needs.

The output will look like this-

I hope you enjoyed reading this tutorial and that you found it helpful for your task too.

Источник