杂项（办公自动化）

import collections
import openpyxl
import numpy as np
import time
import os
import pandas as pd


def qu_path():
    path1=r'D:\pythonProject\CQE\VLRR自動報表'  #路径
    list212=os.listdir(path1)
    list1=[]
    for i in list212:
        file_path_txt=os.path.join(path1,i)
        list1.append(file_path_txt)
    return list1

def qu_path1():
    path1=r'D:\pythonProject\CQE\VLRR自動報表\刷修資料'  #路径
    list212=os.listdir(path1)
    list1=[]
    for i in list212:
        file_path_txt=os.path.join(path1,i)
        list1.append(file_path_txt)
    return list1

def qu_shuju(lsi1):
    zhong=[]
    for i in lsi1:
        fd1 = pd.read_excel(i)
        a = fd1.loc[fd1['DUTYDESC'] == 'CQE']
        b=a['P/N NG PART'].values
        for r in b:
            zhong.append(r)
    pc=[]
    ccbu=[]
    for v in zhong:
        if 'AS' in v:
            pc.append(v)
        if 'A9' in v:
            ccbu.append(v)
    return pc,ccbu

def qu_kb(lsi):
    shangwu_kb=[]
    pc_kb=[]
    for i in lsi:
        fd1 = pd.read_excel(i)
        a = fd1.loc[fd1['DUTYDESC'] == 'CQE']
        b = a[['P/N NG PART', 'MODEL']].values
        for i in b:
            if '0KN1' in i[0]:
                if 'C' == i[1][0]:
                    shangwu_kb.append(i[0])
                elif 'B' == i[1][0]:
                    shangwu_kb.append(i[0])
                elif 'P' == i[1][0]:
                    shangwu_kb.append(i[0])
                else:
                    pc_kb.append(i[0])
    return pc_kb,shangwu_kb

def chuli1_liaohao(c3):
    fd_liaohao = pd.read_excel('D:\pythonProject\CQE\VLRR自動報表\廠商料號對照表.xlsx')
    list222=[]
    for k,v in c3.items():
        list111 = []
        a = fd_liaohao.loc[fd_liaohao['Item No'] == k]
        try:
            list111.append(a['Vendor'].values[0])
        except Exception as f1:
            continue
        list111.append(v)
        list222.append(list111)
    return list222

def chuli(list222):
    ds = []
    for i in list222:
        ds.append(i[0])
    ds=list(set(ds))
    df1=pd.DataFrame(list222)
    df2=df1.groupby(0).sum()
    list333=[]
    for r in ds:
        dict1={}
        dict1[r]=df2.loc[r].values[0]
        list333.append(dict1)
    return list333

def huqi_fenlei():
    lsi1 = qu_path1()
    pc, shangwu = qu_shuju(lsi1)
    pc_kb, shangwu_kb = qu_kb(lsi1)
    c1 = collections.Counter(pc)
    c2 = collections.Counter(shangwu)
    c3 = collections.Counter(pc_kb)
    c4 = collections.Counter(shangwu_kb)
    return c1,c2,c3,c4

def pc_baochun_kb(list333):
    past = 'ASUS_consumer  KB VLRR & IQC data Weekly Report_ WK2337.xlsx'
    fd1 = pd.read_excel(past)

    # 将列表数据写入单元格
    wb = openpyxl.load_workbook('ASUS_consumer  KB VLRR & IQC data Weekly Report_ WK2337.xlsx')

    ws = wb["EMS DATA"]
    for r in list333:
        for k, v in r.items():
            df2 = fd1.loc[fd1['Vendor'] == k]
            # fd1.loc[int(df2.index.tolist()[0]) + 1, 'W' + str(int(past.split('.')[0][-4:]) + 1)] = v
            x=int(df2.index.tolist()[0]) + 3
            y=df2.columns.tolist().index('W' + str(int(past.split('.')[0][-4:]))) +2
            ws.cell(row=x, column=y).value = v

    wb.save('ASUS_consumer  KB VLRR & IQC data Weekly Report_ WK2337.xlsx')
    print('========PC=======kb刷修数据填入OK')

def shangwu_baochun_kb(list333):
    past = 'ASUS_商务KB VLRR & IQC data Weekly Report_ WK2336.xlsx'
    fd1 = pd.read_excel(past)

    # 将列表数据写入单元格
    wb = openpyxl.load_workbook('ASUS_商务KB VLRR & IQC data Weekly Report_ WK2336.xlsx')

    ws = wb["EMS DATA"]
    for r in list333:
        for k, v in r.items():
            df2 = fd1.loc[fd1['Vendor'] == k]
            # fd1.loc[int(df2.index.tolist()[0]) + 1, 'W' + str(int(past.split('.')[0][-4:]) + 1)] = v
            x=int(df2.index.tolist()[0]) + 3
            y=df2.columns.tolist().index('W' + str(int(past.split('.')[0][-4:]) + 2))
            ws.cell(row=x, column=y).value = v

    wb.save('ASUS_商务KB VLRR & IQC data Weekly Report_ WK2336.xlsx')
    print('========商务=======kb刷修数据填入OK')

