Reading only visible sheets using Pandas

Question

I have an existing code which uses pandas to read an excel workbook. It reads all the sheets including the hidden ones however, I want to read only the visible ones. Tried using this, but it didn't help:

The below are the snippets tried out.

xlFile = '/path/file_name.xlsx'
xl = pd.ExcelFile(xlFile)

list_of_visible_sheets = []
sheets = xl.book.sheets()
for sheet in sheets:
    if sheet.visibility == 0:
        list_of_visible_sheets.append(sheets) 
print(list_of_visible_sheets)

and

list_of_visible_sheets = []
sheets = xl.sheet_names
for sheet in sheets:
    if sheet.visibility == 0:
        list_of_visible_sheets.append(sheets) 
print(list_of_visible_sheets)

How can I get the visible sheets alone?

See this question: https://stackoverflow.com/questions/23157643/openpyxl-and-hidden-unhidden-excel-worksheets It seems that you can use `openpyxl` (which is likely the engine that pandas is using for .xlsx). Open the workbook and iterate through the sheets checking for `ws.sheet_state = 'hidden'`. Pass the list of visible sheets as a parameter to `ExcelFile()`. — DS_London, Oct 20 '21 at 11:21

score 0 · Answer 1 · answered Oct 20 '21 at 11:11

0

Try to pass the sheetname argument to pandas.read_excel If there are not too many sheets, you can create the desired list manually, or use the recipe from that answer and lambdas.

answered Oct 20 '21 at 11:11

Sukharkov

1
1
4

Lambda looks like a good idea. I am sure he doesn't want to make a visible list manualy. – gSorry Oct 20 '21 at 11:23

DS_London · Accepted Answer · 2021-10-26T13:30:18.033

0

You can use this code with openpyxl. It roughly does what the pandas read_excel() function does:

import openpyxl
import pandas as pd

filename = 'TestFile.xlsx'
#Number of rows to skip in each sheet    
nSkip = 1

dfs = { ws.title : pd.DataFrame(list(ws.values)[nSkip:])
        for ws in openpyxl.load_workbook(filename,read_only=True).worksheets 
        if ws.sheet_state != 'hidden'  }

print(dfs)

edited Oct 26 '21 at 13:30

answered Oct 20 '21 at 11:53

DS_London

3,644
1
7
24

Thanks a lot for this. It works well. Just that `read_excel()` had an option to mention the number of rows to skip, which was very easy to use. (Took some time to simplify the code to my understanding. :) Posting it [here](https://gist.github.com/pyarisinghk/b873b2f277e28da463f455497b05699e). Thank you once again for introducing me to [openpyxl](https://openpyxl.readthedocs.io/en/stable/tutorial.html) – Pyari Oct 26 '21 at 12:09
@pyari I amended the code to allow for skipping rows – DS_London Oct 26 '21 at 13:30
`@DS_London - That helped. Thank you. – Pyari Nov 09 '21 at 11:02

Reading only visible sheets using Pandas

2 Answers2