Python 写的智慧记进销存辅助程序导入导出 excel 可打印

图样：

就可以导入了

上代码

import tkinter as tk
from tkinter import ttk
import sqlite3
from datetime import datetime
from tkinter import messagebox, filedialog
import pandas as pd
import reclass OrderSystem:def __init__(self, root):self.root = rootself.root.title("订单记录系统")# 创建数据库连接self.conn = sqlite3.connect('orders.db')self.create_table()# 创建界面self.create_ui()# 添加搜索框架self.create_search_frame()# 添加更多功能按钮self.add_function_buttons()# 加载所有订单数据self.load_all_orders()# 设置默认值self.set_default_values()def create_table(self):cursor = self.conn.cursor()cursor.execute('''CREATE TABLE IF NOT EXISTS orders (order_date TEXT,order_number TEXT,customer TEXT,product TEXT,unit TEXT,quantity REAL,price REAL,discount REAL,final_price REAL,total REAL,remarks TEXT,discount_amount REAL,discount_total REAL,delivery TEXT,payment_received REAL,end_customer TEXT,notes TEXT,business TEXT)''')self.conn.commit()def create_ui(self):# 创建主框架来容纳左右两部分main_frame = ttk.Frame(self.root)main_frame.pack(fill="both", expand=True)# 创建左侧框架left_frame = ttk.Frame(main_frame)left_frame.pack(side="left", fill="both", expand=True)# 创建右侧框架right_frame = ttk.Frame(main_frame)right_frame.pack(side="right", fill="y", padx=5)# 创建输入框架（放在左侧）input_frame = ttk.LabelFrame(left_frame, text="订单信息")input_frame.pack(padx=5, pady=5, fill="x")# 修改订单分类框架order_group_frame = ttk.LabelFrame(right_frame, text="订单查询")order_group_frame.pack(padx=5, pady=5, fill="both", expand=True)# 添加筛选框filter_frame = ttk.Frame(order_group_frame)filter_frame.pack(fill="x", padx=5, pady=5)# 单据���号筛选ttk.Label(filter_frame, text="单据编号:").grid(row=0, column=0, padx=5)self.order_number_filter = ttk.Combobox(filter_frame, width=15)self.order_number_filter.grid(row=0, column=1, padx=5)# 客户名称筛选ttk.Label(filter_frame, text="客户名称:").grid(row=0, column=2, padx=5)self.customer_filter = ttk.Combobox(filter_frame, width=15)self.customer_filter.grid(row=0, column=3, padx=5)# 筛选按钮ttk.Button(filter_frame, text="筛选", command=self.filter_orders).grid(row=0, column=4, padx=5)ttk.Button(filter_frame, text="重置", command=self.reset_filter).grid(row=0, column=5, padx=5)ttk.Button(filter_frame, text="打印", command=self.print_filtered_data).grid(row=0, column=6, padx=5)# 绑定下拉框事件self.order_number_filter.bind('<KeyRelease>', self.update_order_number_list)self.customer_filter.bind('<KeyRelease>', self.update_customer_list)# 修改订单分类的树形视图列self.group_tree = ttk.Treeview(order_group_frame, columns=["order_number", "customer", "product", "unit", "quantity", "price", "total", "remarks"], show="headings", height=15)# 设置列标题和宽度columns = [("order_number", "单据编号", 100),("customer", "客户名称", 100),("product", "品名规格", 120),("unit", "单位", 50),("quantity", "数量", 60),("price", "原价", 80),("total", "金额", 80),("remarks", "备注", 100)]for col, heading, width in columns:self.group_tree.heading(col, text=heading)self.group_tree.column(col, width=width)self.group_tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加滚动条group_scrollbar = ttk.Scrollbar(order_group_frame, orient="vertical", command=self.group_tree.yview)group_scrollbar.pack(side="right", fill="y")self.group_tree.configure(yscrollcommand=group_scrollbar.set)# 绑定点击事件self.group_tree.bind('<<TreeviewSelect>>', self.on_group_select)# 修改输入字段列表，确保与数据库字段完全匹配self.entries = {}fields = [("order_date", "单据日期"), ("order_number", "单据编号"),("customer", "客户名称"), ("product", "品名规格"),("unit", "单位"),("quantity", "数量"),("price", "原价"), ("discount", "单行折扣率(%)"),("final_price", "折后价"), ("total", "金额"),("remarks", "备注"), ("discount_amount", "整单折扣率(%)"),("discount_total", "折后金额"), ("delivery", "运费"),("payment_received", "本单已收"), ("end_customer", "结算账户"),("notes", "说明"), ("business", "营业员")]for row, (field, label) in enumerate(fields):ttk.Label(input_frame, text=label).grid(row=row//2, column=(row%2)*2, padx=5, pady=2)self.entries[field] = ttk.Entry(input_frame)self.entries[field].grid(row=row//2, column=(row%2)*2+1, padx=5, pady=2, sticky="ew")# 添加按钮self.btn_frame = ttk.Frame(self.root)self.btn_frame.pack(pady=5)ttk.Button(self.btn_frame, text="保存", command=self.save_order).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="清空", command=self.clear_fields).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导入Excel", command=self.import_from_excel).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导出模板", command=self.export_template).pack(side="left", padx=5)# 修改表格显示，显示所有列self.tree = ttk.Treeview(self.root, columns=["order_date", "order_number", "customer", "product", "unit","quantity", "price", "discount", "final_price", "total","remarks", "discount_amount", "discount_total", "delivery","payment_received", "end_customer", "notes", "business"], show="headings")# 修改列标题定义，显示所有列columns = [("order_date", "单据日期"), ("order_number", "单据编号"),("customer", "客户名称"), ("product", "品名规格"),("unit", "单位"),("quantity", "数量"),("price", "原价"), ("discount", "单行折扣率(%)"),("final_price", "折后价"), ("total", "金额"),("remarks", "备注"),("discount_amount", "整单折扣率(%)"),("discount_total", "折后金额"),("delivery", "运费"),("payment_received", "本单已收"),("end_customer", "结算账户"),("notes", "说明"),("business", "营业员")]for col, heading in columns:self.tree.heading(col, text=heading)self.tree.column(col, width=100)self.tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加滚动条scrollbar = ttk.Scrollbar(self.root, orient="vertical", command=self.tree.yview)scrollbar.pack(side="right", fill="y")self.tree.configure(yscrollcommand=scrollbar.set)# 添加自动计算绑定self.entries['quantity'].bind('<KeyRelease>', self.calculate_total)self.entries['price'].bind('<KeyRelease>', self.calculate_total)self.entries['discount'].bind('<KeyRelease>', self.calculate_total)# 在订单分类框架底部添加合计标签self.total_label = ttk.Label(order_group_frame, text="合计金额: ¥0.00")self.total_label.pack(pady=5)def calculate_total(self, event=None):"""计算折后价和金额"""try:quantity = float(self.entries['quantity'].get() or 0)price = float(self.entries['price'].get() or 0)discount = float(self.entries['discount'].get() or 100)# 计算折后价final_price = price * discount / 100self.entries['final_price'].delete(0, tk.END)self.entries['final_price'].insert(0, f"{final_price:.2f}")# 计算金额total = quantity * final_priceself.entries['total'].delete(0, tk.END)self.entries['total'].insert(0, f"{total:.2f}")except ValueError:passdef create_search_frame(self):search_frame = ttk.LabelFrame(self.root, text="搜索")search_frame.pack(padx=5, pady=5, fill="x")ttk.Label(search_frame, text="搜索条件:").pack(side="left", padx=5)self.search_entry = ttk.Entry(search_frame)self.search_entry.pack(side="left", padx=5, fill="x", expand=True)ttk.Button(search_frame, text="搜索", command=self.search_orders).pack(side="left", padx=5)def add_function_buttons(self):# 在原有btn_frame中添加更多按钮ttk.Button(self.btn_frame, text="编辑", command=self.edit_selected).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="删除", command=self.delete_selected).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导出Excel", command=self.export_to_excel).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="统计报表", command=self.show_statistics).pack(side="left", padx=5)def validate_data(self):"""数据验证"""errors = []# 验证日期格式date = self.entries['order_date'].get().strip()if not date:errors.append("单据日期不能为空")elif not re.match(r'^\d{4}-\d{2}-\d{2}$', date):errors.append("单据日期格式错误，应为 YYYY-MM-DD")# 验证必填字段required_fields = {'order_number': '单据编号','customer': '客户名称','product': '品名规格','unit': '单位','quantity': '数量','price': '原价'}for field, name in required_fields.items():value = self.entries[field].get().strip()if not value:errors.append(f"{name}不能为空")# 验证数字字段number_fields = {'quantity': '数量','price': '原价','discount': '单行折扣率(%)','final_price': '折后价','total': '金额','discount_amount': '整单折扣率(%)','discount_total': '折后金额','payment_received': '本单已收'}for field, name in number_fields.items():value = self.entries[field].get().strip()if value:  # 如果有值才验证try:num = float(value)if field in ['quantity', 'price'] and num <= 0:errors.append(f"{name}必须大于0")elif num < 0:errors.append(f"{name}不能为负数")except ValueError:errors.append(f"{name}必须是数字")if errors:messagebox.showerror("验证错误", "\n".join(errors))return Falsereturn Truedef save_order(self):"""保存订单数据"""if not self.validate_data():returntry:# 获取所有输入值values = []fields_order = ['order_date', 'order_number', 'customer', 'product', 'unit','quantity', 'price', 'discount', 'final_price', 'total','remarks', 'discount_amount', 'discount_total', 'delivery','payment_received', 'end_customer', 'notes', 'business']for field in fields_order:value = self.entries[field].get().strip()# 对数字字段进行转换if field in ['quantity', 'price', 'discount', 'final_price', 'total', 'discount_amount', 'discount_total', 'payment_received']:try:value = float(value) if value else 0.0except ValueError:value = 0.0elif not value:  # 对非数字字段，如果为空则设为空字符串value = ''values.append(value)# 检查单据编号是否重复cursor = self.conn.cursor()cursor.execute('SELECT COUNT(*) FROM orders WHERE order_number = ?', (values[1],))if cursor.fetchone()[0] > 0:if not messagebox.askyesno("警告", "单据编号已存在是否继续保存？"):return# 插入数据try:cursor.execute('''INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', values)self.conn.commit()# 更新表格显示self.tree.insert("", "end", values=values)# 清空输入框并设置默认值self.set_default_values()# 显示成功消息messagebox.showinfo("成功", "订单保存成功！")except sqlite3.Error as e:self.conn.rollback()messagebox.showerror("数据库错误", f"保存失败：{str(e)}")returnexcept Exception as e:messagebox.showerror("错误", f"保存过程中出错：{str(e)}")returnself.update_order_groups()def clear_fields(self):"""清空所有输入框"""for field in self.entries:self.entries[field].delete(0, tk.END)def __del__(self):self.conn.close()def search_orders(self):search_text = self.search_entry.get().strip()if not search_text:self.load_all_orders()returncursor = self.conn.cursor()cursor.execute('''SELECT * FROM orders WHERE order_date LIKE ? OR order_number LIKE ? OR customer LIKE ? OR product LIKE ?''', [f'%{search_text}%'] * 4)self.tree.delete(*self.tree.get_children())for row in cursor.fetchall():self.tree.insert("", "end", values=row)def edit_selected(self):selected = self.tree.selection()if not selected:messagebox.showwarning("提示", "请先选择一条记录")returnitem = self.tree.item(selected[0])values = item['values']# 填充表单for field, value in zip(self.entries.keys(), values):self.entries[field].delete(0, tk.END)self.entries[field].insert(0, str(value))def delete_selected(self):selected = self.tree.selection()if not selected:messagebox.showwarning("提示", "请先选择一条记录")returnif messagebox.askyesno("确认", "确定要删除中的记录吗？"):item = self.tree.item(selected[0])order_number = item['values'][1]cursor = self.conn.cursor()cursor.execute('DELETE FROM orders WHERE order_number = ?', (order_number,))self.conn.commit()self.tree.delete(selected[0])self.update_order_groups()def export_to_excel(self):"""导出数据到Excel"""try:# 先获取保存路径filename = filedialog.asksaveasfilename(defaultextension=".xlsx",filetypes=[("Excel files", "*.xlsx")])if not filename:return# 获取数据cursor = self.conn.cursor()cursor.execute('SELECT * FROM orders')data = cursor.fetchall()# 准备列名columns = ['单据日期', '单据编号', '客户名称', '品名规格','单位', '数量', '原价', '单行折扣率(%)', '折后价', '金额', '备注', '整单折扣率(%)', '折后金额', '运费','本单已收', '结算账户', '说明', '营业员']# 创建DataFramedf = pd.DataFrame(data, columns=columns)# 直接导出df.to_excel(filename, index=False)messagebox.showinfo("成功", "数据已导出到Excel文件")except PermissionError:messagebox.showerror("错误", "无法保存文件，请确保：\n1. 文件未被其他程序打开\n2. 您有写入权限")except Exception as e:messagebox.showerror("错误", f"导出过程中出错：{str(e)}")def show_statistics(self):stats_window = tk.Toplevel(self.root)stats_window.title("统计报表")cursor = self.conn.cursor()# 客户统计cursor.execute('''SELECT customer, COUNT(*) as order_count,SUM(total) as total_amount,SUM(payment_received) as total_receivedFROM orders GROUP BY customer''')# 创建统计表格tree = ttk.Treeview(stats_window, columns=["customer", "count", "amount", "received"], show="headings")tree.heading("customer", text="客户")tree.heading("count", text="订单数")tree.heading("amount", text="总金额")tree.heading("received", text="已收金额")for row in cursor.fetchall():tree.insert("", "end", values=row)tree.pack(padx=5, pady=5, fill="both", expand=True)def load_all_orders(self):"""加载所有订单到表格"""cursor = self.conn.cursor()cursor.execute('SELECT * FROM orders')self.tree.delete(*self.tree.get_children())for row in cursor.fetchall():self.tree.insert("", "end", values=row)# 更新筛选下拉列表self.update_filter_lists()self.update_order_groups()def import_from_excel(self):"""从Excel文件导入数据"""filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")])if not filename:returntry:# 读取Excel文件df = pd.read_excel(filename)# 数字字段列表numeric_columns = ['数量', '原价', '单行折扣率(%)', '折后价', '金额','整单折扣率(%)', '折后金额', '运费', '本单已收']# 转换数字列的数据类型for col in numeric_columns:if col in df.columns:df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)# 检查必需的列是否存在required_columns = ['单据日期', '单据编号', '客户名称', '品名规格', '单位', '数量', '原价', '单行折扣率(%)', '折后价', '金额','备注', '整单折扣率(%)', '折后金额', '运费','本单已收', '结算账户', '说明', '营业员']missing_columns = [col for col in required_columns if col not in df.columns]if missing_columns:messagebox.showerror("错误", f"Excel文件缺少以下列：\n{', '.join(missing_columns)}")return# 创建预览窗口preview_window = tk.Toplevel(self.root)preview_window.title("导入数据预览")preview_window.geometry("800x600")# 创建预览表格preview_tree = ttk.Treeview(preview_window, columns=required_columns[:10], show="headings")# 设置列标题for col in required_columns[:10]:preview_tree.heading(col, text=col)preview_tree.column(col, width=100)# 添加数据到预��表格for _, row in df.iterrows():values = [row[col] for col in required_columns[:10]]preview_tree.insert("", "end", values=values)# 添加滚动条scrollbar = ttk.Scrollbar(preview_window, orient="vertical", command=preview_tree.yview)scrollbar.pack(side="right", fill="y")preview_tree.configure(yscrollcommand=scrollbar.set)preview_tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加按钮框btn_frame = ttk.Frame(preview_window)btn_frame.pack(pady=5)def confirm_import():try:# 将数据插入数据库cursor = self.conn.cursor()# 定义字段映射field_mapping = {'单据日期': 'order_date','单据编号': 'order_number','客户名称': 'customer','品名规格': 'product','单位': 'unit','数量': 'quantity','原价': 'price','单行折扣率(%)': 'discount','折后价': 'final_price','金额': 'total','备注': 'remarks','整单折扣率(%)': 'discount_amount','折后金额': 'discount_total','运费': 'delivery','本单已收': 'payment_received','结算账户': 'end_customer','说明': 'notes','营业员': 'business'}# 获取数据字段顺序db_fields = ['order_date', 'order_number', 'customer', 'product', 'unit','quantity', 'price', 'discount', 'final_price', 'total','remarks', 'discount_amount', 'discount_total', 'delivery','payment_received', 'end_customer', 'notes', 'business']for _, row in df.iterrows():values = []for field in db_fields:# 从Excel列名映射到数据库字段excel_col = [k for k, v in field_mapping.items() if v == field][0]value = row[excel_col]# 处理数值if pd.isna(value):value = 0 if field in ['quantity', 'price', 'discount', 'final_price', 'total', 'discount_amount', 'discount_total', 'payment_received'] else ''elif isinstance(value, (int, float)):value = float(value)else:value = str(value)values.append(value)try:cursor.execute('''INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', values)except sqlite3.Error as e:self.conn.rollback()messagebox.showerror("错误", f"插入数据时出错：{str(e)}\n行数据{values}")returnself.conn.commit()self.load_all_orders()messagebox.showinfo("成功", "数据导入成功！")preview_window.destroy()except Exception as e:self.conn.rollback()messagebox.showerror("错误", f"导入过程中出错：{str(e)}")def cancel_import():preview_window.destroy()# 添加确认和取消按钮ttk.Button(btn_frame, text="确认导入", command=confirm_import).pack(side="left", padx=5)ttk.Button(btn_frame, text="取消", command=cancel_import).pack(side="left", padx=5)# 显示导入数据的总数ttk.Label(preview_window, text=f"共 {len(df)} 条数据").pack(pady=5)except Exception as e:messagebox.showerror("错误", f"导入过程中出错：{str(e)}")self.update_order_groups()def export_template(self):"""导出Excel模板"""filename = filedialog.asksaveasfilename(defaultextension=".xlsx",filetypes=[("Excel files", "*.xlsx")],initialfile="订单导出模板.xlsx")if not filename:returntry:# 创建示例数据 - 使用相同的列名sample_data = {'单据日期': ['2024-01-01'],'单据编号': ['XSD202401001'],'客户名称': ['示例客户'],'品名规格': ['示例产品'],'单位': ['个'],'数量': [1],'原价': [100],'单行折扣率(%)': [100],'折后价': [100],'金额': [100],'备注': ['备注示例'],'整单折扣率(%)': [0],'折后金额': [100],'运费': [0],'本单已收': [0],'结算账户': ['结算账户示例'],'说明': ['说明示例'],'营业员': ['营业员示例']}# 创建DataFramedf = pd.DataFrame(sample_data)# 创建Excel写入器with pd.ExcelWriter(filename, engine='openpyxl') as writer:# 写入数据df.to_excel(writer, index=False, sheet_name='订单数据')# 获取工作表worksheet = writer.sheets['订单数据']# 设置列宽for column in worksheet.columns:max_length = 0column = [cell for cell in column]for cell in column:try:if len(str(cell.value)) > max_length:max_length = len(str(cell.value))except:passadjusted_width = (max_length + 2)worksheet.column_dimensions[column[0].column_letter].width = adjusted_width# 设置样式from openpyxl.styles import PatternFill, Font, Alignment, Border, Side# 定义样式header_fill = PatternFill(start_color='CCE5FF', end_color='CCE5FF', fill_type='solid')header_font = Font(bold=True)center_aligned = Alignment(horizontal='center', vertical='center')border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))# 应用表头样式for cell in worksheet[1]:cell.fill = header_fillcell.font = header_fontcell.alignment = center_alignedcell.border = border# 应用数据行样式for row in worksheet.iter_rows(min_row=2):for cell in row:cell.alignment = center_alignedcell.border = bordermessagebox.showinfo("成功", "模板导出成功！\n请按照模板格式准备数据后再进行导。")except Exception as e:messagebox.showerror("错误", f"导出模板时出错：{str(e)}")def set_default_values(self):"""设置默认值"""# 清空所有输入框self.clear_fields()# 只设置日期和折扣率的默认值today = datetime.now().strftime('%Y-%m-%d')self.entries['order_date'].insert(0, today)  # 默认日期为今天self.entries['discount'].insert(0, '100')    # 默认折扣率为100%# 生成新的单据编号cursor = self.conn.cursor()cursor.execute('''SELECT MAX(order_number) FROM orders WHERE order_number LIKE ?''', [f'XSD{today.replace("-", "")}%'])last_number = cursor.fetchone()[0]if last_number:try:# 从最后一个单号提取序号并加1seq = int(last_number[-3:]) + 1new_number = f'XSD{today.replace("-", "")}{seq:03d}'except ValueError:new_number = f'XSD{today.replace("-", "")}001'else:new_number = f'XSD{today.replace("-", "")}001'self.entries['order_number'].insert(0, new_number)  # 设置新单据编号def update_order_groups(self):"""更新订单分类显示"""cursor = self.conn.cursor()cursor.execute('''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders ORDER BY order_number DESC''')# 清空现有数据self.group_tree.delete(*self.group_tree.get_children())total_sum = 0# 插入新数据for row in cursor.fetchall():formatted_row = list(row[:8])  # 只取前8列显示# 格式化数字列formatted_row[4] = f"{row[4]:.2f}"  # 数量formatted_row[5] = f"¥{row[5]:.2f}"  # 原价formatted_row[6] = f"¥{row[6]:.2f}"  # 金额self.group_tree.insert("", "end", values=formatted_row)total_sum = row[8]  # 获取合计金额# 更新合计标签self.total_label.config(text=f"合计金额: ¥{total_sum:,.2f}")def on_group_select(self, event):"""当选择订单分类时的处理"""selected = self.group_tree.selection()if not selected:return# 获取选中的单据编号order_number = self.group_tree.item(selected[0])['values'][0]# 在主表格中查找并选中对应的记录for item in self.tree.get_children():if self.tree.item(item)['values'][1] == order_number:  # 假设单据编号是第二列self.tree.selection_set(item)self.tree.see(item)  # 确保选中的项可见breakdef filter_orders(self):"""根据筛选条件过滤订单"""order_number = self.order_number_filter.get().strip()customer = self.customer_filter.get().strip()cursor = self.conn.cursor()# 构建查询条件query = '''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders WHERE 1=1'''params = []if order_number:query += " AND order_number LIKE ?"params.append(f"%{order_number}%")if customer:query += " AND customer LIKE ?"params.append(f"%{customer}%")query += " ORDER BY order_number DESC"cursor.execute(query, params)# 清空现有数据self.group_tree.delete(*self.group_tree.get_children())total_sum = 0# 插入新数据for row in cursor.fetchall():formatted_row = list(row[:8])  # 只取前8列显示# 格式化数字列formatted_row[4] = f"{row[4]:.2f}"  # 数量formatted_row[5] = f"¥{row[5]:.2f}"  # 原价formatted_row[6] = f"¥{row[6]:.2f}"  # 金额self.group_tree.insert("", "end", values=formatted_row)total_sum = row[8]  # 获取合计金额# 更新合计标签self.total_label.config(text=f"合计金额: ¥{total_sum:,.2f}")def reset_filter(self):"""重置筛选条件"""self.order_number_filter.delete(0, tk.END)self.customer_filter.delete(0, tk.END)self.update_order_groups()def update_order_number_list(self, event=None):"""更新单据编号下拉列表"""search_text = self.order_number_filter.get().strip()cursor = self.conn.cursor()if search_text:cursor.execute('''SELECT DISTINCT order_number FROM orders WHERE order_number LIKE ? ORDER BY order_number DESC''', [f'%{search_text}%'])else:cursor.execute('''SELECT DISTINCT order_number FROM orders ORDER BY order_number DESC''')order_numbers = [row[0] for row in cursor.fetchall()]if order_numbers:self.order_number_filter['values'] = order_numbersif search_text:self.order_number_filter.event_generate('<Down>')def update_customer_list(self, event=None):"""更新客户名称下拉列表"""search_text = self.customer_filter.get().strip()cursor = self.conn.cursor()if search_text:cursor.execute('''SELECT DISTINCT customer FROM orders WHERE customer LIKE ? ORDER BY customer''', [f'%{search_text}%'])else:cursor.execute('''SELECT DISTINCT customer FROM orders ORDER BY customer''')customers = [row[0] for row in cursor.fetchall()]if customers:self.customer_filter['values'] = customersif search_text:self.customer_filter.event_generate('<Down>')def update_filter_lists(self):"""更新所有筛选下拉列表"""cursor = self.conn.cursor()# 更新单据编号列表cursor.execute('SELECT DISTINCT order_number FROM orders ORDER BY order_number DESC')self.order_number_filter['values'] = [row[0] for row in cursor.fetchall()]# 更新客户名称列表cursor.execute('SELECT DISTINCT customer FROM orders ORDER BY customer')self.customer_filter['values'] = [row[0] for row in cursor.fetchall()]def print_filtered_data(self):"""打印筛选后的数据"""try:# 获取当前筛选条件下的数据order_number = self.order_number_filter.get().strip()customer = self.customer_filter.get().strip()# 构建查询条件query = '''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders WHERE 1=1'''params = []if order_number:query += " AND order_number LIKE ?"params.append(f"%{order_number}%")if customer:query += " AND customer LIKE ?"params.append(f"%{customer}%")query += " ORDER BY order_number DESC"cursor = self.conn.cursor()cursor.execute(query, params)rows = cursor.fetchall()if not rows:messagebox.showinfo("提示", "没有数据可打印")return# 生成HTML内容html_content = f"""<!DOCTYPE html><html><head><meta charset="utf-8"><title>订单查询结果</title><style>body {{ font-family: SimSun, serif; }}table {{ border-collapse: collapse; width: 100%; margin-top: 10px; }}th, td {{ border: 1px solid black; padding: 8px; text-align: center; }}th {{ background-color: #f2f2f2; }}.total {{ text-align: right;padding: 10px;font-weight: bold;}}.header-info {{margin: 10px 0;padding: 10px;border: 1px solid #ddd;background-color: #f9f9f9;}}.header-info p {{margin: 5px 0;}}@media print {{.no-print {{ display: none; }}body {{ margin: 0; }}table {{ page-break-inside: auto; }}tr {{ page-break-inside: avoid; }}}}</style></head><body><h2 style="text-align: center;">订单查询结果</h2><div class="header-info"><p><strong>单据编号：</strong>{order_number if order_number else "全部"}</p><p><strong>客户名称：</strong>{customer if customer else "全部"}</p><p><strong>打印时间：</strong>{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p></div><table><tr><th>品名规格</th><th>单位</th><th>数量</th><th>原价</th><th>金额</th><th>备注</th></tr>"""# 添加数据行for row in rows:html_content += f"""<tr><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]:.2f}</td><td>¥{row[5]:.2f}</td><td>¥{row[6]:.2f}</td><td>{row[7]}</td></tr>"""# 添加合计行total_sum = rows[0][8] if rows else 0html_content += f"""</table><div class="total">合计金额: ¥{total_sum:,.2f}</div><div class="no-print" style="margin-top: 20px; text-align: center;"><button onclick="window.print()">打印</button></div></body></html>"""# 保存HTML文件temp_file = "订单查询结果.html"with open(temp_file, "w", encoding="utf-8") as f:f.write(html_content)# 在默认浏览器中打开HTML文件import webbrowserwebbrowser.open(temp_file)except Exception as e:messagebox.showerror("错误", f"打印过程中出错：{str(e)}")if __name__ == "__main__":root = tk.Tk()app = OrderSystem(root)root.mainloop()

