Search code examples
excelborderxlsxwriter

Issue having outside borders on a range of XL cells using Python xlsxwriter

I'm trying to draw outside borders on a range of cells with this code:

import xlsxwriter

first_row = 9
last col = 3
last_row = 13
last_col = 6

workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()

# Right
worksheet.conditional_format(first_row, last_col, last_row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'right':2})})

# Bottom
worksheet.conditional_format(last_row, first_col, last_row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'bottom':2})})

# Left
worksheet.conditional_format(first_row, first_col, last_row, first_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'left':2})})

# Top
worksheet.conditional_format(first_row, first_col, first_row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'top':2})})

I did it with another different way too by looping on each cell to add borders on the top, left, right and bottom cells to draw output border and I got the same result.

I'm getting the following in the XL spreadsheet. The question is why the top and bottom borders lines are not closing. Thank you

enter image description here

Solution

  • I figured it out. By commenting all except the 1st, and then the 2nd, and so forth, I noticed it worked fine. The border is drawn from 1st limit to the last, but then when I uncommentted the next line in the code, the 2nd line of the code take effect and overwrite the 1st line of the code, just for the 1st cell (top right, top left, bottom left, and bottom right), so the latter need to be dealt with separately. I also changed a bit the code and used loop

    # Left
for row in range(first_row+1, last_row):
    worksheet.conditional_format(row, first_col, row, first_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'left':2})})

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

# Right
for row in range(first_row+1, last_row):
    worksheet.conditional_format(row, last_col, row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'right':2})})
    
# Top right
worksheet.conditional_format(first_row, last_col, first_row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'right':2, 'top':2})})

# Top
for col in range(first_col+1, last_col):
    worksheet.conditional_format(first_row, col, first_row, col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'top':2})})

# Bottom
for col in range(first_col+1, last_col):
    worksheet.conditional_format(last_row, col, last_row, col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'bottom':2})})
    
# Bottom left
worksheet.conditional_format(last_row, first_col, last_row, first_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'left':2, 'bottom':2})})

# Bottom right
worksheet.conditional_format(last_row, last_col, last_row, last_col, {'type':'formula', 'criteria':'True', 'format':workbook.add_format({'right':2, 'bottom':2})})

    enter image description here