---
title: "Excel Automation with Python: Read, Write and Format Files with openpyxl"  
description: "Automate Excel reports with Python and openpyxl. Read and write .xlsx files, add formulas, styles, number formats and charts, handle large files, and"  
author: "Tejas Joshi"  
published: 2026-11-19  
canonical: https://www.mindstick.com/blog/307129/excel-automation-with-python-read-write-and-format-files-with-openpyxl  
category: "python"  
tags: ["excel automation python openpyxl", "python", "openpyxl", "python tutorial", "step by step tutorial"]  
reading_time: 8 minutes  

---

# Excel Automation with Python: Read, Write and Format Files with openpyxl

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.max_row + 1):
    ws[f"E{row}"] = f"=C{row}*D{row}"

total_row = ws.max_row + 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, max_row=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, max_row=total_row - 1)
cats = Reference(ws, min_col=1, min_row=2, max_row=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, max_row=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, max_row=total_row - 1)
cats = Reference(ws, min_col=1, min_row=2, max_row=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.max_row` 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

| Task | Code |
| --- | --- |
| New workbook | `wb = Workbook()` |
| Open existing | `load_workbook("f.xlsx")` |
| Read cached formula results | `load_workbook("f.xlsx", data_only=True)` |
| Write a cell | `ws["A1"] = "Hello"` |
| Add a row | `ws.append([...])` |
| Formula | `ws["E2"] = "=C2*D2"` |
| Bold text | `cell.font = Font(bold=True)` |
| Number format | `cell.number_format = '#,##0'` |
| Column width | `ws.column_dimensions["A"].width = 20` |
| Freeze header | `ws.freeze_panes = "A2"` |
| Large file read | `load_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.

---

Original Source: https://www.mindstick.com/blog/307129/excel-automation-with-python-read-write-and-format-files-with-openpyxl

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.
