[Python知识库]python写入表格，并格式化

# !/usr/bin/python
# -*- coding: utf-8 -*-
"""
@File    :  dataToSheet.py
@Time    :  2022/3/3 20:23
@Author  :  None
@Version :  1.0
@Desc    :  Result下各版本相应时间数据生成表格汇总
"""
import os
import xlwt
from Bases import bases_params


def get_file_list(file_dir):
    file_list = os.listdir(file_dir)
    return file_list


def get_xls_path():
    jump_log_path = bases_params.jump_log_path
    xls_path = jump_log_path + "\\" + "page_skip.xls"
    return xls_path


def get_title_list(file_path):
    fp = open(file_path, encoding='utf-8')
    fp.seek(0)
    i = 1
    title_list = []
    data_list = []
    for line in fp:
        line=line.strip()
        if line:
            line_list=line.split(" ")
            title = line_list[0]
            y_data = list(map(lambda x: int(x), line_list[1:]))
            title_list.append((title,y_data))
    fp.close()
    # print(title_list)
    return title_list


def summary_res(version):
    res_list = []
    paths = bases_params.jump_log_path
    file_path = paths +"\\" + version + "\\clientResponse.log"
    title_list = get_title_list(file_path)
    i = 1
    for (title, y_data) in title_list:
        res_list.append((title, i, y_data))
        i = i+1
    return res_list


def get_max_num(res_list):
    result_list = []
    for (title, hang, res) in res_list:
        result_list.append(len(res))
    return max(result_list)


def handle_avg_list(name_list,avg_list):
    avg_dict = {}
    for data in avg_list:
        for k, v in data.items():
            for k1, v1 in v.items():
                avg_dict.setdefault(k, {})[k1] = v1
    return avg_dict


def write_avg_data(book, name_list, avg_list):
    sheet = book.add_sheet("AVG")
    al = xlwt.Alignment()
    al.horz = 0x02  # 设置水平居中
    al.vert = 0x01  # 设置垂直居中
    # 黑色字体并居中
    style = xlwt.XFStyle()  # 初始化样式
    style.alignment = al
    font = xlwt.Font()  # 为样式创建字体
    font.bold = True  # 黑体
    style.font = font  # 设定样式

    # 红色字体并居中
    alignment = xlwt.Alignment()  # 设置字体在单元格的位置
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
    alignment.vert = xlwt.Alignment.VERT_CENTER  # 竖直方向
    style1 = xlwt.XFStyle()  # 初始化样式
    style1.alignment = alignment
    font1 = xlwt.Font()  # 为样式创建字体
    font1.bold = True  # 黑体
    font1.colour_index = 2
    style1.font = font1  # 设定样式

    # 添加边框
    borders = xlwt.Borders()  # Create Borders
    for i in name_list:
        if name_list.index(i) == 0:
            sheet.col(name_list.index(i)).width = 256 * 30
        else:
            sheet.col(name_list.index(i)).width = 256 * 12
        sheet.write(0, name_list.index(i), i, style)

    avg_dict = handle_avg_list(name_list,avg_list)
    print(avg_dict)
    i = 0
    for k,v in avg_dict.items():
        max_num = max(v.values())
        sheet.write(i + 1, 0, k, style)
        j = 1
        for j in range(len(name_list)):
            for k1,v1 in v.items():
                if name_list[j] == k1:
                    if v1 == max_num:
                        sheet.write(i + 1, j, v1, style1)
                    else:
                        sheet.write(i + 1, j, v1, style)
        i += 1

    xls_path = get_xls_path()
    book.save(xls_path)


def set_style(book,version,avg_list):
    sheet = book.add_sheet(version)
    al = xlwt.Alignment()
    al.horz = 0x02      # 设置水平居中
    al.vert = 0x01      # 设置垂直居中
    style = xlwt.XFStyle() # 初始化样式
    style.alignment = al
    font = xlwt.Font() # 为样式创建字体
    font.bold = True # 黑体
    style.font = font # 设定样式
    # 添加边框
    borders = xlwt.Borders() # Create Borders
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style.borders = borders # Add Borders to Styl
    # 设置列宽度
    sheet.col(0).width=256*36

    res_list = summary_res(version)
    max_num = get_max_num(res_list)
    lie = 1
    sheet.write(0,0, "耗时统计(ms)", style)
    sheet.col(max_num +1).width=256*18

    for i in range(max_num):
        num = "第%s次" %(i+1)
        sheet.write(0, i+1, num, style)

    sheet.write(0, max_num+1, "平均值（ms）", style)
    for (title, hang, res) in res_list:
        AVG= int(sum(res)/ len(res))
        avg_list.append({title:{version:AVG}})
        lie1 = 1
        sheet.write(lie,0, title, style)
        lie+=1
        sheet.write(hang, max_num+1, AVG, style)
        for x in res:
            sheet.write(hang, lie1, x, style)
            lie1+=1
    xls_path = get_xls_path()
    book.save(xls_path)


if __name__ == '__main__':
    paths = bases_params.jump_log_path
    file_list = get_file_list(paths)
    book = xlwt.Workbook()
    avg_list = []
    name_list = ["耗时统计(平均值ms)"]
    for file_name in file_list:
        if not file_name.endswith(".xls"):
            set_style(book,file_name,avg_list)
            name_list.append(file_name)
    print(avg_list)

    write_avg_data(book, name_list, avg_list)