Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to get sheet names using openpyxl in Python?
In this article, we will show you how to get all the sheet names found in an excel file using python openpyxl library.
Assume we have taken an excel file with the name sampleTutorialsPoint.xlsx containing multiple sheets with some random data. We will return all the sheet names found in a given excel file using the sheetnames attribute.
sampleTutorialsPoint.xlsx
firstSheet
| Player Name | Age | Type | Country | Team | Runs | Wickets |
|---|---|---|---|---|---|---|
| Virat Kohli | 33 | Batsman | India | Royal Challengers Bangalore | 6300 | 20 |
| Bhuvaneshwar Kumar | 34 | Batsman | India | Sun Risers Hyderabad | 333 | 140 |
| Mahendra Singh Dhoni | 39 | Batsman | India | Chennai Super Kings | 4500 | 0 |
| Rashid Khan | 28 | Bowler | Afghanistan | Gujarat Titans | 500 | 130 |
SheetTwo
| David Warner | 34 | Batsman | Australia | Delhi Capitals | 5500 | 12 |
| Kieron Pollard | 35 | All rounder | West Indies | Mumbai Indians | 3000 | 67 |
| Rohit Sharma | 33 | Batsman | India | Mumbai Indians | 5456 | 20 |
| Kane Williamson | 33 | Batsman | New Zealand | Sun Risers Hyderabad | 3222 | 5 |
| Kagiso Rabada | 29 | Bowler | South Africa | Lucknow Capitals | 335 | 111 |
3rdsheet
| Tutorials Point | Tutorials Point |
|---|
Algorithm (Steps)
Following are the Algorithm/steps to be followed to perform the desired task −
Use the import keyword, to import the openpyxl module (Openpyxl is a Python package for interacting with and managing Excel files. Excel 2010 and later files with the xlsx/xlsm/xltx/xltm extensions are supported. Data scientists use Openpyxl for data analysis, data copying, data mining, drawing charts, styling sheets, formula addition, and other operations)
pip install openpyxl
Create a variable to store the path of the input excel file.
To create/load a workbook object, pass the input excel file to the openpyxl module's load_workbook() function (loads a workbook).
By applying the sheetnames attribute to the workbook, you obtain a list of all the sheetnames.
Traverse in the sheetNames List using the for loop and print the corresponding sheetNames.
Example
The following program prints all the sheet names found in an input excel file −
# importing openpyxl module import openpyxl # input excel file path inputExcelFile ="sampleTutorialsPoint.xlsx" # creating or loading an excel workbook newWorkbook = openpyxl.load_workbook(inputExcelFile) # printing all the sheetnames in an excel file using sheetnames attribute print('The Sheet names of the given excel file: ') # Getting the sheetnames as a list using the sheetnames attribute sheetNames=newWorkbook.sheetnames # Traversing in the sheetNames list for name in sheetNames: print(name)
Output
On executing, the above program will generate the following output −
The Sheet names of the given excel file: firstSheet SheetTwo 3rdsheet
In our program, we used a sample excel file with dummy data. The excel file comprises a lot of sheets. By applying the sheetnames attribute to the workbook, we get a list of all the sheet names. Then we go through the list one by one, printing the corresponding sheetnames.
Conclusion
We learned how to use the openpyxl module to create a workbook from an excel file. We also learned how to extract the sheetnames of an excel file using the sheetnames attribute and how to display the list's contents one by one.
13K+ Views
