The Excel file uses the .xlsx extension that lets you save a huge amount of data in several forms. Your data could be in tabular form or any other form and you can apply logical as well as mathematical operations to analyze the data in the sheet. The python script is used for programming when you need to read the data from the Excel file.
Basically, Python uses multiple modules that can sort out the interpretation of an excel file. The modules that Python uses are Pandas, xlrd, and openpyxl. Here in this post, you will get to know everything about reading Excel files in Python.
How to Read Excel Files with xlrd Extension
By default, you will not find this extension in the module. For this reason, you need to install the module first of all. The Excel file extension .xlsx is not supported with the updated version of this module that’s why the 1.2.0 version of the module needs to be installed. While installing, click on the Run option given on the required version of xlrd.
$pip install xlrd==1.2.0
Once the installation is done, make a Python file using the following script for reading the sales.xlsx file with the xlrd module. In the script, you will see the open_workbook() function that needs to be activated for reading the xlsx file. The workbook.sheet_by_index() function is already in the process used in the script with a 0 value argument. The nested “for” loop also helps in reading the cell values of the worksheet in terms of row and column values.
Two separate range() functions are used in the script that explain the row and column size depending on the sheet data.
# Import the xlrd module
import xlrd
# Open the Workbook
workbook = xlrd.open_workbook(“sales.xlsx”)
# Open the worksheet
worksheet = workbook.sheet_by_index(0)
# Iterate the rows and columns
for i in range(0, 5):
for j in range(0, 3):
# Print the cell values with tab space
print(worksheet.cell_value(i, j), end=’\t’)
print(”)
Output
As soon as you are done with the above script, the following output will come up.
Reading Excel File in Python with openpyxl
Using the load_workbook() functional, you can open up the Books.xlsx file for reading purposes. In the script, you can witness the dataframe. active to read the values of the max_column and max_row characteristics. Also, note that the values are helpful for the loops because they let you read the content from the Book2.xlsx file.
- Python3
- import openpyxl
- # Define variable to load the dataframe
- dataframe = load_workbook(“Book2.xlsx”)
- # Define variable to read sheet
- dataframe1 = active
- # Iterate the loop to read the cell values
- for row in range(0, dataframe1.max_row):
- for col in iter_cols(1, dataframe1.max_column):
- print(col[row].value)
Output
Reading Excel File with Pandas
Pandas is a special module used for reading Excel sheet data. You have to install it if it is not in the system. Now, run the command to complete the installation of pandas.
$pip install pandas
Once the installation is done, make a python file using the below-given script for reading the sales.xlsx file. The DataFrame() function is capable to read the data of the xlsx file in the data frame. It also saves the values in the variable named data.
# Import pandas
import pandas as pd
# Load the xlsx file
excel_data = pd.read_excel(‘sales.xlsx’)
# Read the values of the file in the dataframe
data = pd.DataFrame(excel_data, columns=[
‘Sales Date’, ‘Sales Person’, ‘Amount’])
# Print the content
print(“The content of the file is:\n”, data)
Output
In this output, you will experience a difference in comparison with the previous one. In the first column, you can notice the printed row numbers, and the row value is noted from 0.
Closing Thoughts
Do you know how to read xlsx file in Python?
Well now, you know how Python read xlsx files instantly. When you need to use different programming tools, python has to work with xlsx files. In this post, you have found three different ways to read the xlsx files using three Python modules. Just as functions, each module serves different features and characteristics for reading xlsx files in Python.
