对openpyxl范围内的所有单元格应用边框 [英] Apply borders to all cells in a range with openpyxl

问题描述

我有一个脚本，拿一个大熊猫的数据框，把它分成几百个块，并将每个块作为单独的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（）））：_ b $ _ $ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
 _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似乎是正确的。

谢谢！

解决方案

可能是这样很方便：

  from openpyxl.reader.excel import load_workbook 
 from openpyxl.style import Border 
 
 def set_border（ws，cell_range）：
 rows = ws.range（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示例：
 ws = load_workbook（'example.xlsx'）。get_active_sheet（）
 set_broder（ws，C3：H10）
  

执行速度相当快。

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!

解决方案

May be this is handy:

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

def set_border(ws, cell_range):
    rows = ws.range(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.

这篇关于对openpyxl范围内的所有单元格应用边框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！