'''
@Author : LuXiaozhen
@createtime : 2021/09/27 16:49:14
@Desc : None
'''
import openpyxl as xl
from win32com.client import Dispatch
import win32com.client
from pathlib import Path
import os
import time
import sys
sys.path.insert(0,r'D:/RPA_ENV')
import rpa_init
from core.config.read_root_path import read_root_path
from core.log.rpalog import Logger
from copy import copy
logger = Logger.getLogger('RPAName')
import warnings
warnings.filterwarnings('ignore')
import pandas as pd
'''
@Desc : None
@Author : LuXiaozhen
@createtime : 2021/09/27 16:49:33
@param1 : None
@return : None
'''
'''
@Desc : 复制插入多行数据
@Author : CaiWenkang
@createtime : 2021/08/18 14:27:57
@param1 : source_file_path = 源文件路径 target_file_path = 目标文件路径 target_new_filename = 新目标文件路径
@return : None
'''
def MatchExcelData(source_file_path,target_file_path,target_new_filename):
# try:
# 读取源文件和目标文件数据
source_data = xl.load_workbook(source_file_path) # 打开源文件的excel表
target_data = xl.load_workbook(target_file_path)# 打开目标文件的excel表
if os.path.exists(target_new_filename):
target_new_data = Workbook()
target_new_data=xl.load_workbook(target_new_filename)#打开新建的excel表
# 获取工作表中的内容
source_table = source_data.sheets()[0]
target_table = target_data.sheet_by_index(0)
#获取源文件的行数和列数
nrows_source = source_table.nrows
ncols_source = source_table.ncols
# 获取目标文件行数和列数
nrows_target = target_table.nrows
ncols_target = target_table.ncols
# 新建新的目标文件用于写入
target_new_data = xlwt.Workbook()
# 在已创建的新的文件中创建一个新的sheet
target_new_table = target_new_data.add_sheet('sheet1', cell_overwrite_ok=True)
#遍历源文件，找出'产品编码' 那一列
flag = False
for m in range(nrows_source):
for n in range(ncols_source):
value_source_col = source_table.cell(m, n).value
if value_source_col == '产品编码':
columns=n
row=m
flag = True
break
if flag == True:
break
#对源文件和目标文件Excel指定数据进行操作
for i in range(6,nrows_source):
#获取源文件第E列数据('产品编码'序列)
value_source_col_4 = source_table.cell(i,n).value
#遍历产品编码那一列：找到不同编码所对应的行
for a in range(6,nrows_target):
value_target_col_4 = target_table.cell(a, n).value
if value_source_col_4 == value_target_col_4:
columns=n
rows=a
break
#遍历目标文件的不同编码行(每一行)
for b in range(ncols_target):
#遍历目标文件不同编码行的值
value_target_row = target_table.cell(a,b).value
#遍历源文件与目标文件的相同编码行的值
value_source_row = source_table.cell(i,b).value
#对比每一行的内容，相同的则进行删除，不同的则填入新建工作表中的对应单元格
if value_source_row == value_target_row:
del value_target_row
else:
target_new_table.write(a, b, value_target_row)
ws_insert.cell(insert_rows, i + 1).number_format = ws_copy.cell(copy_rows, i + 1).number_format
ws_insert.cell(insert_rows, i + 1).font = copy(ws_copy.cell(copy_rows, i + 1).font)
ws_insert.cell(insert_rows, i + 1).alignment = copy(ws_copy.cell(copy_rows, i + 1).alignment)
ws_insert.cell(insert_rows, i + 1).fill = copy(ws_copy.cell(copy_rows, i + 1).fill)
ws_insert.cell(insert_rows, i + 1).border = copy(ws_copy.cell(copy_rows, i + 1).border)
ws_insert.row_dimensions[insert_rows + 1].height = ws_insert.row_dimensions[insert_rows].height
ws_insert.row_dimensions[insert_rows].height = ws_copy.row_dimensions[copy_rows].height
target_new_data.save(target_new_filename)
# #if __name__ == '__main__':
# #copy_path = 'D:\Users\EX-LUXIAOZHEN002\Desktop\副本延付本息表-资管-20210922-20210924.xlsx'
# insert_path = 'D:\robot\rpadata\trademiddesk_department\asset_management_center_admin_team\weekly_report_of_deferred_principal_and_interest\2021\202109\20210923\本期模板\专户产品融资类项目延付本息情况表(20210922-20210924).xlsx'
# insert_multi_row(copy_path,'','7',insert_path,'','7')
