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

Answer from Barmaley on Stack Overflow
🌐
XlsxWriter
xlsxwriter.readthedocs.io › format.html
The Format Class - XlsxWriter - Read the Docs
A cell border is comprised of a border on the bottom, top, left and right. These can be set to the same value using set_border() or individually using the relevant method calls shown above. The following shows the border styles sorted by XlsxWriter index number:
🌐
TutorialsPoint
tutorialspoint.com › python_xlsxwriter › python_xlsxwriter_border.htm
Python XlsxWriter - Border
For example, to set a border around a cell, we can use border property in add_format() method as follows − ... These border methods/properties have an integer value corresponding to the predefined styles as in the following table − · Following ...
Discussions
python 2.7 - how to add border to a range of cells using xlsxwriter? - Stack Overflow
As in I want to see how you combined "set_column" and "condition_format"? Thanks in Advance :) 2018-09-19T12:14:30.9Z+00:00 ... Here I never mind it is blank or not i need to draw border for certain range any ideas for that. Thanks in advance 2022-01-27T04:26:46.49Z+00:00 ... An improvement on the accepted answer to get the conditional formatting method working for both empty cells & non-empty cells. workbook = xlsxwriter... More on stackoverflow.com
🌐 stackoverflow.com
excel - python XlsxWriter set border around multiple cells - Stack Overflow
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)? ... Save this answer. ... Show activity on this post. XlsxWriter is an awesome module that made my old job 1,000x easier (thanks John!), but ... More on stackoverflow.com
🌐 stackoverflow.com
python - Thick border in xlsxwriter - Stack Overflow
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. ... Sign up to request clarification or add additional context in comments. ... Is it possible to change the color of the border ? I am not seeing anything int the documentation. 2020-09-15T14:25:10.037Z+00:00 ... Try set... More on stackoverflow.com
🌐 stackoverflow.com
how can I put a border around a dataframe with xlsxwriter?
I've used xlsxwriter a lot but almost never use pandas so bear with me. I see you have a Workbook object. You can use that to create Format objects for the workbook. format = workbook.add_format() Here are the docs so you can see exactly what you can do with it. https://xlsxwriter.readthedocs.io/format.html?highlight=Format To get you started, lets add a border to the bottom. format.set_top(6) The docs tell us 6 refers to a double width border. https://xlsxwriter.readthedocs.io/format.html?highlight=border#set_border So how do you apply it to your worksheet? Well, your worksheet has a write() method. worksheet.write(501, 0, ' ', format) Assuming your df has 500 rows, this would write a blank cell to the first column immediately underneath it with a thick bordered top. From there, you can do almost anything. If you figure out programmatically where your df will end up in the worksheet you can go around applying formats however you please. xlsxwriter has a ton of options but you'll need to go through the docs and decide which best apply to you. More on reddit.com
🌐 r/learnpython
3
1
March 5, 2021
🌐
XlsxWriter
xlsxwriter.readthedocs.io › example_diagonal_border.html
Example: Diagonal borders in cells — XlsxWriter
# # SPDX-License-Identifier: BSD-2-Clause # # Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org # import xlsxwriter workbook = xlsxwriter.Workbook("diag_border.xlsx") worksheet = workbook.add_worksheet() format1 = workbook.add_format({"diag_type": 1}) format2 = workbook.add_format({"diag_type": 2}) format3 = workbook.add_format({"diag_type": 3}) format4 = workbook.add_format( { "diag_type": 3, "diag_border": 7, "diag_color": "red", } ) worksheet.write("B3", "Text", format1) worksheet.write("B6", "Text", format2) worksheet.write("B9", "Text", format3) worksheet.write("B12", "Text", format4) workbook.close()
🌐
Stack Overflow
stackoverflow.com › questions › 40905842 › how-to-add-border-to-a-range-of-cells-using-xlsxwriter
python 2.7 - how to add border to a range of cells using xlsxwriter? - Stack Overflow
Top answer
1 of 2
17

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

2 of 2
2

An improvement on the accepted answer to get the conditional formatting method working for both empty cells & non-empty cells.