程序简要说明

这是一个订单管理系统，主要功能如下：

1. 基本功能：
- 订单录入和保存
- 订单查询和筛选
- 数据导入导出（Excel格式）
- 打印功能（HTML格式）

2. 界面布局：
- 左侧：订单信息录入表单
- 中间：订单数据列表
- 右侧：订单查询面板（带筛选和统计）

3. 主要特点：
- 自动生成单据编号（格式：XSD + 日期 + 3位序号）
- 支持数字字段自动计算（数量、单价、折扣等）
- 提供下拉选择和手动输入的组合筛选
- 实时显示筛选结果的合计金额

4. 数据管理：
- 使用SQLite数据库存储数据
- 支持Excel导入导出
- 提供数据验证和错误处理

5. 使用方法：
```python
if __name__ == "__main__":
root = tk.Tk()
app = OrderSystem(root)
root.mainloop()
```

6. 依赖库：
- tkinter：GUI界面
- sqlite3：数据库操作
- pandas：Excel处理
- datetime：日期处理
- webbrowser：打印功能

这个程序适合小型企业或个人用于日常订单管理和记录。

1.1 1.1 版本

添加以下功能：

1. 程序启动时需要输入密码才能进入

2. 默认密码为 "admin"

3. 可以通过界面修改登录密码

