📎 This article includes 3 downloadable practice files ↓

📘 Python Beginner Course · Lesson 11 of 12

pandas writes data to Excel; openpyxl makes it look like a report: bold coloured headers, number formats, column widths, formulas and charts. Together they produce a file your manager can open without knowing Python exists.

Step 1: write data with pandas

import pandas as pd
summary = df.groupby("region", as_index=False)["amount"].sum()
with pd.ExcelWriter("region_report.xlsx") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False)
    df.to_excel(xw, sheet_name="Data", index=False)

Step 2: format with openpyxl

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
wb = load_workbook("region_report.xlsx")
ws = wb["Summary"]
for c in ws[1]:                                   # header row
    c.font = Font(bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor="1F6F43")
for c in ws["B"][1:]:                             # column B except the header
    c.number_format = "#,##0"
ws.column_dimensions["A"].width = 12

For Indian grouping in the Excel file itself, use the number format ##,##,##0.

Step 3: formulas and a chart

from openpyxl.chart import BarChart, Reference
n = ws.max_row
ws.append(["Total", f"=SUM(B2:B{n})"])            # a real Excel formula
ch = BarChart(); ch.title = "Sales by region"
ch.add_data(Reference(ws, min_col=2, min_row=1, max_row=n), titles_from_data=True)
ch.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n))
ws.add_chart(ch, "D2")
wb.save("region_report.xlsx")

Reading cells

wb = load_workbook("input.xlsx", data_only=True)   # data_only=True reads cached values, not formulas
ws = wb.active
ws["B2"].value
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

What openpyxl can’t do

It can’t run VBA macros, refresh pivots or Power Query, or calculate formulas. For .xlsb files use pyxlsb (read-only); to drive Excel itself on Windows, xlwings is the tool.

Where beginners go wrong

Practice

Download the script (and data file, if listed) below into one folder. Open a terminal in that folder and run python lesson-NN.py. Compare with the expected output, then change something and run it again: that’s how Python is learned.