python怎么筛选excel数据_Python操作三个excel并过滤数据,python,筛选,其中

# -*- 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)