4. 数据库使用相同的密码进行加密保护

5. 密码以哈希形式存储在 config.json 文件中 (放入 db目录里)

使用说明：

1. 首次运行时，默认密码为 "admin"

2. 登录后可以点击"修改密码"按钮更改密码

3. 新密码会同时用于程序登录和数据库加密

4. 如果忘记密码，需要删除 config.json 文件，程序会重置为默认密码

注意事项：

1. 修改密码后，请务必记住新密码，因为它同时用于程序登录和数据库加密

2. 建议定期备份数据库文件（数据库也放在 db目录里）

login.py

import tkinter as tk
from tkinter import ttk, messagebox
import hashlib
import json
import osclass LoginWindow:def __init__(self):self.window = tk.Tk()self.window.title("登录")self.window.geometry("300x150")self.login_success = False  # 添加登录状态标志# 居中显示self.window.update_idletasks()width = self.window.winfo_width()height = self.window.winfo_height()x = (self.window.winfo_screenwidth() // 2) - (width // 2)y = (self.window.winfo_screenheight() // 2) - (height // 2)self.window.geometry(f"{width}x{height}+{x}+{y}")# 创建登录框架frame = ttk.Frame(self.window, padding="20")frame.pack(fill="both", expand=True)ttk.Label(frame, text="请输入密码:").pack(pady=5)self.password_var = tk.StringVar()self.password_entry = ttk.Entry(frame, show="*", textvariable=self.password_var)self.password_entry.pack(pady=5, fill="x")btn_frame = ttk.Frame(frame)btn_frame.pack(pady=10)ttk.Button(btn_frame, text="登录", command=self.login).pack(side="left", padx=5)ttk.Button(btn_frame, text="修改密码", command=self.change_password).pack(side="left", padx=5)self.password_entry.bind('<Return>', lambda e: self.login())self.password_entry.focus()self.load_password()def load_password(self):"""加载保存的密码哈希"""try:if os.path.exists('db/config.json'):with open('db/config.json', 'r') as f:config = json.load(f)self.password_hash = config.get('password_hash', self.hash_password('admin'))else:# 默认密码为 'admin'self.password_hash = self.hash_password('admin')self.save_password()except Exception:self.password_hash = self.hash_password('admin')def save_password(self):"""保存密码哈希到配置文件"""config = {'password_hash': self.password_hash}with open('db/config.json', 'w') as f:json.dump(config, f)def hash_password(self, password):"""对密码进行哈希处理"""return hashlib.sha256(password.encode()).hexdigest()def login(self):"""验证密码"""if self.hash_password(self.password_var.get()) == self.password_hash:self.login_success = True  # 设置登录成功标志self.window.destroy()return Trueelse:messagebox.showerror("错误", "密码错误！")self.password_var.set("")return Falsedef change_password(self):"""修改密码"""change_window = tk.Toplevel(self.window)change_window.title("修改密码")change_window.geometry("300x400")# 居中显示change_window.update_idletasks()width = change_window.winfo_width()height = change_window.winfo_height()x = (change_window.winfo_screenwidth() // 2) - (width // 2)y = (change_window.winfo_screenheight() // 2) - (height // 2)change_window.geometry(f"{width}x{height}+{x}+{y}")frame = ttk.Frame(change_window, padding="20")frame.pack(fill="both", expand=True)# 当前密码ttk.Label(frame, text="当前密码:").pack(pady=5)current_password = ttk.Entry(frame, show="*")current_password.pack(pady=5, fill="x")# 新密码ttk.Label(frame, text="新密码:").pack(pady=5)new_password = ttk.Entry(frame, show="*")new_password.pack(pady=5, fill="x")# 确认新密码ttk.Label(frame, text="确认新密码:").pack(pady=5)confirm_password = ttk.Entry(frame, show="*")confirm_password.pack(pady=5, fill="x")def do_change():if self.hash_password(current_password.get()) != self.password_hash:messagebox.showerror("错误", "当前密码错误！")returnif new_password.get() != confirm_password.get():messagebox.showerror("错误", "两次输入的新密码不一致！")returnif not new_password.get():messagebox.showerror("错误", "新密码不能为空！")returnself.password_hash = self.hash_password(new_password.get())self.save_password()messagebox.showinfo("成功", "密码修改成功！")change_window.destroy()ttk.Button(frame, text="确认修改", command=do_change).pack(pady=10)def run(self):"""运行登录窗口"""self.window.protocol("WM_DELETE_WINDOW", self.on_closing)  # 添加窗口关闭事件处理self.window.mainloop()return self.login_success  # 返回登录状态def on_closing(self):"""窗口关闭事件处理"""if messagebox.askokcancel("退出", "确定要退出程序吗？"):self.login_success = Falseself.window.destroy()

