[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:

A computer with Python installed and callable straight from the command line;
The openpyxl library installed;
A real, runnable script that opens demo.xlsx, reads A1, writes B1, and saves as demo_新.xlsx;
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")

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).
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".
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

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

bash
python --version

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.

🛠️ Batch-Process Excel with Python + openpyxl