def chuli1_liaohao_zhonglei(c1):
    fd_liaohao = pd.read_excel('D:\pythonProject\CQE\VLRR自動報表\廠商料號對照表.xlsx')
    list222=[]
    for k,v in c1.items():
        list111 = []
        try:
            a = fd_liaohao.loc[fd_liaohao['Item No'] == k]
            list111.append(a['Vendor'].values[0])
            list111.append(a['Cate'].values[0])
            list111.append(v)
        except Exception as f1:
            continue
        list222.append(list111)
    return list222

def xieru_liaohao_PC(fenlei_shuju_PC):
    past = '2023 PCQ WK2336 consumer VLRR report.xlsx'
    wb = openpyxl.load_workbook('2023 PCQ WK2336 consumer VLRR report.xlsx')
    for zhi in fenlei_shuju_PC:
        fd1 = pd.read_excel(past, sheet_name=zhi[1])
        # 将列表数据写入单元格
        ws = wb[zhi[1]]
        try:
            df2 = fd1.loc[fd1['Vendor'] == zhi[0]]
            a = df2.index.tolist()[0]
            c = df2.columns.tolist()[0]
        except Exception as f1:
            continue
        # fd1.loc[int(df2.index.tolist()[0]) + 1, 'W' + str(int(past.split('.')[0][-4:]) + 1)] = v
        x = int(df2.index.tolist()[0]) + 3
        y = df2.columns.tolist().index('W' + str(int(past.split('WK')[1][2:4]) + 1)) + 1

        ws.cell(row=x, column=y).value = zhi[2]
    wb.save('2023 PCQ WK2336 consumer VLRR report.xlsx')
    print('========PC=======刷修数据填入OK')

def xieru_liaohao_shangwu(fenlei_shuju_sahngwu):
    past = 'WK2336-- CCBU NB Weekly Report- EMS.XLSX'
    wb = openpyxl.load_workbook('WK2336-- CCBU NB Weekly Report- EMS.XLSX')
    for zhi in fenlei_shuju_sahngwu:
        fd1 = pd.read_excel(past, sheet_name=zhi[1])
        # 将列表数据写入单元格
        ws = wb[zhi[1]]
        try:
            df2 = fd1.loc[fd1['Vendor'] == zhi[0]]
            a = df2.index.tolist()[0]
            c = df2.columns.tolist()[0]
        except Exception as f1:
            continue
        x = int(df2.index.tolist()[0]) + 3
        y = df2.columns.tolist().index('W'+str(int(past.split('WK')[1][0:4])+1)) + 1

        ws.cell(row=x, column=y).value = zhi[2]
    wb.save('WK2336-- CCBU NB Weekly Report- EMS.XLSX')
    print('========商务=======刷修数据填入OK')

def xunhuan(c1):
    list1111=[]
    for k,v in c1.items():
        item={}
        item[k]=v
        list1111.append(item)
    return list1111

def run1():
    c1, c2, c3, c4=huqi_fenlei()
    PC_liao=xunhuan(c1)
    shengwu_liao=xunhuan(c2)
    KB_PC_liao=xunhuan(c3)
    KB_shangwu_liao=xunhuan(c4)
    for i in KB_PC_liao:
        PC_liao.append(i)
    for i in KB_shangwu_liao:
        shengwu_liao.append(i)
    return PC_liao,shengwu_liao

def shouye_lianhao_touru_PC_baochun_shuxiu(list222,past):
    fd1 = pd.read_excel(past,sheet_name='List')
    # 将列表数据写入单元格
    wb = openpyxl.load_workbook(past)
    ws = wb["List"]
    for r in list222:
        for k, v in r.items():
            try:
                df2 = fd1.loc[fd1['Item No'] == k]
                a = df2.index.tolist()[0]
                c = df2.columns.tolist()[0]
            except Exception as f1:
                continue
            x = int(df2.index.tolist()[0]) + 3    #投入数
            y = df2.columns.tolist().index(past.split('報')[1][0:3]) + 2
            ws.cell(row=x, column=y).value = v
    wb.save(past)
    print('========PC=======刷修数据填入OK============================')