主程序.py

import tkinter as tk
from tkinter import ttk
import sqlite3
from datetime import datetime
from tkinter import messagebox, filedialog
import pandas as pd
import re
import json
import sys
from login import LoginWindowclass OrderSystem:def __init__(self, root):self.root = rootself.root.title("订单记录系统")# 创建数据库连接self.db_password = self.get_db_password()  # 获取数据库密码self.conn = self.create_db_connection()self.create_table()# 创建界面self.create_ui()# 添加搜索框架self.create_search_frame()# 添加更多功能按钮self.add_function_buttons()# 加载所有订单数据self.load_all_orders()# 设置默认值self.set_default_values()def get_db_password(self):"""从配置文件获取数据库密码"""try:with open('db/config.json', 'r') as f:config = json.load(f)return config.get('password_hash', '')except Exception:return ''def create_db_connection(self):"""创建加密的数据库连接"""try:conn = sqlite3.connect('orders.db')# 设置数据库密码conn.execute(f"PRAGMA key = '{self.db_password}'")return connexcept sqlite3.Error as e:messagebox.showerror("错误", f"数据库连接失败：{str(e)}")sys.exit(1)def create_table(self):cursor = self.conn.cursor()cursor.execute('''CREATE TABLE IF NOT EXISTS orders (order_date TEXT,order_number TEXT,customer TEXT,product TEXT,unit TEXT,quantity REAL,price REAL,discount REAL,final_price REAL,total REAL,remarks TEXT,discount_amount REAL,discount_total REAL,delivery TEXT,payment_received REAL,end_customer TEXT,notes TEXT,business TEXT)''')self.conn.commit()def create_ui(self):# 创建主框架来容纳左右两部分main_frame = ttk.Frame(self.root)main_frame.pack(fill="both", expand=True)# 创建左侧框架left_frame = ttk.Frame(main_frame)left_frame.pack(side="left", fill="both", expand=True)# 创建右侧框架right_frame = ttk.Frame(main_frame)right_frame.pack(side="right", fill="y", padx=5)# 创建输入框架（放在左侧）input_frame = ttk.LabelFrame(left_frame, text="订单信息")input_frame.pack(padx=5, pady=5, fill="x")# 修改订单分类框架order_group_frame = ttk.LabelFrame(right_frame, text="订单查询")order_group_frame.pack(padx=5, pady=5, fill="both", expand=True)# 添加筛选框filter_frame = ttk.Frame(order_group_frame)filter_frame.pack(fill="x", padx=5, pady=5)# 单据编号筛选ttk.Label(filter_frame, text="单据编号:").grid(row=0, column=0, padx=5)self.order_number_filter = ttk.Combobox(filter_frame, width=15)self.order_number_filter.grid(row=0, column=1, padx=5)# 客户名称筛选ttk.Label(filter_frame, text="客户名称:").grid(row=0, column=2, padx=5)self.customer_filter = ttk.Combobox(filter_frame, width=15)self.customer_filter.grid(row=0, column=3, padx=5)# 筛选按钮ttk.Button(filter_frame, text="筛选", command=self.filter_orders).grid(row=0, column=4, padx=5)ttk.Button(filter_frame, text="重置", command=self.reset_filter).grid(row=0, column=5, padx=5)ttk.Button(filter_frame, text="打印", command=self.print_filtered_data).grid(row=0, column=6, padx=5)# 绑定下拉框事件self.order_number_filter.bind('<KeyRelease>', self.update_order_number_list)self.customer_filter.bind('<KeyRelease>', self.update_customer_list)# 修改订单分类的树形视图列self.group_tree = ttk.Treeview(order_group_frame, columns=["order_number", "customer", "product", "unit", "quantity", "price", "total", "remarks"], show="headings", height=15)# 设置列标题和宽度columns = [("order_number", "单据编号", 100),("customer", "客户名称", 100),("product", "品名规格", 120),("unit", "单位", 50),("quantity", "数量", 60),("price", "原价", 80),("total", "金额", 80),("remarks", "备注", 100)]for col, heading, width in columns:self.group_tree.heading(col, text=heading)self.group_tree.column(col, width=width)self.group_tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加滚动条group_scrollbar = ttk.Scrollbar(order_group_frame, orient="vertical", command=self.group_tree.yview)group_scrollbar.pack(side="right", fill="y")self.group_tree.configure(yscrollcommand=group_scrollbar.set)# 绑定点击事件self.group_tree.bind('<<TreeviewSelect>>', self.on_group_select)# 修改输入字段列表，确保与数据库字段完全匹配self.entries = {}fields = [("order_date", "单据日期"), ("order_number", "单据编号"),("customer", "客户名称"), ("product", "品名规格"),("unit", "单位"),("quantity", "数量"),("price", "原价"), ("discount", "单行折扣率(%)"),("final_price", "折后价"), ("total", "金额"),("remarks", "备注"), ("discount_amount", "整单折扣率(%)"),("discount_total", "折后金额"), ("delivery", "运费"),("payment_received", "本单已收"), ("end_customer", "结算账户"),("notes", "说明"), ("business", "营业员")]for row, (field, label) in enumerate(fields):ttk.Label(input_frame, text=label).grid(row=row//2, column=(row%2)*2, padx=5, pady=2)self.entries[field] = ttk.Entry(input_frame)self.entries[field].grid(row=row//2, column=(row%2)*2+1, padx=5, pady=2, sticky="ew")# 添加按钮self.btn_frame = ttk.Frame(self.root)self.btn_frame.pack(pady=5)ttk.Button(self.btn_frame, text="保存", command=self.save_order).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="清空", command=self.clear_fields).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导入Excel", command=self.import_from_excel).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导出模板", command=self.export_template).pack(side="left", padx=5)# 修改表格显示，显示所有列self.tree = ttk.Treeview(self.root, columns=["order_date", "order_number", "customer", "product", "unit","quantity", "price", "discount", "final_price", "total","remarks", "discount_amount", "discount_total", "delivery","payment_received", "end_customer", "notes", "business"], show="headings")# 修改列标题定义，显示所有列columns = [("order_date", "单据日期"), ("order_number", "单据编号"),("customer", "客户名称"), ("product", "品名规格"),("unit", "单位"),("quantity", "数量"),("price", "原价"), ("discount", "单行折扣率(%)"),("final_price", "折后价"), ("total", "金额"),("remarks", "备注"),("discount_amount", "整单折扣率(%)"),("discount_total", "折后金额"),("delivery", "运费"),("payment_received", "本单已收"),("end_customer", "结算账户"),("notes", "说明"),("business", "营业员")]for col, heading in columns:self.tree.heading(col, text=heading)self.tree.column(col, width=100)self.tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加滚动条scrollbar = ttk.Scrollbar(self.root, orient="vertical", command=self.tree.yview)scrollbar.pack(side="right", fill="y")self.tree.configure(yscrollcommand=scrollbar.set)# 添加自动计算绑定self.entries['quantity'].bind('<KeyRelease>', self.calculate_total)self.entries['price'].bind('<KeyRelease>', self.calculate_total)self.entries['discount'].bind('<KeyRelease>', self.calculate_total)# 在订单分类框架底部添加合计标签self.total_label = ttk.Label(order_group_frame, text="合计金额: ¥0.00")self.total_label.pack(pady=5)def calculate_total(self, event=None):"""计算折后价和金额"""try:quantity = float(self.entries['quantity'].get() or 0)price = float(self.entries['price'].get() or 0)discount = float(self.entries['discount'].get() or 100)# 计算折后价final_price = price * discount / 100self.entries['final_price'].delete(0, tk.END)self.entries['final_price'].insert(0, f"{final_price:.2f}")# 计算金额total = quantity * final_priceself.entries['total'].delete(0, tk.END)self.entries['total'].insert(0, f"{total:.2f}")except ValueError:passdef create_search_frame(self):search_frame = ttk.LabelFrame(self.root, text="搜索")search_frame.pack(padx=5, pady=5, fill="x")ttk.Label(search_frame, text="搜索条件:").pack(side="left", padx=5)self.search_entry = ttk.Entry(search_frame)self.search_entry.pack(side="left", padx=5, fill="x", expand=True)ttk.Button(search_frame, text="搜索", command=self.search_orders).pack(side="left", padx=5)def add_function_buttons(self):# 在原有btn_frame中添加更多按钮ttk.Button(self.btn_frame, text="编辑", command=self.edit_selected).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="删除", command=self.delete_selected).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="导出Excel", command=self.export_to_excel).pack(side="left", padx=5)ttk.Button(self.btn_frame, text="统计报表", command=self.show_statistics).pack(side="left", padx=5)def validate_data(self):"""数据验证"""errors = []# 验证日期格式date = self.entries['order_date'].get().strip()if not date:errors.append("单据日期不能空")elif not re.match(r'^\d{4}-\d{2}-\d{2}$', date):errors.append("单据日期格式错误，应为 YYYY-MM-DD")# 验证必填字段required_fields = {'order_number': '单据编号','customer': '客户名称','product': '品名规格','unit': '单位','quantity': '数量','price': '原价'}for field, name in required_fields.items():value = self.entries[field].get().strip()if not value:errors.append(f"{name}不能为空")# 验证数字字段number_fields = {'quantity': '数量','price': '原价','discount': '单行折扣率(%)','final_price': '折后价','total': '金额','discount_amount': '整单折扣率(%)','discount_total': '折后金额','payment_received': '本单已收'}for field, name in number_fields.items():value = self.entries[field].get().strip()if value:  # 如果有值才验证try:num = float(value)if field in ['quantity', 'price'] and num <= 0:errors.append(f"{name}必须大于0")elif num < 0:errors.append(f"{name}不能为负数")except ValueError:errors.append(f"{name}必须是数字")if errors:messagebox.showerror("验证错误", "\n".join(errors))return Falsereturn Truedef save_order(self):"""保存订单数据"""if not self.validate_data():returntry:# 获取所有输入值values = []fields_order = ['order_date', 'order_number', 'customer', 'product', 'unit','quantity', 'price', 'discount', 'final_price', 'total','remarks', 'discount_amount', 'discount_total', 'delivery','payment_received', 'end_customer', 'notes', 'business']for field in fields_order:value = self.entries[field].get().strip()# 对数字字段进行转换if field in ['quantity', 'price', 'discount', 'final_price', 'total', 'discount_amount', 'discount_total', 'payment_received']:try:value = float(value) if value else 0.0except ValueError:value = 0.0elif not value:  # 对非数字字段，如果为空则设为空字��串value = ''values.append(value)# 检查单据编号是否重复cursor = self.conn.cursor()cursor.execute('SELECT COUNT(*) FROM orders WHERE order_number = ?', (values[1],))if cursor.fetchone()[0] > 0:if not messagebox.askyesno("警告", "单据编号已存在是否继续保存？"):return# 插入数据try:cursor.execute('''INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', values)self.conn.commit()# 更新表格显示self.tree.insert("", "end", values=values)# 清空输入框并设置默认值self.set_default_values()# 显示成功消息messagebox.showinfo("成功", "订单保存成功！")except sqlite3.Error as e:self.conn.rollback()messagebox.showerror("数据库错误", f"保存失败：{str(e)}")returnexcept Exception as e:messagebox.showerror("错误", f"保存过程中出错：{str(e)}")returnself.update_order_groups()def clear_fields(self):"""清空所有输入框"""for field in self.entries:self.entries[field].delete(0, tk.END)def __del__(self):self.conn.close()def search_orders(self):search_text = self.search_entry.get().strip()if not search_text:self.load_all_orders()returncursor = self.conn.cursor()cursor.execute('''SELECT * FROM orders WHERE order_date LIKE ? OR order_number LIKE ? OR customer LIKE ? OR product LIKE ?''', [f'%{search_text}%'] * 4)self.tree.delete(*self.tree.get_children())for row in cursor.fetchall():self.tree.insert("", "end", values=row)def edit_selected(self):selected = self.tree.selection()if not selected:messagebox.showwarning("提示", "请先选择一条记录")returnitem = self.tree.item(selected[0])values = item['values']# 填充表单for field, value in zip(self.entries.keys(), values):self.entries[field].delete(0, tk.END)self.entries[field].insert(0, str(value))def delete_selected(self):selected = self.tree.selection()if not selected:messagebox.showwarning("提示", "请先选择一条记录")returnif messagebox.askyesno("确认", "确定要删除中的记录吗？"):item = self.tree.item(selected[0])order_number = item['values'][1]cursor = self.conn.cursor()cursor.execute('DELETE FROM orders WHERE order_number = ?', (order_number,))self.conn.commit()self.tree.delete(selected[0])self.update_order_groups()def export_to_excel(self):"""导出数据到Excel"""try:# 先获取保存路径filename = filedialog.asksaveasfilename(defaultextension=".xlsx",filetypes=[("Excel files", "*.xlsx")])if not filename:return# 获取数据cursor = self.conn.cursor()cursor.execute('SELECT * FROM orders')data = cursor.fetchall()# 准备列名columns = ['单据日期', '单据编号', '客户名称', '品名规格','单位', '数量', '原价', '单行折扣率(%)', '折后价', '金额', '备注', '整单折扣率(%)', '折后金额', '运费','本单已收', '结算账户', '说明', '营业员']# 创建DataFramedf = pd.DataFrame(data, columns=columns)# 直接导出df.to_excel(filename, index=False)messagebox.showinfo("成功", "数据已导出到Excel文件")except PermissionError:messagebox.showerror("错误", "无法保存文件，请确保：\n1. 文件未被其他程序打开\n2. 您有写入权限")except Exception as e:messagebox.showerror("错误", f"导出过程中出错：{str(e)}")def show_statistics(self):stats_window = tk.Toplevel(self.root)stats_window.title("统计报表")cursor = self.conn.cursor()# 客户统计cursor.execute('''SELECT customer, COUNT(*) as order_count,SUM(total) as total_amount,SUM(payment_received) as total_receivedFROM orders GROUP BY customer''')# 创建统计表格tree = ttk.Treeview(stats_window, columns=["customer", "count", "amount", "received"], show="headings")tree.heading("customer", text="客户")tree.heading("count", text="订单数")tree.heading("amount", text="总金额")tree.heading("received", text="已收金额")for row in cursor.fetchall():tree.insert("", "end", values=row)tree.pack(padx=5, pady=5, fill="both", expand=True)def load_all_orders(self):"""加载所有订单到表格"""cursor = self.conn.cursor()cursor.execute('SELECT * FROM orders')self.tree.delete(*self.tree.get_children())for row in cursor.fetchall():self.tree.insert("", "end", values=row)# 更新筛选下拉列表self.update_filter_lists()self.update_order_groups()def import_from_excel(self):"""从Excel文件导入数据"""filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")])if not filename:returntry:# 读取Excel文件df = pd.read_excel(filename)# 数字字段列表numeric_columns = ['数量', '原价', '单行折扣率(%)', '折后价', '金额','整单折扣率(%)', '折后金额', '运费', '本单已收']# 转换数字列的数据类型for col in numeric_columns:if col in df.columns:df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)# 检查必需的列是否存在required_columns = ['单据日期', '单据编号', '客户名称', '品名规格', '单位', '数量', '原价', '单行折扣率(%)', '折后价', '金额','备注', '整单折扣率(%)', '折后金额', '运费','本单已收', '结算账户', '说明', '营业员']missing_columns = [col for col in required_columns if col not in df.columns]if missing_columns:messagebox.showerror("错误", f"Excel文件缺少以下列：\n{', '.join(missing_columns)}")return# 创建预览窗口preview_window = tk.Toplevel(self.root)preview_window.title("导入数据预览")preview_window.geometry("800x600")# 创建预览表格preview_tree = ttk.Treeview(preview_window, columns=required_columns[:10], show="headings")# 设置列标题for col in required_columns[:10]:preview_tree.heading(col, text=col)preview_tree.column(col, width=100)# 添加数据到预览表格for _, row in df.iterrows():values = [row[col] for col in required_columns[:10]]preview_tree.insert("", "end", values=values)# 添加滚动条scrollbar = ttk.Scrollbar(preview_window, orient="vertical", command=preview_tree.yview)scrollbar.pack(side="right", fill="y")preview_tree.configure(yscrollcommand=scrollbar.set)preview_tree.pack(padx=5, pady=5, fill="both", expand=True)# 添加按钮框btn_frame = ttk.Frame(preview_window)btn_frame.pack(pady=5)def confirm_import():try:# 将数据���入数据库cursor = self.conn.cursor()# 定义字段映射field_mapping = {'单据日期': 'order_date','单据编号': 'order_number','客户名称': 'customer','品名规格': 'product','单位': 'unit','数量': 'quantity','原价': 'price','单行折扣率(%)': 'discount','折后价': 'final_price','金额': 'total','备注': 'remarks','整单折扣率(%)': 'discount_amount','折后金额': 'discount_total','运费': 'delivery','本单已收': 'payment_received','结算账户': 'end_customer','说明': 'notes','营业员': 'business'}# 获取数据字段顺序db_fields = ['order_date', 'order_number', 'customer', 'product', 'unit','quantity', 'price', 'discount', 'final_price', 'total','remarks', 'discount_amount', 'discount_total', 'delivery','payment_received', 'end_customer', 'notes', 'business']for _, row in df.iterrows():values = []for field in db_fields:# 从Excel列名映射到数据库字段excel_col = [k for k, v in field_mapping.items() if v == field][0]value = row[excel_col]# 处理数值if pd.isna(value):value = 0 if field in ['quantity', 'price', 'discount', 'final_price', 'total', 'discount_amount', 'discount_total', 'payment_received'] else ''elif isinstance(value, (int, float)):value = float(value)else:value = str(value)values.append(value)try:cursor.execute('''INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', values)except sqlite3.Error as e:self.conn.rollback()messagebox.showerror("错误", f"插入数据时出错：{str(e)}\n行数据{values}")returnself.conn.commit()self.load_all_orders()messagebox.showinfo("成功", "数据导入成功！")preview_window.destroy()except Exception as e:self.conn.rollback()messagebox.showerror("错误", f"导入过程中出错：{str(e)}")def cancel_import():preview_window.destroy()# 添加确认和取消按钮ttk.Button(btn_frame, text="确认导入", command=confirm_import).pack(side="left", padx=5)ttk.Button(btn_frame, text="取消", command=cancel_import).pack(side="left", padx=5)# 显示导入数据的总数ttk.Label(preview_window, text=f"共 {len(df)} 条数据").pack(pady=5)except Exception as e:messagebox.showerror("错误", f"导入过程中出错：{str(e)}")self.update_order_groups()def export_template(self):"""导出Excel模板"""filename = filedialog.asksaveasfilename(defaultextension=".xlsx",filetypes=[("Excel files", "*.xlsx")],initialfile="订单导出模板.xlsx")if not filename:returntry:# 创建示例数据 - 使用相同的列名sample_data = {'单据日期': ['2024-01-01'],'单据编号': ['XSD202401001'],'客户名称': ['示例客户'],'品名规格': ['示例产品'],'单位': ['个'],'数量': [1],'原价': [100],'单行折扣率(%)': [100],'折后价': [100],'金额': [100],'备注': ['备注示例'],'整单折扣率(%)': [0],'折后金额': [100],'运费': [0],'本单已收': [0],'结算账户': ['结算账户示例'],'说明': ['说明示例'],'营业员': ['营业员示例']}# 创建DataFramedf = pd.DataFrame(sample_data)# 创建Excel写入器with pd.ExcelWriter(filename, engine='openpyxl') as writer:# 写入数据df.to_excel(writer, index=False, sheet_name='订单数据')# 获取工作表worksheet = writer.sheets['订单数据']# 设置列宽for column in worksheet.columns:max_length = 0column = [cell for cell in column]for cell in column:try:if len(str(cell.value)) > max_length:max_length = len(str(cell.value))except:passadjusted_width = (max_length + 2)worksheet.column_dimensions[column[0].column_letter].width = adjusted_width# 设置样式from openpyxl.styles import PatternFill, Font, Alignment, Border, Side# 定义样式header_fill = PatternFill(start_color='CCE5FF', end_color='CCE5FF', fill_type='solid')header_font = Font(bold=True)center_aligned = Alignment(horizontal='center', vertical='center')border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))# 应用表头样式for cell in worksheet[1]:cell.fill = header_fillcell.font = header_fontcell.alignment = center_alignedcell.border = border# 应用数据行样式for row in worksheet.iter_rows(min_row=2):for cell in row:cell.alignment = center_alignedcell.border = bordermessagebox.showinfo("成功", "模板导出成功！\n请按照模板格式准备数据后再进行导。")except Exception as e:messagebox.showerror("错误", f"导出模板时出错：{str(e)}")def set_default_values(self):"""设置默认值"""# 清空所有输入框self.clear_fields()# 只设置日期和折扣率的默认值today = datetime.now().strftime('%Y-%m-%d')self.entries['order_date'].insert(0, today)  # 默认日期为今天self.entries['discount'].insert(0, '100')    # 默认折扣率为100%# 生成新的单据编号cursor = self.conn.cursor()cursor.execute('''SELECT MAX(order_number) FROM orders WHERE order_number LIKE ?''', [f'XSD{today.replace("-", "")}%'])last_number = cursor.fetchone()[0]if last_number:try:# 从最后一个单号提取序号并加1seq = int(last_number[-3:]) + 1new_number = f'XSD{today.replace("-", "")}{seq:03d}'except ValueError:new_number = f'XSD{today.replace("-", "")}001'else:new_number = f'XSD{today.replace("-", "")}001'self.entries['order_number'].insert(0, new_number)  # 设置新单据编号def update_order_groups(self):"""更新订单分类显示"""cursor = self.conn.cursor()cursor.execute('''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders ORDER BY order_number DESC''')# 清空现有数据self.group_tree.delete(*self.group_tree.get_children())total_sum = 0# 插入新数据for row in cursor.fetchall():formatted_row = list(row[:8])  # 只取前8列显示# 格式化数字列formatted_row[4] = f"{row[4]:.2f}"  # 数量formatted_row[5] = f"¥{row[5]:.2f}"  # 原价formatted_row[6] = f"¥{row[6]:.2f}"  # 金额self.group_tree.insert("", "end", values=formatted_row)total_sum = row[8]  # 获取合计金额# 更新合计标签self.total_label.config(text=f"合计金额: ¥{total_sum:,.2f}")def on_group_select(self, event):"""当选择订单分类时的处理"""selected = self.group_tree.selection()if not selected:return# 获取选中的单据编号order_number = self.group_tree.item(selected[0])['values'][0]# 在主表格中查找并选中对应的记录for item in self.tree.get_children():if self.tree.item(item)['values'][1] == order_number:  # 假设单据编号是第二列self.tree.selection_set(item)self.tree.see(item)  # 确保选中的项可见breakdef filter_orders(self):"""根据筛选条件过滤订单"""order_number = self.order_number_filter.get().strip()customer = self.customer_filter.get().strip()cursor = self.conn.cursor()# 构建查询条件query = '''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders WHERE 1=1'''params = []if order_number:query += " AND order_number LIKE ?"params.append(f"%{order_number}%")if customer:query += " AND customer LIKE ?"params.append(f"%{customer}%")query += " ORDER BY order_number DESC"cursor.execute(query, params)# 清空现有数据self.group_tree.delete(*self.group_tree.get_children())total_sum = 0# 插入新数据for row in cursor.fetchall():formatted_row = list(row[:8])  # 只取前8列显示# 格式化数字列formatted_row[4] = f"{row[4]:.2f}"  # 数量formatted_row[5] = f"¥{row[5]:.2f}"  # 原价formatted_row[6] = f"¥{row[6]:.2f}"  # 金额self.group_tree.insert("", "end", values=formatted_row)total_sum = row[8]  # 获取合计金额# 更新合计标签self.total_label.config(text=f"合计金额: ¥{total_sum:,.2f}")def reset_filter(self):"""重置筛选条件"""self.order_number_filter.delete(0, tk.END)self.customer_filter.delete(0, tk.END)self.update_order_groups()def update_order_number_list(self, event=None):"""更新单据编号下拉列表"""search_text = self.order_number_filter.get().strip()cursor = self.conn.cursor()if search_text:cursor.execute('''SELECT DISTINCT order_number FROM orders WHERE order_number LIKE ? ORDER BY order_number DESC''', [f'%{search_text}%'])else:cursor.execute('''SELECT DISTINCT order_number FROM orders ORDER BY order_number DESC''')order_numbers = [row[0] for row in cursor.fetchall()]if order_numbers:self.order_number_filter['values'] = order_numbersif search_text:self.order_number_filter.event_generate('<Down>')def update_customer_list(self, event=None):"""更新客户名称下拉列表"""search_text = self.customer_filter.get().strip()cursor = self.conn.cursor()if search_text:cursor.execute('''SELECT DISTINCT customer FROM orders WHERE customer LIKE ? ORDER BY customer''', [f'%{search_text}%'])else:cursor.execute('''SELECT DISTINCT customer FROM orders ORDER BY customer''')customers = [row[0] for row in cursor.fetchall()]if customers:self.customer_filter['values'] = customersif search_text:self.customer_filter.event_generate('<Down>')def update_filter_lists(self):"""更新所有筛选下拉列表"""cursor = self.conn.cursor()# 更新单据编号列表cursor.execute('SELECT DISTINCT order_number FROM orders ORDER BY order_number DESC')self.order_number_filter['values'] = [row[0] for row in cursor.fetchall()]# 更新客户名称列表cursor.execute('SELECT DISTINCT customer FROM orders ORDER BY customer')self.customer_filter['values'] = [row[0] for row in cursor.fetchall()]def print_filtered_data(self):"""打印筛选后的数据"""try:# 获取当前筛选条件下的数据order_number = self.order_number_filter.get().strip()customer = self.customer_filter.get().strip()# 构建查询条件query = '''SELECT order_number, customer, product, unit, quantity, price, total, remarks,SUM(total) OVER () as total_sumFROM orders WHERE 1=1'''params = []if order_number:query += " AND order_number LIKE ?"params.append(f"%{order_number}%")if customer:query += " AND customer LIKE ?"params.append(f"%{customer}%")query += " ORDER BY order_number DESC"cursor = self.conn.cursor()cursor.execute(query, params)rows = cursor.fetchall()if not rows:messagebox.showinfo("提示", "没有数据可打印")return# 生成HTML内容html_content = f"""<!DOCTYPE html><html><head><meta charset="utf-8"><title>订单查询结果</title><style>body {{ font-family: SimSun, serif; }}table {{ border-collapse: collapse; width: 100%; margin-top: 10px; }}th, td {{ border: 1px solid black; padding: 8px; text-align: center; }}th {{ background-color: #f2f2f2; }}.total {{ text-align: right;padding: 10px;font-weight: bold;}}.header-info {{margin: 10px 0;padding: 10px;border: 1px solid #ddd;background-color: #f9f9f9;}}.header-info p {{margin: 5px 0;}}@media print {{.no-print {{ display: none; }}body {{ margin: 0; }}table {{ page-break-inside: auto; }}tr {{ page-break-inside: avoid; }}}}</style></head><body><h2 style="text-align: center;">订单查询结果</h2><div class="header-info"><p><strong>单据编号：</strong>{order_number if order_number else "全部"}</p><p><strong>客户名称：</strong>{customer if customer else "全部"}</p><p><strong>��印时间：</strong>{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p></div><table><tr><th>品名规格</th><th>单位</th><th>数量</th><th>原价</th><th>金额</th><th>备注</th></tr>"""# 添加数据行for row in rows:html_content += f"""<tr><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]:.2f}</td><td>¥{row[5]:.2f}</td><td>¥{row[6]:.2f}</td><td>{row[7]}</td></tr>"""# 添加合计行total_sum = rows[0][8] if rows else 0html_content += f"""</table><div class="total">合计金额: ¥{total_sum:,.2f}</div><div class="no-print" style="margin-top: 20px; text-align: center;"><button onclick="window.print()">打印</button></div></body></html>"""# 保存HTML文件temp_file = "订单查询结果.html"with open(temp_file, "w", encoding="utf-8") as f:f.write(html_content)# 在默认浏览器中打开HTML文件import webbrowserwebbrowser.open(temp_file)except Exception as e:messagebox.showerror("错误", f"打印过程中出错：{str(e)}")if __name__ == "__main__":# 显示登录窗口login = LoginWindow()if login.run():  # 只有在登录成功时才继续root = tk.Tk()app = OrderSystem(root)root.mainloop()else:sys.exit(0)  # 登录失败或关闭登录窗口时退出程序