workbook = xlsxwriter.Workbook('hello_.xlsx')
border = workbook.add_format ({ 'border':1})
worksheet = workbook.add_worksheet('results')

headers = ['Total', 'GST', 'Total(Inc GST)', 'Commission', 'Final Payment']
vals = [31514.47, 3151.44 , 34665.917, 2773.27, 31892.64]


start_row=9 
start_col=3
worksheet.write_column (start_row, start_col ,headers)
worksheet.write_column (start_row, start_col+4 ,vals)

# This method works for all  cell regardless of being empty or not
worksheet.conditional_format( start_row, start_col, start_row+5, start_col+6, { 'type' : 'cell' , 
                                'criteria': 'not equal to',
                                'value':    '"Text_with_0_probability"',
                                'format' : border} )
workbook.close()
🌐
GitHub
gist.github.com › jftuga › f699e5e9bea8ce6f4ff2f27e67fd8393
A simple hack - set outer border for a range using xlsxwriter, a Python library · GitHub
A simple hack - set outer border for a range using xlsxwriter, a Python library · Raw · set_outer_border_for_range_xlsx.py · This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
🌐
Stack Overflow
stackoverflow.com › questions › 21599809 › python-xlsxwriter-set-border-around-multiple-cells
excel - python XlsxWriter set border around multiple cells - Stack Overflow
Top answer
1 of 7
18

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)
2 of 7
7

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})})
🌐
Cxn03651
cxn03651.github.io › write_xlsx › cell_formatting.html
Cell Formatting
Category Description Property Method ... Diagonal border diag_border set_diag_border() Diagonal color diag_color set_diag_color() There are two ways of setting Format properties: by using the object method interface or by setting the property directly. For example, a typical use ...
🌐
Stack Overflow
stackoverflow.com › questions › 49641690 › thick-border-in-xlsxwriter
python - Thick border in xlsxwriter - Stack Overflow
Top answer
1 of 1
20

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.

Find elsewhere
🌐
Reddit
reddit.com › r/learnpython › how can i put a border around a dataframe with xlsxwriter?
r/learnpython on Reddit: how can I put a border around a dataframe with xlsxwriter?
March 5, 2021 -

Hi. I'm writing a dataframe to Excel and need to put a border around the whole dataframe. I'm finding it quite difficult.

Given a typical case:

writer = pd.ExcelWriter(output_file, engine='xlsxwriter')

df.to_excel(writer, sheet_name = 'great', index=False)

workbook = writer.book

worksheet = writer.sheets['great']

The only way I've found to write a border is like this, but you need to write the data (header in this example) and format with a border.

headers = df.columns

row_index = 10

start_col = 0

end_col = start_col + len(headers)

for i in range(start_col, end_col):

worksheet.write(row_index, i, headers[i-start_col], format2)

I don't need to write the data - it's already in the dataframe. I just want to put a line at the bottom at the least, and preferably a box around the whole thing.

How to do it?

Top answer
1 of 1
2
I've used xlsxwriter a lot but almost never use pandas so bear with me. I see you have a Workbook object. You can use that to create Format objects for the workbook. format = workbook.add_format() Here are the docs so you can see exactly what you can do with it. https://xlsxwriter.readthedocs.io/format.html?highlight=Format To get you started, lets add a border to the bottom. format.set_top(6) The docs tell us 6 refers to a double width border. https://xlsxwriter.readthedocs.io/format.html?highlight=border#set_border So how do you apply it to your worksheet? Well, your worksheet has a write() method. worksheet.write(501, 0, ' ', format) Assuming your df has 500 rows, this would write a blank cell to the first column immediately underneath it with a thick bordered top. From there, you can do almost anything. If you figure out programmatically where your df will end up in the worksheet you can go around applying formats however you please. xlsxwriter has a ton of options but you'll need to go through the docs and decide which best apply to you.
🌐
PerlMonks
perlmonks.org
Adding Borders to Cells using Excel::Writer::XLSX
GuiPerl has asked for the wisdom of the Perl Monks concerning the following question: · I have been trying to add borders only to the top sides of cells in Excel using the Excel::Writer module. The code samples now follows: $worksheet->set_row( 8, 0 ); $worksheet->set_column( 'A:B', 30 ); ...
🌐
YouTube
youtube.com › einar stensson
Cell Borders for Your Excel Tables in xlsxwriter for Python - YouTube
07:07
Excel cell borders in the Python xlsxwriter module is a big topic that we will discuss in several tutorials. Here, we'll cover the basics of using formats to...
Published: October 13, 2017
Views: 7K
🌐
YouTube
youtube.com › einar stensson
Python to Excel: Cell borders across multiple columns in xlsxwriter - YouTube
05:26
Creating borders in your Python to Excel files using xlsxwriter uses the same operation we used to create borders for single cells. The difference is that we...
Published: October 14, 2017
Views: 5K
🌐
Stack Overflow
stackoverflow.com › questions › 35806193 › python-xlsxwriter-set-border-dynamically
excel - python xlsxwriter, set border dynamically - Stack Overflow
Top answer
1 of 1
2

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.