def shouye_lianhao_touru_shangwu_baochun_shuxiu(list222,past):

    fd1 = pd.read_excel(past,sheet_name='List')
    # 将列表数据写入单元格
    wb = openpyxl.load_workbook(past)
    ws = wb["List"]
    for r in list222:
        for k, v in r.items():
            try:
                df2 = fd1.loc[fd1['Item No'] == k]
                a = df2.index.tolist()[0]
                c = df2.columns.tolist()[0]
            except Exception as f1:
                continue
            x = int(df2.index.tolist()[0]) + 3    #投入数
            y = df2.columns.tolist().index(past.split('報')[1][0:3]) + 2
            ws.cell(row=x, column=y).value = v
    wb.save(past)
    print('========商务=======刷修数据填入OK===========================')

class Excel_open():
    def __init__(self, path='', sheet=None, infos=None, flag=True):
        self.path=path                            #文件位置
        self.sheet=sheet                          #打开表格的名字
        self.infos=infos                          #如果格式需要是字典类型，则会提供只字典的key
        self.student01=[]                         #输出的效果[[],[],[],[],[]]
        self.student02=[]                         #输入的效果[{},{},{},{}]
        self.flag=flag                            #是TRUE 表面有表头数据重第二行开始，是f就没有表头

    def read_file(self):
        #实例化对象，打开的那个文件
        workbook=openpyxl.load_workbook(self.path)
        #定义一个sheet，打开的是那个表
        sheet=workbook[self.sheet]
        for index,row in enumerate(sheet.rows):      #sheet.rows()取的是那一行
            #判断有没有表头
            if self.flag and index==0:               #如果有表头就跳过第一行
                continue
            one_row_list=[]                         #定义一个集合存储，小集合
            one_row_dict={}.fromkeys(self.infos)    #定义一个dict

            for col_index,col_value in enumerate(row):
                #把每一个单元格加到小集合中
                one_row_list.append(col_value.value)     #点value的意思是value这个函数
                one_row_dict[self.infos[col_index]]=col_value.value
            self.student01.append(one_row_list)
            self.student02.append(one_row_dict)

class Excel_write:
    def __init__(self, path='', sheet='', infos=[]):
        self.path=path            #文件位置
        self.sheet=sheet          #文件名自己定
        self.infos=infos          #如果infos为空就美没有表头

    def write_file(self,data):
        wrokbook=openpyxl.Workbook()      #实例化对象
        sheet=wrokbook.active             #激活一个sheet
        sheet.title=self.sheet            #设置表格名字
        if len(self.infos)!=0:            #写入表头
            data.insert(0,self.infos)       #在data的第一行添加
        for index,row in enumerate(data):
            for col_index,col_val in enumerate(row):
                sheet.cell(row=index+1,column=col_index+1,value=col_val) #多少行多少列 写入的数据是什么

        wrokbook.save(self.path)              #写入文件，传入地址
        print("写入成功")

def qu_path_touru():
    path1=r'D:\pythonProject\CQE\VLRR自動報表\SAP投入數'  #路径
    list212=os.listdir(path1)
    list1=[]
    for i in list212:
        file_path_txt=os.path.join(path1,i)
        list1.append(file_path_txt)
    return list1

def qu_shuju_touru(lsit1):
    list2=[]
    for i in lsit1:
        path=i
        sheet=i.split('數')[1].split('.')[0][1:]
        if 'KB' == sheet:
            continue
        infos=['s1','s2','s3','s4','s5','s6','s7 s8','s9','s10','s11','s12','s13','s14','s15','s16','s17']
        obj=Excel_open(path=path,sheet=sheet,infos=infos)
        obj.read_file()
        for i in obj.student02:
            list3 = []
            list3.append(i['s5'])
            list3.append(int(str(i['s17']).split('-')[1]))
            list2.append(list3)
    pc=[]
    ccbu=[]
    for v in list2:
        if 'AS' in v[0]:

            pc.append(v)
        if 'A9' in v[0]:
            ccbu.append(v)
    return pc,ccbu

def qu_shuju_touru_kb(lsit1):
    shangwu_kb=[]
    pc_kb=[]
    for i in lsit1:
        path=i
        sheet=i.split('數')[1].split('.')[0][1:]
        if 'KB' == sheet:
            infos=['s1','s2','s3','s4','s5','s6','s7','s8','s9','s10','s11','s12','s13','s14','s15','s16']
            obj=Excel_open(path=path,sheet=sheet,infos=infos)
            obj.read_file()
            for i in obj.student02:
                list3 = []
                if 'C' == str(i['s7']).split(' ')[3][0]:
                    list3.append(i['s5'])
                    list3.append(int(str(i['s16']).split('-')[1]))
                    shangwu_kb.append(list3)
                elif 'B' == str(i['s7']).split(' ')[3][0]:
                    list3.append(i['s5'])
                    list3.append(int(str(i['s16']).split('-')[1]))
                    shangwu_kb.append(list3)
                elif 'P' == str(i['s7']).split(' ')[3][0]:
                    list3.append(i['s5'])
                    list3.append(int(str(i['s16']).split('-')[1]))
                    shangwu_kb.append(list3)
                else:
                    list3.append(i['s5'])
                    list3.append(int(str(i['s16']).split('-')[1]))
                    pc_kb.append(list3)
    return pc_kb,shangwu_kb

