---
title_en: "Batch-Process Excel with Python + openpyxl"
title_cn: "用 Python + openpyxl 批量处理 Excel"
series: "AI-Powered Office: From Beginner to Expert"
article_code: "B25"
level: "L1"
category: "AI Office"
subcategory: "Office Development"
tags: "Python,openpyxl,Excel,Batch,L1"
status: "草稿"
bilingual_switch: "双语"
author: "程晓"
translator: "易安"
---


> 📎 **配套资产 / Companion assets**：本篇所有示例的可下载文件在仓库 `配套资产/B25_批量Excel/`——批量处理脚本 + 示例文件生成器 + requirements.txt，每个子目录都带中文 README 与 English README。
> English: downloadable files for every example in this article live in `配套资产/B25_批量Excel/` — batch-processing script + sample-file generator + requirements.txt, each with a bilingual README.
# [Office Development]Batch-Process Excel with Python + openpyxl

> **Summary**: Even with zero programming background you can get your first Excel batch script running — read one cell, write one cell, save as a new file. By the end you'll own a real, runnable Python script: the foundation for "using code to do repetitive office work in batches" later.

---

## 1. The Pain Point: Tired of Editing Dozens of Excel Files by Hand?

First, a word on the bar to entry: this is one of the two L1 pieces with the higher hands-on threshold (it requires installing a Python environment). The article builds in safety nets for installation and troubleshooting, but we still recommend finishing B1/B5/B9/B21 to build the feel before touching the code.

You've almost certainly run into one of these:

- Month-end: you need to change the same header and add a "Responsible Person" column across 30 sales sheets — open each one, fill it in by hand, save, copy and paste until your hand cramps;
- Every week you apply the exact same formatting to N departments' files — identical steps, pure mechanical labor;
- You want to "move column A over to column B" or "change what's in cell C" across a pile of files, but there's no built-in button, so you do it click by click.

What these jobs have in common: **fixed rules, high volume, repetition**. Humans miss things and make mistakes; code lives for exactly this "do the same thing over and over, by the rules" kind of work.

You don't need to become a programmer first. This article walks you through using Python plus a library called **openpyxl** to get the smallest viable script running: read one cell, write one cell, save as a new file. Get "code edits one file for you" working first; we'll talk about handling a hundred files later.

---

## 2. What You'll Walk Away With (Goal)

When you finish and follow along, you'll have:

1. A computer with Python installed and callable straight from the command line;
2. The `openpyxl` library installed;
3. A real, runnable script that opens `demo.xlsx`, reads `A1`, writes `B1`, and saves as `demo_新.xlsx`;
4. A one-line prompt template that says "ask AI to help you read / rewrite this code."

> **Note**: This is an **L1 beginner article** — all you need is the ability to use a mouse and to open the "Command Prompt / Terminal." Every snippet is copy-and-paste ready; you don't have to type anything yourself.

---

## 3. Environment Setup

### 3.1 Install Python (Make Sure to Tick "Add Python to PATH")

1. Open a browser, go to the Python downloads page (python.org → Downloads), and download the latest stable Windows installer (e.g., `python-3.x.x.exe`).
2. **Double-click the installer to launch the setup wizard. The single most important step: on the first screen, at the bottom, make sure to tick the `Add Python to PATH` checkbox ("Add Python to PATH"), then click "Install Now".**
3. When the install finishes, close the window.

> Why tick PATH? PATH is the operating system's "command search path." Once ticked, you can type `python` in the command line from any folder and the system knows where to find it. If you skip it, both `python` and `pip` will later throw "'python' is not recognized as an internal or external command," and every step stalls. Missed it? No problem — run the installer again, choose "Modify," and tick "Add Python to environment variables."

### 3.2 Verify Python Works

1. Press `Win`, type `cmd`, press Enter to open the Command Prompt (PowerShell works too).
2. Type this and press Enter:

```bash
python --version
```

3. If it prints something like `Python 3.x.x`, PATH is set — keep going.
   - If you see "'python' is not recognized as an internal or external command," PATH isn't configured. Go back to 3.1 and reinstall with PATH ticked.

### 3.3 Install the openpyxl Library

