Rename Excel Sheet Name Using openpyxl in Python

Imagine you have multiple Excel files in a folder and you want to change the sheet name of all Excel files that contain “Documentation*” to just “Documentation”. As an example, let’s say you have two Excel files in a folder, and each Excel file has two sheets. One of the sheet names contains “Documentation”, and you want to change this sheet name to just “Documentation.” For more information, please refer to the following picture:

Here is how you would do it:

import os
from glob import glob
import openpyxl

# Set Working Directory and Get all *.xlsx files
working_directory = r'C:\tests'
os.chdir(working_directory)
all_files = glob('*.xlsx')

# Change 'Documentation*' sheetname to 'Documentation'
for file in all_files: 
    wb = openpyxl.load_workbook(file)
    sheetname_docs = [sheetname for sheetname in wb.sheetnames if 'Documentation' in sheetname]
    sheet = wb[sheetname_docs[0]]
    sheet.title = 'Documentation'
    wb.save(file)
    
    print("Your sheetname: '{}', already changed to '{}'.".format(sheetname_docs[0], sheet.title))

Output:

Your sheetname: 'Documentation for RSBSS001 - 2G', already changed to 'Documentation'.
Your sheetname: 'Documentation for RSLTE000 - Sy', already changed to 'Documentation'.

Explanation for the script above:

1. Import the necessary modules

import os
from glob import glob
import openpyxl

The import os module sets the working directory, the from glob import glob module retrieves the path and file names of the Excel files, and the import openpyxl module loads the Excel file workbook.

2. Sets the working directory and retrieves the path and names of the Excel files

# Set Working Directory and Get all *.xlsx files
working_directory = r'C:\tests'
os.chdir(working_directory)
all_files = glob('*.xlsx')

3. Load a Workbook

wb = openpyxl.load_workbook(file)

4. Get the sheet name if contain ‘Documentation*’

sheetname_docs = [sheetname for sheetname in wb.sheetnames if 'Documentation' in sheetname]

5. Changing the sheet name from ‘Documentation*’ to just ‘Documentation’

sheet = wb[sheetname_docs[0]]
sheet.title = 'Documentation'
wb.save(file)