produce dataframe of Count no. of sheets in all workbooks in a directory-CodePudding

path =r"C:\Users\Ayush\Desktop\New folder"

filenames = glob.glob(path   "\*.xlsx")
print('File names:', filenames)

xl = pd.ExcelFile('C:\\Users\\Ayush\\Desktop\\New folder\\1.xlsx')
res = len(xl.sheet_names)
print(res)

for file in filenames
xl1 = pd.ExcelFile(filenames)
res = len(xl1.sheet_names)
print(res)

CodePudding user response：

Try this...

import pandas as pd
from os import listdir
from os.path import isfile, join

mypath = r"C:\Users"
onlyfiles = [f for f in listdir(mypath) if isfile(join(mypath, f))]

filename, cnt, sheets = [],[],[]
for file in onlyfiles:
    if ".xlsx" in file:
        df = pd.ExcelFile(mypath "\\" file)
        filename.append(file)
        cnt.append(len(df.sheet_names))
        sheets.append(df.sheet_names)
df_final = pd.DataFrame({"filename":filename,"sheets cnt":cnt,"sheets name":sheets})

Hope this helps...