26.1 项目目标

在第25章的基础上，我们希望能够：

1. 导出数据到CSV文件（通用，Excel可打开）
2. 导出数据到Excel文件（.xlsx），支持更好的表格样式
3. 支持导出当前月份或所有数据

26.2 新增功能按钮

在操作按钮区新增：

• 导出CSV
• 导出Excel

26.3 核心代码

下面基于第25章的 BudgetApp，新增数据导出功能：

import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
from datetime import datetime
import matplotlib.pyplot as plt
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
import csv
import openpyxl   # 用于生成Excel

class BudgetApp:
    def __init__(self, root):
        self.root = root
        self.root.title("记账本（数据导出版）")
        self.root.geometry("900x600")

        # 数据库
        self.conn = sqlite3.connect("budget.db")
        self.cursor = self.conn.cursor()
        self.create_table()

        # 输入区
        frame_input = tk.Frame(self.root)
        frame_input.pack(pady=10)

        tk.Label(frame_input, text="日期(YYYY-MM-DD):").grid(row=0, column=0, padx=5)
        self.entry_date = tk.Entry(frame_input, width=12)
        self.entry_date.grid(row=0, column=1, padx=5)
        self.entry_date.insert(0, datetime.now().strftime("%Y-%m-%d"))

        tk.Label(frame_input, text="收支类型:").grid(row=0, column=2, padx=5)
        self.combo_type = ttk.Combobox(frame_input, values=["收入", "支出"], width=8)
        self.combo_type.grid(row=0, column=3, padx=5)
        self.combo_type.current(0)
        self.combo_type.bind("<<ComboboxSelected>>", self.update_categories)

        tk.Label(frame_input, text="类别:").grid(row=0, column=4, padx=5)
        self.combo_category = ttk.Combobox(frame_input, values=[], width=10)
        self.combo_category.grid(row=0, column=5, padx=5)
        self.update_categories()

        tk.Label(frame_input, text="金额:").grid(row=0, column=6, padx=5)
        self.entry_amount = tk.Entry(frame_input, width=10)
        self.entry_amount.grid(row=0, column=7, padx=5)

        tk.Label(frame_input, text="备注:").grid(row=0, column=8, padx=5)
        self.entry_note = tk.Entry(frame_input, width=15)
        self.entry_note.grid(row=0, column=9, padx=5)

        tk.Button(frame_input, text="添加记录", command=self.add_record).grid(row=0, column=10, padx=10)

        # 月份筛选区
        frame_month = tk.Frame(self.root)
        frame_month.pack(pady=5)

        tk.Label(frame_month, text="选择月份:").pack(side=tk.LEFT, padx=5)
        self.combo_month = ttk.Combobox(frame_month, values=self.get_months(), width=10)
        self.combo_month.pack(side=tk.LEFT, padx=5)
        self.combo_month.set(datetime.now().strftime("%Y-%m"))
        tk.Button(frame_month, text="查询", command=self.load_records).pack(side=tk.LEFT, padx=10)

        # 表格
        self.tree = ttk.Treeview(self.root, columns=("id", "date", "type", "category", "amount", "note"), show="headings")
        for col in ("id", "date", "type", "category", "amount", "note"):
            self.tree.heading(col, text=col.upper())
        self.tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # 操作按钮
        frame_btn = tk.Frame(self.root)
        frame_btn.pack(pady=5)

        tk.Button(frame_btn, text="删除记录", command=self.delete_record).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="统计收支", command=self.show_summary).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="收支饼图", command=self.show_pie_chart).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="收支趋势图", command=self.show_line_chart).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="类别统计图", command=self.show_category_chart).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="导出CSV", command=self.export_csv).pack(side=tk.LEFT, padx=10)
        tk.Button(frame_btn, text="导出Excel", command=self.export_excel).pack(side=tk.LEFT, padx=10)

        # 初始加载
        self.load_records()

    def create_table(self):
        self.cursor.execute("""
        CREATE TABLE IF NOT EXISTS records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            type TEXT,
            category TEXT,
            amount REAL,
            note TEXT
        )
        """)
        self.conn.commit()

    def update_categories(self, event=None):
        if self.combo_type.get() == "收入":
            categories = ["工资", "奖金", "理财", "其他"]
        else:
            categories = ["餐饮", "交通", "购物", "住房", "娱乐", "医疗", "其他"]
        self.combo_category["values"] = categories
        self.combo_category.current(0)

    def add_record(self):
        try:
            amount = float(self.entry_amount.get())
        except ValueError:
            messagebox.showerror("错误", "金额必须是数字")
            return

        self.cursor.execute(
            "INSERT INTO records (date, type, category, amount, note) VALUES (?, ?, ?, ?, ?)",
            (self.entry_date.get(), self.combo_type.get(), self.combo_category.get(), amount, self.entry_note.get())
        )
        self.conn.commit()
        self.load_records()
        self.entry_amount.delete(0, tk.END)
        self.entry_note.delete(0, tk.END)

    def get_months(self):
        self.cursor.execute("SELECT DISTINCT substr(date, 1, 7) FROM records ORDER BY date DESC")
        months = [row[0] for row in self.cursor.fetchall()]
        if not months:
            months = [datetime.now().strftime("%Y-%m")]
        return months

    def load_records(self):
        for row in self.tree.get_children():
            self.tree.delete(row)

        month = self.combo_month.get()
        self.cursor.execute("SELECT * FROM records WHERE substr(date,1,7)=? ORDER BY date DESC", (month,))
        for row in self.cursor.fetchall():
            self.tree.insert("", tk.END, values=row)

    def delete_record(self):
        selected = self.tree.selection()
        if not selected:
            messagebox.showwarning("提示", "请选择要删除的记录")
            return
        record_id = self.tree.item(selected[0])["values"][0]
        self.cursor.execute("DELETE FROM records WHERE id=?", (record_id,))
        self.conn.commit()
        self.load_records()

    def show_summary(self):
        month = self.combo_month.get()
        self.cursor.execute("SELECT type, SUM(amount) FROM records WHERE substr(date,1,7)=? GROUP BY type", (month,))
        result = self.cursor.fetchall()

        income = sum(r[1] for r in result if r[0] == "收入")
        expense = sum(r[1] for r in result if r[0] == "支出")

        messagebox.showinfo("统计结果", f"{month} 收支情况：\n总收入: {income} 元\n总支出: {expense} 元\n结余: {income - expense} 元")

    def show_pie_chart(self):
        pass  # 为简化展示，这里保留前章的饼图函数

    def show_line_chart(self):
        pass  # 同上

    def show_category_chart(self):
        pass  # 同上

    # ========== 新增功能 ==========
    def export_csv(self):
        """导出当前月份数据到 CSV"""
        month = self.combo_month.get()
        self.cursor.execute("SELECT * FROM records WHERE substr(date,1,7)=?", (month,))
        records = self.cursor.fetchall()
        if not records:
            messagebox.showwarning("提示", "没有数据可导出")
            return

        file_path = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV文件", "*.csv")])
        if not file_path:
            return

        with open(file_path, "w", newline="", encoding="utf-8-sig") as f:
            writer = csv.writer(f)
            writer.writerow(["ID", "日期", "类型", "类别", "金额", "备注"])
            writer.writerows(records)

        messagebox.showinfo("导出成功", f"数据已导出到 {file_path}")

    def export_excel(self):
        """导出当前月份数据到 Excel"""
        month = self.combo_month.get()
        self.cursor.execute("SELECT * FROM records WHERE substr(date,1,7)=?", (month,))
        records = self.cursor.fetchall()
        if not records:
            messagebox.showwarning("提示", "没有数据可导出")
            return

        file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel文件", "*.xlsx")])
        if not file_path:
            return

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "记账数据"

        ws.append(["ID", "日期", "类型", "类别", "金额", "备注"])
        for row in records:
            ws.append(row)

        # 设置列宽
        for col in ["A", "B", "C", "D", "E", "F"]:
            ws.column_dimensions[col].width = 15

        wb.save(file_path)
        messagebox.showinfo("导出成功", f"数据已导出到 {file_path}")

    def __del__(self):
        self.conn.close()

if __name__ == "__main__":
    root = tk.Tk()
    app = BudgetApp(root)
    root.mainloop()

26.4 功能演示

1. 打开应用 → 选择某个月份
2. 点击 导出CSV → 生成 2025-09.csv
3. 点击 导出Excel → 生成 2025-09.xlsx
4. 用 Excel 打开时，看到整齐的表格数据

26.5 小结

• 学会了用 csv 模块导出 CSV 文件
• 学会了用 openpyxl 生成 Excel 文件并设置列宽
• 增强了记账本的 数据共享与备份 能力