对比两个xlsx表格中的某一列数据是否相同,将相同数据输出到某一个xlsx文件中。
import xlrd
INPUT_XLSX1 = 'l1.xlsx' #要对比的表格1路径
INPUT_XLSX2 = 'l2.xlsx' #要对比的表格2路径
OUTPUT_XLSX = 'thesame.xlsx' #输出表格路径
XLSX1_COLUMN = 2 #表格1要对比的列数
XLSX2_COLUMN = 2 #表格2要对比的列数
thesame = [] # 相同值列表
def compared_data(dat1, dat2):
data1 = xlrd.open_workbook(dat1) # 读l1.xlsx
data2 = xlrd.open_workbook(dat2) # 读l2.xlsx
print('sheet:' + str(data1.sheet_names()) + str(data2.sheet_names()))
table1 = data1.sheet_by_index(0) # 取l1.xlsx第一个sheet
table2 = data2.sheet_by_index(0) # 取l2.xlsx第一个sheet
col1 = table1.col_values(XLSX1_COLUMN) # 对比列
col2 = table2.col_values(XLSX2_COLUMN) # 对比列
for i in col1:
if i in col2:
thesame.append(i)
print(thesame)
def write_data(path):
with open(path, 'w', encoding='utf-8') as data:
for i in range(len(thesame)):
data.write(str(thesame[i]))
data.write('\n')
if __name__ == "__main__":
compared_data(INPUT_XLSX1,INPUT_XLSX2)
write_data(OUTPUT_XLSX)