图样：
就可以导入了
上代码
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
class OrderSystem:
def __init__(self, root):
self.root = root
self.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 / 100
self.entries['final_price'].delete(0, tk.END)
self.entries['final_price'].insert(0, f"{final_price:.2f}")
# 计算金额
total = quantity * final_price
self.entries['total'].delete(0, tk.END)
self.entries['total'].insert(0, f"{total:.2f}")
except ValueError:
pass
def 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 False
return True
def save_order(self):
"""保存订单数据"""
if not self.validate_data():
return
try:
# 获取所有输入值
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.0
except ValueError:
value = 0.0
elif 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)}")
return
except Exception as e:
messagebox.showerror("错误", f"保存过程中出错：{str(e)}")
return
self.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()
return
cursor = 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("提示", "请先选择一条记录")
return
item = 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("提示", "请先选择一条记录")
return
if 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 = [
'单据日期', '单据编号', '客户名称', '品名规格',
'单位', '数量', '原价', '单行折扣率(%)', '折后价',
'金额', '备注', '整单折扣率(%)', '折后金额', '运费',
'本单已收', '结算账户', '说明', '营业员'
]
# 创建DataFrame
df = 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 t
最低0.47元/天 解锁文章
扫一扫
914
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
