python编辑excel做报表给manager看

import xlrdimport osimport refrom numpy import arangeimport zipfileimport matplotlib.pyplot as pltimport redef 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))