【python实战】-- mtf覆盖率计算

系列文章目录

前言

一、mtf覆盖率计算

1.背景

1、mtf测试报表中有多个工作表，需要提取满足正则表达式的工作表(不包含隐藏工作表)；
2、oqc抽检报表中提取对应件号，对应模穴
3、去重后，计算模穴数
4、计算覆盖率

2.优化代码

import re
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
import numpy as np
from openpyxl.styles import numbers
from openpyxl.styles import Alignment
from openpyxl.styles import colors,PatternFill

def match_worksheet_name(worksheet_name,pattern):
    return re.match(pattern,worksheet_name) is not None

def read_worksheets_from_xlsm(file_path,pattern):
    xlsm_file = pd.ExcelFile(file_path)
    worksheets = xlsm_file.sheet_names
    matched_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_names
    sheet_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")
    if region == "2":
        file_OQC_path = 'OQC性能抽检报表.xlsm'
    elif region == "6":
        file_OQC_path = 'OQC月份汇总.xlsm'
    else:
        print("非指定命令，请重新运行！！！")

    print("请检查文件名是否正确！\n 2号楼为：MTF良率报表.xlsm  和 OQC性能抽检报表.xlsm\n 6号楼为：MTF良率报表.xlsx  和  OQC月份汇总.xlsm")
    mode = input("请选择分析模式：1MTF 2日常烘烤 3包装跌落 4夹具跌落 5杂光 \n")
    if mode == "1":
        sheet_oqc_name = "MTF"
        df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
        file_out = 'MTF.xlsx'
    elif mode == "2":
        sheet_oqc_name = "日常烘烤"
        df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
        file_out = '日常烘烤.xlsx'
    elif mode == "3":
        sheet_oqc_name = "包装跌落"
        df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
        file_out = '包装跌落.xlsx'
    elif mode == "4":
        sheet_oqc_name = "夹具跌落"
        df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
        file_out = '夹具跌落.xlsx'
    elif mode == "5":
        sheet_oqc_name = "杂光"
        df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
        file_out = '杂光.xlsx'
    else:
        print("非指定命令，请重新运行！！！")
    #***************
    wb = Workbook()
    ws = wb.active
    ws.titles = "Coveragr"
    #*****************
    mold_oqc_list = []
    if region == "2":
        file_path = 'MTF良率报表.xlsm'
        Workbook = load_workbook(file_path)
    elif region == "6":
        file_path = 'MTF良率报表.xlsx'
        Workbook = load_workbook(file_path)
    else:
        print("非指定命令，请重新运行！！！")

    sheet_Total = []    
    #pattern = r'^[0-9]+[a-zA-Z]+[-]+[0-9]+$'
    pattern = r'^[A-Za-z0-9-]+$'
    print_matching_sheet_names(file_path,pattern)
    i = 1
    j = 2   
    centered_alignment = Alignment(horizontal='center',vertical='center')
    fill_style = PatternFill(start_color ='FFFF0000', end_color='FFFF0000',fill_type='solid')
    for item in sheet_Total:
        #mtf报表****************************************************************
        mold_list = []
        print(item)
        ws.cell(row = 1,column = i).value = item
        sheet = 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_alignment
            a = ws.cell(row = 3,column=i,value = MTF_test_num).value
            continue
        #*************************************************************************

        #oqc报表*******************************************************************
        mold_oqc_list = []
        for index,row_oqc in df_oqc.iterrows():
            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 "/" 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] == "":
                        continue
                    else:
                        mold_oqc_list.append(row_oqc[7])
                        mold_oqc_list = [x for x in mold_oqc_list if x.lower() !='nan']
                continue

        try:
            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_alignment
            b = ws.cell(row = 3,column=i+1,value = MTF_sampling_num).value
            fraction_of_coverage = ws.cell(row=3,column=i+2,value=b/a)
            ws.cell(row=3,column=i+2).number_format = numbers.FORMAT_PERCENTAGE
            ws.cell(row=3,column=i+2).alignment = centered_alignment
            continue  
        #******************************************************************* 
        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_style
            continue        
        i = i + 3    
    wb.save(file_out)

3.初版代码

import re
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
import numpy as np
from openpyxl.styles import numbers
from openpyxl.styles import Alignment

def match_worksheet_name(worksheet_name,pattern):
    return re.match(pattern,worksheet_name) is not None

def read_worksheets_from_xlsm(file_path,pattern):
    xlsm_file = pd.ExcelFile(file_path)
    worksheets = xlsm_file.sheet_names
    matched_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_names
    sheet_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__':
    file_OQC_path = '12月性能抽检报表.xlsm'
    sheet_oqc_name = "MTF"
    df_oqc = pd.read_excel(file_OQC_path,sheet_oqc_name)
    sheet_Total = []
    file_path = '2024年12月MTF良率报表.xlsm'
    #pattern = r'^[0-9]+[a-zA-Z]+[-]+[0-9]+$'
    pattern = r'^[A-Za-z0-9-]+$'
    print_matching_sheet_names(file_path,pattern)
    #***************
    wb = Workbook()
    ws = wb.active
    ws.titles = "MTF_coveragr"
    #*****************
    i = 1
    j = 2   
    mold_oqc_list = []
    Workbook = load_workbook(file_path)
    centered_alignment = Alignment(horizontal='center',vertical='center')
    for item in sheet_Total:
        #mtf报表****************************************************************
        mold_list = []
        print(item)
        ws.cell(row = 1,column = i).value = item
        sheet = 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 = "MTF test num")  
            ws.cell(row = 2,column=i+1,value="MTF 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_alignment
            a = ws.cell(row = 3,column=i,value = MTF_test_num).value
            continue
        #*************************************************************************

        #oqc报表*******************************************************************
        mold_oqc_list = []
        for index,row_oqc in df_oqc.iterrows():
            if row_oqc[3] == item:
                mold_oqc_list.append(row_oqc[7])
                continue
            continue
        try:
            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_alignment
            b = ws.cell(row = 3,column=i+1,value = MTF_sampling_num).value
            fraction_of_coverage = ws.cell(row=3,column=i+2,value=b/a)
            ws.cell(row=3,column=i+2).number_format = numbers.FORMAT_PERCENTAGE
            ws.cell(row=3,column=i+2).alignment = centered_alignment
            continue        
        #******************************************************************* 
        i = i + 3
    file_out = 'MTF.xlsx'
    wb.save(file_out)

总结

分享：
是故形而上者谓之道，形而下者谓之器，化而裁之谓之变，推而行之谓之通，举而措之天下之民谓之事业。