根据execl报表数据及汇总报表模板生成当月汇总数据

一、报表数据格式及模板格式

        所有文件：

.py文件执行程序；

output.txt程序执行的日志；

run.bat双击自动执行.py文件；

两个  11月25日  的问题数报表数据样例；

最后一个是汇总文件模板；

        报表数据格式样例：

        汇总报表格式样例：

二、参考代码

2.1 run.bat

CALL C:\ProgramData\anaconda3\Scripts\activate.bat C:\ProgramData\anaconda3
@echo off
python detect_statistics.py > output.txt 2>&1
echo

2.2 .py执行脚本

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time        :2024/11/25 10:19
# @Author      :weiz
# @ProjectName :weizTools
# @File        :detect_statistics.py
# @Description :
import openpyxl
import pandas as pd
import re
from openpyxl import load_workbook
import os
from datetime import datetime
import json
import shutil


def readLineTXT(path):
    """
    按行读取txt文件
    :param path:
    :return:
    """
    txtLines = []
    file = open(path)
    for line in file:
        line = line.strip('\n')
        txtLines.append(line)
    file.close()
    return txtLines


def get_month():
    now = datetime.now()
    current_month = now.strftime('%m')
    return current_month


def num_to_excel_col(n):
    """
    将数字索引转换为Excel列的字母表示。
    :param n: 数字索引（从1开始）
    :return: Excel列的字母表示
    """
    result = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        result = chr(65 + remainder) + result
    return result


def excel_filterAndSummary(table_data, error_list_type, detect_result_name="检测结果", detect_result_name_value="NOK"):
    table_data_values = table_data.values

    ind_detect_result_name = -1
    ind_car_type = -1
    ind_error_type = -1
    ind_error_detail = -1
    ind_VIN = -1

    for ind, column_name_tmp in enumerate(table_data.head(1)):
        if column_name_tmp == detect_result_name:
            ind_detect_result_name = ind
        if column_name_tmp == "车型":
            ind_car_type = ind
        if column_name_tmp == "错误类型":
            ind_error_type = ind
        if column_name_tmp == "错误详情":
            ind_error_detail = ind
        if column_name_tmp == "VIN":
            ind_VIN = ind

    if ((ind_detect_result_name < 0) or (ind_car_type < 0) or (ind_error_type < 0) or (ind_error_detail < 0) or
            (ind_VIN < 0)):
        print("execl表中的未查询到[{},{},{},{},{}]中的其中一种".format(detect_result_name, "车型", "错误类型",
                                                                       "错误详情", "VIN"))
        return {}

    dest_excel_data = table_data_values[table_data_values[:, ind_detect_result_name] == detect_result_name_value]

    vin_statistics_nok = {}
    vin_statistics_ok = {}
    for table_line in table_data_values:
        nok_and_ok = table_line[ind_detect_result_name]
        vin = table_line[ind_VIN]
        car_type = table_line[ind_car_type]
        error_type = table_line[ind_error_type]
        error_detail = table_line[ind_error_detail]

        vin_new = vin + ' ' + car_type
        if nok_and_ok == detect_result_name_value:
            if vin_new in vin_statistics_nok:  # 统计单个vin码有多少错误类型
                if error_type in vin_statistics_nok[vin_new]:
                    vin_statistics_nok[vin_new][error_type] = vin_statistics_nok[vin_new][error_type] + 1
                else:
                    vin_statistics_nok[vin_new][error_type] = 1
            else:
                vin_statistics_nok[vin_new] = {}
                vin_statistics_nok[vin_new][error_type] = 1
        else:
            if vin_new in vin_statistics_ok:
                vin_statistics_ok[vin_new] = vin_statistics_ok[vin_new] + 1
            else:
                vin_statistics_ok[vin_new] = 1

    total_number_nok = len(vin_statistics_nok)
    total_number = total_number_nok + len(vin_statistics_ok)
    print("   过滤结果：{} {}项，vin码归并后有 {} 项, vin总项为 {} 项".format(detect_result_name_value,
                                                       len(dest_excel_data), total_number_nok, total_number))

    table_data_statistics_not = {}
    for dict_key in vin_statistics_nok:
        error_type = ""
        is_find_error_type = False
        for error_type_tmp in error_list_type:  # 遍历错误类型确定最终的错误
            for error_type_tmp_tmp in error_type_tmp:
                if error_type_tmp_tmp in vin_statistics_nok[dict_key]:
                    # print(ind, error_type_tmp)
                    error_type = error_type_tmp[-1]
                    is_find_error_type = True
                    break

            if is_find_error_type:
                break

        if error_type:
            vin, car_type = dict_key.split(' ')
            if car_type in table_data_statistics_not:
                if error_type in table_data_statistics_not[car_type]:
                    table_data_statistics_not[car_type][error_type] = table_data_statistics_not[car_type][error_type] + 1
                else:
                    table_data_statistics_not[car_type][error_type] = 1
            else:
                table_data_statistics_not[car_type] = {}
                table_data_statistics_not[car_type][error_type] = 1

        else:
            print("数据标注出现未知的错误类型：{}".format(vin_statistics_nok[dict_key]))

    table_data_statistics_ok = {}
    for dict_key in vin_statistics_ok:
        vin, car_type = dict_key.split(' ')
        if car_type in table_data_statistics_ok:
            table_data_statistics_ok[car_type] = table_data_statistics_ok[car_type] + 1
        else:
            table_data_statistics_ok[car_type] = 1

    # print(table_data_statistics)
    return table_data_statistics_not, table_data_statistics_ok


