Users Pricing

blog

Home Blogs Excel Automation with Python: Read, Write and Format Files with openpyxl – MindStick
Excel Automation with Python: Read, Write and Format Files with openpyxl

Excel Automation with Python: Read, Write and Format Files with openpyxl

Tejas Joshi 854 19 Nov 2026

In a lot of offices, someone spends Monday morning copying numbers from one spreadsheet into another, fixing column widths and emailing the result. It takes an hour, and it goes wrong whenever they’re on leave.

That’s exactly the kind of job Python handles well. With openpyxl, you can create, read and format real .xlsx files, with formulas, colours, frozen headers and charts, without Excel installed. That makes it a good fit for servers and scheduled jobs.

Here’s a complete, working script, then a walk through the parts you’ll reuse most.

Install

pip install openpyxl

(Or uv add openpyxl if you use uv.) openpyxl works with .xlsx and .xlsm files. It does not read the old binary .xls format, so convert those first or use a different library for them.

The complete example: a formatted sales report

This script builds a small sales sheet for four cities, adds revenue formulas and a total row, styles it, and inserts a bar chart. Then it reads the file back.

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference

sales = [
    ("Lucknow", "Laptops", 42, 52000),
    ("Prayagraj", "Laptops", 31, 52000),
    ("Kanpur", "Printers", 18, 14500),
    ("Lucknow", "Printers", 25, 14500),
    ("Varanasi", "Laptops", 22, 52000),
]

wb = Workbook()
ws = wb.active
ws.title = "Sales"

headers = ["City", "Product", "Units", "Unit Price", "Revenue"]
ws.append(headers)
for city, product, units, price in sales:
    ws.append([city, product, units, price])

# Add a live Excel formula for revenue in each row
for row in range(2, ws.marow + 1):
    ws[f"E{row}"] = f"=C{row}*D{row}"

total_row = ws.marow + 1
ws[f"A{total_row}"] = "Total"
ws[f"C{total_row}"] = f"=SUM(C2:C{total_row - 1})"
ws[f"E{total_row}"] = f"=SUM(E2:E{total_row - 1})"

# Styling
header_fill = PatternFill("solid", fgColor="1D3557")
thin = Side(style="thin", color="BBBBBB")
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")
for row in ws.iter_rows(min_row=2, marow=total_row):
    for cell in row:
        cell.border = Border(top=thin, bottom=thin, left=thin, right=thin)
for col in ("D", "E"):
    for cell in ws[col][1:]:
        cell.number_format = '#,##0'
for cell in ws[total_row]:
    cell.font = Font(bold=True)

# Auto-fit column widths (approximate)
for i, column in enumerate(ws.columns, start=1):
    longest = max(len(str(c.value)) if c.value is not None else 0 for c in column)
    ws.column_dimensions[get_column_letter(i)].width = min(max(longest + 2, 10), 30)

ws.freeze_panes = "A2"

# Bar chart of units by row
chart = BarChart()
chart.title = "Units sold"
chart.y_axis.title = "Units"
data = Reference(ws, min_col=3, min_row=1, marow=total_row - 1)
cats = Reference(ws, min_col=1, min_row=2, marow=total_row - 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "G2")

wb.save("sales_report.xlsx")
print("Saved sales_report.xlsx")

# Reading it back
wb2 = load_workbook("sales_report.xlsx")
for city, product, units, *_ in wb2["Sales"].iter_rows(min_row=2, marow=6, values_only=True):
    print(city, product, units)

Run it, and you’ll get sales_report.xlsx with a navy header row, borders, thousand separators, a frozen header and a chart beside the data.

Breaking it down

Workbooks, sheets and cells

wb = Workbook()          # new workbook with one sheet
ws = wb.active           # that sheet
ws.title = "Sales"

There are two ways to write to cells:

ws["B3"] = "Laptops"              # by address
ws.cell(row=3, column=2).value = "Laptops"   # by number, handy in loops

ws.append([...]) adds a whole row after the last used row. It’s the quickest way to write tabular data.

Need more sheets? wb.create_sheet("Summary").

Formulas are written, not calculated

ws[f"E{row}"] = f"=C{row}*D{row}"

Any string starting with = is stored as an Excel formula. Excel calculates it when someone opens the file.

This is the most important thing to understand about openpyxl: it doesn’t calculate formulas. It has no calculation engine. That leads to the most common question about it…

“Why does my formula cell read back as None?”

When you load a workbook, openpyxl gives you the formula text by default:

wb = load_workbook("sales_report.xlsx")
print(wb["Sales"]["E2"].value)    # '=C2*D2'

With data_only=True, it gives you the cached result that Excel saved the last time the file was opened and saved there:

wb = load_workbook("sales_report.xlsx", data_only=True)
print(wb["Sales"]["E2"].value)    # None, if Excel has never saved this file

A file created by openpyxl has never been calculated by Excel, so there’s no cached value, and you get None.

Your options:

  1. Calculate the value in Python and write the number (perhaps alongside the formula in another column) when you need it in code.
  2. Open and save the file in Excel (or LibreOffice) once. After that, data_only=True returns values.
  3. Recalculate headlessly with LibreOffice on a server: soffice --headless --convert-to xlsx --outdir out sales_report.xlsx recalculates as it converts.

One more trap: if you load with data_only=True and then save, the formulas are replaced by their cached values, or by nothing at all. Keep formula-preserving loads and value-reading loads separate.

Styling that makes a report look finished

header_fill = PatternFill("solid", fgColor="1D3557")
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = Alignment(horizontal="center")
cell.border = Border(top=thin, bottom=thin, left=thin, right=thin)

Style objects are immutable. You can’t change cell.font.bold directly; assign a new Font instead. Create shared objects once (like header_fill above) and reuse them.

Number formats use the same codes as Excel’s Format Cells dialog:

cell.number_format = '#,##0'          # 1,234,567
cell.number_format = '#,##0.00'       # two decimals
cell.number_format = '0.0%'           # percentage
cell.number_format = 'dd-mmm-yyyy'    # 15-Jan-2027

For Indian-style grouping (12,34,567), Excel needs a custom format such as [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0. It works, but test it in the version of Excel your readers use.

Column widths and frozen panes

openpyxl has no “auto-fit”, because column width depends on fonts that only Excel knows how to measure. The script approximates it from the longest value in each column:

ws.column_dimensions["A"].width = 18

Then freeze the header row so it stays visible while scrolling:

ws.freeze_panes = "A2"

"B2" would freeze both the first row and the first column.

Charts

chart = BarChart()
data = Reference(ws, min_col=3, min_row=1, marow=total_row - 1)
cats = Reference(ws, min_col=1, min_row=2, marow=total_row - 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "G2")

Reference points at cell ranges, so the chart is a real Excel chart linked to the data. If someone edits a number in Excel, the chart updates. LineChart, PieChart and ScatterChart work the same way.

Reading data from existing spreadsheets

Most automation starts with someone else’s file. To loop over rows as plain values:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx", data_only=True)
ws = wb["Orders"]

for order_id, customer, amount, status in ws.iter_rows(min_row=2, values_only=True):
    if order_id is None:      # stop at the first empty row
        break
    print(order_id, customer, amount, status)

values_only=True returns tuples of values instead of cell objects, which is simpler and faster.

Real-world spreadsheets are messy, so build in a few defences:

  • Look up columns by header name rather than position. Someone will insert a column eventually.
  • Expect numbers stored as text, dates stored as text, and trailing spaces.
  • ws.marow can include rows that only have formatting, so check for empty values rather than trusting it.

Large files

For big workbooks, use the optimised modes:

# Reading: fast and low-memory, rows only, no random access
wb = load_workbook("big.xlsx", read_only=True)

# Writing: streams rows to disk; you can only append
wb = Workbook(write_only=True)
ws = wb.create_sheet()
for record in records:
    ws.append(record)
wb.save("big_out.xlsx")

In read-only mode, call wb.close() when you’re done, because the file stays open.

Where pandas fits in

If your task is mostly analysis, such as filtering, grouping and pivoting, pandas is quicker to write:

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
summary = df.groupby("City")["Amount"].sum().reset_index()
summary.to_excel("summary.xlsx", index=False)

pandas uses openpyxl underneath for .xlsx files. A good pattern is to calculate with pandas, then format with openpyxl: write the DataFrame, reopen the file with openpyxl, and apply styles, widths and charts.

Turning it into a real automation

The script above becomes genuinely useful once it runs without you:

  • Pull data from your database or an API instead of a hard-coded list.
  • Save with a dated file name, such as sales_report_2027-01-15.xlsx.
  • Email it using the script from our guide to sending emails with Python over SMTP.
  • Schedule it with Windows Task Scheduler or cron, so it arrives every Monday at 8 a.m.

Quick reference

TaskCode
New workbookwb = Workbook()
Open existingload_workbook("f.xlsx")
Read cached formula resultsload_workbook("f.xlsx", data_only=True)
Write a cellws["A1"] = "Hello"
Add a rowws.append([...])
Formulaws["E2"] = "=C2*D2"
Bold textcell.font = Font(bold=True)
Number formatcell.number_format = '#,##0'
Column widthws.column_dimensions["A"].width = 20
Freeze headerws.freeze_panes = "A2"
Large file readload_workbook(f, read_only=True)

Start with one report that someone currently builds by hand. Automate it, schedule it, and give that Monday-morning hour back.


Tejas Joshi

C Developer at Google India

Hi, I am Tejas. I work as a C Developer at Google India and my work is mostly about system programming in C on Linux. Before this I spent 4 years at Razorpay. I studied B.Tech in Computer Science from the BMS College of Engineering, Bengaluru. I am originally from Mumbai, Maharashtra. Weekends are for reading history books.


Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md