使用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()