Build a Calendar Sheet in Excel with Python

Team rotas, release plans, school terms, maintenance windows and holiday planners all end up as the same artefact: a calendar in a spreadsheet, one grid per month, with some days coloured and some text in the squares. Building one by hand means fiddling with which weekday the 1st falls on, twelve times, every year. Python's calendar module already knows the layout of every month, so a script can produce the grids, shade the weekends, mark holidays, drop events from a list into the right squares and set the page up for printing. Next year's calendar is then a change of one number. This page is part of Styling Excel Cells with openpyxl.

Prerequisites

pip install openpyxl pandas

events.xlsx lists team events with a date and a description; two fall on the same day and one spans two days. The calendar covers October to December 2026, with 25 and 26 December as public holidays.

Step 1 — Get the month layout from the calendar module

calendar.monthcalendar(year, month) returns the month as a list of weeks, each a list of seven day numbers, with zeros for days that belong to the neighbouring months. That is exactly the grid a calendar needs:

import calendar

calendar.setfirstweekday(calendar.MONDAY)
for week in calendar.monthcalendar(2026, 10):
    print(week)
print(calendar.month_name[10], calendar.day_abbr[0])

October 2026 starts on a Thursday, so the first week is [0, 0, 0, 1, 2, 3, 4]. setfirstweekday decides whether weeks start on Monday (the ISO and European convention) or Sunday (common in the US); every other part of the code follows from it.

Step 2 — Write one month's grid

Write a title, the weekday names, and the day numbers, with each day cell tall enough to hold a few lines of text:

from datetime import date
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

thin = Side(style="thin", color="CBD5E1")
BOX = Border(left=thin, right=thin, top=thin, bottom=thin)
HEAD = PatternFill("solid", fgColor="4338CA")
WEEKEND = PatternFill("solid", fgColor="EEF2FF")
HOLIDAY = PatternFill("solid", fgColor="FDEFD8")
FONT = "Calibri"

def write_month(ws, year, month, top, holidays=frozenset()):
    ws.cell(top, 2, f"{calendar.month_name[month]} {year}").font = Font(name=FONT, bold=True, size=16)
    ws.cell(top + 1, 1, "Wk").font = Font(name=FONT, bold=True, color="64748B")
    for i, name in enumerate(calendar.day_abbr):
        c = ws.cell(top + 1, i + 2, name)
        c.font, c.fill, c.alignment = Font(name=FONT, bold=True, color="FFFFFF"), HEAD, Alignment(horizontal="center")
    cells = {}
    for w, week in enumerate(calendar.monthcalendar(year, month)):
        r = top + 2 + w
        ws.row_dimensions[r].height = 58
        first_day = next(d for d in week if d)
        wk = ws.cell(r, 1, date(year, month, first_day).isocalendar().week)
        wk.font, wk.alignment = Font(name=FONT, color="64748B"), Alignment(vertical="top")
        for i, d in enumerate(week):
            c = ws.cell(r, i + 2)
            c.border = BOX
            c.alignment = Alignment(vertical="top", wrap_text=True)
            if not d:
                continue
            day = date(year, month, d)
            c.value = str(d)
            c.font = Font(name=FONT, bold=True)
            if day in holidays:
                c.fill = HOLIDAY
            elif i >= 5:
                c.fill = WEEKEND
            cells[day] = c
    return cells, top + 2 + len(calendar.monthcalendar(year, month))

wb = Workbook()
ws = wb.active
ws.title = "Q4 2026"
ws.column_dimensions["A"].width = 5
for col in "BCDEFGH":
    ws.column_dimensions[col].width = 18
holidays = {date(2026, 12, 25), date(2026, 12, 26)}
day_cells, next_row = write_month(ws, 2026, 10, 1, holidays)
print(len(day_cells), "days; next free row", next_row)

Each day number is written as text so that events can be appended to the same cell in the next step. The ISO week number for each row comes from the first real day in that week — date.isocalendar().week — and sits in a narrow first column, which is how most European planners read week numbers. The function returns the cell for every date, which makes placing events a dictionary lookup, and the next free row, so months can be stacked on one sheet.

Step 3 — Place the events

Read the event list, group events by date, and append them to the day cells under the day number, one per line:

import pandas as pd

events = pd.read_excel("events.xlsx", parse_dates=["Date"])
by_day = events.groupby(events["Date"].dt.date)["Event"].apply(list)

def place_events(cells, by_day):
    placed = 0
    for day, items in by_day.items():
        if day in cells:
            cell = cells[day]
            cell.value = cell.value + "\n" + "\n".join(f"• {e}" for e in items)
            cell.font = Font(name=FONT, size=9)
            placed += len(items)
    return placed

