# 🐍 B25｜Batch-Process Excel with Python + openpyxl — Companion Assets

> **Series**: AI Office Practice: From Beginner to Expert
> **Article**: B25 (Track 7 Office Development / Level 1 Beginner)
> **Title (CN)**: 用 Python + openpyxl 批量处理 Excel
> **Title (EN)**: Batch-Process Excel with Python + openpyxl

---

## 1. Who is this for? What problem does it solve?

For anyone who, **at the end of every month**, has to update 30 sales sheets with the same header, add a "Manager" column, or recompute "completion rate" — opening each file, typing by hand, saving, repeat until your wrist aches.

The main tutorial shows how to install Python and run your first openpyxl script. This folder ships a **complete, runnable mini-pipeline**:

| File | Purpose |
|------|---------|
| `数据源_sample.csv` | 6-row sample data (department, manager, quarterly sales, …) |
| `生成示例文件_en.py` | One-shot script to turn the CSV into a test `.xlsx` |
| `批量处理Excel_en.py` | Core: read → compute → reorder → write new sheet → style |
| `requirements.txt` | Dependencies (`openpyxl>=3.0.0`) |

After running this folder, you have a reusable "read Excel → process → write new Excel" template. Swap in your own data path and business logic and you can rename sheets, add columns, sort, restyle, etc.

---

## 2. How to use (5-step recipe)

### Step 1 — Install Python (tick "Add to PATH")

1. Visit `python.org → Downloads` and grab the latest Windows installer.
2. **Tick "Add Python to PATH"** on the first screen of the installer, then click *Install Now*.
3. Close the installer.

> Forgot to tick PATH? Re-run the installer → *Modify* → tick *Add Python to environment variables*.

### Step 2 — Verify Python

Press `Win + R` → type `cmd` → Enter:

```bash
python --version
```

You should see `Python 3.x.x`. If it says "not recognized", go back to Step 1.

### Step 3 — Install openpyxl

```bash
pip install openpyxl
```

> If PATH was missed or you have multiple Python versions, use `py -m pip install openpyxl` instead — the `py` launcher is always registered in `C:\Windows\py.exe`, and `-m` ensures pip runs *under the same* Python that your script will use.

### Step 4 — Generate the test Excel

```bash
cd "C:\Users\cynix\WorkBuddy\VBA.net\配套资产\B25_批量Excel"
python 生成示例文件_en.py
```

This creates `SalesReport_Raw.xlsx` (6 data rows + styled blue header).

### Step 5 — Run the core script

```bash
python 批量处理Excel_en.py
```

It creates `SalesReport_Processed.xlsx` with two extra columns (AnnualTotal / CompletionRateRecalc), two extra rows (TOTAL + AVERAGE), sorted by AnnualTotal desc, with borders, centered alignment, percent format. **The original file is untouched** — that's the first discipline of any Excel-automation script: always save to a new file name.

---

## 3. File list

| File | Type | Purpose | Lines |
|------|------|---------|-------|
| `B25_BatchExcel_EnglishREADME.md` | Markdown | This file | — |
| `B25_批量Excel_中文README.md`        | Markdown | Chinese README | — |
| `批量处理Excel_en.py` | Python | Core: read + process + write new sheet (English) | ~140 |
| `批量处理Excel_zh.py` | Python | Core (Chinese) | ~140 |
| `生成示例文件_en.py` | Python | Generate test Excel (English) | ~80 |
| `生成示例文件_zh.py` | Python | Generate test Excel (Chinese) | ~80 |
| `数据源_sample.csv` | CSV | 6-row sample data | 7 |
| `requirements.txt` | Text | `openpyxl>=3.0.0` | 1 |

---

## 4. Practical code (key snippets explained)

### ① Minimal read/write skeleton

```python
from openpyxl import load_workbook

wb = load_workbook("SalesReport_Raw.xlsx")
ws = wb.active                        # current worksheet

# Read A1
print(ws["A1"].value)

# Write B1 (in memory only)
ws["B1"] = "written by openpyxl"

# Save as a NEW file — never overwrite the source
wb.save("SalesReport_New.xlsx")
```

**Four APIs**: `load_workbook` opens existing; `wb.active` selects the current sheet; `ws["A1"].value` reads; `ws["A1"] = ...` writes; `wb.save("new_name")` saves as a new file.

### ② Compute totals + sort (the business logic)

```python
processed = []
for row in data_rows:
    dept, mgr, q1, q2, q3, q4, target, _ = row
    nums = [q1 or 0, q2 or 0, q3 or 0, q4 or 0]      # None / strings → 0
    y_sum = sum(nums)
    rate  = (y_sum / target) if target else 0.0       # avoid divide-by-zero
    processed.append([dept, mgr, q1, q2, q3, q4, target, y_sum, rate])

# Sort by AnnualTotal (col 7 in the new list) desc
processed.sort(key=lambda r: r[7], reverse=True)
```

**Three small tricks**: ① `or 0` defends against `None`; ② `if target else 0.0` defends against zero division; ③ `sort(..., reverse=True)` is a one-liner.

### ③ Styling (optional polish)

```python
from openpyxl.styles import Font, PatternFill, Alignment

header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
for col_idx in range(1, len(new_headers) + 1):
    cell = new_ws.cell(row=1, column=col_idx)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

new_ws.cell(row=row_idx, column=9).number_format = "0.0%"
```

**Three most-used styles**: `Font` (text), `PatternFill` (background), `number_format` (`#,##0` thousands, `0.0%` percent).

---

## 5. FAQ / Troubleshooting

1. **`ModuleNotFoundError: No module named 'openpyxl'`**
   openpyxl wasn't installed, or got installed under a different Python. Use `py -m pip install openpyxl` to target the same Python that runs your script.

2. **`FileNotFoundError: 'demo.xlsx'`**
   `批量处理Excel_en.py` can't find `SalesReport_Raw.xlsx`. Either run `生成示例文件_en.py` first, or put the script and data in the same folder and `cd` there.

3. **`BadZipFile: File is not a zip file`**
   The file is probably old `.xls`. openpyxl **does not support `.xls`** — only zip-format `.xlsx`. Save it from Excel as `.xlsx` first.

4. **Original file got overwritten**
   `wb.save()` writes to whatever path you give. Always pass a *new* filename; never the source path.

5. **Garbled Chinese headers / data**
   Don't hand-edit `.csv` in Notepad (default ANSI). Use **VS Code** or have Excel export CSV as **UTF-8** — or just stick with `.xlsx`, which openpyxl handles natively.

---

## 6. Next steps

After this folder, you can write small "read → process → write" scripts. Natural next moves:

- **B26 — pandas for data analysis**: when the task is filter / group / merge rather than cell-level edits, pandas is much friendlier. Same Track 7.
- **Batch hundreds of files**: wrap the core loop in `for f in glob.glob("*.xlsx")`, or jump to **B18 — Script Batch Office Processing** (PowerShell + COM).
- **Let AI rewrite the code**: see the main tutorial's Section 6 ("How to use AI to read / rewrite the code"), or revisit **A3 — Prompt Engineering** to get better results from the AI.

---

> 📌 **Quick checklist** before running:
> - [ ] Python installed and PATH ticked;
> - [ ] `python --version` prints a version;
> - [ ] `pip install openpyxl` (or `py -m pip install openpyxl`) succeeded;
> - [ ] `cd`-ed into the B25 folder;
> - [ ] Run `生成示例文件_en.py` first, then `批量处理Excel_en.py`;
> - [ ] The core script only saves to `*_Processed.xlsx` — the original is untouched.