Python Xlsxwriter Set Border Around Multiple Cells

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)

how to add border to a range of cells using xlsxwriter?

I don't know how with xlsxwriter you can add format at range with set_column or set_row methods, but you can try do it with conditional formatting like this:

worksheet.conditional_format( 'A1:D12' , { 'type' : 'no_blanks' , 'format' : border_format} )

Changing border of group of cells in excell to thick box border with xlsxwriter

Have you tried to loop through it somehow?

As far as I know conditional formatting gives you the ability to use cell ranges:

worksheet.conditional_format(cells_range, {'type': 'cell',
                                 'criteria': '=',
                                 'value': 'border2', 'format': format})

python xlsxwriter, set border dynamically

Set the range dynamically based on the length of the data you receive

data = [...]
worksheet.set_column(0, len(data), 15, formater)

set_column() docs for the reference.

Thick border in xlsxwriter

Is there any option to add thick border in Excel using xlsxwriter?

You can use a cell format with a border style 2 or 5 for this. See the weight column in the Format set_border() method:

import xlsxwriter

workbook = xlsxwriter.Workbook('borders.xlsx')
worksheet = workbook.add_worksheet()

format1 = workbook.add_format({'border': 1})
format2 = workbook.add_format({'border': 2})
format3 = workbook.add_format({'border': 5})

worksheet.write('B3', 'Border 1', format1)
worksheet.write('B6', 'Border 2', format2)
worksheet.write('B9', 'Border 5', format3)

workbook.close()

Output:

Note however, as far as I know Excel doesn't support/allow thicker borders in conditional formats. As far as I can see the only weight allowed, via the dialog, is 1, i.e., standard thickness.

Also note, there is no direct way in XlsxWriter to apply a border around a range. You will need to apply the appropriate border formats (8+ formats) to all the cells at the edges of the range. This is what Excel does, it is just hidden behind the GUI.

Xlsxwriter can't set border width

Cell border can be changed by changing border style via set_border on a format class:

from xlsxwriter.workbook import Workbook

workbook = Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

format = workbook.add_format()
format.set_border(style=1)

worksheet.write('A1', "Hello, world!", format=format)

workbook.close()

Also see documentation.

UPD: I think you cannot currently change cell border width directly from xlsxwriter.

Hope that helps.

How do I add a border after inserting an image or a hyperlink on xlswriter

In relation to the first part of your question, you can't format the border of an image with XlsxWriter.

In relation to the second part of the question, you can add a border around hyperlink cell like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet = workbook.add_worksheet()

# Widen the column for clarity.
worksheet.set_column('B:B', 30)

# Add a sample alternative link format.
link_format = workbook.add_format({
    'font_color': 'blue',
    'underline': 1,
    'border': 1,
})

# Write the link.
worksheet.write_url('B2', 'http://www.python.org/', link_format)

# Write the link with a string alternative.
worksheet.write_url('B3', 'http://www.python.org/', link_format, "String 1")

# Same as the previous example with named arguments.
worksheet.write_url('B4', 'http://www.python.org/', string="String 2", cell_format=link_format)

workbook.close()

Output:

See the docs on worksheet.write_url().

---

Related Topics