系列文章目录
文章目录
- 系列文章目录
- 前言
- 一、pyinstaller封装的exe启动慢，占用内存大
- 1.建立新环境路径
- 2.安装对应库
- 3.更新库版本
- 4.更新pip
- 5.封装指定图标
- 二、覆盖率计算
- 1.引入库
- 总结
前言
一、pyinstaller封装的exe启动慢，占用内存大
1.建立新环境路径
python">python3 -m venv myenv
2.安装对应库
python"> D:\Users\gxcaoty\Desktop\性能覆盖率\newpython\myenv\Scripts\pip3.7.exe install pyinstaller -i https://pypi.mirrors.ustc.edu.cn/simple/pandas
openpyxl
3.更新库版本
python">python3 -m pip install --upgrade pipreqs -i https://pypi.mirrors.ustc.edu.cn/simple/
注意：
原因：xlrd1.2.0之后的版本不支持xlsx格式，支持xls格式，特殊情况需要指定版本
python">D:\Users\gxcaoty\Desktop\性能覆盖率\newpython\myenv\Scripts\pip3.10.exe install xlrd==1.2.0 -i https://pypi.mirrors.ustc.edu.cn/simple/
4.更新pip
python">D:\Users\gxcaoty\Desktop\性能覆盖率\newpython\myenv\Scripts\python3.exe -m pip install --upgrade pip -i https://pypi.mirrors.ustc.edu.cn/simple/
5.封装指定图标
前提：用转换软件将图片转为.ico文件
python"> D:\Users\gxcaoty\Desktop\性能覆盖率\newpython\myenv\Scripts\pyinstaller.exe -F --icon='3.ico' .\Coverage.py
二、覆盖率计算
1.引入库
代码如下（示例）：
python">import re
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import numbers
from openpyxl.styles import Alignment
from openpyxl.styles import colors,PatternFilldef match_worksheet_name(worksheet_name,pattern):return re.match(pattern,worksheet_name) is not Nonedef read_worksheets_from_xlsm(file_path,pattern):xlsm_file = pd.ExcelFile(file_path)worksheets = xlsm_file.sheet_namesmatched_worksheets = [sheet for sheet in worksheets if match_worksheet_name(sheet,pattern)]return [xlsm_file.parse(sheet) for sheet in matched_worksheets]def print_matching_sheet_names(workbook,pattern):#excel_file = pd.ExcelFile(file_path)excel_file = load_workbook(file_path)#sheet_names = excel_file.sheet_namessheet_names = [sheet for sheet in excel_file.sheetnames if excel_file[sheet].sheet_state == 'visible']matched_sheets = [sheet for sheet in sheet_names if re.match(pattern,sheet)]for sheet in matched_sheets:sheet_Total.append(sheet)#print(sheet)if __name__ == '__main__': region = input("请选择区域：2 或 6 \n")wb = Workbook()while True:if region == "2":file_OQC_path = 'OQC性能抽检报表.xlsm'file_path = 'MTF良率报表.xlsm'Workbook = load_workbook(file_path)sheet_Total = [] pattern = r'^[A-Za-z0-9-]+$'print_matching_sheet_names(file_path,pattern) break elif region == "6":file_OQC_path = 'OQC月份汇总.xlsm'file_path = 'MTF良率报表.xlsx'Workbook = load_workbook(file_path)sheet_Total = [] pattern = r'^[A-Za-z0-9-]+$'print_matching_sheet_names(file_path,pattern)breakelse:print("非指定命令，请重新输入!\n")region = input("请选择区域：2 或 6 \n")print("请检查文件名是否正确！\n 2号楼为：MTF良率报表.xlsm 和 OQC性能抽检报表.xlsm\n 6号楼为：MTF良率报表.xlsx 和 OQC月份汇总.xlsm")mode = input("请选择分析模式：1MTF 2日常烘烤 3包装跌落 4夹具跌落 5杂光 6输出全部 \n")while True:if mode == "1":sheet_oqc_name = ["MTF"]breakelif mode == "2":sheet_oqc_name = ["日常烘烤"]breakelif mode == "3":sheet_oqc_name = ["包装跌落"]breakelif mode == "4":sheet_oqc_name = ["夹具跌落"]breakelif mode == "5":sheet_oqc_name = ["杂光"]breakelif mode == "6":sheet_oqc_name = ['MTF','日常烘烤','包装跌落','夹具跌落','杂光']breakelse:print("非指定命令，请重新输入！")mode = input("请选择分析模式：1MTF 2日常烘烤 3包装跌落 4夹具跌落 5杂光 6输出全部 \n")centered_alignment = Alignment(horizontal='center',vertical='center')fill_style = PatternFill(start_color ='FFFF0000', end_color='FFFF0000',fill_type='solid')for sheet_t in sheet_oqc_name:#ws = wb.activei = 1j = 2ws = wb.create_sheet(title=sheet_t) print('开始计算'+sheet_t + '覆盖率') #mtf报表****************************************************************for item in sheet_Total: mold_list = []print(item)ws.cell(row = 1,column = i).value = itemsheet = Workbook[item]column1 = sheet['D']column2 = sheet['E']for row in range(2,sheet.max_row): if column1[row].value is None or column2[row].value is None:continue else:#mold = ws.cell(row = row + 1 ,column = i).value = f"{column1[row].value}-{column2[row].value}"mold = f"{column1[row].value}-{column2[row].value}"mold_list.append(mold)unique_sorted_list = sorted(list(set(mold_list)))MTF_test_num = len(unique_sorted_list)for row,moxue_mtf in enumerate(unique_sorted_list,start=1):#print(moxue_mtf)ws.cell(row=row+3,column=i,value = moxue_mtf) ws.cell(row = 2,column=i,value = "test num") ws.cell(row = 2,column=i+1,value="sampling num") ws.cell(row = 2,column=i+2,value="fraction of coverage") ws.cell(row = 3,column=i,value = MTF_test_num) ws.cell(row = 3,column=i).alignment = centered_alignmenta = ws.cell(row = 3,column=i,value = MTF_test_num).valuecontinue#oqc报表*******************************************************************mold_oqc_list = []df_oqc = pd.read_excel(file_OQC_path,sheet_t)for index,row_oqc in df_oqc.iterrows():#df_oqc = df_oqc.dropna(subset='7')if df_oqc[df_oqc.columns[7]].isnull().values.any():df_oqc[df_oqc.columns[7]] = df_oqc[df_oqc.columns[7]].astype(str)else:if row_oqc[3] == item:if pd.isna(row_oqc[7]):continueelif "/" in row_oqc[7]:mold_oqc_list.append(row_oqc[7].split('/')[0])mold_oqc_list = [x for x in mold_oqc_list if x.lower() !='nan']elif "#" in row_oqc[7]:mold_oqc_list.append(row_oqc[7].split('#')[0])mold_oqc_list = [x for x in mold_oqc_list if x.lower() !='nan']elif row_oqc[7] == "":continueelse:mold_oqc_list.append(row_oqc[7])mold_oqc_list = [x for x in mold_oqc_list if x.lower() !='nan']continuetry:unique_oqc_sorted_list = sorted(list(set(mold_oqc_list)))#print(unique_oqc_sorted_list)MTF_sampling_num = len(unique_oqc_sorted_list)except TypeError:unique_oqc_sorted_list = sorted(list(set(mold_oqc_list)),key=str)#print(unique_oqc_sorted_list)MTF_sampling_num = sum(x is not np.nan for x in unique_oqc_sorted_list)for row,moxue_oqc in enumerate(unique_oqc_sorted_list,start=1):#print(moxue)ws.cell(row=row+3,column=i+1,value = moxue_oqc) ws.cell(row = 3,column=i+1,value = MTF_sampling_num) ws.cell(row = 3,column=i+1).alignment = centered_alignmentb = ws.cell(row = 3,column=i+1,value = MTF_sampling_num).valuefraction_of_coverage = ws.cell(row=3,column=i+2,value=b/a)ws.cell(row=3,column=i+2).number_format = numbers.FORMAT_PERCENTAGEws.cell(row=3,column=i+2).alignment = centered_alignmentcontinue #******************************************************************* list_mtf_oqc = set(unique_sorted_list) - set(unique_oqc_sorted_list)for row,moxue_cov in enumerate(list_mtf_oqc,start=1): ws.cell(row=row + 3,column= i + 2, value = moxue_cov)ws.cell(row=row+3,column=i+2).fill = fill_stylecontinue i = i + 3 file_out = 'Coverage.xlsx' wb.remove(wb['Sheet'])wb.save(file_out)总结
分享：
如果我们想让自己的心灵世界再无纷扰，最好的方法就是用优秀的品格占据心灵