def read_summary_execl(summary_table_path, summary_sheet_ind):
    # 读取Excel文件
    wb = load_workbook(summary_table_path)
    sheet = wb.worksheets[summary_sheet_ind]
    # print(sheet)

    # 获取合并单元格的信息
    merged_cells = sheet.merged_cells.ranges
    # print(merged_cells)

    # 读取数据到Pandas DataFrame
    data = []
    for row in sheet.iter_rows(values_only=True):
        data.append(list(row))

    df = pd.DataFrame(data[1:], columns=data[0])

    # 处理合并单元格
    for merged_cell in merged_cells:
        min_col, min_row, max_col, max_row = merged_cell.bounds
        value = sheet.cell(row=min_row, column=min_col).value
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                df.iat[row - 2, col - 1] = value  # 注意索引从0开始

    # 显示处理后的数据
    # print(df.values)

    return df.values, sheet, wb


def find_time_ind(summary_data_values, time_day):
    """
    找时间的索引
    :param summary_data_values:
    :return:
    """
    time_ind = -1
    is_find = False
    for ind_x, table_line in enumerate(summary_data_values):
        for ind, value in enumerate(table_line):
            if value == time_day:
                is_find = True
                time_ind = ind
                break

        if is_find:
            break

    if is_find:
        return time_ind
    else:
        print("未找到对应时间索引{}".format(time_day))
        exit(1)


def error_count_summary(factory_floor_flag, car_type, error_type, num, time_ind, summary_data_values, special_items=None):
    """
    错误类型的数量写入summary_data_values中
    :param factory_floor_flag:
    :param car_type:
    :param error_type:
    :param num:
    :param time_ind:
    :param summary_data_values:
    :param special_items:
    :return:
    """
    fix_item = ""
    if special_items is None:
        for ind, error_type_tmp in enumerate(g_error_list_type[:-1]):
            if error_type in error_type_tmp:
                fix_item = g_count_class_list[ind]
                break
    else:
        fix_item = special_items

    is_ok = False
    for ind_x, table_line in enumerate(summary_data_values):
        if factory_floor_flag == table_line[0] and car_type == table_line[1] and fix_item == table_line[2]:
            summary_data_values[ind_x][time_ind] = summary_data_values[ind_x][time_ind] + num
            is_ok = True
            # print("----错误类型是:{},厂:{},车型:{},数量:{}".format(error_type, factory_floor_flag, car_type,num))
            # print("----table_line[0]:{} table_line[1]:{} table_line[2]:{}".format(table_line[0],table_line[1],table_line[2]))

    if not is_ok:
        print("    标准错误项:{}".format(fix_item), end='')
        print("    对应错误类型未写入统计总表，该错误类型是:{},厂:{},车型:{},数量:{}".format(error_type,
                                                                                                   factory_floor_flag,
                                                                                                   car_type,num))


