Excel文档内容对比（不区分大小写）

对两份Excel文档的内容进行比对，查看一份文档的记录是否存在于另一份文档中。



用Python来实现筛选匹配：

import openpyxl
import pandas as pd

path1 = r"C:UsersAdministratorDesktopfile1.xlsx"        #文档1
path2 = r"C:UsersAdministratorDesktopfile2.xlsx"        #文档2

#读取文件（读取文件时文件需要关闭）
file1 = pd.read_excel(path1)
file2 = pd.read_excel(path2)

for i in range(len(file1)):
	#获得file1中第i行各列的值
    site_short_name = file1.loc[i,'site_short_name']
    TOPIC_LV1 = file1.loc[i,'TOPIC_LV1']
    TOPIC_LV2 = file1.loc[i,'TOPIC_LV2']
    TOPIC_LV3 = file1.loc[i,'TOPIC_LV3']
    
    #查找file2中是否有符合全部条件的记录
    value = file2[(file2['TOPIC_LV1'] == TOPIC_LV1)&(file2['TOPIC_LV2'] == TOPIC_LV2)&(file2['TOPIC_LV3'] == TOPIC_LV3)]
    
    #判断value是否为空
    if len(value):
        file1.loc[i,'match'] = 'Y'    #如果不为空，则表示有符合条件的记录
    else:
        file1.loc[i,'match'] = 'N'

运行程序，查看匹配结果

#print(file1)

# 筛选查看“match”列为“Y”的记录
file1[file1['match']=='Y']

PS：想要不区分大小写时

for i in range(len(file1)):
	#casefold()强制将str转换为小写
    site_short_name = file1.loc[i,'site_short_name'].casefold()
    TOPIC_LV1 = file1.loc[i,'TOPIC_LV1'].casefold()
    TOPIC_LV2 = file1.loc[i,'TOPIC_LV2'].casefold()
    TOPIC_LV3 = file1.loc[i,'TOPIC_LV3'].casefold()
    
    #将dataframe转为str后再转为小写
    value = file2[(file2['TOPIC_LV1'].str.casefold() == TOPIC_LV1)&(file2['TOPIC_LV2'].str.casefold() == TOPIC_LV2)&(file2['TOPIC_LV3'].str.casefold() == TOPIC_LV3)]
    
    #判断value是否为空
    if len(value):
        file1.loc[i,'match'] = 'Y'    #如果不为空，则表示有符合条件的记录
    else:
        file1.loc[i,'match'] = 'N'