python XlsxWriter set border around multiple cells

Question

I need an easy way to set border around multiple cells, like so:

All I found was border of 1 cell, and merge cells, which is not what I need.

I was expecting for something like:

worksheet.range_border(first_row, first_col, last_row, last_col)

Is there a way that this can be done (that is not involving setting top_border, bottom_border, left_border, right_border for each cell individually)?

Cumber

Please take a look at this answer https://mcmap.net/q/378328/-apply-format-to-a-cell-after-being-written-in-xlsxwriter – Compurgation

Answer 1

XlsxWriter is an awesome module that made my old job 1,000x easier (thanks John!), but formatting cells with it can be time-consuming. I've got a couple helper functions I use to do stuff like this.

First, you need to be able to create a new format by adding properties to an existing format:

def add_to_format(existing_format, dict_of_properties, workbook):
    """Give a format you want to extend and a dict of the properties you want to
    extend it with, and you get them returned in a single format"""
    new_dict={}
    for key, value in existing_format.__dict__.iteritems():
        if (value != 0) and (value != {}) and (value != None):
            new_dict[key]=value
    del new_dict['escapes']

    return(workbook.add_format(dict(new_dict.items() + dict_of_properties.items())))

Now build off of that function with:

def box(workbook, sheet_name, row_start, col_start, row_stop, col_stop):
    """Makes an RxC box. Use integers, not the 'A1' format"""

    rows = row_stop - row_start + 1
    cols = col_stop - col_start + 1

    for x in xrange((rows) * (cols)): # Total number of cells in the rectangle

        box_form = workbook.add_format()   # The format resets each loop
        row = row_start + (x // cols)
        column = col_start + (x % cols)

        if x < (cols):                     # If it's on the top row
            box_form = add_to_format(box_form, {'top':1}, workbook)
        if x >= ((rows * cols) - cols):    # If it's on the bottom row
            box_form = add_to_format(box_form, {'bottom':1}, workbook)
        if x % cols == 0:                  # If it's on the left column
            box_form = add_to_format(box_form, {'left':1}, workbook)
        if x % cols == (cols - 1):         # If it's on the right column
            box_form = add_to_format(box_form, {'right':1}, workbook)

        sheet_name.write(row, column, "", box_form)

Ian

This was helpful for me and I basically copied your code to add a border and some other styling around a box of cells. One addendum is that your current example utilizes the formats, but once the box(..) function is run you'll loose references to the Format objects. I had to create a mapping and return it from the box function so I could later reuse the format objects when writing text specific data to the cells within the box call such that they wouldn't loose their formatting when I wrote the sheet + wb. – Aman

Matteius is right. That aspect of xlsxwriter is annoying. I've updated how I deal with this problem. Check out this question for better instructions: #37907906 – Ian

Answer 2

Gilad's answer is great b/c it is compatible with Python 3. I modified it further to handle scenarios with a single column or row.

# Format cell borders via a configurable RxC box
def draw_frame_border(workbook, worksheet, first_row, first_col, rows_count, cols_count,thickness=1):

    if cols_count == 1 and rows_count == 1:
        # whole cell
        worksheet.conditional_format(first_row, first_col,
                                     first_row, first_col,
                                     {'type': 'formula', 'criteria': 'True',
                                     'format': workbook.add_format({'top': thickness, 'bottom':thickness,
                                                                    'left': thickness,'right':thickness})})    
    elif rows_count == 1:
        # left cap
        worksheet.conditional_format(first_row, first_col,
                                 first_row, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': thickness, 'left': thickness,'bottom':thickness})})
        # top and bottom sides
        worksheet.conditional_format(first_row, first_col + 1,
                                 first_row, first_col + cols_count - 2,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'top': thickness,'bottom':thickness})})

        # right cap
        worksheet.conditional_format(first_row, first_col+ cols_count - 1,
                                 first_row, first_col+ cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': thickness, 'right': thickness,'bottom':thickness})})

    elif cols_count == 1:
        # top cap
        worksheet.conditional_format(first_row, first_col,
                                 first_row, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': thickness, 'left': thickness,'right':thickness})})

        # left and right sides
        worksheet.conditional_format(first_row + 1,              first_col,
                                 first_row + rows_count - 2, first_col,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'left': thickness,'right':thickness})})

        # bottom cap
        worksheet.conditional_format(first_row + rows_count - 1, first_col,
                                 first_row + rows_count - 1, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'bottom': thickness, 'left': thickness,'right':thickness})})

    else:
        # top left corner
        worksheet.conditional_format(first_row, first_col,
                                 first_row, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': thickness, 'left': thickness})})

        # top right corner
        worksheet.conditional_format(first_row, first_col + cols_count - 1,
                                 first_row, first_col + cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': thickness, 'right': thickness})})

        # bottom left corner
        worksheet.conditional_format(first_row + rows_count - 1, first_col,
                                 first_row + rows_count - 1, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'bottom': thickness, 'left': thickness})})

        # bottom right corner
        worksheet.conditional_format(first_row + rows_count - 1, first_col + cols_count - 1,
                                 first_row + rows_count - 1, first_col + cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'bottom': thickness, 'right': thickness})})

        # top
        worksheet.conditional_format(first_row, first_col + 1,
                                     first_row, first_col + cols_count - 2,
                                     {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'top': thickness})})

        # left
        worksheet.conditional_format(first_row + 1,              first_col,
                                     first_row + rows_count - 2, first_col,
                                     {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'left': thickness})})

        # bottom
        worksheet.conditional_format(first_row + rows_count - 1, first_col + 1,
                                     first_row + rows_count - 1, first_col + cols_count - 2,
                                     {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'bottom': thickness})})

        # right
        worksheet.conditional_format(first_row + 1,              first_col + cols_count - 1,
                                     first_row + rows_count - 2, first_col + cols_count - 1,
                                     {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'right': thickness})})

