[Python知识库]使用openpyxl模块实现内容写入excel并设置单元格字体样式

关键代码

#将统计数据添加到问题统计模板表中
def static_toexcel(data_list,datafile_path):
    wb = load_workbook(datafile_path)
    for sheet in wb:
        print("sheet.title=", sheet.title)
        if sheet.title == "Sheet":
            sheet.title = "问题统计模板"
    ws = wb['问题统计模板']
    print("ws.max_row=",type(ws.max_row))
    if ws.max_row == 1:
        for r in range(len(data_list)):
            for c in range(len(data_list[0])):
                ws.cell(r+1,c+1).value = data_list[r][c]
        #为统计数据头设置字体大小颜色等
        title_font = Font(name="宋体",size="11",bold=True,color="FF0000")
        ws['A1'].font = title_font
        ws.merge_cells("A{}:B{}".format(1,1))
        ws['C1'].font = title_font
        ws.merge_cells("C{}:D{}".format(1, 1))
    else:
        current_max_row = ws.max_row    #获取插入表内容的最大行
        print("current_max_row=", current_max_row)
        for r in range(len(data_list)):
            for c in range(len(data_list[0])):
                ws.cell(r + current_max_row + 2, c + 1).value = data_list[r][c]
        next_min_row = current_max_row + 2   #获取下一个表内容的最小行
        print("next_min_row=",next_min_row)
        # 为统计数据头设置字体大小颜色等
        title_font = Font(name="宋体", size="11", bold=True, color="FF0000")
        ws['A{}'.format(next_min_row)].font = title_font
        ws.merge_cells("A{}:B{}".format(next_min_row, next_min_row))
        ws['C{}'.format(next_min_row)].font = title_font
        ws.merge_cells("C{}:D{}".format(next_min_row, next_min_row))
    wb.save(datafile_path)
    print("成功写入 {}sheet,文件路径 {}".format('问题统计模板',datafile_path))
    return 1

结果

?