python中用xlwings逐一筛选包含有特定数据的工作表

import os
import sys
import xlwings as xw
import argparse

def open_excel_workbook(file_path):
“”"
打开 Excel 工作簿并返回 Workbook 对象

Parameters:
    file_path (str): Excel 文件的路径

Returns:
    xlwings.main.Book: Excel 工作簿对象，如果文件不存在则返回 None
"""
if not os.path.exists(file_path):
    print(f"文件 '{file_path}' 不存在.")
    return None

app = xw.App(visible=False, add_book=False)
wb = app.books.open(file_path)
return wb

def close_excel_workbook(wb):
“”"
关闭 Excel 工作簿和 Excel 应用程序

Parameters:
    wb (xlwings.main.Book): Excel 工作簿对象
"""
if wb:
    wb.save()
    wb.close()

def get_column_letter(column_index):
“”"
将列索引转换为 Excel 中的字母表示

Parameters:
    column_index (int): 列索引

Returns:
    str: Excel 中的字母表示
"""
dividend = column_index
column_letter = ''
while dividend > 0:
    remainder = (dividend - 1) % 26
    column_letter = chr(65 + remainder) + column_letter
    dividend = (dividend - remainder) // 26
return column_letter

def get_column_number(sheet, target_title):
“”"
获取指定标题所在的列号

Parameters:
    sheet (xlwings.main.Sheet): Excel 工作表对象
    target_title (str): 目标标题

Returns:
    int: 目标标题所在的列号，如果未找到则返回 -1
"""
# 获取第一行的标题列表
titles = sheet.range("1:1").value

# 在标题列表中查找目标标题
if target_title in titles:
    # 返回目标标题所在的列号
    return titles.index(target_title) + 1
else:
    # 如果未找到目标标题，则返回 -1
    return -1

def find_first_non_empty_cell(sheet):
“”"
查找工作表中第一个非空单元格的值

Parameters:
    sheet (xlwings.main.Sheet): Excel 工作表对象

Returns:
    str: 第一个非空单元格的值
"""
first_cell_value = sheet.cells(1, 1).value
if first_cell_value is None:
    sheet.cells(1, 1).value = "^_^"
return sheet.cells(1, 1).value

def find_target_cells(sheet, target_value):
“”"
在工作表中查找指定值的单元格的位置

Parameters:
    sheet (xlwings.main.Sheet): Excel 工作表对象
    target_value (str): 要查找的目标值

Returns:
    list: 包含目标单元格位置的列表，每个位置由行列索引组成
"""
used_range = sheet.used_range
values = used_range.value
target_cells = []
for row_index, row in enumerate(values):
    for col_index, cell_value in enumerate(row):
        if cell_value == target_value:
            target_cells.append((row_index + 1, col_index + 1))
return target_cells

def set_cell_and_next_three_red(sheet, row, column):
“”"
将指定单元格及其后三个单元格的底色设置为红色

Parameters:
    sheet (xlwings.main.Sheet): Excel 工作表对象
    row (int): 单元格所在行索引
    column (int): 单元格所在列索引
"""
target_range = sheet.cells(row, column)
fourth_cell_value = sheet.cells(row, column + 3).value
if fourth_cell_value != 0:
    target_range.resize(1, 4).color = (255, 0, 0)  # 设置红色底色

def apply_highlight(file_path):
“”"
将指定 Excel 文件中符合条件的单元格标记为红色

Parameters:
    file_path (str): Excel 文件的路径
"""
wb = open_excel_workbook(file_path)
if not wb:
    return

try:
    sheet = wb.sheets.active
    first_cell_value = find_first_non_empty_cell(sheet)
    
    target_value = "原阳"
    target_cells = find_target_cells(sheet, target_value)
    
    if target_cells:
        for row, col in target_cells:
            set_cell_and_next_three_red(sheet, row, col)
    else:
        print("未找到符合条件的单元格。")
except Exception as e:
    print("发生错误:", e)
finally:
    print("标记红色操作已完成!!!")
    close_excel_workbook(wb)

def apply_filter_to_workbook(file_path):
“”"
对指定 Excel 文件中的工作表应用筛选器

Parameters:
    file_path (str): Excel 文件的路径
"""
wb = open_excel_workbook(file_path)
search_string = "原阳"

if not wb:
    return

sheets_to_keep = []
for sheet in wb.sheets:
    target_title = "运维单位名称"
    search_column = get_column_number(sheet, target_title)
    if search_column == -1:
        sheets_to_keep.append(sheet)
        continue
    column_letter = get_column_letter(search_column)
    rng = sheet.range(column_letter + '2').expand('table')
    for cell in rng:
        if cell.value is not None and search_string in str(cell.value):
            sheets_to_keep.append(sheet)
            break

for sheet in reversed(wb.sheets):
    if sheet not in sheets_to_keep and sheet != wb.sheets[0]:
        sheet.delete()

if sheets_to_keep:
    for sheet in sheets_to_keep[1:]:
        sheet.api.AutoFilterMode = False

        # 应用新的筛选条件
        sheet.used_range.api.AutoFilter(Field=search_column, Criteria1="=*{}*".format(search_string))

print("数据筛选操作结束!!!")
close_excel_workbook(wb)        

if name == “main”:
# 解析命令行参数
parser = argparse.ArgumentParser(description=‘将筛选器应用到Excel工作簿’)

# 添加命令行参数
parser.add_argument('file_path', type=str, help='Excel 文件的路径。')

# 获取命令行参数
args = parser.parse_args()

# 应用标红到工作簿的第一个工作表
apply_highlight(args.file_path)

# 应用筛选条件到工作簿
apply_filter_to_workbook(args.file_path)