openpyxl — when you need cell-level control

When you reach for openpyxl

Pandas treats Excel like a flat table. If you need the spreadsheety bits — colours, formulas, merged cells, comments, charts — drop down to openpyxl.

from openpyxl import Workbook, load_workbook

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

ws["A1"] = "Customer"
ws["B1"] = "Revenue"
ws["A2"] = "Acme"
ws["B2"] = 1200
ws["A3"] = "Beta"
ws["B3"] = 800

ws["B4"] = "=SUM(B2:B3)"   # a real Excel formula

wb.save("openpyxl_demo.xlsx")

Reading existing files

wb = load_workbook("openpyxl_demo.xlsx")
ws = wb["Summary"]

print(ws["A1"].value)         # 'Customer'
print(ws.cell(row=2, column=2).value)   # 1200

for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

Styling — bold, fill, number format

from openpyxl.styles import Font, PatternFill

bold_white = Font(bold=True, color="FFFFFF")
green_fill = PatternFill(start_color="217346", end_color="217346", fill_type="solid")

for cell in ws[1]:                # first row = headers
    cell.font = bold_white
    cell.fill = green_fill

ws["B2"].number_format = '"$"#,##0.00'

Column widths and freeze panes

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

Walkthrough: turn a DataFrame into a styled report

import pandas as pd
df = pd.DataFrame({"Customer": ["Acme","Beta","Gamma"], "Revenue": [1200,800,1500]})
df.to_excel("report.xlsx", index=False, sheet_name="Sales")

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="217346")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

for cell in ws["B"][1:]:                # data rows
    cell.number_format = '"$"#,##0.00'

ws.column_dimensions["A"].width = 22
ws.column_dimensions["B"].width = 16
ws.freeze_panes = "A2"

wb.save("report.xlsx")