python开发箱号批量查询关联SN号码的程序

# 需要导入的包
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import pyodbc
import pandas as pd

# 创建GUI框
class CartonQueryApp:
  def __init__(self, root):
    self.root = root
    self.root.title("箱号查询工具 v1.0")
    self.root.geometry("800x600")

    # 数据库配置（需要修改以下信息）
    self.server = ''  # 修改为你的服务器地址
    self.database = ''  # 修改为你的数据库名
    self.username = ''
    self.password = ''

    self.create_widgets()

def create_widgets(self):
    # 主容器
    main_frame = ttk.Frame(self.root)
    main_frame.pack(fill='both', expand=True, padx=10, pady=10)

    # 输入区域
    input_frame = ttk.LabelFrame(main_frame, text="批量输入箱号（每行一个）")
    input_frame.pack(fill='x', pady=5)

    self.txt_input = tk.Text(input_frame, height=10, font=('微软雅黑', 10))
    self.txt_input.pack(fill='both', expand=True, padx=5, pady=5)

    # 按钮区域
    btn_frame = ttk.Frame(main_frame)
    btn_frame.pack(fill='x', pady=5)

    ttk.Button(btn_frame, text="清空输入", command=self.clear_input).pack(side='left', padx=5)
    ttk.Button(btn_frame, text="执行查询", command=self.execute_query).pack(side='left', padx=5)
    ttk.Button(btn_frame, text="导出Excel", command=self.export_excel).pack(side='left', padx=5)

    # 结果表格区域
    table_frame = ttk.LabelFrame(main_frame, text="查询结果")
    table_frame.pack(fill='both', expand=True, pady=5)

    # 创建Treeview和滚动条
    self.create_result_table(table_frame)

def create_result_table(self, parent):
    """创建带滚动条的表格"""
    # 表格容器
    container = ttk.Frame(parent)
    container.pack(fill='both', expand=True, padx=5, pady=5)

    # 创建Treeview
    self.tree = ttk.Treeview(
        container,
        columns=('箱号', '工单条码', '客户条码'),
        show='headings',
        height=15,
        selectmode='browse'
    )

    # 配置表头
    self.tree.heading('箱号', text='箱号', anchor='w')
    self.tree.heading('工单条码', text='工单条码', anchor='w')
    self.tree.heading('客户条码', text='客户条码', anchor='w')

    # 配置列宽
    self.tree.column('箱号', width=150, minwidth=100)
    self.tree.column('工单条码', width=250, minwidth=150)
    self.tree.column('客户条码', width=250, minwidth=150)

    # 垂直滚动条
    vsb = ttk.Scrollbar(container, orient="vertical", command=self.tree.yview)
    self.tree.configure(yscrollcommand=vsb.set)

    # 布局
    self.tree.grid(row=0, column=0, sticky='nsew')
    vsb.grid(row=0, column=1, sticky='ns')

    # 配置容器网格
    container.grid_columnconfigure(0, weight=1)
    container.grid_rowconfigure(0, weight=1)

def get_connection(self):
    """创建数据库连接"""
    conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};' \
               f'SERVER={self.server};DATABASE={self.database};' \
               f'UID={self.username};PWD={self.password}'
    try:
        return pyodbc.connect(conn_str)
    except Exception as e:
        messagebox.showerror("数据库错误", f"连接失败: {str(e)}")
        return None

def clear_input(self):
    """清空输入框"""
    self.txt_input.delete('1.0', tk.END)

def execute_query(self):
    """执行查询操作"""
    # 获取输入的箱号列表
    cartons = self.txt_input.get('1.0', tk.END).strip().split('\n')
    cartons = [c.strip() for c in cartons if c.strip()]

    if not cartons:
        messagebox.showwarning("输入错误", "请输入至少一个箱号")
        return

    # 建立数据库连接
    conn = self.get_connection()
    if not conn: return

    try:
        # 参数化查询
        sql = f"""
            SELECT 
                CartonNo AS '箱号',
                SN AS '工单条码',
                ParentSn as '客户条码'
            FROM Prod_Unit
            WHERE CartonNo IN ({','.join(['?'] * len(cartons))})
        """

        df = pd.read_sql(sql, conn, params=cartons)

        # 清空旧数据
        self.tree.delete(*self.tree.get_children())

        # 插入新数据
        if not df.empty:
            for _, row in df.iterrows():
                self.tree.insert('', 'end', values=tuple(row))
            msg = f"找到 {len(df)} 条记录"
        else:
            msg = "未找到匹配数据"

        # 自动调整列宽
        self.auto_resize_columns()
        messagebox.showinfo("查询完成", msg)

    except Exception as e:
        messagebox.showerror("查询错误", str(e))
    finally:
        conn.close()

def auto_resize_columns(self):
    """自动调整列宽"""
    for col in self.tree['columns']:
        max_width = max(
            [len(str(self.tree.set(item, col))) * 10 for item in self.tree.get_children()] or [100]
        )
        self.tree.column(col, width=max_width)

def export_excel(self):
    """导出到Excel"""
    if not self.tree.get_children():
        messagebox.showwarning("导出错误", "没有可导出的数据")
        return

    # 获取保存路径
    filepath = filedialog.asksaveasfilename(
        defaultextension='.xlsx',
        filetypes=[("Excel Files", "*.xlsx"), ("All Files", "*.*")]
    )
    if not filepath: return

    # 提取数据
    data = []
    for item in self.tree.get_children():
        data.append(self.tree.item(item)['values'])

    # 创建DataFrame并保存
    df = pd.DataFrame(data, columns=['箱号', '工单条码', '客户条码'])
    try:
        df.to_excel(filepath, index=False, engine='openpyxl')
        messagebox.showinfo("导出成功", f"文件已保存至：\n{filepath}")
    except Exception as e:
        messagebox.showerror("导出失败", str(e))


if __name__ == '__main__':
root = tk.Tk()
app = CartonQueryApp(root)
root.mainloop()