There is a group of task data, which needs to be downloaded from excel. If it is only exported, it is very convenient to export with csv.

But to export beautiful styles, you need to merge cells, set back color, font, border, etc. CSV is in plain text format and does not support setting various styles.

After a day of research, I wrote the code.

Back color reference address, search for \

Style setting reference address

The complete code is as follows:

import xlwt

book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheetname')

def set_style(color='white', height=260, bold=False):

    #: set cell font
    style = xlwt.XFStyle()
    font = xlwt.Font()
    # font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font

    # Set cell background color
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = xlwt.Style.colour_map[color]
    style.pattern = pattern

    #: set cell border line
    borders = xlwt.Borders()  # Create Borders
    # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR,
    # MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED,
    # THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED,
    # SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    style.borders = borders  # Add Borders to Style

    #: format cell center
    alignment = xlwt.Alignment()  # Create Alignment
    # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED,
    # HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # Horizontal centring
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: vert? Top, vert? Center, vert? Bottom, vert? Justified, vert? Distributed
    alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT  # : Wrap
    style.alignment = alignment
    return style

#: analog data
data = [
    {
        "goal_id": 17,
        "goal_name": "\u5b9e\u73b0OKR\u4e0e\u4efb\u52a1\u7ba1\u7406\u7cfb\u7edf\u4e0a\u7ebf\u4e14\u672c\u7ec4\u5185\u65e5\u5e38\u5316\u4f7f\u7528",
        "goal_progress": 0,
        "name": "\u589e\u52a0\u76ee\u6807\u3001\u4efb\u52a1\u7684\u5ef6\u671f\u63d0\u9192\u4e0e\u5c55\u793a\u3002",
        "owner": "aaron.yang",
        "progress": 0,
        "result_id": 26,
        "result_name": "\u5b8c\u6210\u4efb\u52a1\u3001OKR\u3001\u62a5\u8868\u3001\u7ee9\u6548\u529f\u80fd\u6a21\u5757\u5f00\u53d1\u4e0e\u4e0a\u7ebf",
        "result_progress": 0,
    },
    {
        "goal_id": 17,
        "goal_name": "\u5b9e\u73b0OKR\u4e0e\u4efb\u52a1\u7ba1\u7406\u7cfb\u7edf\u4e0a\u7ebf\u4e14\u672c\u7ec4\u5185\u65e5\u5e38\u5316\u4f7f\u7528",
        "goal_progress": 0,
        "name": "task2",
        "owner": "aaron.yang",
        "progress": 0,
        "result_id": 26,
        "result_name": "\u5b8c\u6210\u4efb\u52a1\u3001OKR\u3001\u62a5\u8868\u3001\u7ee9\u6548\u529f\u80fd\u6a21\u5757\u5f00\u53d1\u4e0e\u4e0a\u7ebf",
        "result_progress": 0,
    },
    {
        "goal_id": 17,
        "goal_name": "\u5b9e\u73b0OKR\u4e0e\u4efb\u52a1\u7ba1\u7406\u7cfb\u7edf\u4e0a\u7ebf\u4e14\u672c\u7ec4\u5185\u65e5\u5e38\u5316\u4f7f\u7528",
        "goal_progress": 0,
        "name": "task3",
        "owner": "aaron.yang",
        "progress": 0,
        "result_id": 26,
        "result_name": "\u5b8c\u6210\u4efb\u52a1\u3001OKR\u3001\u62a5\u8868\u3001\u7ee9\u6548\u529f\u80fd\u6a21\u5757\u5f00\u53d1\u4e0e\u4e0a\u7ebf",
        "result_progress": 0,
    },
]
# Write the first row of data
row0 = ['Target name', 'Target progress', 'Key result name', 'Progress of key results', 'Task name', 'Task leader', 'Task progress']
sheet.write_merge(0, 0, 0, 3, 'OKR', set_style('purple_ega', height=500))
sheet.write_merge(0, 0, 4, 6, 'task', set_style('cyan_ega', height=500))

# Write the second line of data
for i in range(0, len(row0)):
    sheet.write(1, i, row0[i], set_style('sky_blue'))

# Count how many rows to merge the cells to be merged, and cache them in the form of key and value.
goal_tmp = {}
result_tmp = {}
for task in data:
    if task['goal_id'] not in goal_tmp:
        goal_tmp[task['goal_id']] = 1
    else:
        goal_tmp[task['goal_id']] += 1

    if task['result_id'] not in result_tmp:
        result_tmp[task['result_id']] = 1
    else:
        result_tmp[task['result_id']] += 1

goal_ids = []
result_ids = []
for index, task in enumerate(data):
    index = index + 1
    row_start = 1 + index
    #: if cells have been merged, append id to the list to avoid repeated merging
    if task['goal_id'] not in goal_ids:
        row_end = index + goal_tmp[task['goal_id']]
        sheet.write_merge(row_start, row_end, 0, 0, task['goal_name'], set_style())
        sheet.write_merge(row_start, row_end, 1, 1, task['goal_progress'], set_style())
        goal_ids.append(task['goal_id'])
    if task['result_id'] not in result_ids:
        row_end = index + result_tmp[task['result_id']]
        sheet.write_merge(row_start, row_end, 2, 2, task['result_name'], set_style())
        sheet.write_merge(row_start, row_end, 3, 3, task['result_progress'], set_style('yellow'))
        result_ids.append(task['result_id'])

    # Write the next few columns of data and set the background color based on the key columns
    sheet.write(row_start, 4, task['name'], set_style())
    sheet.write(row_start, 5, task['owner'], set_style())
    sheet.write(row_start, 6, task['progress'], set_style('yellow'))

#: set cell width
sheet.col(0).width = 10000
sheet.col(2).width = 10000
sheet.col(3).width = 4000
sheet.col(4).width = 10000
sheet.col(5).width = 5000
#: write to file
book.save('/tmp/bookname.xls')