# -*- coding: utf-8 -*- """ generate_expense_sheet_en.py ============================= One-shot generator for "an expense sheet .xlsx that does the math for you". Companion tutorial: B1 "Build an Expense Sheet in 10 Minutes with AI" (English edition). This script does the three key things from the tutorial: 1) Write a worksheet with openpyxl; 2) Convert the data range into an Excel Table (the equivalent of Ctrl+T); 3) Add a Total Row to the table, summing the Amount column automatically (so even rows you add later will be included). One-time install: pip install openpyxl Quickest usage (defaults): python generate_expense_sheet_en.py Custom paths: python generate_expense_sheet_en.py --input sample_data.csv --output expense_2026Q3.xlsx """ # ↑↑↑ ↑↑↑ ↑↑↑ ↑↑↑ ↑↑↑ # English: this script is saved in UTF-8 encoding # 中文: 本脚本使用 UTF-8 编码保存 import argparse # command-line argument parsing / 命令行参数解析 import csv # reading sample CSV / 读取示例 CSV import sys # exit codes / 退出码 from pathlib import Path # path handling / 路径处理 try: # core objects / 核心对象 from openpyxl import Workbook # new workbook / 新建工作簿 from openpyxl.styles import Font, PatternFill, Alignment # styling / 样式 from openpyxl.worksheet.table import Table, TableStyleInfo # Excel Table / 超级表 except ImportError: # friendly hint if not installed / 没装 openpyxl 就给出友好提示 sys.stderr.write( "[Error] openpyxl not found. Run: pip install openpyxl\n" "[错误] 没找到 openpyxl，请先运行：pip install openpyxl\n" ) sys.exit(1) # ============================================================ # Configuration / 配置区 # ============================================================ # Field definitions: column name -> column width # 字段定义：列名 -> 列宽 FIELDS = [ ("Serial", 8), # 序号 ("Date", 14), # 日期 ("Category", 12), # 类别 ("Amount", 12), # 金额 ("Description", 36), # 说明 ("InvoiceNo", 22), # 发票号 ("Submitter", 12), # 报销人 ] # Column index (1-based) where the Amount lives. The Total Row will sum this column. # 金额列所在的列号（从 1 开始）。总计行将对这一列求和。 AMOUNT_COL_INDEX = 4 # Amount is the 4th item above / 字段定义里"金额"是第 4 项 # Display name of the Excel Table (shows up in Table Tools). # 超级表的"显示名"，在 Excel 里会出现在"表格工具"中。 TABLE_NAME = "ExpenseSheet" # Built-in table style name. Pick from TableStyleLight1 ~ TableStyleDark11. # 表格样式（内置样式名）。可换成 TableStyleLight1 ~ TableStyleDark11。 TABLE_STYLE = "TableStyleMedium2" # Default paths / 默认文件路径 DEFAULT_INPUT = Path(__file__).parent / "sample_data.csv" # default CSV in this folder DEFAULT_OUTPUT = Path(__file__).parent / "expense_sheet.xlsx" # default xlsx in this folder # ============================================================ # Read sample data / 读取示例数据 # ============================================================ def read_rows(csv_path: Path): """ Read CSV and return list[dict], one dict per row. 读取 CSV 文件，返回 list[dict]，每行一个字典（键是字段名）。 """ # Open CSV with UTF-8 (also safe on Windows) # 打开 CSV，UTF-8 编码（Windows 下也安全） with csv_path.open("r", encoding="utf-8-sig", newline="") as f: reader = csv.DictReader(f) rows = [dict(r) for r in reader] # read all / 全部读出来 return rows # ============================================================ # Write worksheet / 写入工作表 # ============================================================ def write_rows(ws, rows): """ Write list[dict] to worksheet, header on row 1, data from row 2. 把 list[dict] 写入工作表，第一行表头，第二行起数据。 """ # ---- Header / 表头 ---- header = [name for name, _ in FIELDS] # take field names / 取字段名 for col_idx, name in enumerate(header, start=1): cell = ws.cell(row=1, column=col_idx, value=name) # bold white text on dark fill / 表头加粗、白字、深灰底 cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="305496") cell.alignment = Alignment(horizontal="center", vertical="center") # ---- Data / 数据 ---- for r_offset, row in enumerate(rows, start=2): # start from row 2 / 从第 2 行开始 for c_idx, (name, _) in enumerate(FIELDS, start=1): value = row.get(name, "") # empty string if missing / 取值，没有就填空字符串 ws.cell(row=r_offset, column=c_idx, value=value) # ---- Column widths / 列宽 ---- for c_idx, (_, width) in enumerate(FIELDS, start=1): ws.column_dimensions[ws.cell(row=1, column=c_idx).column_letter].width = width def format_money(ws, last_data_row): """ Apply Currency format to the Amount column. 给金额列设"货币"格式，避免出现 553 / ¥553.00 两样。 """ amount_col_letter = ws.cell(row=2, column=AMOUNT_COL_INDEX).column_letter # header through last data row / 选整列：表头到数据最后一行 for r in range(2, last_data_row + 1): ws[f"{amount_col_letter}{r}"].number_format = '"¥"#,##0.00' # ============================================================ # Apply Excel Table + Total Row / 套"超级表" + 总计行 # ============================================================ def apply_table(ws, last_data_row): """ Convert data range to an Excel Table and enable Total Row summing Amount. 把数据区域转成超级表，并启用"总计行"，对金额列自动求和。 """ last_col_letter = ws.cell(row=1, column=len(FIELDS)).column_letter ref = f"A1:{last_col_letter}{last_data_row}" # table reference / 表格引用范围 # create Table object / 创建超级表对象 tbl = Table(displayName=TABLE_NAME, ref=ref) # table style / 表格样式 tbl.tableStyleInfo = TableStyleInfo( name=TABLE_STYLE, showFirstColumn=False, showLastColumn=False, showRowStripes=True, # row stripes / 隔行底色 showColumnStripes=False, ) # ---- KEY: turn on the Total Row ---- # ---- 关键：开启总计行 ---- tbl.totalsRowShown = True # show a totals row / 在表末尾多显示一行 tbl.totalsRowCount = 1 # one totals row / 总计行就一行 # Each column needs a totals function (None = leave as label). # 每列都需要配置一个 totals 函数（None 表示该列不出现在总计行公式中）。 field_names = [name for name, _ in FIELDS] totals_function = [] totals_label = [] for name in field_names: if name == "Amount": totals_function.append("sum") # Amount column uses SUM totals_label.append("Total/总计") # label shown on the totals row else: totals_function.append("custom") totals_label.append("") # Order must match the column order. # openpyxl 要求 totalsRowFunction / totalsRowLabel 与字段顺序一一对应。 tbl.totalsRowFunction = totals_function tbl.totalsRowLabel = totals_label # Actually add the table to the worksheet / 真正把表加到工作表 ws.add_table(tbl) # ============================================================ # Main flow / 主流程 # ============================================================ def build_expense_sheet(input_path: Path, output_path: Path): """ Main flow: read CSV → write sheet → apply Table+Total Row → save. 主流程：读取 CSV → 写表 → 套超级表 + 总计行 → 保存。 """ rows = read_rows(input_path) if not rows: # friendly message when CSV is empty / 没有数据也要提示 sys.stderr.write(f"[Warning] CSV is empty: {input_path}\n[警告] CSV 是空的：{input_path}\n") return False wb = Workbook() ws = wb.active ws.title = "Expense" # sheet tab name / 工作表标签名 write_rows(ws, rows) # write header + data format_money(ws, last_data_row=1 + len(rows)) # currency format on Amount apply_table(ws, last_data_row=1 + len(rows)) # table + total row # ---- Save / 保存 ---- output_path.parent.mkdir(parents=True, exist_ok=True) # ensure folder exists wb.save(output_path) print(f"[Done] Generated {len(rows)} expense rows → {output_path}") print(f"[Next] Open {output_path.name} in Excel; the Amount column uses") print(f" currency format and the Total Row sums it automatically.") print(f"[下一步] 用 Excel 打开 {output_path.name}，金额列已设货币，") print(f" 末尾的「总计/Total」行会自动对金额列求和。") return True def parse_args(): """command-line arguments / 命令行参数""" p = argparse.ArgumentParser( description="One-shot generator for an expense .xlsx with a Total Row / 一键生成带「总计行」的报销表 .xlsx" ) p.add_argument("--input", "-i", type=Path, default=DEFAULT_INPUT, help="input CSV / 输入 CSV") p.add_argument("--output", "-o", type=Path, default=DEFAULT_OUTPUT, help="output xlsx / 输出 xlsx") return p.parse_args() def main(): args = parse_args() if not args.input.exists(): sys.stderr.write(f"[Error] Input file not found: {args.input}\n") sys.exit(2) ok = build_expense_sheet(args.input, args.output) sys.exit(0 if ok else 3) if __name__ == "__main__": main()