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)?

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)