Python Program to Read an Excel File Using Openpyxl Module

Python Program to Read an Excel File Using Openpyxl Module

by

In this article we are going to see how we can read excel sheets in Python language. To do this we will be using openpyxl library which is used to read and write to an excel file.

Read an Excel File Using Openpyxl

Python Program to Read an Excel File Using Openpyxl Module

In Python we have openpyxl library which is used to create, modify, read and write different types of excel files like xlsx/xlsm/xltx/xltm etc. When user operates with thousands of records in a excel file and want to pick out few useful information or want to change few records then he/she can do it very easily by using openpyxl library.

To use openpyxl library we first have to install it using pip.

Command : pip install openpyxl

After installation we can use the library to create and modify excel files.

Let’s see different programs to understand it more clearly.

Input File:

Python Program to Read and Excel File Using openpyxl Library

Program-1: Python Program to Print a Particular Cell Value of Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module
  • Store the path to the excel workbook in a variable
  • Load the workbook using the load_workbook( ) function passing the path as a parameter
  • From the workbook object we created, we extract the active sheet from the active attribute
  • Then we create cell objects from the active sheet object
  • Print the value from the cell using the value attribute of the cell object

Program:

# Import the openpyxl library
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Created a cell object from the active sheet using the cell name
cell1 = activeSheet['A2']

# Printing the cell value
print(cell1.value)

Output:

Sejal

Program-2: Python Program to Print Total Number of Rows in Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module
  • Store the path to the excel workbook in a variable
  • Load the workbook using the load_workbook( ) function passing the path as a parameter
  • From the workbook object we created, we extract the active sheet from the active attribute
  • Then we print the number of rows using the max_row attribute of the sheet object

Program:

# Import the openpyxl library
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Printing the number of rows in the sheet
print("Number of rows : ", activeSheet.max_row)

Output:

Number of rows :  7

Program-3: Python Program to Print Total Number of Columns in Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module
  • Store the path to the excel workbook in a variable
  • Load the workbook using the load_workbook( ) function passing the path as a parameter
  • From the workbook object we created, we extract the active sheet from the active attribute
  • Then we print the number of columns using the max_column attribute of the sheet object

Program:

# Import the openpyxl library
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Printing the number of columns in the sheet
print("Number of columns : ", activeSheet.max_column)

Output:

Number of columns :  2

Program-4: Python Program to Print All Column Names of Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module
  • Store the path to the excel workbook in a variable
  • Load the workbook using the load_workbook( ) function passing the path as a parameter
  • From the workbook object we created, we extract the active sheet from the active attribute
  • Then we find and store the number of columns in a variable cols
  • We run a for loop from 1 to cols+1 that creates cell objects and prints their value

Program:

# Import the openpyxl library
from ast import For
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Number of columns
cols = activeSheet.max_column

# Printing the column names using a for loop
for i in range(1, cols + 1):
    currCell = activeSheet.cell(row=1, column=i)
    print(currCell.value)

Output:

Name
Regd. No

Program-5: Python Program to Print First Column Value of Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module.
  • Store the path to the excel workbook in a variable.
  • Load the workbook using the load_workbook( ) function passing the path as a parameter.
  • From the workbook object we created, we extract the active sheet from the active attribute.
  • Then we find and store the number of rows in a variable rows.
  • We run a for loop from 1 to rows+1 that creates cell objects and prints their value.

Program:

# Import the openpyxl library
from ast import For
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Number of rows
rows = activeSheet.max_row

# Printing the first column values using for loop
for i in range(1, rows + 1):
    currCell = activeSheet.cell(row=i, column=1)
    print(currCell.value)

Output:

Name
Sejal
Abhijit
Ruhani
Rahim
Anil
Satyam
Pushpa

Program-6: Python Program to Print a Particular Row Value of Excel File Using Openpyxl Module

Approach:

  • First of all we have to import the openpyxl module.
  • Store the path to the excel workbook in a variable.
  • Load the workbook using the load_workbook( ) function passing the path as a parameter.
  • From the workbook object we created, we extract the active sheet from the active attribute.
  • We use a variable rowNum to store the row number we want to read values from and a cols variable that stores the total number of columns.
  • We run a for loop from 1 to cols+1 that creates cell objects of the specified rows and prints their value.

Program:

# Import the openpyxl library
from ast import For
import openpyxl as opxl

# Path to the excel file
path = "E:\\Article\\Python\\file1.xlsx"

# Created a workbook object that loads the workbook present
# at the path provided
wb = opxl.load_workbook(path)

# Getting the active workbook sheet from the active attribute
activeSheet = wb.active

# Number of columns
cols = activeSheet.max_column

# The row number we want to print from
rowNum = 2

# Printing the row
for i in range(1, cols + 1):
    currCell = activeSheet.cell(row=rowNum, column=i)
    print(currCell.value)

Output:

Sejal 19012099

 

 

Leave a Comment