python操作三个excel并筛选其中数据

# -*- coding: UTF-8 -*-
 
import os
import xlrd
import xlwt
import xlwings as xw


root_dir = os.path.abspath('.')
specs_path = root_dir +"\\规格清单模板.xlsx"
data_path = root_dir +"\\数据.xlsx" 
rule_path = root_dir +"\\规则.xlsx"
write_excel_path = root_dir +"\\test.csv"

#获取excel文件的workbook(工作簿)对象
data_excel = xlrd.open_workbook(data_path,encoding_override="UTF-8")
rule_excel = xlrd.open_workbook(rule_path,encoding_override="UTF-8")
specs_excel = xlrd.open_workbook(specs_path,encoding_override="UTF-8")
write_excel_book = xw.Book()
data_sheet = data_excel.sheet_by_index(0)
rule_sheet = rule_excel.sheet_by_index(0)
specs_sheet = specs_excel.sheet_by_index(0)
sht= write_excel_book.sheets('sheet1')

#查询规则名称
rule_rows_cols = []
for i in range(rule_sheet.nrows):
    cell = rule_sheet.cell(i,0).value
    rule_rows_cols.append(cell)
#查询巡检规格清单模板
specs_rows_cols = []
for i in range(specs_sheet.ncols):
    cell = specs_sheet.cell(0,i).value
    specs_rows_cols.append(cell)
#查询结果数据
sheet_cols = []
for data_row in range(data_sheet.nrows):#循环打印每一行
    #获取具体结果数据
    data_rows_cols = []
    for data_col in range(data_sheet.ncols):#循环打印每一列
        data_cell = data_sheet.cell(data_row,data_col).value
        if str(data_cell).find('(') != -1:
            data_rows_cols.append(data_cell[0:str(data_cell).find('(')])
        elif str(data_cell).find('（') != -1:
            data_rows_cols.append(data_cell[0:str(data_cell).find('（')])
        else:
            data_rows_cols.append(data_cell)
    #获取具体规则数据
    rule_data = []
    rule_cell = data_rows_cols[6]
    rule_num = rule_rows_cols.index(rule_cell)
    for rule_col in range(rule_sheet.ncols):
        rule_cell_data = rule_sheet.cell(rule_num,rule_col).value
        if rule_cell_data.find('(') != -1:
            rule_data.append(rule_cell_data[0:str(rule_cell_data).find('(')])
        elif str(rule_cell_data).find('（') != -1:
            rule_data.append(rule_cell_data[0:str(rule_cell_data).find('（')])
        else:
            rule_data.append(rule_cell_data)
    #循环对比
    sheet_col = []
    for i in range(len(specs_rows_cols)):
        if i>=7:
            if specs_rows_cols[i] not in rule_data:
                sheet_col.append('N')
            elif specs_rows_cols[i] in data_rows_cols:
                sheet_col.append('TBC')
            else:
                sheet_col.append('Y')
        else:
            sheet_col.append(specs_rows_cols[i])
    sheet_cols.append(sheet_col)

#写到excel里
for each_col in range(len(sheet_cols)):
    col = 'A' + str(each_col +1)
    sht.range(col).value = sheet_cols[each_col]
write_excel_book.save(write_excel_path)