How to set formatting for entire row or column in xlsxwriter Python?

Question

workbook = xlsxwriter.Workbook('demo1.xlsx')
worksheet = workbook.add_worksheet()
format = workbook.add_format({ 'bg_color': '#5081BB','font_color': '#FFFFFF','font_size': 12,'text_wrap':'true'})
textWrap = workbook.add_format({'text_wrap':'true'})
col = 0
row = 0
for i in data["tabledata"]:
    for j in i:
        worksheet.write(row, col, j,textWrap)
        col = col+1
    row = row+1
    col = 0
worksheet.set_row(0, 20, format)
worksheet.set_row(1, 20, format)
workbook.close()

this doesn't properly work, then what is the use of set_row and set_column?
How to apply the format for specific row/column? I also need to apply date format for date columns.

worksheet.set_column(7, none, Dateformat)

Answer 1

Formatting works in XlsxWriter like in Excel: a cell format overrides a row format which in turn overrides a column format.

So if you want a cell to have a format plus the same formatting as the row or column you will have to define a format object that contains all the formats that you want.

Answer 2

To expand on jmcnamara's answer, it's not enough to create a deep copy of the format and then modify it.

import xlsxwriter
import copy

wb = xlsxwriter.Workbook('test.xlsx')
default = wb.add_format({'bg_color': 'yellow'})

ws = wb.add_worksheet()
ws.set_row(0, None, default)

ws.write_string(0, 0, 'default format')

fmt = copy.deepcopy(default)
fmt.set_bold()
ws.write_string(0, 1, 'bolded and yellow', fmt)

wb.close()

The code above produces the following:

It appears that you must use the Workbook.add_format method method to create a new format. Replacing

fmt = copy.deepcopy(default)
fmt.set_bold()

with

fmt = wb.add_format({'bg_color': 'yellow', 'bold': True})

does work: