openpyxl basics: read and write Excel from Python

You can dislike it all you want: the shop runs on Excel. Tool inventories, production logs, setup sheets, quality records — I've replaced some of them with real software, but the spreadsheet is the shop floor's lingua franca and it isn't going anywhere. Which is why openpyxl is one of the highest-leverage libraries a manufacturing engineer can learn: it makes .xlsx just another file format your Python scripts read and write. No Excel installation required, no VBA, runs on a server or a scheduled task.

Reading a workbook

from openpyxl import load_workbook

wb = load_workbook("tool_inventory.xlsx", data_only=True)
ws = wb["Endmills"]              # or wb.active for the first sheet

print(ws["B2"].value)            # one cell

# iterate rows as plain values, skipping the header
for row in ws.iter_rows(min_row=2, values_only=True):
    tool_id, diameter, flutes, location, qty = row
    if qty is not None and qty < 2:
        print(f"LOW STOCK: {tool_id} ({qty} left)")

Writing a report people will actually open

The classic shop use case: a folder of per-machine CSV logs that someone merges into a weekly summary by hand. Here's the whole job — merge, total, and format it so it looks like a report instead of a data dump:

import csv
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "Week 27"

headers = ["Machine", "Date", "Parts", "Scrap"]
ws.append(headers)
for cell in ws[1]:
    cell.font = Font(bold=True)

for log in sorted(Path("logs").glob("*.csv")):
    with open(log, newline="") as fh:
        for r in csv.DictReader(fh):
            ws.append([r["machine"], r["date"],
                       int(r["parts"]), int(r["scrap"])])

ws.append([])
last = ws.max_row + 1
ws[f"A{last}"] = "TOTAL"
ws[f"A{last}"].font = Font(bold=True)
ws[f"C{last}"] = f"=SUM(C2:C{last - 2})"
ws[f"D{last}"] = f"=SUM(D2:D{last - 2})"

ws.column_dimensions["A"].width = 14
ws.freeze_panes = "A2"

wb.save("weekly_production.xlsx")

Note the trick in the middle: you can write formulas as strings and Excel evaluates them on open. Bold headers, frozen top row, sensible column widths — ten lines of polish is the difference between "the script output" and "the weekly report," and adoption lives in that difference.

openpyxl or pandas?

• pandas when the job is analysis: filtering, grouping, joining, statistics. pd.read_excel (which uses openpyxl underneath) gets the data into a DataFrame and out of spreadsheet-land.
• openpyxl when the job is the spreadsheet itself: formatting, formulas, multiple sheets, templates — producing a file for humans.
• Both, often: crunch in pandas, then hand the result to openpyxl for presentation. They're teammates, not rivals.

• `.xlsx` only. Ancient .xls files need a different tool — open and re-save them once, then move on.
• Excel locks open files. If someone has the workbook open, your save fails. Write to a new filename (report_2026-07-04.xlsx) or handle the PermissionError; on a shared drive, someone always has it open.
• Dates come back as `datetime` objects — that's a feature, but it surprises people the first time.

If you're new to Python entirely, this is genuinely the best entry point — more so than anything machine-side — because the wins are immediate and visible to everyone. (A complete, runnable version of the merge-and-report script above is in the code library.) It's step one in my recommended path for manufacturing engineers. And when a spreadsheet has grown into a monster that needs to be an actual application, that's the conversation I'm here for.