Unexpected Sheet Renaming in OpenPyXL When Only Case Changes

I'm using OpenPyXL in Python to manipulate Excel workbooks. I encountered an unexpected behavior when renaming a sheet. If I change the name of a sheet to a name that only differs in letter case from its previous name, OpenPyXL appends a '1' to the new name.

Here is a Minimal Reproducible Example:

import openpyxl
# Create a new workbook
wb = openpyxl.Workbook()
# Get the default sheet
sheet = wb.active
# Rename the sheet to 'Input (Raw)'
sheet.title = 'Input (Raw)'
# Rename the sheet to 'Input (raw)'
sheet.title = 'Input (raw)'
# Print the current title of the sheet
print(sheet.title)

Expected Output:

Input (raw)

Actual Output:

Input (raw)1

I'm using Python 3.11.1 and openpyxl 3.1.2.

Solution

Per the documentation in the code when renaming or setting the Sheet title:

"""
Set a sheet title, ensuring it is valid. 
Limited to 31 characters, no special characters. 
Duplicate titles will be incremented numerically
"""

So if the Sheet name is 'abc', when you change it to 'Abc' during the title rename the code will determine that there is a sheet already with the name 'Abc' even though it's the same one that will be renamed, and increment the new name by adding 1.

Arguably this is not a desired operation but that is how the code is written.
I guess you'll need to raise it with the Devs if you want it changed or re-write the code yourself; this check is made in the avoid_duplicate_name function in 'child.py'.
The function is a blind check i.e. it just checks if the new Sheet name matches (case insensitive) any existing Sheet name (from wb.worksheets list) and performs the increment if it does.

Modifying the code that calls 'avoid_duplicate_name' will fix the issue by removing the current Sheet name if it exists from the Sheets list so that the duplication check does not try a match against the original name.
Existing code in child.py (line 95, 3.1.2)

        if self.title is not None and self.title != value:
            value = avoid_duplicate_name(self.parent.sheetnames, value)

If the sheet already has a title then remove it from the Sheet list before performing the duplication check;

        if self.title is not None and self.title != value:
            sheet_list = self.parent.sheetnames
            if self.title:
                sheet_list.remove(self.title)
            value = avoid_duplicate_name(sheet_list, value)

^{*no guarantee that modifying openpyxl code wont break other operation}

Of course you could also just use an intermediary name.