How to iterate over worksheets in workbook, openpyxl

python excel openpyxl
Mike picture Mike · Aug 28, 2013 · Viewed 31.9k times · Source

I've been using the openpyxl module to do some processing on some .xlsx files. I've been trying to figure out how to iterate over sheets in a workbook. I'm not sure if I can get it figured out. I've tried the 2 codes below which both return empty results. My .xlsx file has about 20 sheets, so something should return.

The one thing I couldn't find on the internet, is how to set a workbook to an actual workbook. Usually I am writing to a workbook, so I just initialize it by setting a variable to en empty workbook workbook = Workbook() but in this case, I am unsure if I can open a workbook by doing workbook = Workbook(r"C:\Excel\LOOKUP_TABLES_edited.xlsx")

If anyone can identify what it is I am doing wrong, I would appreciate it.

Here is my code:

workbook = Workbook(r"C:\Excel\LOOKUP_TABLES_edited.xlsx")

for sheet in workbook.worksheets:
    print sheet

# or

for sheet in workbook.worksheets:
    print sheet.title

Answer

alecxe picture alecxe · Aug 28, 2013

Open the workbook via load_workbook() and iterate over worksheets: 

from openpyxl import load_workbook

wb = load_workbook(r"C:\Excel\LOOKUP_TABLES_edited.xlsx")

for sheet in wb.worksheets:
    print sheet

Related questions

Pandas: Looking up the list of sheets in an excel file

The new version of Pandas uses the following interface to load Excel files: read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA']) but what if I don't know the sheets that are available? For example, …

Read More
getting sheet names from openpyxl

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 …

Read More
how to write to a new cell in python using openpyxl

I wrote code which opens an excel file and iterates through each row and passes the value to another function. import openpyxl wb = load_workbook(filename='C:\Users\xxxxx') for ws in wb.worksheets: for row in ws.rows: print …

Read More