In the same command line (pip is Python's built-in package manager for installing third-party libraries):

```bash
pip install openpyxl
```

> If `pip` also says "command not found," PATH is probably the culprit. The most reliable fix is the **`py` launcher** (registered to `C:\Windows\py.exe` by default during Python install, so it works even without "Add Python to PATH"): `py -m pip install openpyxl`. If you've confirmed `python` launches fine, you can also use `python -m pip install openpyxl` — its advantage is guaranteeing the package lands in "this exact python," avoiding installing to the wrong one when several Pythons exist on your machine.

### 3.4 Verify the Library Installed

```bash
python -c "import openpyxl; print(openpyxl.__version__)"
```

If it prints a version number (e.g., `3.1.2`), congratulations — openpyxl is ready and you can start writing scripts.

> Still seeing `ModuleNotFoundError: No module named 'openpyxl'`? The library isn't installed, or it landed on a different Python. Re-run `pip install openpyxl`, and make sure the `python` you run the script with is the same one you installed with (use `python -m pip install openpyxl` to be safe).

---

## 4. Your First Script: Read a Cell, Write a Cell, Save As

### 4.1 Prepare a Test File

First create an Excel file, put something in it, and name it **`demo.xlsx`**, then save it. For example:

| A1 | B1 |
|----|----|
| Original data | (leave blank) |

> ⚠️ **Back it up first!** Always practice with a "throwaway / unimportant" `demo.xlsx` — never rehearse on a real business file you're actively using. openpyxl's `save()` **overwrites** whatever file sits at the path you give it, and once the original data is overwritten it's very hard to get back.

### 4.2 Write the Script

Open Notepad (or any text editor), paste the code below, and save it as **`run.py`** in the same folder as `demo.xlsx`.

```python
# run.py -- read and write Excel with openpyxl (minimal L1 example)
from openpyxl import load_workbook

# 1. Open an existing Excel file
#    Note: openpyxl only supports the .xlsx format; it cannot open legacy .xls files
wb = load_workbook("demo.xlsx")

# 2. Get the current worksheet (active = the sheet selected when the file was last saved)
ws = wb.active

# 3. Read the contents of cell A1
print("The original content of cell A1 is:", ws["A1"].value)

# 4. Demo of the cell-coordinate API: cell.row / cell.column
c = ws["A1"]
print("This cell's row number =", c.row)      # prints 1
print("This cell's column number =", c.column)  # prints 1 (column A = column 1)

# 5. Write to cell B1 (assignment writes; at this point the change lives only in memory, not yet on disk)
ws["B1"] = "Written by openpyxl"

# 6. Save as a new file; never overwrite the original in place
#    A name suffix like "_新" or "_out" helps tell the copy apart from the original
wb.save("demo_新.xlsx")

print("Done! Created demo_新.xlsx")
```

> Note: openpyxl **only handles `.xlsx`**. If you feed it an old `.xls` file you'll hit a `BadZipFile: File is not a zip file` error (an `.xlsx` is really a zip archive under the hood). In Excel, use "Save As" to convert it to `.xlsx` first.

### 4.3 Run It

In the command line, first `cd` into the folder containing `run.py` (e.g., `cd C:\Users\you\Desktop\practice`), then run:

```bash
python run.py
```

If you see `The original content of cell A1 is: ...` and `Done! Created demo_新.xlsx` printed, you've succeeded. Open the generated **`demo_新.xlsx`** and you'll see `B1` now holds "Written by openpyxl," while the original `demo.xlsx` is untouched.

> `FileNotFoundError: [Errno 2] No such file or directory: 'demo.xlsx'`: the script can't find the file. Make sure `demo.xlsx` and `run.py` are in the **same folder**, and that your command line's current directory is that folder (use `cd` to get in there before running).

### 4.4 Key Takeaways

- `load_workbook("demo.xlsx")`: opens an **existing** `.xlsx` file.
- `wb.active`: gets the current worksheet; you can also grab it by name with `wb["Sheet1"]`.
- `ws["A1"].value`: reads a cell's content; `ws["B1"] = ...`: writes a cell.
- `c.row` / `c.column`: a cell's row number and column number (both integers, A=1, B=2 …).
- `wb.save("demo_新.xlsx")`: **save-as** to a new file name to avoid overwriting.

> Want to create a file from scratch? Use `from openpyxl import Workbook`, then `wb = Workbook()`, `ws = wb.active`, `ws["A1"]="你好"`, `wb.save("新建.xlsx")`. `Workbook()` is "open a brand-new book," `load_workbook()` is "open an existing book" — just remember the difference.

---

## 5. Using AI to Understand / Rewrite This Code

You don't need to memorize the API. Throwing the code at an AI (a general-purpose assistant) and asking it to explain or modify is the fastest way to learn. Here's a copy-paste prompt template:

```
Below is some Python openpyxl code for processing Excel:
[Paste the contents of run.py here]

Please do three things for me:
1. Explain in plain, everyday language what each line of this script does;
2. I want to change it so it "loops through every row that has content in column A, and writes 'twice the value in column A' into column B of each row." Give me the complete modified code directly;
3. List the situations where this code would throw an error (e.g., file doesn't exist, not a .xlsx), and how to avoid them.
```

When you get the result back, **double-check it yourself first** (are the values correct? did it damage the original file?) before you hand it over — that's basic discipline for using AI to write code.

---

## 6. Recap & Next Steps

**One-line recap**: **Install Python with PATH → `pip install openpyxl` → use `load_workbook` to open the `.xlsx`, read/write with `ws["A1"]`, and `wb.save` to a new file — and your first Excel batch script is running.**

To keep going:

- **B26 "Write Power Query M Queries with AI"** (next article in the same T7 Office Development track): when you need more than "change one cell" — "filter by condition, group and summarize, merge across tables" — Power Query M is handier than openpyxl, and a good next step into data cleaning.
- **Other articles in the T7 Office Development series**: from "single-file read/write" to "batch-loop through hundreds of files" and "auto-generate reports from a template," gradually handing all the repetitive work to code.
- **Review the prompt (A3)**: the prompt template in section 5 of this article, combined with the A3 prompt primer, will make you better and better at "directing AI to write/modify code for you."

---

## 7. CTA (Optional)

Doing it once beats reading ten times: create a `demo.xlsx` right now, get the `run.py` from section 4 running, then ask AI to change it into "batch-edit column B." The moment it runs, you've already crossed the "zero-basis" threshold.

---

_This article is part of the "AI-Powered Office: From Beginner to Expert" series, B25 (T7 Office Development / L1). Author: Cheng Xiao. Translator: 易安 (Yi An). Status: Draft._