Reading an excel file using Python

One can retrieve information from a spreadsheet. Reading, writing, or modifying the data can be done in Python can be done in using different methods. Also, the user might have to go through various sheets and retrieve data based on some criteria or modify some rows and columns and do a lot of work. Here, we will see the different methods to read our excel file.

Required Module

pip install xlrd

Input File:

Method 1: Reading an excel file using Python using Pandas

In this method, We will first import the Pandas module then we will use Pandas to read our excel file. You can read more operations using the excel file using Pandas in this article. Click here

Python3

# import pandas lib as pd
import pandas as pd

# read by default 1st sheet of an excel file
dataframe1 = pd.read_excel('book2.xlsx')

print(dataframe1)

Output:

Method 2: Reading an excel file using Python using openpyxl

The load_workbook() function opens the Books.xlsx file for reading. This file is passed as an argument to this function. The object of the dataframe.active has been created in the script to read the values of the max_row and the max_column properties. These values are used in the loops to read the content of the Books2.xlsx file. You can read other operations using openpyxl in this article.

Python3

import openpyxl

# Define variable to load the dataframe
dataframe = openpyxl.load_workbook("Book2.xlsx")

# Define variable to read sheet
dataframe1 = dataframe.active

# Iterate the loop to read the cell values
for row in range(0, dataframe1.max_row):
    for col in dataframe1.iter_cols(1, dataframe1.max_column):
        print(col[row].value)

Output: 

Method 3: Reading an excel file using Python using Xlwings

Xlwings can be used to insert data in an Excel file similarly as it reads from an Excel file. Data can be provided as a list or a single input to a certain cell or a selection of cells. You can read other operations using Xlwings in this article.

Python3

# Python3 code to select
# data from excel
import xlwings as xw

# Specifying a sheet
ws = xw.Book("Book2.xlsx").sheets['Sheet1']

# Selecting data from
# a single cell
v1 = ws.range("A1:A7").value
# v2 = ws.range("F5").value
print("Result:", v1, v2)

Output:

Result: ['Name  Age    Stream  Percentage', 
'0      Ankit   18      Math          95', 
'1      Rahul   19   Science          90', 
'2    Shaurya   20  Commerce          85', 
'3  Aishwarya   18      Math          80', 
'4   Priyanka   19   Science          75', 
None]