基于python的Excel读取和可视化图表工具

import tkinter as tk from tkinter import ttk, filedialog, messagebox, simpledialog import openpyxl from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill import os import re from datetime import datetime import matplotlib.pyplot as plt from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg import numpy as np import matplotlib.font_manager as fm class ExcelGUI: def __init__(self, master): self.master = master self.master.title("Excel操作工具") self.master.geometry("1000x600") self.master.config(bg="#f0f0f0") # 设置字体 self.font = ("微软雅黑", 12) self.title_font = ("微软雅黑", 14, "bold") # 创建主框架 self.main_frame = tk.Frame(self.master, bg="#f0f0f0") self.main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10) # 创建菜单栏 self.create_menu() # 创建文件操作区域 self.create_file_frame() # 创建数据操作区域 self.create_data_frame() # 创建数据展示区域 self.create_display_frame() # 初始化变量 self.workbook = None self.sheet = None self.file_path = None # 设置中文字体 plt.rcParams['font.sans-serif'] = ['SimHei'] # 设置中文字体为黑体 plt.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题 def create_menu(self): """创建菜单栏""" menubar = tk.Menu(self.master) self.master.config(menu=menubar) # 文件菜单 file_menu = tk.Menu(menubar, tearoff=0) menubar.add_cascade(label="文件", menu=file_menu) file_menu.add_command(label="打开", command=self.open_file) file_menu.add_command(label="保存", command=self.save_file) file_menu.add_separator() file_menu.add_command(label="退出", command=self.master.quit) # 编辑菜单 edit_menu = tk.Menu(menubar, tearoff=0) menubar.add_cascade(label="编辑", menu=edit_menu) edit_menu.add_command(label="排序", command=self.sort_data) edit_menu.add_command(label="筛选", command=self.filter_data) edit_menu.add_command(label="统计", command=self.analyze_data) # 视图菜单 view_menu = tk.Menu(menubar, tearoff=0) menubar.add_cascade(label="视图", menu=view_menu) view_menu.add_command(label="图表", command=self.show_chart) # 帮助菜单 help_menu = tk.Menu(menubar, tearoff=0) menubar.add_cascade(label="帮助", menu=help_menu) help_menu.add_command(label="关于", command=self.show_about) def create_file_frame(self): """创建文件操作区域""" file_frame = tk.LabelFrame(self.main_frame, text="文件操作", font=self.title_font, bg="#f0f0f0") file_frame.pack(fill=tk.X, padx=5, pady=5) # 文件路径输入 path_label = tk.Label(file_frame, text="文件路径:", font=self.font, bg="#f0f0f0") path_label.grid(row=0, column=0, padx=5, pady=5, sticky="e") self.path_entry = tk.Entry(file_frame, width=50, font=self.font) self.path_entry.grid(row=0, column=1, padx=5, pady=5) # 浏览按钮 browse_button = tk.Button(file_frame, text="浏览", command=self.browse_file, font=self.font) browse_button.grid(row=0, column=2, padx=5, pady=5) # 打开按钮 open_button = tk.Button(file_frame, text="打开", command=self.open_file, font=self.font) open_button.grid(row=0, column=3, padx=5, pady=5) # 保存按钮 save_button = tk.Button(file_frame, text="保存", command=self.save_file, font=self.font) save_button.grid(row=0, column=4, padx=5, pady=5) def create_data_frame(self): """创建数据操作区域""" data_frame = tk.LabelFrame(self.main_frame, text="数据操作", font=self.title_font, bg="#f0f0f0") data_frame.pack(fill=tk.X, padx=5, pady=5) # 选择工作表 sheet_label = tk.Label(data_frame, text="选择工作表:", font=self.font, bg="#f0f0f0") sheet_label.grid(row=0, column=0, padx=5, pady=5, sticky="e") self.sheet_combobox = ttk.Combobox(data_frame, font=self.font) self.sheet_combobox.grid(row=0, column=1, padx=5, pady=5) self.sheet_combobox.bind("<<ComboboxSelected>>", self.on_sheet_selected) # 排序 sort_label = tk.Label(data_frame, text="排序列:", font=self.font, bg="#f0f0f0") sort_label.grid(row=1, column=0, padx=5, pady=5, sticky="e") self.sort_entry = tk.Entry(data_frame, width=10, font=self.font) self.sort_entry.grid(row=1, column=1, padx=5, pady=5) sort_button = tk.Button(data_frame, text="排序", command=self.sort_data, font=self.font) sort_button.grid(row=1, column=2, padx=5, pady=5) # 筛选 filter_label = tk.Label(data_frame, text="筛选条件:", font=self.font, bg="#f0f0f0") filter_label.grid(row=2, column=0, padx=5, pady=5, sticky="e") self.filter_entry = tk.Entry(data_frame, width=20, font=self.font) self.filter_entry.grid(row=2, column=1, padx=5, pady=5) filter_button = tk.Button(data_frame, text="筛选", command=self.filter_data, font=self.font) filter_button.grid(row=2, column=2, padx=5, pady=5) # 统计 analyze_button = tk.Button(data_frame, text="数据统计", command=self.analyze_data, font=self.font) analyze_button.grid(row=3, column=1, padx=5, pady=5) def create_display_frame(self): """创建数据展示区域""" display_frame = tk.LabelFrame(self.main_frame, text="数据展示", font=self.title_font, bg="#f0f0f0") display_frame.pack(fill=tk.BOTH, expand=True, padx=5, pady=5) # 创建表格 self.tree = ttk.Treeview(display_frame, show="headings") self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True) # 添加垂直滚动条 vsb = ttk.Scrollbar(display_frame, orient="vertical", command=self.tree.yview) vsb.pack(side=tk.RIGHT, fill=tk.Y) self.tree.configure(yscrollcommand=vsb.set) # 添加水平滚动条 hsb = ttk.Scrollbar(display_frame, orient="horizontal", command=self.tree.xview) hsb.pack(side=tk.BOTTOM, fill=tk.X) self.tree.configure(xscrollcommand=hsb.set) def browse_file(self): """浏览文件""" file_path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx"), ("All Files", "*.*")]) if file_path: self.path_entry.delete(0, tk.END) self.path_entry.insert(0, file_path) def open_file(self): """打开Excel文件""" file_path = self.path_entry.get() if not file_path: messagebox.showerror("错误", "请选择一个Excel文件") return try: self.workbook = openpyxl.load_workbook(file_path) self.file_path = file_path self.update_sheet_list() messagebox.showinfo("成功", "文件已成功打开") except Exception as e: messagebox.showerror("错误", f"无法打开文件: {str(e)}") def save_file(self): """保存Excel文件""" if not self.workbook: messagebox.showerror("错误", "没有打开的文件") return try: self.workbook.save(self.file_path) messagebox.showinfo("成功", "文件已成功保存") except Exception as e: messagebox.showerror("错误", f"无法保存文件: {str(e)}") def update_sheet_list(self): """更新工作表列表"""