🌐
Cxn03651
cxn03651.github.io › write_xlsx › format.html
Format Methods
Default state: Border is off Default action: Set border type 1 Valid args: 0-13, See below. Set the diagonal border style.
🌐
w3reference
w3reference.com › blog › python-xlsxwriter-set-border-around-multiple-cells
How to Set Borders Around Multiple Cells in Python with XlsxWriter: A Simple Guide — w3reference.com
Custom Borders for Headers vs. Data ... Python Installed: XlsxWriter requires Python 3.4 or later. Download it from python.org. Basic Python Knowledge: Familiarity with variables, functions, and loops will help, but we’ll keep examples simple.
🌐
Libxlsxwriter
libxlsxwriter.github.io › format_8h.html
libxlsxwriter: format.h File Reference
Here is an example of how to set up a solid fill in a cell: ... The color should be an RGB integer value, see Working with Colors. ... The format_set_fg_color() method can be used to set the foreground color of a pattern. The color should be an RGB integer value, see Working with Colors. ... Set the cell border style.
🌐
Dpldocs
xlsxwriter.dpldocs.info › xlsxwriter.format.format_set_border.html
format_set_border (xlsxwriter.format.format_set_border)
xlsxwriter format · @brief Set the cell border style. @param format Pointer to a Format instance. @param style Border style index. Set the cell border style: @code format_set_border(format, LXW_BORDER_THIN); @endcode ·
🌐
Stack Overflow
stackoverflow.com › questions › 55263618 › add-outer-borders-using-xlsxwritter
python 3.x - Add outer borders using xlsxwritter - Stack Overflow
Top answer
1 of 1
2

One way you could do it is to set the format of J1 through J183 with a thick left border and set the format of A184 to I184 to have a thick top border.

I've posted a fully reproducible example of this below. In my example, I make use of df.shape to set the borders dependant on dimensionality of my dataframe. 

import xlsxwriter
import pandas as pd
import numpy as np

# Creating a dataframe 
df = pd.DataFrame(np.random.randn(182, 9), columns=list('ABCDEFGHI'))
column_list = df.columns
# Create a Pandas Excel writer using XlsxWriter engine.
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer, index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']

leftFormat = workbook.add_format({'left': 5})
topFormat = workbook.add_format({'top': 5})

for row in range(0, df.shape[0] + 1):
    worksheet.write(row, df.shape[1], '', leftFormat)

for col in range(0, df.shape[1]):
    worksheet.write(df.shape[0] + 1, col, '', topFormat)

worksheet.freeze_panes(1, 0) #freezing top row 

writer.save()

With Expected Output:

🌐
Readthedocs
xlsxwriterlua.readthedocs.io › format.html
The Format Class - Creating Excel files with Lua and Xlsxwriter
These can be set to the same colour using set_border_color() or individually using the relevant method calls shown above. The color can be a Html style #RRGGBB string or a limited number of named colors, see Working with Colors. ... Set the color of the bottom cell border.
🌐
XlsxWriter
xlsxwriter.readthedocs.io › examples.html
Examples - XlsxWriter - Read the Docs
The following are some of the examples included in the examples directory of the XlsxWriter distribution.