【使用python搜索Excel表，查找内容 -窗口显示版本】

使用python搜索Excel表，查找内容 -窗口显示版本

# -*- coding: utf-8 -*-
# 编辑人:  z
# 编辑时间: 2022/10/8 18:39
import PySimpleGUI
import PySimpleGUI as sg
import xlrd
import os

careful_text = sg.Text('注意事项:   \n 1: 搜索的内容存在很多时，程序运行很慢，耐性等待就好，不要关闭 \n 2: 查找时不要开着文件夹下任意一个表,'
                       '会导致程序运行失败 ', font=("微软雅黑", 10), text_color='yellow')
keyword_text = sg.Text('关键字:', font=("微软雅黑", 11))
keyword_input = sg.Input(key='_DATA_')
query_button = sg.Button('查询', size=6, bind_return_key=True)
eliminate_button = sg.Button('清除内容', size=6)
progress_text = sg.Text('查询结果:')
sg.theme('LightGrey2')
layout = [
    [careful_text],
    [sg.Text('请选择文件夹:', size=(30, 1))],
    [sg.Input('  ', key='_FOLDER_', readonly=True,
              size=(36, 1)),
     sg.FolderBrowse(button_text='选择文件夹', size=(10, 1))],
    [keyword_text], [keyword_input],
    [query_button], [eliminate_button],
    [progress_text],
    [sg.Output(size=(80,30), font=("微软雅黑", 11), background_color='light gray')]
]


def walk_all_excel(path):
    for root, dirs, fs in os.walk(path):
        for f in fs:
            if f.endswith('xlsx') or f.endswith('xls'):
                yield os.path.join(root, f)


def check_excel(f_path, target_strs, fileArr):
    # 打开excel文件
    workbook = xlrd.open_workbook(f_path)

    # excel的sheet数组

    sheet_names = workbook.sheet_names()
    for sheet_name in sheet_names:
        sheet = workbook.sheet_by_name(sheet_name)
        # 行数
        nrows = sheet.nrows
        for i in range(nrows):
            # 一行中的所有单元格
            blocks = sheet.row_values(i)
            col = 0
            for block in blocks:
                # block就是一行中的一个个单元格
                # if isinstance(block, str):
                block_1 = str(block)
                for target_str in target_strs:
                    if target_str in block_1:
                        fileArr.append(f_path + ":   " + sheet_name + "\n")
                        return

                col = col + 1


window = sg.Window('查询小工具', layout)

while True:
    event, value = window.read()

    if event == sg.WIN_CLOSED:
        break
    if event == '查询':
        a: int = 0
        address = value['_FOLDER_']
        keyword = value['_DATA_']
        path_check = address
        res: str = keyword
        target_strs = res.split(",")
        # 定义一个列表
        fileArr = []
        for f in walk_all_excel(path_check):
            check_excel(f, target_strs, fileArr)

        for file in fileArr:
            a += 1
            print(file)
        else:
            print('总共', a, '个')
    if event == '清除内容':
        sg.popup('没做，不会做')

window.close()