python XlsxWriter set border around multiple cells
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)
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 boxdef 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})})