Python Read Excel file

Python Read Excel file

Excel is the spreadsheet application for Window, which is developed by Microsoft. The Excel stores data in the tabular form. It provides easy access to analyze and maintain the data. It is widely used in many different software fields.

An excel spreadsheet document is saved in the file with .xlsx extension. The first row of the spreadsheet commonly reserved for the header, while the first column identifies the sampling unit.

A box at a specific column and row is called a cell, and each cell can include a number or text value. The grid of cells with data forms a sheet.

Read from the Excel file

Python provides facilities to read, write, and modify the excel file. The xlrd module is used to work with the excel file. Python does not come with the xlrd module. First, we need to install this module by using following command:

pip install xlrd

Creating a Workbook:

A workbook holds all the data in the excel file. Let’s consider the following input (excel) file.

Consider the following code:

import xlrd
# Give the location of the file
loc = ("location of file")
 # To open a Workbook
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0) 
 #for row 0 and column 0
sheet.cell_value(0, 0) 

In the above example, first, we imported the xlrd module and declared the loc variable, which holds the location of the file. Then we opened the working workbook from the excel file.

• Extract the number of columns and rows

import xlrd
loc = ("location of file")
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
sheet.cell_value(0,0)
print("The number of column:",sheet.ncols)
print("The number of rows:",sheet.nrows)

Output:

The number of column: 3
The number of rows: 7

• Extracting all column name

import xlrd
loc = ("location of file")
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
# For row 0 and column 0
sheet.cell_value(0, 0)
for i in range(sheet.ncols):
    print(sheet.cell_value(0, i))

Output:

Roll No.
Name
Year 

• Extracting a particular row value

import xlrd
# loc = ("location of file")
loc = ("location of file")
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
# For row 0 and column 0
sheet.cell_value(0, 0)
print(sheet.row_values(1)) #It will return an list containing the row value
print(sheet.row_values(2)) #It will return an list containing the row value

Output:

[90017.0, 'Himanshu Dubey', 1.0]
[90018.0, 'Sachin Tiwari', 1.0] 

Reading from the Pandas

Pandas is an open-source Python library which is built on the top of the NumPy library. First, we need to import the pandas module. It supports both xls and xlsx extensions from the URL. Consider the following example:

import pandas as pd
#Using read_excel function to read file
df = pd.read_excel('location of file')
print(df) 

Output:

 Roll No. Name Year
 0 90017 Himanshu Dubey 1
 1 90018 Sachin Tiwari  1
 2 90019 Krishna Shukla 1
 3 80014 Prince Sharma  2
 4 80015 Anubhav Panday 2
 5 80013 Aradhya        3  

• Exacting rows name

import pandas as pd
#Using read_excel function to read file
df = pd.read_excel('location of file')
print(df.columns) 

Output

(['Roll No.', 'Name', 'Year'], dtype='object')

Reading from the openpyxl

Python provides openpyxl which can perform multiple operations on the excel files such as reading, writing, and arithmetic operations. We need to install openpyxl using pip from the command line.

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet_title = sheet.title
print("My sheet title: " + sheet_title)

Output:

My sheet title: Sheet