getting sheet names from openpyxl

Question

I have a moderately large xlsx file (around 14 MB) and OpenOffice hangs trying to open it. I was trying to use openpyxl to read the content, following this tutorial. The code snippet is as follows:

 from openpyxl import load_workbook
 wb = load_workbook(filename = 'large_file.xlsx', use_iterators = True)
 ws = wb.get_sheet_by_name(name = 'big_data') 

The problem is, I don't know the sheet name, and Sheet1/Sheet2.. etc. didn't work (returned NoneType object). I could not find a documentation telling me How to get the sheet names for an xlsx files using openpyxl. Can anyone help me?

Reorganization

Answer 1

Use the sheetnames property:

sheetnames

Returns the list of the names of worksheets in this workbook.

Names are returned in the worksheets order.

Type: list of strings

print (wb.sheetnames)

You can also get worksheet objects from wb.worksheets:

ws = wb.worksheets[0]

Zimmermann

wb.sheetnames is the preferred spelling. – Admittedly

It would be so incredibly handy to have sheet name as a property in WorkSheet class. – Hackle

@ÁronLőrincz Sheet name is avaialble on a WorkSheet objects as wsObj.title – Morgenthaler

This answer is out of date. get_sheet_names() is now deprecated. Use the wb.sheetnames attribute. – Runion

@Runion thanks for heads up! Updated the answer accordingly. – Zimmermann

Answer 2

As a complement to the other answers, for a particular worksheet, you can also use cf documentation in the constructor parameters:

ws.title

Streamlet

Answer 3

python 3.x for get sheet name you must use attribute

g_sheet=wb.sheetnames

return by list

for i in g_sheet:
    print(i)

**shoose any name **

ws=wb[g_sheet[0]]

or ws=wb[any name] suppose name sheet is paster

ws=wb["paster"]

Titanic

Answer 4

As mentioned the earlier answer you can get the list of sheet names by using the ws.sheetnames

But if you know the sheet names you can get that worksheet object by

ws.get_sheet_by_name("YOUR_SHEET_NAME")

Another way of doing this is as mentioned in earlier answer

ws['YOUR_SHEET_NAME']

Rodeo

Note .get_sheet_by_name is depreciated so you should only use the second option wb[sheetname] also noting that it is wb (workbook) not ws (worksheet) so neither answer here is completely correct. – Leiker

Answer 5

To get the names of the sheets from workbook;

List_of_sheets = wb.sheetnames
print(List_of_sheets) 

Canaletto

Answer 6

for worksheet in workbook:
    print(worksheet.name)

Ruthenic

There is no attribute name for the worksheet object in openpyxl, so worksheet.name would just return an attribute error. The correct attribute is title so the line should be print(worksheet.title) – Leiker

Answer 7

You can look up the worksheet object by name with the workbook name index itself.

ws = wb["big_data"]

Micro