excel表内关键字查改删工具

dl_hou

我遇到的工作场景是一个大表里面有很多关键字，需要把这些关键字检索出来，编辑或者删除。可以使用这个工具

https://wwzk.lanzouo.com/iqq4a2r822kh
密码:abb7

[Asm] 纯文本查看 复制代码

?

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559

import tkinter as tk from tkinter import ttk, filedialog, messagebox import pandas as pd import openpyxl   class ExcelProcessor:     def __init__(self, root):         self.root = root         self.root.title('Excel表内关键字查修改删V1.6 by来了老弟')         self.df = None         self.file_path = None         # 设置默认窗口大小         window_width = 1200         window_height = 900         screen_width = root.winfo_screenwidth()         screen_height = root.winfo_screenheight()         x = (screen_width - window_width) // 2         y = (screen_height - window_height) // 2         root.geometry(f"{window_width}x{window_height}+{x}+{y}")         # 设置窗口最小尺寸         root.minsize(1000, 800)         self.setup_ui()       def setup_ui(self):         # 设置主窗口样式         style = ttk.Style()         style.configure('TLabelframe', padding=8)         style.configure('TButton', padding=5)         style.configure('TFrame', padding=3)         style.configure('TLabel', padding=2)         style.configure('TEntry', padding=2)                   # 设置窗口图标         try:             # 获取图标文件路径             import sys             if getattr(sys, 'frozen', False):                 # 打包后的路径                 base_path = sys._MEIPASS             else:                 # 开发环境路径                 base_path = os.path.dirname(os.path.abspath(__file__))                           icon_path = os.path.join(base_path, 'logo.ico')             self.root.iconbitmap(icon_path)         except:             pass           # 文件选择区域         file_frame = ttk.LabelFrame(self.root, text='文件操作')         file_frame.pack(fill='x', padx=8, pady=(8,4))           btn_container = ttk.Frame(file_frame)         btn_container.pack(pady=8)           self.file_btn = ttk.Button(btn_container, text='选择Excel文件', command=self.open_file, width=15)         self.file_btn.pack(side='left', padx=8)           self.save_btn = ttk.Button(btn_container, text='保存更改', command=self.save_file, state='disabled', width=15)         self.save_btn.pack(side='left', padx=8)           # 搜索条件区域         search_frame = ttk.LabelFrame(self.root, text='搜索条件')         search_frame.pack(fill='x', padx=8, pady=6)           self.search_entries = []         self.search_combos = []         self.search_frames = []           search_controls = ttk.Frame(search_frame)         search_controls.pack(fill='x', pady=8)           # 默认添加两个搜索条件         for _ in range(2):             self.add_search_condition()           control_frame = ttk.Frame(search_frame)         control_frame.pack(fill='x', pady=8)           self.add_condition_btn = ttk.Button(control_frame, text='添加条件', command=self.add_search_condition, width=12)         self.add_condition_btn.pack(side='left', padx=8)           self.search_btn = ttk.Button(control_frame, text='搜索', command=self.search_data, state='disabled', width=12)         self.search_btn.pack(side='left', padx=8)           # 数据显示区域         data_frame = ttk.LabelFrame(self.root, text='数据显示')         data_frame.pack(fill='both', expand=True, padx=8, pady=6)           # 创建表格容器         table_container = ttk.Frame(data_frame)         table_container.pack(fill='both', expand=True, padx=8, pady=8)           # 创建表格         self.tree = ttk.Treeview(table_container)         self.tree.pack(fill='both', expand=True, side='left')           # 添加垂直滚动条         y_scrollbar = ttk.Scrollbar(table_container, orient='vertical', command=self.tree.yview)         y_scrollbar.pack(fill='y', side='right')           # 添加水平滚动条         x_scrollbar = ttk.Scrollbar(table_container, orient='horizontal', command=self.tree.xview)         x_scrollbar.pack(fill='x', side='bottom')           self.tree.configure(yscrollcommand=y_scrollbar.set, xscrollcommand=x_scrollbar.set)           # 操作按钮区域         # 操作按钮区域         btn_frame = ttk.LabelFrame(self.root, text='操作')         btn_frame.pack(fill='x', padx=8, pady=(6,8))                   btn_container = ttk.Frame(btn_frame)         btn_container.pack(fill='x', pady=8)                   self.edit_btn = ttk.Button(btn_container, text='编辑选中行', command=self.edit_row, state='disabled', width=12)         self.edit_btn.pack(side='left', padx=8)                   self.delete_btn = ttk.Button(btn_container, text='删除选中行', command=self.delete_row, state='disabled', width=12)         self.delete_btn.pack(side='left', padx=8)                   # 添加帮助和赏赞按钮         ttk.Separator(btn_container, orient='vertical').pack(side='left', padx=10, fill='y')                   help_btn = ttk.Button(btn_container, text='帮助', command=self.show_help, width=8)         help_btn.pack(side='left', padx=8)                   reward_btn = ttk.Button(btn_container, text='赏赞', command=self.show_reward, width=8)         reward_btn.pack(side='left', padx=8)       def open_file(self):         file_path = filedialog.askopenfilename(filetypes=[             ('Excel文件', '*.xlsx;*.xls')         ])         if file_path:             try:                 # 禁用按钮，显示加载提示                 self.file_btn['state'] = 'disabled'                 self.root.config(cursor='wait')                 self.root.update()                   self.file_path = file_path                 self.df = pd.read_excel(file_path)                 self.update_table()                 self.update_search_fields()                 self.enable_buttons()                   # 恢复按钮状态和光标                 self.file_btn['state'] = 'normal'                 self.root.config(cursor='')                 messagebox.showinfo('成功', '文件加载成功！')             except Exception as e:                 self.file_btn['state'] = 'normal'                 self.root.config(cursor='')                 messagebox.showerror('错误', f'文件加载失败：{str(e)}')       def update_table(self):         # 清空现有数据         for item in self.tree.get_children():             self.tree.delete(item)           # 设置列         self.tree['columns'] = list(self.df.columns)         self.tree['show'] = 'headings'           # 计算每列的最大宽度         max_widths = {}         for column in self.df.columns:             # 计算列标题的宽度             header_width = len(str(column)) * 10             # 计算数据的最大宽度             max_data_width = self.df[column].astype(str).str.len().max() * 10             max_widths[column] = min(max(header_width, max_data_width, 100), 300)           for column in self.df.columns:             self.tree.heading(column, text=column,                 command=lambda col=column: self.sort_treeview(col))             self.tree.column(column, width=max_widths[column], minwidth=50)           # 添加数据         for idx, row in self.df.iterrows():             self.tree.insert('', 'end', values=list(row))       def sort_treeview(self, col):         """对表格按指定列排序"""         if not hasattr(self, 'sort_reverse'):             self.sort_reverse = {}         self.sort_reverse[col] = not self.sort_reverse.get(col, False)           # 获取所有数据         data = [(self.tree.set(child, col), child) for child in self.tree.get_children('')]                   # 排序         data.sort(reverse=self.sort_reverse[col])                   # 重新插入数据         for idx, item in enumerate(data):             self.tree.move(item[1], '', idx)       def update_search_fields(self):         columns = list(self.df.columns)         for combo in self.search_combos:             combo['values'] = columns             if columns:                 combo.set(columns[0])       def enable_buttons(self):         self.save_btn['state'] = 'normal'         self.search_btn['state'] = 'normal'         self.edit_btn['state'] = 'normal'         self.delete_btn['state'] = 'normal'       def search_data(self):         if self.df is None:             return           try:             # 禁用搜索按钮并显示等待光标             self.search_btn['state'] = 'disabled'             self.root.config(cursor='wait')             self.root.update()               filtered_df = self.df.copy()             for combo, entry in zip(self.search_combos, self.search_entries):                 if combo.get() and entry.get():                     column = combo.get()                     # 将输入值按全角和半角逗号分割成多个关键字                     keywords = [kw.strip() for kw in entry.get().replace('，', ',').split(',')]                     # 使用正则表达式构建'或'的检索条件                     pattern = '|'.join(keywords)                     filtered_df = filtered_df[filtered_df[column].astype(str).str.contains(pattern, case=False, na=False, regex=True)]               # 更新显示             self.update_table_with_df(filtered_df)               if len(filtered_df) == 0:                 messagebox.showinfo('提示', '未找到匹配的数据')         except Exception as e:             messagebox.showerror('错误', f'搜索失败：{str(e)}')         finally:             # 恢复搜索按钮状态和光标             self.search_btn['state'] = 'normal'             self.root.config(cursor='')             self.root.update()       def update_table_with_df(self, df):         for item in self.tree.get_children():             self.tree.delete(item)         for idx, row in df.iterrows():             self.tree.insert('', 'end', values=list(row))       def edit_row(self):         selected_items = self.tree.selection()         if not selected_items:             messagebox.showwarning('警告', '请先选择要编辑的行！')             return           # 获取选中行的数据         item = selected_items[0]         values = self.tree.item(item)['values']                   # 创建编辑窗口         edit_window = tk.Toplevel(self.root)         edit_window.title('编辑数据')         edit_window.transient(self.root)  # 设置为主窗口的子窗口         edit_window.grab_set()  # 模态窗口                   # 创建滚动区域         canvas = tk.Canvas(edit_window)         scrollbar = ttk.Scrollbar(edit_window, orient='vertical', command=canvas.yview)         scrollable_frame = ttk.Frame(canvas)           scrollable_frame.bind(             '<Configure>',             lambda e: canvas.configure(scrollregion=canvas.bbox('all'))         )           canvas.create_window((0, 0), window=scrollable_frame, anchor='nw')         canvas.configure(yscrollcommand=scrollbar.set)                   entries = []         for i, (column, value) in enumerate(zip(self.df.columns, values)):             frame = ttk.Frame(scrollable_frame)             frame.pack(fill='x', padx=10, pady=3)             ttk.Label(frame, text=f'{column}:', width=15).pack(side='left')             entry = ttk.Entry(frame)             entry.insert(0, str(value))             entry.pack(side='left', fill='x', expand=True, padx=5)             entries.append(entry)           # 按钮区域         btn_frame = ttk.Frame(edit_window)         btn_frame.pack(fill='x', padx=10, pady=10)           def save_changes():             try:                 edit_window.config(cursor='wait')                 edit_window.update()                                   new_values = [entry.get() for entry in entries]                 # 更新DataFrame                 idx = self.tree.index(item)                 for col, value in zip(self.df.columns, new_values):                     self.df.iloc[idx, self.df.columns.get_loc(col)] = value                 # 更新树形视图                 self.tree.item(item, values=new_values)                 edit_window.destroy()                 messagebox.showinfo('成功', '数据更新成功！')             except Exception as e:                 edit_window.config(cursor='')                 messagebox.showerror('错误', f'保存失败：{str(e)}')           def cancel_edit():             edit_window.destroy()           ttk.Button(btn_frame, text='保存', command=save_changes).pack(side='left', padx=5)         ttk.Button(btn_frame, text='取消', command=cancel_edit).pack(side='left')           # 设置滚动区域布局         canvas.pack(side='left', fill='both', expand=True, padx=5, pady=5)         scrollbar.pack(side='right', fill='y')           # 设置窗口大小和位置         edit_window.update()         window_width = min(edit_window.winfo_reqwidth() + 30, 800)         window_height = min(edit_window.winfo_reqheight() + 30, 600)         screen_width = edit_window.winfo_screenwidth()         screen_height = edit_window.winfo_screenheight()         x = (screen_width - window_width) // 2         y = (screen_height - window_height) // 2         edit_window.geometry(f'{window_width}x{window_height}+{x}+{y}')       def delete_row(self):         selected_items = self.tree.selection()         if not selected_items:             messagebox.showwarning('警告', '请先选择要删除的行！')             return           if messagebox.askyesno('确认', '确定要删除选中的行吗？'):             for item in selected_items:                 idx = self.tree.index(item)                 self.df.drop(self.df.index[idx], inplace=True)                 self.tree.delete(item)             self.df.reset_index(drop=True, inplace=True)             messagebox.showinfo('成功', '数据删除成功！')       def save_file(self):         if self.file_path and self.df is not None:             try:                 self.df.to_excel(self.file_path, index=False)                 messagebox.showinfo('成功', '文件保存成功！')             except Exception as e:                 messagebox.showerror('错误', f'文件保存失败：{str(e)}')       def add_search_condition(self):         frame = ttk.Frame(self.root.children['!labelframe2'].children['!frame'])         frame.pack(fill='x', pady=3)                   condition_num = len(self.search_frames) + 1         ttk.Label(frame, text=f'条件{condition_num}:', width=8).pack(side='left')                   combo = ttk.Combobox(frame, state='readonly', width=20)         combo.pack(side='left', padx=(0,5))                   entry = ttk.Entry(frame)         entry.pack(side='left', padx=5, fill='x', expand=True)         # 添加提示标签         ttk.Label(frame, text='(可用逗号分隔多个关键字)', foreground='gray').pack(side='left', padx=5)                   # 添加删除按钮         def remove_condition():             frame.destroy()             self.search_frames.remove(frame)             self.search_combos.remove(combo)             self.search_entries.remove(entry)             # 更新剩余条件的编号             for i, f in enumerate(self.search_frames, 1):                 f.children['!label']['text'] = f'条件{i}:'                   if len(self.search_frames) > 1:  # 只有当有多个条件时才显示删除按钮             delete_btn = ttk.Button(frame, text='×', width=3, command=remove_condition)             delete_btn.pack(side='left', padx=(5,0))                   self.search_frames.append(frame)         self.search_combos.append(combo)         self.search_entries.append(entry)                   if self.df is not None:             combo['values'] = list(self.df.columns)             if combo['values']:                 combo.set(combo['values'][0])       def show_help(self):         help_window = tk.Toplevel(self.root)         help_window.title('使用说明')         help_window.transient(self.root)         help_window.grab_set()           # 创建滚动区域         canvas = tk.Canvas(help_window)         scrollbar = ttk.Scrollbar(help_window, orient='vertical', command=canvas.yview)         scrollable_frame = ttk.Frame(canvas)           scrollable_frame.bind(             '<Configure>',             lambda e: canvas.configure(scrollregion=canvas.bbox('all'))         )           canvas.create_window((0, 0), window=scrollable_frame, anchor='nw')         canvas.configure(yscrollcommand=scrollbar.set)           # 读取README.md文件内容         try:             # 获取资源文件路径             import sys             if getattr(sys, 'frozen', False):                 # 打包后的路径                 base_path = sys._MEIPASS             else:                 # 开发环境路径                 base_path = os.path.dirname(os.path.abspath(__file__))                           readme_path = os.path.join(base_path, 'README.md')             with open(readme_path, 'r', encoding='utf-8') as f:                 help_text = f.read()             # 仅显示使用说明部分             start = help_text.find('## 使用说明')             if start != -1:                 help_text = help_text[start:]         except Exception as e:             help_text = f'无法加载帮助文档：{str(e)}'           # 显示帮助文本         text_widget = tk.Text(scrollable_frame, wrap='word', width=60, height=20)         text_widget.insert('1.0', help_text)         text_widget.configure(state='disabled')         text_widget.pack(padx=10, pady=10)           # 设置滚动区域布局         canvas.pack(side='left', fill='both', expand=True)         scrollbar.pack(side='right', fill='y')           # 设置窗口大小和位置         help_window.update()         window_width = min(help_window.winfo_reqwidth() + 30, 800)         window_height = min(help_window.winfo_reqheight() + 30, 600)         screen_width = help_window.winfo_screenwidth()         screen_height = help_window.winfo_screenheight()         x = (screen_width - window_width) // 2         y = (screen_height - window_height) // 2         help_window.geometry(f'{window_width}x{window_height}+{x}+{y}')       def show_reward(self):         reward_window = tk.Toplevel(self.root)         reward_window.title('赏赞支持')         reward_window.transient(self.root)         reward_window.grab_set()           # 创建容器框架         container = ttk.Frame(reward_window)         container.pack(padx=20, pady=20)           # 显示感谢文字         thank_label = ttk.Label(container, text='您的慷慨是对作者最大的支持', font=('微软雅黑', 12))         thank_label.pack(pady=(0, 10))           # 显示赏赞二维码         try:             # 检查PIL模块是否可用             try:                 from PIL import Image, ImageTk             except ImportError:                 raise ImportError('缺少必要的图片处理模块。请运行 pip install Pillow 安装所需模块。')               import os             # 获取资源文件路径             import sys             if getattr(sys, 'frozen', False):                 # 打包后的路径                 base_path = sys._MEIPASS             else:                 # 开发环境路径                 base_path = os.path.dirname(os.path.abspath(__file__))                           image_path = os.path.join(base_path, 'reward.jpg')                           # 添加加载提示             loading_label = ttk.Label(container, text='正在加载赏赞二维码...')             loading_label.pack(pady=5)             reward_window.update()                           if not os.path.exists(image_path):                 loading_label.destroy()                 raise FileNotFoundError('赏赞二维码图片文件(reward.jpg)不存在，请确保该文件在程序目录中。')                           try:                 # 加载并处理图片                 image = Image.open(image_path)                 # 调整图片大小，保持纵横比                 width, height = image.size                 max_size = 300                 ratio = min(max_size/width, max_size/height)                 new_size = (int(width*ratio), int(height*ratio))                 image = image.resize(new_size, Image.Resampling.LANCZOS)                                   # 创建图片标签并显示                 photo = ImageTk.PhotoImage(image)                 image_label = ttk.Label(container, image=photo)                 image_label.image = photo  # 保持引用                                   # 移除加载提示并显示图片                 loading_label.destroy()                 image_label.pack(pady=10)                                   # 添加成功提示                 ttk.Label(container, text='赏赞二维码加载成功', foreground='green').pack(pady=5)                   # 设置固定窗口大小                 reward_window.update()                 window_width = max(400, image_label.winfo_reqwidth() + 40)                 window_height = image_label.winfo_reqheight() + thank_label.winfo_reqheight() + 80                 reward_window.geometry(f'{window_width}x{window_height}')             except Exception as img_error:                 loading_label.destroy()                 error_msg = f'图片加载失败：{str(img_error)}'                 ttk.Label(container, text=error_msg, foreground='red', wraplength=300).pack(pady=10)                 window_width = 400                 window_height = 200                 reward_window.geometry(f'{window_width}x{window_height}')         except ImportError as e:             error_msg = str(e)             error_label = ttk.Label(container, text=error_msg, foreground='red', wraplength=300)             error_label.pack(pady=10)             window_width = 400             window_height = 200             reward_window.geometry(f'{window_width}x{window_height}')         except Exception as e:             error_msg = f'无法加载赏赞二维码：{str(e)}'             error_label = ttk.Label(container, text=error_msg, foreground='red', wraplength=300)             error_label.pack(pady=10)             window_width = 400             window_height = 200             reward_window.geometry(f'{window_width}x{window_height}')           # 设置窗口位置         screen_width = reward_window.winfo_screenwidth()         screen_height = reward_window.winfo_screenheight()         x = (screen_width - window_width) // 2         y = (screen_height - window_height) // 2         reward_window.geometry(f'+{x}+{y}')   def main():     root = tk.Tk()     app = ExcelProcessor(root)     root.geometry('800x600')     root.mainloop()   if __name__ == '__main__':     main()

yyss007

感谢楼主分享的技术，这个在实务工作中，非常有用！
我记得在使用EXCEL之初，我们选择表里的相关共性单元格，多数情况下，使用的是Sumif，现在有了这个技术，那可能更加迅捷。

mingaiki

现在用wps的正则函数直接就可以多关键词替换了

waishr

感谢分享

paomianli

好东西啊支持支持

Apple666666

感谢分享，谢谢～~

zhuimeng34

很好的软件感谢分享

zxcwcxzb

感谢分享，谢谢～

suncxyz

感谢分享!

gxts1

谢谢楼主分享，已收藏

caixf88

感谢分享，谢谢