- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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 values of all rows in a particular column in openpyxl in Python?
In this article, we will show you how to get all the row values of a particular column in an excel file using the python openpyxl library.
Assume we have taken an excel file with the name sampleTutorialsPoint.xlsx containing some random data. We will return all the row values of a given particular column in an excel file.
|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|
|Hardik Pandya||29||All rounder||India||Gujarat Titans||2400||85|
|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|
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, pass the input excel file as an argument to the openpyxl module's load_workbook() function (loads a workbook).
Access the specific sheet of the workbook by giving the sheet name as the index to the workbook object.
Pass the column index to the worksheet and traverse through all the rows of the column.
Print the values of each row in that column
The following program prints all the row values of a particular column (here A)in an 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) # Accessing specific sheet of the workbook. firstWorksheet = newWorkbook["Sheet1"] # Passing the column index to the worksheet and traversing through the each row of the column for column_data in firstWorksheet['E']: # Printing the column values of every row print(column_data.value)
On executing, the above program will generate the following output
Team Royal Challengers Bangalore Sun Risers Hyderabad Chennai Super Kings Gujarat Titans Gujarat Titans Delhi Capitals Mumbai Indians Mumbai Indians Sun Risers Hyderabad Lucknow Capitals
We utilized a sample excel file with dummy data in our software. Using the openpyxl module, we loaded the excel sheet as a workbook and selected the first sheet by passing the sheet name as the index to the workbook Object. The column index was then passed as an index to the worksheet, which iterated over all of the column values and printed them.
We learned how to use the openpyxl module to fetch the excel file as a workbook and get the specified sheet as a worksheet by index. We learned how to retrieve all the values of the rows of a specific column, which is useful in many applications such as retrieving college names, student names, and so on in excel files.
- Related Articles
- How to get all the values of a particular column based on a condition in a worksheet in Selenium with python?
- How to get sheet names using openpyxl in Python?
- Get the sum of a column in all MySQL rows?
- How to increment all the rows of a particular column by 1 in a single MySQL query (ID column +1)?
- How to subset rows of an R data frame based on duplicate values in a particular column?
- How to get all the values of a particular row based on a condition in a worksheet in Selenium with python?
- How to get the values of a particular row in a table in Selenium with python?
- In MySQL how to replace all NULL values in a particular field of a particular table?
- Update all varchar column rows to display values before slash in MySQL?
- How to get all the values in a worksheet in Selenium with python?
- How to subset a matrix based on values in a particular column in R?
- How to concatenate all values of a single column in MySQL?
- Get the number of rows in a particular table with MySQL
- How to delete matrix rows if a particular column value satisfies some condition in R?
- Python - Remove a column with all null values in Pandas