Automate Excel Sheet Creation in Seconds with Python and openpyxl

Introduction

In a recent Python community discussion, a user asked how to automate the creation of Excel workbooks for each year with monthly sheets and predefined column headers. Manually copying and renaming sheets would take several minutes, but a Python solution can finish in about ten seconds.

Problem Statement

The original attempt using Python created the files but failed to write month names and column headers correctly.

Solution

The following code, provided by an experienced contributor, uses pandas together with the openpyxl engine to generate the desired Excel files efficiently.

# coding: utf-8
import pandas as pd
import openpyxl

df = pd.DataFrame({'A': [], 'B': [], 'C': []})
for year in range(1999, 2022):
    path_name = f'./{year}年.xlsx'
    with pd.ExcelWriter(path_name, engine='openpyxl', mode='w+') as writer:
        for month in range(1, 13):
            df.to_excel(writer, index=False, sheet_name=f'{month}月份')
print('文件生成完成')

This script iterates over the years 1999‑2021, creates an Excel file for each year, and adds twelve sheets named after the months, each containing the columns A, B, and C.

Result

Running the script produces Excel files in the current directory, each with the correct month sheets and column headers, as shown in the screenshots.

All generated files can be accessed via the provided GitHub repository:

https://github.com/cassieeric/Python-office-automation