python优化办公（excel）02

import time
startt = time.time()
import win32com.client as win32
import os
import pandas as pd
#获取需要比较的月份的新旧
def get_old_new_excel():
    need_exc = []
    for i in os.listdir():
        if len(i.split('月')[0].split('年')) > 1:
            need_exc.append(i)
    old_month = need_exc[0]
    new_month = need_exc[1]
    if int(need_exc[1].split('月')[0].split('年')[0]) < int(need_exc[0].split('月')[0].split('年')[0]):
        old_month = need_exc[1]
        new_month = need_exc[0]
    elif int(need_exc[1].split('月')[0].split('年')[0]) == int(need_exc[0].split('月')[0].split('年')[0]) and int(need_exc[1].split('月')[0].split('年')[1]) < int(need_exc[0].split('月')[0].split('年')[1]):
        old_month = need_exc[1]
        new_month = need_exc[0]
    return old_month,new_month
# 读取数据
print('pandas正在读取 ',get_old_new_excel()[0],' 和 ',get_old_new_excel()[1],' 的数据\nwaiting.......')
excel_old = pd.DataFrame(pd.read_excel(get_old_new_excel()[0],sheetname='塔类'))
excel_new = pd.DataFrame(pd.read_excel(get_old_new_excel()[1],sheetname='塔类'))
excel = win32.Dispatch('Excel.Application')
excel.Visible = True
# need_col= [17,21,24,51,61,73,77,84,89,99,109,113,117,121,125]
need_col= ['Q','U','Y','AW','BI','BU','BY','CF','CK','CU','DE','DI','DM','DQ','DU','EG']
print('win32com正在读取 ',get_old_new_excel()[0],' 和 ',get_old_new_excel()[1],' 的数据\nwaiting.......')
Excel_old = excel.Workbooks.Open(os.getcwd()+'\\'+ get_old_new_excel()[0])
Excel_new = excel.Workbooks.Open(os.getcwd()+'\\'+ get_old_new_excel()[1])
Excel_old_Sheet = Excel_old.Worksheets('塔类')
Excel_new_Sheet = Excel_new.Worksheets('塔类')
print('读取数据完成，即将比较数据')
#开始比较，先找到需要比较的行 i  ,再与上月的同行 old_index相比较 ，一样则执行操作
for i in excel_new.loc[excel_new['产品服务费与上月相比是否变化'] == '是'].index.tolist(): 
    i = int(i + 2)
    print('正在比较第 ',i,' 行')
    new_b = Excel_new_Sheet.Cells(i,2).value
    old_index = int(excel_old.loc[excel_old['产品业务确认单编号'] == new_b].index.tolist()[0]) + 2
    cha_ju = ''
    for lie in need_col:
        if Excel_new_Sheet.Cells(i,lie).value != Excel_old_Sheet.Cells(old_index,lie).value:
            Excel_new_Sheet.Cells(i,lie).Interior.ColorIndex = 6
            cha_ju = cha_ju + str(lie) + ' '
        else:
            continue
    Excel_new_Sheet.Cells(i,'EB').value = cha_ju
#保存，退出，总结，暂停给用户看
print('比较完成，正在保存并退出....')
Excel_old.save
Excel_old.close
Excel_new.save
Excel_new.close
excel.Visible = 0
stopt = time.time()
print('一共比较 ',len(excel_new.loc[excel_new['产品服务费与上月相比是否变化'] == '是'].index.tolist()),' 行，共花费大约',(stopt - startt)/60,'分钟')
os.system('pause')