[FIXED] How to iterate through seperate sheets to change their properties using Openpyxl

Issue

I am trying to iterate through several sheets in my code so I can format all the sheets to my need in one go. I need this for a planner I am creating, however, my attempt does not work. It only formats the "January" sheet correctly. The default "Sheet" and the other months are not formatted. Could you please help me solve this?

import numpy as np
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font

wb = Workbook()
jan = wb.create_sheet("January")
feb = wb.create_sheet("February")
mar = wb.create_sheet("March")
apr = wb.create_sheet("April")
may = wb.create_sheet("May")
jun = wb.create_sheet("June")
jul = wb.create_sheet("July")
aug = wb.create_sheet("August")
sep = wb.create_sheet("September")
octo = wb.create_sheet("October")
nov = wb.create_sheet("November")
dec = wb.create_sheet("December")

months = np.array([jan, feb, mar, apr, may, jun, jul, aug, sep, octo, nov, dec])

num = 1
for m in range(12):
      for row in range(1, 33):
        months[m].row_dimensions[num].height = 30
        num = num + 1
        for col in range(1, 4):
            months[m].cell(row, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text="center")
            months[m].cell(row, col).font = Font(size="15")

wb.save("sample.xlsx")

Also, I am new to Python and Openpyxl, so this may not be the most efficient way or there could be some stupid coding that I did, if there is please let me know. Thanks in advance!

Solution

Your problem is that you intialise num before your for loop, and then increment it in your loop without ever resetting it. If you scroll down on the sheets of February, March etc you will find that the formatting does exist, it is just 33*m rows further down than you intended. You can solve this by putting num=1 in your for loop.

for m in range(12):
    num = 1
    for row in range(1, 33):
        months[m].row_dimensions[num].height = 30
        num = num + 1
        for col in range(1, 4):
            months[m].cell(row, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text="center")
            months[m].cell(row, col).font = Font(size="15")

However, you are trying to increment num when you already have an incrementing variable - row. Both variables have the same value, so num is not needed and you can replace is with row.

for m in range(12):
    for row in range(1, 33):
        months[m].row_dimensions[row].height = 30
        for col in range(1, 4):
            months[m].cell(row, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text="center")
            months[m].cell(row, col).font = Font(size="15")

You could also format your sheets so that there are only as many rows as days in that month (plus a heading row). A full working example showing this is:

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
from calendar import monthrange

# Create workbook
wb = Workbook()

# List of the months
months = [
    "January",
    "February",
    "March",
    "April",
    "May",
    "June",
    "July",
    "August",
    "September",
    "October",
    "November",
    "December",
    ]

# Create a sheet for each month
month_sheets = [wb.create_sheet(month) for month in months]

# Formatting sheets
for i, sheet in enumerate(month_sheets):
    days_in_month = monthrange(2021, i+1)[1]    # The year input only matters for February (leap years)
    for row in range(1, days_in_month+2):       # +2 to allow for heading row as well
        sheet.row_dimensions[row].height = 30
        for col in range(1, 4):
            sheet.cell(row, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text="center")
            sheet.cell(row, col).font = Font(size="15")

wb.save("sample.xlsx")

Answered By - jezza_99