Draw Borders Without Overriding Previous Borders in Openpyxl (Python)

Question

I have been using openpyxl 3.0.6 for a project recently, and wanted to automate drawing and resizing of some complicated tables in Excel 2016. Everything was going well until I attempted to outline a range of cells and the previous borders were overwritten. My question pertains to the overwriting of the previous border. For instance

import openpyxl

workbook = openpyxl.load_workbook("Borders Test.xlsx")
sheet = workbook.active
cell = sheet["B6"]

border = openpyxl.styles.borders.Side(style = "thick")

# Draw a border on top of cell B6 #
cell.border = openpyxl.styles.Border(top = border)

# Draw a border on the bottom of cell B6, overwrites the above border #
cell.border = openpyxl.styles.Border(bottom = border)
workbook.save("Borders Test.xlsx")
workbook.close()

will only set a border on the bottom of 'B6' since this was last, but the output I want is cell B6 with both the top and bottom with borders. Note that in the case of drawing borders around a range, we can draw borders on the outside, so we could get the desired result by setting the lower border of B5 and the upper boarder of B7 to thick, but this does not address the overwriting problem. I also tried modifying 'cell.border' as such

cell.border.left = openpyxl.styles.Border(left = border)

but I got the error message:

'AttributeError: Style objects are immutable and cannot be changed. Reassign the style with a copy'

Though this is an option, it kind of sucks and I would like to see if there is a better way of doing it.

I am using 64-bit python in windows 10 and python 3.7.8 if this important.

Thank you in advance for the help, hope my first post isn't too bad!

Answer 1

I didn't find @Romn answer to work. Here is a code snippet that worked for me. It does use "copy" which the OP did mention "would suck", however it's quite short and does the job.

def add_border(cell: Cell, left: Union[Side,None]=None, right: Union[Side,None]=None, top: Union[Side,None]=None, bottom: Union[Side,None]=None) -> None:

    border_copy: Border = copy(cell.border)

    if left:
        border_copy.left = left
    if right:
        border_copy.right = right
    if top:
        border_copy.top = top
    if bottom:
        border_copy.bottom = bottom
    
    cell.border = border_copy

It will only overwrite the sides that are given.

Answer 2

Here is my solution to this question:

• get existing border attribute

• replace them only on the sides defined in your attributes.

  def add_border(c, b, overwrite=True):
     """Add a border to a cell.
  
     :param c: the cell to apply the new border
     :param b: the new border of type openpyxl.styles.borders.Border
     :param overwrite: (OPTIONAL) remove existing borders on sides not defined in b (default True)
     """
  
     def get_border_attr(b):
         return {
            'left': getattr(b, 'left'),
            'right': getattr(b, 'right'),
            'top': getattr(b, 'top'),
            'bottom': getattr(b, 'bottom'),
        }
  
     if overwrite:
         c.border = b
     else:
         saved_border = get_border_attr(c.border)
         new_border = get_border_attr(b)
         c.border = Border(
             left = new_border['left'] if new_border['left'] else saved_border['left'],
             right = new_border['right'] if new_border['right'] else saved_border['right'],
             top = new_border['top'] if new_border['top'] else saved_border['top'],
             bottom = new_border['bottom'] if new_border['bottom'] else saved_border['bottom'],
        )
  

You can then use this function in your code to add a thick line arround a range of cells and a thin line at the bottom of the top cells:

import openpyxl
from openpyxl.styles import Side, Border

wb = openpyxl.Workbook()
ws = wb.create_sheet("My sheet")

thin = Side(border_style="thin", color="000000")
thick = Side(border_style="thick", color="000000")

# Not directly related to the question but nice to have
def set_cell_range_border(ws, cell_range, border_style, overwrite=True):
    rows = ws[cell_range]
    for row in rows:
        add_border(row[0], Border(left=border_style), overwrite)
        add_border(row[-1], Border(right=border_style), overwrite)
    for c in rows[0]:
        add_border(c, Border(top=border_style), overwrite)
    for c in rows[-1]:
        add_border(c, Border(bottom=border_style), overwrite)
    add_border(rows[0][0], Border(left=border_style, top=border_style), overwrite)
    add_border(rows[0][-1], Border(right=border_style, top=border_style), overwrite)
    add_border(rows[-1][0], Border(left=border_style, bottom=border_style), overwrite)
    add_border(rows[-1][-1], Border(right=border_style, bottom=border_style), overwrite)

# Apply thick border around A1:E5
set_cell_range_border(ws, 'A1:E5', thick)

# Add a thin line under the top row cells without removing the thick borders previously set
for c in ws['A1:E1'][0]
     add_border(c, Border(bottom=thin), overwrite=False)

Answer 3

I manage do this making copies of the current borders.

For example, if you want change just de left border of cell H10:

cell = worksheet['H10']

# copy other cell borders
right_s = Side(**d.border.right.__dict__)
top_s = Side(**d.border.top.__dict__)
bottom_s = Side(**d.border.bottom.__dict__)

# set style of left border
left_s = Side(border_style="thin", color="000000")

# reassign border
border = Border(left=left_s, right=right_s, top=top_s, bottom=bottom_s)
cell.border = border

Answer 4

While Alan is correct in saying that this may be done, my goal (which I articulated not well) was to be able to come back and make edits after the first assignment without losing the previous borders. Here's an ugly solution I cooked up:

__elements__ = ('left', 'right', 'top', 'bottom')
def drawSafely(cell, __side = Thick_side, _where = "left"):
   ### Overwrites only the value at _where
   kwargs = {element:getattr(cell.border,element) for element in __elements__}
   kwargs[_where] = __side
   border = openpyxl.styles.Border(**kwargs)
   cell.border = border

where cell is as in the question statement, and 'Thick_side' is of type 'openpyxl.styles.borders.Side'. This just saves the old data in a dict, updates the dict, remakes the border, and assigns that value to cell.border. Also, '_where' must be in

openpyxl.styles.borders.Side.__elements__

(and not the one in my solution, which is only a subset). Again, could be better, but it works.

Answer 5

Both changes to the borders should be made at the same time. Border can take a tuple, which avoids setting a border and then overwriting it.

Instead of:

# Draw a border on top of cell B6 #
cell.border = openpyxl.styles.Border(top = border)

# Draw a border on the bottom of cell B6, overwrites the above border #
cell.border = openpyxl.styles.Border(bottom = border)

Replace with:

cell.border = openpyxl.styles.Border(top = border, bottom = border)