Learn Python – Python read excel file- Basic and advance

Excel is a spreadsheet utility which is developed by way of Microsoft. It is an without problems available tool to organize, analyze, and save the facts in tables. It is extensively used in many exceptional applications all over the world. From Analysts to CEOs, a variety of authorities use Excel for each speedy stats and serious information crunching.

Excel Documents

An Excel spreadsheet report is known as a workbook which is saved in a file with .xlsx extension. The first row of the spreadsheet is usually reserved for the header, while the first column identifies the sampling unit. Each workbook can include multiple sheets that are also known as a worksheets. A field at a particular column and row is called a cell, and each mobile can include a variety or textual content value. The grid of cells with facts forms a sheet.

The lively sheet is described as a sheet in which the person is currently viewing or ultimate considered earlier than closing Excel.

Reading from an Excel file

First, you want to write a command to installation the xlrd module.

pip install xlrd    

Creating a Workbook

A workbook consists of all the data in the excel file. You can create a new workbook from scratch, or you can without problems create a workbook from the excel file that already exists.

Input File

We have taken the snapshot of the workbook.

Code

# Import the xlrd module      
import xlrd     
      
# Define the location of the file     
loc = ("path of file")     
      
# To open the Workbook     
wb = xlrd.open_workbook(loc)     
sheet = wb.sheet_by_index(0)     
      
# For row 0 and column 0     
sheet.cell_value(0, 0)  

Explanation: In the above example, firstly, we have imported the xlrd module and defined the area of the file. Then we have opened the workbook from the excel file that already exists.

Reading from the Pandas

Pandas is defined as an open-source library which is built on the top of the NumPy library. It provides quickly analysis, facts cleaning, and preparation of the data for the consumer and helps each xls and xlsx extensions from the URL.

It is a python bundle which affords a really helpful information shape known as a statistics frame.

Example

Example -   
import pandas as pd    
    
# Read the file    
data = pd.read_csv(".csv", low_memory=False)    
    
# Output the number of rows    
print("Total rows: {0}".format(len(data)))    
    
# See which headers are available    
print(list(data))    

Reading from the openpyxl

First, we want to install an openpyxl module using pip from the command line.

pip install openpyxl    

After that, we need to import the module.

We can additionally read records from the existing spreadsheet using openpyxl. It additionally approves the person to function calculations and add content that was no longer section of the original dataset.

Example

import openpyxl    
my_wb = openpyxl.Workbook()    
my_sheet = my_wb.active    
my_sheet_title = my_sheet.title    
print("My sheet title: " + my_sheet_title)    

Output:

My sheet title: Sheet

To research greater about openpyxl, visit our entire tutorial Click Here. We have discussed crucial element in this tutorial.