Python Intermediate Lesson 7: Formatted Excel Reports With openpyxl

📎 This article includes 3 downloadable practice files ↓

📘 Python Intermediate Course · Lesson 7 of 10

pandas’ to_excel dumps data. openpyxl lets you build a finished report: formatting, formulas that keep working when someone edits a number, and charts.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference

wb = Workbook(); ws = wb.active; ws.title = "Sales"
ws.append(["Invoice", "Date", "Code", "Qty", "Rate", "Amount", "GST"])
for i, r in enumerate(rows, start=2):
    ws.append([r["invoice"], r["date"], r["code"], int(r["qty"]), price,
               f"=D{i}*E{i}", f"=ROUND(F{i}*{gst},2)"])        # live Excel formulas
for c in ws[1]:
    c.font = Font(bold=True, color="FFFFFF"); c.fill = PatternFill("solid", fgColor="217346")
for c in ws["F"][1:]: c.number_format = "#,##0.00"
ws.freeze_panes = "A2"

A summary sheet with SUMIFS and a chart

s = wb.create_sheet("Summary")
s.append(["Code", "Product", "Amount"])
s.append(["P104", "USB mouse", "=SUMIFS(Sales!F:F,Sales!C:C,A2)"])
chart = BarChart(); chart.add_data(Reference(s, min_col=3, min_row=1, max_row=7), titles_from_data=True)
s.add_chart(chart, "E2")
wb.save("output/sales-report.xlsx")

Common mistakes

• Saving over a file that’s open in Excel: PermissionError. Close it, or save under a new name with a timestamp.
• Dates as text: write datetime.date objects and set number_format = "dd-mmm-yyyy".

Practice

Unzip the practice data into a folder, put this lesson’s script in the same folder and run it (python pyi-NN.py). Compare with the expected output file. Then change one thing (a filter, a column, a format) and predict the new output before you run it.