# 🧾 B01 | Build an Expense Sheet in 10 Minutes with AI — Companion Assets

> **Series**: AI-Powered Office: From Beginner to Expert
> **Article**: B1 (Track 1 Spreadsheets / Level 1 Basics)
> **Title (EN)**: Build an Expense Sheet in 10 Minutes with AI
> **Title (中文)**: AI 帮你 10 分钟做完一张报销表

---

## 1. Who Is This For? What Problem Does It Solve?

This asset is for **anyone who dreads Excel and breaks out in a sweat at month-end expense time**: no need to memorize functions, no need to type fast. The script in this folder lets you **one-shot generate an Excel expense sheet that "does the math for you and never misses a row"** — you just drop your expenses (Date, Category, Amount, Description, InvoiceNo, Submitter) into the sample CSV, run the Python script, and you get a finished `.xlsx` with currency formatting, an Excel Table, and an automatic Total Row. No more "the bottom line is always wrong" or "the formatting looks embarrassing."

If you've already read the main tutorial `B1_报销表_双语.md` (English section), this folder is the **hands-on version** — what you get out of the script is exactly what the screenshots in the tutorial should show.

---

## 2. How to Use

### Step 1: Set up your environment (once, ~5 min)

Make sure you have **Python 3.8+** and **openpyxl**:

```bash
# Check Python (works on Windows / macOS / Linux)
python --version

# Install openpyxl (third-party library for .xlsx generation)
pip install openpyxl
```

> 💡 If you can't install Python on a corporate machine, try Anaconda + Spyder / VS Code / PyCharm to run the `.py` files in this folder.

### Step 2: Edit the sample data

Open `示例数据_sample.csv` in this folder and replace it with your real expense rows:

```csv
序号,日期,类别,金额,说明,发票号,报销人
1,2026-08-02,机票,1280,北京-上海往返机票,INV-2026-0802-A01,张李
2,2026-08-02,酒店,640,差旅住宿一晚,INV-2026-0802-H01,张李
…
```

Field meanings are listed in the **Field Reference** section below. As long as you keep the column order, **you don't need to touch the Python script**.

### Step 3: Run the script

```bash
# Enter this folder
cd companion-assets/B01_ExpenseSheet

# Simplest usage (default sample data + default file name)
python generate_expense_sheet_en.py

# Custom paths
python generate_expense_sheet_en.py --input my_august.csv --output expense_august_2026.xlsx
```

After it runs, you'll see a new `expense_sheet.xlsx` in this folder. Open it in Excel:

- Header row is bold with a dark fill; the data range is wrapped in an Excel Table;
- The Amount column uses currency format (`¥553.00` instead of `553`);
- A `Total/总计` row appears at the bottom and **automatically sums** the Amount column; rows you add later are picked up too.

### Step 4: Add rows / replace data on your own

- Just **add a new row** in the table and fill in Date / Category / Amount / Description — the Total Row keeps following the table, no manual edits needed;
- Don't like the style? Open `generate_expense_sheet_en.py` and change `TABLE_STYLE = "TableStyleMedium2"` (options: `TableStyleLight1`~`TableStyleDark11`);
- Need an `=D2*E2` formula (Unit Price × Quantity)? The default fields don't include those, so check the FAQ #7 for how to extend.

---

## 3. File Listing

| File | Purpose |
|------|---------|
| `B01_ExpenseSheet_EnglishREADME.md` | This file (English instructions) |
| `B01_报销表_中文README.md` | Chinese version of this README (same structure) |
| `generate_expense_sheet_en.py` | English-named Python script; comments are English-led with Chinese support |
| `生成报销表_zh.py` | Chinese-named Python script; comments are Chinese-led with English support |
| `示例数据_sample.csv` | 5-row sample data (flight / hotel / taxi / meal / office supplies) |

---

## 4. Field Reference (`示例数据_sample.csv`)

| # | Field Name (CSV header) | English | Type | Example | Required | Notes |
|---|------------------------|---------|------|---------|----------|-------|
| 1 | 序号 | Serial | int | `1` | ✅ | Start from 1, hand-numbered |
| 2 | 日期 | Date | date | `2026-08-02` | ✅ | When the expense happened; `YYYY-MM-DD` recommended |
| 3 | 类别 | Category | text | `机票` (Flight) | ✅ | E.g., flight, hotel, taxi, meal, office supplies |
| 4 | 金额 | Amount | number | `1280` | ✅ | Per-row amount in your local currency; formatted by script |
| 5 | 说明 | Description | text | `北京-上海往返机票` | ✅ | One short sentence explaining the expense |
| 6 | 发票号 | InvoiceNo | text | `INV-2026-0802-A01` | ✅ | Matches the receipt / invoice for audit |
| 7 | 报销人 | Submitter | text | `张李` | ✅ | Person who paid and is claiming reimbursement |

> Want to add fields your company requires (`Department`, `EmployeeID`, `ProjectCode`)? Just add the column header to the CSV **and** append a tuple to `FIELDS` in the script (with a column width). If the Amount column moves, also update `AMOUNT_COL_INDEX`.

---

## 5. Real, Working Code (Core Snippet)

This is the "Excel Table + Total Row" triplet from `generate_expense_sheet_en.py` — the code equivalent of the tutorial's "Step 4: convert to Excel Table and check the Total Row":