def data_records(table_path, factory_floor_list, is_save=None):
    data_records_name = "data_records.json"
    data_records_name_path = os.path.join(os.path.dirname(table_path), data_records_name)
    data_records = {}
    if os.path.exists(data_records_name_path):
        with open(data_records_name_path, 'r', encoding='utf-8') as file:
            data_records = json.load(file)

    factory_floor_flag = os.path.basename(table_path)[:2]

    day_summary = ""
    month_summary = ""

    is_find_ok = False
    for factory_floor_list_tmp in factory_floor_list:
        if factory_floor_flag in factory_floor_list_tmp:
            factory_floor_flag = factory_floor_list_tmp[-1]
            is_find_ok = True
            break
    if not is_find_ok:
        print("该 {} 不是标准格式命名文件".format(os.path.basename(table_path)))
        # print("    标准格式命名文件: xx厂xx月xx日检测结果记录")
        return False, factory_floor_flag, month_summary, day_summary

    file_path = os.path.basename(table_path)
    file_month_ind = file_path.find("月")
    if file_month_ind > 2:
        month_summary = "{:02d}".format(int(file_path[2:file_month_ind]))
    else:
        print("    该 {} 不是标准格式命名文件".format(os.path.basename(table_path)))
        # print("    标准格式命名文件: xx厂xx月xx日检测结果记录")
        return False, factory_floor_flag, month_summary, day_summary

    pattern = r'月(\d+)日'
    match = re.search(pattern, table_path)
    if match:
        day_summary = str(int(match.group(1))) + '日'
    else:
        print("    该 {} 不是标准格式命名文件".format(os.path.basename(table_path)))
        # print("    标准格式命名文件: xx厂xx月xx日检测结果记录")
        return False, factory_floor_flag, month_summary, day_summary

    time_key = month_summary + "{:02d}".format(int(match.group(1)))
    if time_key in data_records:
        if factory_floor_flag in data_records[time_key]:
            print("    该 {} 文件已经被执行，所以忽略".format(os.path.basename(table_path)))
            return False, factory_floor_flag, month_summary, day_summary
        else:
            data_records[time_key].append(factory_floor_flag)
    else:
        data_records[time_key] = []
        data_records[time_key].append(factory_floor_flag)

    if is_save:
        with open(data_records_name_path, 'w', encoding='utf-8') as file:
            json.dump(data_records, file, indent=4, ensure_ascii=False)
    else:
        print("厂：{} 时间：{}".format(factory_floor_flag, month_summary + "月" + day_summary))

    return True, factory_floor_flag, month_summary, day_summary


def find_template(folder_path):
    """
    寻找总表模板文件
    :param folder_path:
    :return:
    """
    folder_path = os.path.dirname(table_path)
    file_name_list = os.listdir(folder_path)

    count_num = 0
    template_name = ""
    for file_name in file_name_list:
        if "xx月合格率统计表模板" in file_name:
            template_name = file_name
            count_num = count_num + 1

    if count_num == 0:
        print("未找到总表模板文件 eg:防错项目xx月合格率统计表模板.xlsx")
        return False, template_name
    elif count_num > 1:
        print("找到多个总表模板文件 ")
        return False, template_name

    return True, template_name



