26

I am reading a excel file using python.

import pandas as pd
import os

xls = pd.ExcelFile('D:\DirectoryProject\Mapping.xlsx')

It has several number of data sheets which I don't know. How can I count the total number of sheets in Mapping.xlsx file using Python?

jpp
  • 159,742
  • 34
  • 281
  • 339

2 Answers2

60

openpyxl

import openpyxl

wb = openpyxl.load_workbook('file.xlsx') 
res = len(wb.sheetnames)

pandas

import pandas as pd

xl = pd.ExcelFile('file.xlsx')
res = len(xl.sheet_names)

xlrd

import xlrd

# use on_demand=True to avoid loading worksheet data into memory
wb = xlrd.open_workbook('file.xlsx', on_demand=True)
res = len(wb.sheet_names())  # or wb.nsheets
jpp
  • 159,742
  • 34
  • 281
  • 339
1

Just to add to the previous answer -

len(pd.read_excel(r"D:\DirectoryProject\Mapping.xlsx", sheet_name="None"))

This way you can get the number of sheets as well.

neanderslob
  • 2,633
  • 6
  • 40
  • 82
Sankar
  • 546
  • 4
  • 15
  • 2
    This solution works, but it involves reading all the worksheets into memory (via a dictionary) before counting the number of sheets. As such, it appears overkill. – jpp Mar 07 '20 at 14:31
  • that is true, in that case we can use xlrd's on_demand option as you have mentioned in your answer – Sankar Mar 07 '20 at 17:21