```python
# -*- coding: utf-8 -*-
import argparse, csv, sys
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.worksheet.table import Table, TableStyleInfo

# Field definitions / 字段定义
FIELDS = [
    ("Serial",      8),
    ("Date",        14),
    ("Category",    12),
    ("Amount",      12),  # Amount column / 金额列
    ("Description", 36),
    ("InvoiceNo",   22),
    ("Submitter",   12),
]
AMOUNT_COL_INDEX = 4  # "Amount" is the 4th column / "金额"在第 4 列
TABLE_NAME = "ExpenseSheet"
TABLE_STYLE = "TableStyleMedium2"

# ---- Write header and data ----
wb = Workbook()
ws = wb.active
ws.title = "Expense"

for c_idx, (name, width) in enumerate(FIELDS, start=1):
    cell = ws.cell(row=1, column=c_idx, value=name)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="305496")
    cell.alignment = Alignment(horizontal="center", vertical="center")
    ws.column_dimensions[cell.column_letter].width = width

# 'rows' is a list[dict] loaded from the CSV
for r_offset, row in enumerate(rows, start=2):
    for c_idx, (name, _) in enumerate(FIELDS, start=1):
        ws.cell(row=r_offset, column=c_idx, value=row.get(name, ""))

# Currency format on the Amount column
for r in range(2, len(rows) + 2):
    ws.cell(row=r, column=AMOUNT_COL_INDEX).number_format = '"¥"#,##0.00'

# ---- KEY: Excel Table + Total Row ----
last_col_letter = ws.cell(row=1, column=len(FIELDS)).column_letter
ref = f"A1:{last_col_letter}{len(rows) + 1}"

tbl = Table(displayName=TABLE_NAME, ref=ref)
tbl.tableStyleInfo = TableStyleInfo(
    name=TABLE_STYLE, showRowStripes=True
)
tbl.totalsRowShown = True                 # ← Key 1: turn on the Total Row
tbl.totalsRowCount = 1                    # ← Key 2: only one totals row
tbl.totalsRowFunction = [                 # ← Key 3: per-column totals function
    "custom", "custom", "custom", "sum",  # "Amount" column uses SUM
    "custom", "custom", "custom",
]
tbl.totalsRowLabel = ["", "", "", "Total/总计", "", "", ""]

ws.add_table(tbl)
wb.save("expense_sheet.xlsx")
```

> This snippet is essentially the code version of the tutorial's "Step 4" — you **don't need to press `Ctrl+T` by hand**; the script does it for you.

---

## 6. FAQ / Pitfall Guide

1. **`ModuleNotFoundError: No module named 'openpyxl'`**
   openpyxl isn't installed. Run `pip install openpyxl`. If you're on a corporate machine, try a Chinese mirror (`pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple`) or an offline `.whl`.

2. **The Amount column shows `553` instead of `¥553.00`**
   Make sure `format_money(...)` is being called. In rare cases Excel shows `$553.00` because of the Windows regional setting — that's a locale issue, not a bug, and doesn't affect the calculation.

3. **The Total Row's Amount cell is blank instead of summing**
   This is a `totalsRowFunction` configuration problem: it must be the lowercase string `"sum"` (not `"SUM"` or `None`); the list length must **equal the column count**; and the position of "Amount" must be correct.

4. **Chinese characters look garbled when opening the CSV in Excel**
   The script reads with `encoding="utf-8-sig"`, which strips the BOM. If you save the CSV in Excel as `ANSI / GBK`, the script will see garbled text — save it as `UTF-8` or `CSV UTF-8` instead.

5. **Added a `Department / EmployeeID` column to the CSV but it didn't show up in the .xlsx**
   You must change both the CSV **and** the script: CSV adds a header; the script's `FIELDS` appends a tuple with a column width. If a new column participates in the totals, also extend `totalsRowFunction / totalsRowLabel` to match.

6. **Excel says "file is corrupt" when opening the .xlsx**
   Almost always an outdated openpyxl. Run `pip install -U openpyxl` to upgrade.

7. **You want the `=D2*E2` (Unit Price × Quantity) version**
   The default fields don't include `UnitPrice / Quantity`, so no formula is written. To add it: change `FIELDS` to include them; in `write_rows`, write the formula `"=B%d*C%d" % (r_offset, r_offset)` for the Amount cell (Excel will calculate as soon as the file opens); keep `format_money` running on the Amount column as usual.

---

## 7. Where to Go Next

- **B2 Clean Messy Data in One Click**: Coworkers often send messy expense sheets (merged cells, blank rows, mixed date formats). This follow-up shows you how to clean them up with AI and then drop them into the script in this folder.
- **A3 Prompt Engineering for Beginners**: Want AI to "add a Department column" or "switch to a Unit-Price / Quantity version" cleanly? Learn the three-element prompt pattern ("field list + sample + output format") and AI stops hallucinating.

> 💬 **Feedback**: If the script fails or Excel shows an error, please keep the output of `python generate_expense_sheet_en.py --help`, plus your Python version (`python --version`) and openpyxl version (`pip show openpyxl`), and send them to the series feedback email.

---

*This folder is the companion asset for article B1 in the series "AI-Powered Office: From Beginner to Expert". Every script runs out-of-the-box on Python 3.8+ with openpyxl installed; the sample CSV is ready to edit and reuse.*