def chuli1(list222):
    ds = []
    for i in list222:
        ds.append(i[0])
    ds=list(set(ds))
    df1=pd.DataFrame(list222)
    df2=df1.groupby(0).sum()
    list333=[]
    for r in ds:
        dict1={}
        dict1[r]=df2.loc[r].values[0]
        print(dict1)
        list333.append(dict1)
    return list333

def shouye_lianhao_touru_PC_baochun(list222,past):

    fd1 = pd.read_excel(past,sheet_name='List')
    # 将列表数据写入单元格
    wb = openpyxl.load_workbook(past)
    ws = wb["List"]
    for r in list222:
        for k, v in r.items():
            try:
                df2 = fd1.loc[fd1['Item No'] == k]
                a = df2.index.tolist()[0]
                c = df2.columns.tolist()[0]
            except Exception as f1:
                continue
            x = int(df2.index.tolist()[0]) + 2    #投入数
            y = df2.columns.tolist().index(past.split('報')[1][0:3]) + 2
            ws.cell(row=x, column=y).value = v
    wb.save(past)
    print('========PC=======投入数据填入OK============================')
    return list222

def shouye_lianhao_touru_shangwu_baochun(list222,past):
    fd1 = pd.read_excel(past,sheet_name='List')
    # 将列表数据写入单元格
    wb = openpyxl.load_workbook(past)
    ws = wb["List"]
    for r in list222:
        for k, v in r.items():
            try:
                df2 = fd1.loc[fd1['Item No'] == k]
                a = df2.index.tolist()[0]
                c = df2.columns.tolist()[0]
            except Exception as f1:
                continue
            x = int(df2.index.tolist()[0]) + 2    #投入数
            y = df2.columns.tolist().index(past.split('報')[1][0:3]) + 2
            ws.cell(row=x, column=y).value = v
    wb.save(past)
    print('========商务=======投入数据填入OK===========================')

def run():
    lsit1 = qu_path_touru()
    pc_kb,shangwu_kb=qu_shuju_touru_kb(lsit1)
    pc,ccbu=qu_shuju_touru(lsit1)

    KB_PC_zong=chuli1(pc_kb)                #投入数键盘求和
    KB_shangwu_zong=chuli1(shangwu_kb)      #投入数键盘求和
    pc_zong=chuli1(pc)
    ccbu_zong=chuli1(ccbu)
    for i in KB_PC_zong:
        pc_zong.append(i)
    for i in KB_shangwu_zong:
        ccbu_zong.append(i)
    return pc_zong,ccbu_zong

import win32com.client as win32

def sendmail(mailto, mailsubject, mailbody, filename):
    # 建立Outlook應用程式物件
    outlook = win32.Dispatch('Outlook.Application')
    # 建立郵件物件
    mail = outlook.CreateItem(0)
    # 設定收件者、主旨、HTML內容、編碼格式
    mail.To = mailto  # 收件人
    mail.Subject = mailsubject
    mail.HTMLBody = mailbody
    mail.BodyFormat = 2  # 編碼格式，3代表UTF-8
    mail.Attachments.Add(Source=filename)
    # 發送郵件
    mail.Send()

if __name__ == '__main__':
    past_pc = 'CQE電子料PC良率周報W36.xlsx'
    past_shangwu = 'CQE電子料商務良率周報W36.xlsx'
    PC_liao,shengwu_liao=run1()
    shouye_lianhao_touru_PC_baochun_shuxiu(PC_liao,past_pc)
    shouye_lianhao_touru_shangwu_baochun_shuxiu(shengwu_liao,past_shangwu)
    pc_zong,ccbu_zong=run()
    shouye_lianhao_touru_PC_baochun(pc_zong,past_pc)
    shouye_lianhao_touru_shangwu_baochun(ccbu_zong,past_shangwu)

    filepath = r"D:\pythonProject\CQE\VLRR自動報表\CQE電子料PC良率周報W36.xlsx"
    mailsubject = "出勤報表"
    mailtxt = '''<html><body>
    <font  color="blue"><h4>Dear Sir:</h4></font><br>
    附件為今天的貴部門員工的出勤資料，請參考，謝謝！
              </body></html>
            '''
    mailto = "Evan_Liang@pegatroncorp.com"
    sendmail(mailto, mailsubject, mailtxt, filepath)