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?
Advertisement
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