Can someone explain why this happens when using openpyxl 2.6.2?

Question

According to my understanding, workbook.get_sheet_names() is deprecated and we should use wb.sheetnames instead with openpyxl 2.6.2.

wb.sheetnames returns a list of all the sheets, so why can't we do this?

    import openpyxl
    wb=openpyxl.load_workbook('example.xlsx') #loading the workbook
    wb.sheetnames #getting the sheetnames with the new recomended code
    #output:['Sheet']
    wb.sheetnames[0]='Another name'
    wb.sheetnames[0] # Checking if it were changed or not
    #output:['Sheet']

Why didn't the above code work if wb.sheetnames acts as a list? No error was displayed but I didn't see the expected changes

This is what example.xls looks like:

A                         B             C
1 4/5/2015 13:34  Apples          73
2 4/5/2015 3:41   Cherries        85
3 4/6/2015 12:46  Pears           14
4 4/8/2015 8:59   Oranges         52
5 4/10/2015 2:07  Apples          152
6 4/10/2015 18:10 Bananas         23
7 4/10/2015 2:40  Strawberries    98

I am a new programmer and can work around this with changing the title attribute but i dont want to leave any gaps in my learning — , Jun 07 '19 at 06:56
You can use [gist](https://gist.github.com) or Google drive or just copy paste your data here in the question on SO. Will work — Amazing Things Around You, Jun 07 '19 at 07:01
You need not have to link in comment, just edit it in the question above. It will work. — Amazing Things Around You, Jun 07 '19 at 07:02
Possible duplicate of [How to rename the sheet name in the spread-sheet using Python?](https://stackoverflow.com/questions/39540789/how-to-rename-the-sheet-name-in-the-spread-sheet-using-python) — Smart Manoj, Jun 07 '19 at 07:03
I mentioned above that i can work around this by using that format in ur suggested link — , Jun 07 '19 at 07:06
all i want is an explanation on why this code doesnt work and how the new wb.sheetnames operates — , Jun 07 '19 at 07:06
@JadAH will it be ok if I give you alternate solution using Pandas? it works — Amazing Things Around You, Jun 07 '19 at 07:07
you can use the .get_sheet_by_name and the .title attributes but is there any way around it? — , Jun 07 '19 at 07:07
All i want is widening my perspective and understanding of old and new versions of openpyxl — , Jun 07 '19 at 07:08
and how excel sheets work in general im new to working with them — , Jun 07 '19 at 07:09
Worksheets are more than just their titles which is why you need to edit this specifically. `wb.sheetnames` tells you it is giving just a list of the sheet names, not the sheets themselves. — Charlie Clark, Jun 07 '19 at 08:39

score 4 · Answer 1 · answered Jun 07 '19 at 07:10

It's because sheetnames is a property; in other words, it looks like an instance attribute, but when you access it, a function is in fact called. You can see this answer for more details.

Now, if we look at the openpyxl source, we can find the definition of sheetnames:

    @property
    def sheetnames(self):
        return [s.title for s in self._sheets]

sheetnames creates and returns a new list every time it is called, which you can prove with id:

from openpyxl import Workbook

book = Workbook()

a = book.sheetnames
b = book.sheetnames

print(id(a) == id(b))

Output:

False

Due to this, you are in fact just modifying the first element of this new list that is created every time you call sheetnames.

the only way is to change the title attribbute directly – Jun 07 '19 at 07:14 — , Jun 07 '19 at 07:14

buran · Accepted Answer · 2019-06-07T07:38:15.180

Link to relevant section of the docs

wb.sheetnames will return list of strings (i.e. just the sheet names), wb.sheetnames[0] is just a string. When you do wb.sheetnames[0]='Another name' you change the first string in the list returned by wb.sheetnames, but this change has no any practical/visible effect, because this list is not assigned to any variable.

there is also wb.worksheets which will return list of sheet objects. You can use this method access the respective sheet and using Worksheet.title property do wb.worksheets[0].title='New Title'

from openpyxl import Workbook
wb = Workbook()
print(wb.sheetnames)
print(type(wb.sheetnames[0]))
print(wb.worksheets) 
print(type(wb.worksheets[0]))
wb.worksheets[0].title='New Title'
print(wb.sheetnames)
print(wb.worksheets)

output

['Sheet']
<class 'str'>
[<Worksheet "Sheet">]
<class 'openpyxl.worksheet.worksheet.Worksheet'>
['New Title']
[<Worksheet "New Title">]

Note - for the example I am using new Workbook instance with just one sheet inside it

Can someone explain why this happens when using openpyxl 2.6.2?

2 Answers2