How to read only visible sheets from Excel using Pandas?

I have to get some random Excel sheets where I want to read only visible sheets from those files.

Consider one file at a time, let’s say I have Mapping_Doc.xls which contains 2-visible sheets and 2-hidden sheets.

As the sheets are less here, I can parse them with names like this:

Code :

xls = pd.ExcelFile('D:\ExcelRead\Mapping_Doc.xls')
print xls.sheet_names
df1 = xls.parse('Sheet1') #visible sheet
df2 = xls.parse('Sheet2') #visible sheet

Output:

[u'sheet1',u'sheet2',u'sheet3',u'sheet4']

How can I get only the visible sheets?

Answer

Pandas uses the xlrd library internally (have a look at the excel.py source code if you’re interested).

You can determine the visibility status by accessing each sheet’s visibility attribute. According to the comments in the xlrd source code, these are the possible values:

• 0 = visible
• 1 = hidden (can be unhidden by user — Format -> Sheet -> Unhide)
• 2 = “very hidden” (can be unhidden only by VBA macro).

Here’s an example that reads an Excel file with 2 worksheets, the first one visible and the second one hidden:

import pandas as pd

xls = pd.ExcelFile('test.xlsx')

sheets = xls.book.sheets()

for sheet in sheets:
    print(sheet.name, sheet.visibility)

Output:

Sheet1 0
Sheet2 1