Changing multiple sheetnames using openpyxl-CodePudding

I have an excel file with worksheets named from 1 to 15

wb.sheetnames = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15']

I am trying to replace the existing sheetnames using a list of new sheetnames e.g. ['a', 'b', 'c'...]

Would this be possible using for loops?

CodePudding user response：

One 'for' loop should be sufficient.
In simplest form this first code example will just cycle through the list of sheet names and changes each as it exists in the workbook with the the next letter of alphabet starting from 'a'.
This may be a problem if the sheets are jumbled in the workbook and you want a specific sheet to have a specific name.

from openpyxl import load_workbook


excelfile = 'example.xlsx'
wb = load_workbook(excelfile)

for enum, sheet in enumerate(wb.sheetnames,97):
    new_sheet_name = chr(enum)
    print(f'Changing Sheet name {sheet} to {new_sheet_name}')
    wb[sheet].title = new_sheet_name

wb.save('out_'   excelfile)

The code sample above specifically changes the sheet name to a letter of the alphabet. If you're only using the alphabet as an example in your question, and actually want to use other names, you can instead create a list of the names you want and use 'enum' to select the element in that list as you loop through (starting enum at 0 rather than 97).

The code exmple below enures the sheet is named what you want it to be.
In this case the existing name is mapped to the new name in the dictionary 'new_tabname_dict'. Then the existing name is used to look up the new name in the dictionary and applied to the sheet. Again if actual use is sheet names that are not numbers and letters, the commented dictionary can be used and modified to your requirement.

...
from string import ascii_lowercase
### The following line creates the dictionary below it (in comments) using dictionary comprehension.
new_tabname_dict = {str(k):v for k,v in enumerate(ascii_lowercase,1)}
"""
new_tabname_dict = {'1': 'a', '2': 'b', '3': 'c', '4': 'd', '5': 'e',
                    '6': 'f', '7': 'g', '8': 'h', '9': 'i', '10': 'j',
                    '11': 'k', '12': 'l', '13': 'm', '14': 'n', '15': 'o'
                    }
"""

for sheet in wb.sheetnames:
    new_sheet_name = new_tabname_dict[sheet]
    print(f'Changing Sheet name {sheet} to {new_sheet_name}')
    wb[sheet].title = new_sheet_name

wb.save('out_'   excelfile)