Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

python - Apply borders to all cells in a range with openpyxl

I have a script that takes a pandas dataframe and chops it up into several hundred chunks and saves each chunk as a separate excel file. Each chunk will have the same number of columns but the number of rows varies. I've figured out how to apply all the other necessary formatting to these files with openpyxl, but I haven't yet determined the fastest way to apply borders. Also, I think I'm just not applying borders correctly, because the code below (which I suspect shouldn't need to loop over each cell individually) doesn't apply any borders.

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)

So this code works, but it doesn't apply the border I expect (the default border in excel) and it takes a lot more steps than I'd prefer. My expectation is that I should be able to do something like this:

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

Does this functionality exist? If not, can someone please be so kind as to at least explain how to apply the default border style and not this slightly thicker border? None of Border.BORDER_THICK, Border.BORDER_MEDIUM, Border.BORDER_THIN, or Border.BORDER_HAIR seem correct.

Thanks!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

May be this is handy:

from openpyxl.reader.excel import load_workbook
from openpyxl.style import Border

def set_border(ws, cell_range):
    rows = was[cell_range]
    for row in rows:
        row[0].style.borders.left.border_style = Border.BORDER_THIN
        row[-1].style.borders.right.border_style = Border.BORDER_THIN
    for c in rows[0]:
        c.style.borders.top.border_style = Border.BORDER_THIN
    for c in rows[-1]:
        c.style.borders.bottom.border_style = Border.BORDER_THIN

#usage example:
ws = load_workbook('example.xlsx').get_active_sheet()
set_broder(ws, "C3:H10")

It performs reasonably fast.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...