How to read xlsx file with openpyxl in Python

In this tutorial, you will learn how to read an Excel file using Python using openpyxl. In our day-to-day task of analyzing the data, our data can be in CSV or XLSX format.  The pandas library is useful for handling both formats, but the openpyxl provides more functions for the Excel file.

You can check: How to install openpyxl in Python on any OS

The openpyxl library allows the user to open the Excel file and do the operations by visualizing it. I am using the below Excel file:

Reading the file Row wise

To read the file row-wise, .iter_rows() function is used.

from openpyxl import load_workbook


workbook = load_workbook(filename='/content/excelfile.xlsx')

# Provide the sheet name you want to access
worksheet = workbook['Data']  

for row in worksheet.iter_rows(values_only=True):
    print(row)

Output

('Country', 'Age', 'Salary', 'Purchased') 
('France', 44, 72000, 'No') 
('Spain', 27, 48000, 'Yes') 
('Germany', 30, 54000, 'No') 
('Spain', 38, 61000, 'No') 
('Germany', 40, None, 'Yes') 
('France', 35, 58000, 'Yes') 
('Spain', None, 52000, 'No') 
('France', 48, 79000, 'Yes') 
('Germany', 50, 83000, 'No') 
('France', 37, 67000, 'Yes')

Reading the file Cell wise

To read the file cell-wise, .cell() function is used.

from openpyxl import load_workbook

workbook = load_workbook(filename='/content/excelfile.xlsx')

worksheet = workbook['Data'] 

for row in range(1, worksheet.max_row + 1):
    for column in range(1, worksheet.max_column + 1):
        cell_value = worksheet.cell(row=row, column=column).value
        print(cell_value)

Output

Country 
Age 
Salary 
Purchased 
France 
44 
72000 
No 
....
....
37 
67000 
Yes

Also read: Openpyxl – Iterate through all Rows in a Specific Column