使用Openpyxl将边框应用于单元格范围 [英] Apply Border To Range Of Cells Using Openpyxl
问题描述
我正在使用python 2.7.10和openpyxl 2.3.2,并且我是Python新手.
I am using python 2.7.10 and openpyxl 2.3.2 and I am a Python newbie.
我正在尝试将边框应用于Excel工作表中指定范围的单元格(例如C3:H10
).我在下面的尝试失败,并显示以下消息:
I am attempting to apply a border to a specified range of cells in an Excel worksheet (e.g. C3:H10
). My attempt below is failing with the the following message:
AttributeError:单元"对象没有属性样式".
AttributeError: 'Cell' object has no attribute 'styles'.
如何在单元格上附加边框?任何见解将不胜感激.
How do I attach a border to a cell? Any insights would be gratefully received.
我当前的代码:
import openpyxl
from openpyxl.styles import Border, Side
def set_border(ws, cell_range):
rows = ws.iter_rows(cell_range)
for row in rows:
row[0].styles.borders = Border(left=Side(border_style='thin', color="FF000000"))
row[-1].styles.borders = Border(right=Side(border_style='thin', color="FF000000"))
for c in rows[0]:
c.styles.borders = Border(top=Side(border_style='thin', color="FF000000"))
for c in rows[-1]:
c.styles.borders = Border(bottom=Side(border_style='thin', color="FF000000"))
# Example call to set_border
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
set_border(ws, "B3:H10")
推荐答案
首先将所有属性称为style
(不是styles
)和border
(不是borders
).另外,要更改边框,您应该直接设置cell.border
.
First of all properties are called style
(not styles
) and border
(not borders
). Also to change border you should set cell.border
directly.
除了边界逻辑存在一些问题外,由于迭代器和弯角,使它正确工作也更加复杂.这是一个粗略的版本(它很简单,但内存效率低):
Besides that you have some problems with borders logic, it's more complex to get it working correctly, because of iterators and corners. Here is a rough version (it is as simple as I could get it, but not memory efficient):
def set_border(ws, cell_range):
rows = ws[cell_range]
side = Side(border_style='thin', color="FF000000")
rows = list(rows) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
这篇关于使用Openpyxl将边框应用于单元格范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!