Morph

Answer 3

Currently there is no easy way to do that.

Centriole

I scanned the docs and the github page but couldn't tell - has this has been added yet? Thx – Ounce

No. It still isn't supported. – Centriole

Answer 4

The current solution by @aubaub draws an empty box. I needed to draw a frame around existing values without overriding them. This is my function in case it helps anyone:

def draw_frame_border(workbook, worksheet, first_row, first_col, rows_count, cols_count):

    # top left corner
    worksheet.conditional_format(first_row, first_col,
                                 first_row, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': 1, 'left': 1})})
    # top right corner
    worksheet.conditional_format(first_row, first_col + cols_count - 1,
                                 first_row, first_col + cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'top': 1, 'right': 1})})
    # bottom left corner
    worksheet.conditional_format(first_row + rows_count - 1, first_col,
                                 first_row + rows_count - 1, first_col,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'bottom': 1, 'left': 1})})
    # bottom right corner
    worksheet.conditional_format(first_row + rows_count - 1, first_col + cols_count - 1,
                                 first_row + rows_count - 1, first_col + cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True',
                                  'format': workbook.add_format({'bottom': 1, 'right': 1})})

    # top
    worksheet.conditional_format(first_row, first_col + 1,
                                 first_row, first_col + cols_count - 2,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'top': 1})})
    # left
    worksheet.conditional_format(first_row + 1,              first_col,
                                 first_row + rows_count - 2, first_col,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'left': 1})})
    # bottom
    worksheet.conditional_format(first_row + rows_count - 1, first_col + 1,
                                 first_row + rows_count - 1, first_col + cols_count - 2,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'bottom': 1})})
    # right
    worksheet.conditional_format(first_row + 1,              first_col + cols_count - 1,
                                 first_row + rows_count - 2, first_col + cols_count - 1,
                                 {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'right': 1})})

Northey

There is a problem if the cells are on a single row or a single column. To fix it: if nrows_count > 1: before #left and #right and if ncols_count > 1: before #top and #bottom – Wilkinson