def main(table_path, table_sheet_ind, summary_sheet_ind, error_list_type, factory_floor_list):
    is_ok, factory_floor_flag, month_summary, day_summary = data_records(table_path, factory_floor_list)
    if not is_ok:
        return
    # print(factory_floor_flag)

    is_ok, summary_table_name = find_template(table_path) # "奇瑞防错项目xx月合格率统计表模板.xlsx"
    if not is_ok:
        return
    summary_table_path = os.path.join(os.path.dirname(table_path),
                                          summary_table_name.replace("xx", month_summary).replace("模板", ""))

    if not os.path.exists(os.path.join(os.path.dirname(table_path),summary_table_name)):
        print("     奇瑞防错项目xx月合格率统计表模板.xlsx 该文件未找到")
        return

    if not os.path.exists(summary_table_path):
        shutil.copy(os.path.join(os.path.dirname(table_path),summary_table_name), summary_table_path)

    table_data = pd.read_excel(io=table_path, sheet_name=table_sheet_ind)

    table_data_statistics_nok, table_data_statistics_ok = excel_filterAndSummary(table_data, error_list_type)
    print("    nok项数据详情:", table_data_statistics_nok, end='')
    print("     ok项数据详情:", table_data_statistics_ok)

    summary_data_values, summary_data, wb = read_summary_execl(summary_table_path, summary_sheet_ind)

    time_ind = find_time_ind(summary_data_values, day_summary)

    # 处理NOK数据
    nok_number = {}
    for car_type in table_data_statistics_nok:
        total_number_tmp = 0
        for error_type in table_data_statistics_nok[car_type]:
            num = table_data_statistics_nok[car_type][error_type]
            total_number_tmp = total_number_tmp + num
            error_count_summary(factory_floor_flag, car_type, error_type, num, time_ind, summary_data_values,
                                special_items=None)

        error_count_summary(factory_floor_flag, car_type, "", total_number_tmp, time_ind, summary_data_values,
                            special_items="NG总数")

        if car_type in nok_number:
            nok_number[car_type] = nok_number[car_type] + total_number_tmp
        else:
            nok_number[car_type] = total_number_tmp

    # 处理OK数据
    for car_type in table_data_statistics_ok:
        total_number_tmp = table_data_statistics_ok[car_type]
        if car_type in nok_number:
            total_number_tmp = nok_number[car_type] + total_number_tmp

        error_count_summary(factory_floor_flag, car_type, "", total_number_tmp, time_ind,
                            summary_data_values, special_items="总检数")

    # 准备保存数据
    # print(summary_data_values)
    for ind_x, table_line in enumerate(summary_data_values):
        if table_line[2] in g_count_class_list:
            for ind_y, value in enumerate(table_line):
                if ind_y > 2:
                    execl_xy = num_to_excel_col(ind_y+1) + str(ind_x+2)
                    summary_data[execl_xy] = value
                    # print(execl_xy)

    # print(table_line)
    # print(summary_data[ind_x])
    # 保存文件
    wb.save(summary_table_path)
    data_records(table_path, factory_floor_list, True)


g_table_path = r"C:\Users\Administrator\Desktop\detect_statistics\超一11月24日检测结果记录.xls"
g_table_sheet_ind = 1  # Excel数据所在页索引
g_summary_table_path = r"C:\Users\Administrator\Desktop\detect_statistics\奇瑞防错项目11月合格率统计表.xlsx"  # 总表路径
g_summary_sheet_ind = 2  # Excel数据所在页索引
# 错误类型，越靠前表示问题权重越大 与g_count_class_list有联动，不要轻易更改它们的顺序
g_error_list_type = [["错漏装"], ["测试误报"], ["新物料","新物料\新零件号", "新零件号","新物料/新零件号"], ["人工问题"], ["设备问题"],
                     ["模型问题", "模型问题(结构件)","模型问题（结构件）"],["近似色问题"],["其他"]]
g_factory_floor_list = [["超一"], ["22线", "二总"], ["青岛"]]
g_count_class_list = ["检出NG总数","测试误报NG总数","新物料NG总数","车辆操作NG总数","设备问题NG总数","模型问题（结构件）NG总数",
                      "近似色NG总数","总检数","NG总数","漏检总数","视觉系统NG总数","有效检测数"]
# 三厂的xx厂xx月xx日检测结果记录所有记录，总表也可以放在里面。注意一个厂只能放一天的否则会累计
g_table_folder_path = os.getcwd() # r"C:\Users\Administrator\Desktop\detect_statistics"
if __name__ == "__main__":
    # main(g_table_path, g_table_sheet_ind, g_summary_table_path, g_summary_sheet_ind, g_error_list_type, g_factory_floor_list)

    folder_name_list = os.listdir(g_table_folder_path)
    for folder_name in folder_name_list:
        table_path = os.path.join(g_table_folder_path, folder_name)
        main(table_path, g_table_sheet_ind, g_summary_sheet_ind, g_error_list_type, g_factory_floor_list)