Reading only visible sheets using Pandas-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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'

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

print(dfs)