Answer 5

Building off @Gilad I wrote this, which sets the border on the outside. This works for an outside box which is helpful if you're writing with formatting on the inside. I realize I didn't have to do the corners with this method; however, I realized this after I wrote the code. Hopefully, this helps someone.

def draw_frame_border_outside(workbook, worksheet, first_row, first_col, rows_count, cols_count):
# verify type of data passed in
if(first_row <= 0):
    first_row = 1
if(first_col <= 0):
    first_col = 1
cols_count = abs(cols_count)
rows_count = abs(rows_count)
# top left corner
worksheet.conditional_format(first_row - 1, first_col,
                             first_row - 1, first_col,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'bottom': 5,'border_color': '#0000FF'})})
worksheet.conditional_format(first_row, first_col - 1,
                             first_row, first_col - 1,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'right': 5,'border_color': '#0000FF'})})
# top right corner
worksheet.conditional_format(first_row - 1, first_col + cols_count - 1,
                             first_row - 1, first_col + cols_count - 1,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'bottom': 5,'border_color': '#0000FF'})})
worksheet.conditional_format(first_row, first_col + cols_count,
                             first_row, first_col + cols_count,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'left': 5,'border_color': '#0000FF'})})
# bottom left corner
worksheet.conditional_format(first_row + rows_count - 1, first_col - 1,
                             first_row + rows_count - 1, first_col - 1,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'right': 5,'border_color': '#0000FF'})})
worksheet.conditional_format(first_row + rows_count, first_col,
                             first_row + rows_count, first_col,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'top': 5,'border_color': '#0000FF'})})
# bottom right corner
worksheet.conditional_format(first_row + rows_count - 1, first_col + cols_count,
                             first_row + rows_count - 1, first_col + cols_count,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'left': 5,'border_color': '#0000FF'})})
worksheet.conditional_format(first_row + rows_count, first_col + cols_count - 1,
                             first_row + rows_count, first_col + cols_count - 1,
                             {'type': 'formula', 'criteria': 'True',
                              'format': workbook.add_format({'top': 5,'border_color': '#0000FF'})})
# top
worksheet.conditional_format(first_row -1, first_col + 1,
                             first_row - 1, first_col + cols_count - 2,
                             {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'bottom': 5,'border_color': '#0000FF'})})
# left
worksheet.conditional_format(first_row + 1, first_col - 1,
                             first_row + rows_count - 2, first_col - 1,
                             {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'right': 5,'border_color': '#0000FF'})})
# bottom
worksheet.conditional_format(first_row + rows_count, first_col + 1,
                             first_row + rows_count, first_col + cols_count - 2,
                             {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'top': 5,'border_color': '#0000FF'})})
# right
worksheet.conditional_format(first_row + 1, first_col + cols_count,
                             first_row + rows_count - 2, first_col + cols_count,
                             {'type': 'formula', 'criteria': 'True', 'format': workbook.add_format({'left': 5,'border_color': '#0000FF'})})

Iamb

Answer 6

There is an easy way to do this using conditional_format method.

We need to first define the style for border. Then we will mention the 1st 4 parameters as Start Row, Start Column, End Row and End Column and the last parameter as the format. This will help in filling the excel with border for multiple cells without using any loops.

format = workbook.add_format({'border': 1})
worksheet.conditional_format(7, 3, 16, 7, {'type': 'no_blanks','format': format})

conditional_format also accept the excel cell range (for eg,A1:B2). In this case, we can also implement like below.

worksheet.conditional_format('D7:H16', {'type': 'no_blanks','format': format})

Note that the numbers mentioned above is an Index range. That is, if you starting row is 2 in excel, then in the code you have to mention as 1.

Hath

Answer 7

I know I'm quite late, but if anyone still has questions I managed to do it as follows

for col in range(ord('B'), ord('I') + 1):
            column_letter = chr(col)
            worksheet.write(f"{column_letter}{row}", "", body_format)

Lublin