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.
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.
-
- Python Program to Print a Particular Cell Value of Excel File Using Openpyxl Module
- Python Program to Print Total Number of Rows in Excel File Using Openpyxl Module
- Python Program to Print Total Number of Columns in Excel File Using Openpyxl Module
- Python Program to Print All Column Names of Excel File Using Openpyxl Module
- Python Program to Print First Column Value of Excel File Using Openpyxl Module
- Python Program to Print a Particular Row Value of Excel File Using Openpyxl Module
Input File:
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 acols
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