How to read only visible sheets from Excel using Pandas?
I need to get some random Excel sheets where I want to read only visible sheets from these files.
Consider one file at a time, let's say I have Mapping_Doc.xls
one that contains 2-visible sheets and 2-hidden sheets.
Since the sheets are smaller here, I can parse them by 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 visible sheets?
source to share
Pandas uses an internal library xlrd
(have a look if you are interested in excel.py ).
You can determine the visibility status by referring to each attribute visibility
. According to the comments in the xlrd source code , these are the possible values:
- 0 = visible
- 1 = hidden (can be hidden by user - Format -> Sheet -> Unhide)
- 2 = "very hidden" (can only be hidden by VBA macro).
Here's an example that reads an Excel file with two sheets, 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
source to share
@ ƘɌỈSƬƠƑ's answer to this also helped me. I want to add a few points.
For macro files (.xlsm), visibility is unpredictable. Maybe because the VBA code is behind the visibility setting. Even though the sheet is displayed when the file is opened in Excel, the visibility is not always 0 when reading the xlrd.
Check out the screenshots below:
This is what I see in Excel.
Visibility values obtained with xlrd
source to share