print(place_events(day_cells, by_day), "events placed in October")

Two events on 2 October appear as two bullet lines in one cell. The font of the whole cell drops to 9 points once it holds events, because a cell has a single font — the day number and the events cannot be styled differently without rich text, which Write Rich Text in a Cell with openpyxl covers if the day number should stay bold. Wrapped text and a fixed row height keep the grid regular; more than three or four events on one day will not fit, which is usually a sign the day needs its own list rather than a bigger cell. Events outside the months on the sheet are ignored here — the next step adds the other months.

Step 4 — A quarter on one sheet, or a sheet per month

Stack the months with a gap between them, or give each its own tab. One sheet suits printing a quarter; tabs suit a year that people navigate:

wb = Workbook()
ws = wb.active
ws.title = "Q4 2026"
ws.column_dimensions["A"].width = 5
for col in "BCDEFGH":
    ws.column_dimensions[col].width = 18

row, all_cells, breaks = 1, {}, []
for month in (10, 11, 12):
    cells, row = write_month(ws, 2026, month, row, holidays)
    all_cells.update(cells)
    breaks.append(row)
    row += 1
print(place_events(all_cells, by_day), "events placed")
for d in sorted(holidays):
    all_cells[d].value += "\nPublic holiday"

The offsite on 18 and 19 November lands in both days, because the event list has a row for each. Holidays get a label as well as the colour, so the calendar still makes sense printed in black and white.

Step 5 — Set the page up for printing

A calendar is usually printed. Landscape, fitted to the page width, one month per page, with the gridlines off because the day cells have their own borders:

from openpyxl.worksheet.pagebreak import Break

ws.page_setup.orientation = "landscape"
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
for r in breaks[:-1]:
    ws.row_breaks.append(Break(id=r))
ws.sheet_view.showGridLines = False
ws.print_options.horizontalCentered = True
wb.save("calendar_q4_2026.xlsx")
print("row breaks after rows:", breaks[:-1])

A manual row break after each month puts every month on its own page; fitToHeight = 0 lets the page count follow the breaks instead of squeezing the quarter onto one page. Set the Print Area and Page Setup with openpyxl covers the options in detail. To check the result without Excel, convert the file to PDF with LibreOffice and look at the pages:

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf calendar_q4_2026.xlsx > /dev/null 2>&1
python -c "from pypdf import PdfReader; print(len(PdfReader('calendar_q4_2026.pdf').pages), 'pages')"

Three pages, one per month, is what the breaks promised.

Common pitfalls

• Week start. Decide Monday or Sunday first with calendar.setfirstweekday; mixing conventions puts weekend shading on the wrong columns.
• Week numbers. ISO weeks start on Monday and week 1 contains the first Thursday of the year; with Sunday-first calendars, use a different rule and say which.
• Unnamed fonts. A Font() without a name is shown in the application's default font, which LibreOffice renders as a serif face; name the font on every style you create.
• Merged cells for titles. They complicate printing and later edits; a bold title in one cell is enough.
• Events as dates with times. Group by .dt.date, or an event at 09:00 does not match the date key.
• Colour-only meaning. Add a text label for holidays so printed copies still carry the information.

Conclusion

Let calendar.monthcalendar lay out each month, write the grid with weekday headers and ISO week numbers, shade weekends and holidays, and keep a dictionary from each date to its cell so events from a list can be appended in one loop. Stack months on one sheet with a page break after each, or give each month a tab, and set landscape, fit-to-width printing with gridlines off. Changing the year or the event file produces next year's calendar.

Frequently asked questions

How do I create a calendar in Excel with Python? Use calendar.monthcalendar(year, month) to get each month as weeks of day numbers, write the numbers into a grid of cells with openpyxl, and style weekends and holidays. Setting the first weekday with calendar.setfirstweekday controls whether weeks start on Monday or Sunday.

How do I add events to the calendar cells? Keep a dictionary from each date to the cell written for it, group your events by date, and append each date's events to its cell's text with line breaks. Turn on wrap_text so they display on separate lines.

How do I show week numbers? Write date(year, month, day).isocalendar().week for the first real day of each week in a column next to the grid. ISO week numbers assume Monday-first weeks.

How do I print one month per page? Add a manual row break after each month with ws.row_breaks.append(Break(id=row)), set the page to landscape and fit to one page wide with fitToHeight set to 0.

Related

• Up one level: Styling Excel Cells with openpyxl — fonts, fills, borders and layout.
• Wrap Text and Align Cells with openpyxl — the cell settings the day squares use.
• Group Excel Data by Fiscal Year and ISO Week — the ISO week rules in detail.
• Create a Gantt Chart in Excel with Python — another way to show work over time.