openpyxl — formatting, column width, conditional formatting, charts

We'll format report.xlsx from Lesson 3.

1. Open, select, save

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Summary"]          # or wb.active
# ... changes ...
wb.save("report.xlsx")

Cells: ws["A1"], ws.cell(row=2, column=3). Whole row 1: ws[1]. Size: ws.max_row, ws.max_column.

2. Header style and borders

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

header_fill = PatternFill("solid", start_color="1F4E78")
thin = Side(style="thin", color="BFBFBF")

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=1, max_row=ws.max_row, max_col=ws.max_column):
    for cell in row:
        cell.border = Border(top=thin, bottom=thin, left=thin, right=thin)

Colours are hex codes without #.

3. Number formats

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):     # column B, from row 2
    for cell in row:
        cell.number_format = "#,##0"

Other formats: "0.00%", "dd-mmm-yyyy", '"₹"#,##0'.

4. Column widths (auto-fit style)

openpyxl has no AutoFit, so measure the longest value:

from openpyxl.utils import get_column_letter

for col_cells in ws.columns:
    longest = max(len(str(c.value)) if c.value is not None else 0 for c in col_cells)
    ws.column_dimensions[get_column_letter(col_cells[0].column)].width = longest + 3

5. Freeze panes and filter

ws.freeze_panes = "A2"              # keep row 1 visible while scrolling
ws.auto_filter.ref = ws.dimensions  # filter arrows on the whole table, e.g. "A1:B4"

6. Conditional formatting

Highlight totals below 70,000 in red:

from openpyxl.formatting.rule import CellIsRule, ColorScaleRule

last = ws.max_row
ws.conditional_formatting.add(
    f"B2:B{last}",
    CellIsRule(operator="lessThan", formula=["70000"],
               fill=PatternFill("solid", start_color="FFC7CE"), font=Font(color="9C0006")))

3-colour scale (red → yellow → green) on the raw data amounts:

raw = wb["Raw Data"]
raw.conditional_formatting.add(
    f"E2:E{raw.max_row}",
    ColorScaleRule(start_type="min", start_color="F8696B", mid_type="percentile", mid_value=50,
                   mid_color="FFEB84", end_type="max", end_color="63BE7B"))

These are real Excel rules — they keep working if someone changes the numbers.

7. A chart

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Sales by Region"
chart.y_axis.title = "Amount (₹)"
data = Reference(ws, min_col=2, min_row=1, max_row=last)     # includes header for series name
cats = Reference(ws, min_col=1, min_row=2, max_row=last)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.width, chart.height = 16, 8
ws.add_chart(chart, "E2")

Also available: LineChart, PieChart, ScatterChart. The chart is a native Excel chart, editable by the user.

8. Formulas

ws[f"A{last + 1}"] = "Total"
ws[f"B{last + 1}"] = f"=SUM(B2:B{last})"
ws[f"B{last + 1}"].font = Font(bold=True)

Excel calculates the formula when the file is opened.

9. Make it a function

Put formatting in a function you call for every sheet — that's exactly what the mini project in Lesson 6 does.

Common mistakes

Forgetting wb.save(). Using #1F4E78 instead of 1F4E78. Hard-coding ranges like B2:B4 instead of using ws.max_row. Opening the file in Excel while the script saves it.