多个文件打包成EXE

我可以帮你使用 PyInstaller 将这两个 Python 文件打包成一个单独的 exe 文件。以下是具体步骤：

1. 首先安装 PyInstaller：
pip install pyinstaller

2. 创建一个 spec 文件，命名为 `zhi.spec`：

# -*- mode: python ; coding: utf-8 -*-block_cipher = Nonea = Analysis(['zhi.py'],  # 主程序文件pathex=[],binaries=[],datas=[('login.py', '.'), ('config.json', '.')],  # 添加其他需要的文件hiddenimports=[],hookspath=[],hooksconfig={},runtime_hooks=[],excludes=[],win_no_prefer_redirects=False,win_private_assemblies=False,cipher=block_cipher,noarchive=False,
)pyz = PYZ(a.pure, a.zipped_data, cipher=block_cipher)exe = EXE(pyz,a.scripts,a.binaries,a.zipfiles,a.datas,[],name='订单记录系统',  # exe文件名debug=False,bootloader_ignore_signals=False,strip=False,upx=True,upx_exclude=[],runtime_tmpdir=None,console=False,  # 设置为False不显示控制台窗口disable_windowed_traceback=False,target_arch=None,codesign_identity=None,entitlements_file=None,icon='icon.ico',  # 如果有图标文件的话
)