python编辑excel做报表给manager看

import xlrd
import os
import re
from numpy import arange
import zipfile
import matplotlib.pyplot as plt
import re



def read_excel_make_picture(file):
    workbook = xlrd.open_workbook(file)
    mySheet = workbook.sheet_by_index(0)
    filename = os.path.basename(file)
    counter_dict_by_mo_id = dict()
    # 获取文件中的时间数据
    times = workbook.sheet_by_index(0).row_values(0)
    times.pop(0)
    #画图取得不同颜色的数组
    color_sequence = ['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c',
                      '#98df8a', '#d62728', '#ff9896', '#9467bd', '#c5b0d5',
                      '#8c564b', '#c49c94', '#e377c2', '#f7b6d2', '#7f7f7f',
                      '#c7c7c7', '#bcbd22', '#dbdb8d', '#17becf', '#9edae5']

    i = 0
    # 循环分析excel中的每个sheet
    for table in workbook.sheets():
        # 获取此sheet的mo
        mo = table.cell_value(0, 0)
        counter_dict_by_mo_id[mo] = dict()
        # 获取此sheet中包含的kpi
        keys = table.col_values(0)
        keys.pop(0)
        i += 1
        plt.figure(i)
        # 循环获取每个kpi的具体数值
        for index, key in enumerate(keys):
            data = table.row_values(index + 1)
            data.pop(0)
            counter_dict_by_mo_id[mo][key] = data
            # 读取sheets名字
            count1 = len(workbook.sheets())
        # 这里需要加一个正则表达式一个是对时间正则
        reobj = re.compile(r'LR\d+_D\d+_E\d+')
        count = counter_dict_by_mo_id[mo]

        # 获取时间信息
        temp_times = []
        for time in times:
            temp_times.append(time[20:26] + '+' + time[35:39])
        #print(temp_times)
        #figures取得是excel的count值
        figures = []
        for figure in keys:
            figures.append(figure[:])
        #获取几个count
        x_label = len(figures)
        # 此处应该加一个循环获取figures的长度，根据长度设置子图，已加
        figures_xnumber = []
        for figures_number in count.values():
            # print(figures_number)
            figures_xnumber.append(figures_number[:])
            # print(figures_xnumber[0])
        y1 = temp_times[:]
        x1 = arange(len(temp_times))
        #设置图片的大小
        fig=plt.figure(figsize=(20,10))
        #循环取数
        for x_number in arange(x_label):
            # print(x_number)
            ax = plt.subplot(x_label, 1, x_number + 1)
            if x_number==0:
                ax.set_title('kpi' + mo)
            RGB_number=x_number%len(color_sequence)
            RGB_color=color_sequence[RGB_number]
            ax.plot(figures_xnumber[x_number],linewidth=1,color=RGB_color, label=figures[x_number],  ls=':', marker='o')
            # print(len(figures))
            # print(figures[0])
            box = ax.get_position()
            ax.legend(loc='upper right')
            #判断x轴长度
            if len(temp_times)%2==0:
                cut_picutre=len(temp_times)/2
            elif len(temp_times)%2==1:
                cut_picutre=len(temp_times)/2 - 1/2
            ax.axvline(cut_picutre, color='black')
            plt.xticks(arange(len(temp_times)), temp_times[:], color='black', rotation=40)
        #保存文件

        file_name = ('kpi' + '_' + mo).replace('.', '_') + '.png'
        plt.savefig(r'E:\test\\'+file_name, format='png')
        print(file_name + ' saved.')
        plt.close()
        zipAllPngFiles("E:\\test",filename)
        # temp_times.pop(0)

    #plt.show()



def zipAllPngFiles(inputFolder,filename):#打包项目文件
    res_name = os.path.join(inputFolder, filename+'.zip')
    f = zipfile.ZipFile(res_name, 'w', zipfile.ZIP_DEFLATED)
    for file in os.listdir(inputFolder):
        if file.endswith('.png'):
            f.write(os.path.join(inputFolder, file), file)
    f.close()
    print('********** zip all png files done !!   **********')

excel = r'E:\kpi_LR16_Avg_UL_Noise_per_PRB_Grp_01_OTH_addNOK_001a_dBm.xls'

print(read_excel_make_picture(excel))