Read an Excel File in Python
To read an Excel file you have to open the spreadsheet using the load_workbook() method. After that, you can use the active to select the first sheet available and the cell attribute to select the cell by passing the row and column parameter. The value attribute prints the value of the particular cell. See the below example to get a better understanding.
Note: The first row or column integer is 1, not 0.
Example:
In this example, a Python program uses the openpyxl module to read an Excel file (“gfg.xlsx”), opens the workbook, and retrieves the value of the cell in the first row and first column, printing it to the console.
Python3
# import openpyxl module import openpyxl # Give the location of the file path = "gfg.xlsx" # To open the workbook # workbook object is created wb_obj = openpyxl.load_workbook(path) # Get workbook active sheet object # from the active attribute sheet_obj = wb_obj.active cell_obj = sheet_obj.cell(row=1, column=1) print(cell_obj.value) |
Output:
Name
Python Openpyxl Read multiple cells
There can be two ways of reading from multiple cells:
- Reading through Rows and Columns in Excel with openpyxl
- Read from multiple cells using the cell name
Reading through Rows and Columns in Excel with openpyxl
We can get the count of the total rows and columns using the max_row and max_column respectively. We can use these values inside the for loop to get the value of the desired row or column or any cell depending upon the situation. Let’s see how to get the value of the first column and first row.
In this example, a Python program using the openpyxl module reads an Excel file (“gfg.xlsx”). It retrieves and prints the total number of rows and columns in the active sheet, followed by displaying the values of the first column and first row through iterative loops.
Python3
import openpyxl # Give the location of the file path = "gfg.xlsx" wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active row = sheet_obj.max_row column = sheet_obj.max_column print("Total Rows:", row) print("Total Columns:", column) print("\nValue of first column") for i in range(1, row + 1): cell_obj = sheet_obj.cell(row=i, column=1) print(cell_obj.value) print("\nValue of first row") for i in range(1, column + 1): cell_obj = sheet_obj.cell(row=2, column=i) print(cell_obj.value, end=" ") |
Output:
Total Rows: 6
Total Columns: 4
Value of first column
Name
Ankit
Rahul
Priya
Nikhil
Nisha
Value of first row
Ankit B.Tech CSE 4
Read from Multiple Cells Using the Cell Name
We can also read from multiple cells using the cell name. This can be seen as the list slicing of Python. In this example, a Python program utilizes the openpyxl module to read an Excel file (“gfg.xlsx”). It creates a cell object by specifying a range from ‘A1’ to ‘B6’ in the active sheet and prints the values of each cell pair within that range using a for loop.
Python3
import openpyxl # Give the location of the file path = "gfg.xlsx" wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active cell_obj = sheet_obj['A1': 'B6'] for cell1, cell2 in cell_obj: print(cell1.value, cell2.value) |
Output:
Name Course
Ankit B.Tech
Rahul M.Tech
Priya MBA
Nikhil B.Tech
Nisha B.Tech
Refer to the below article to get detailed information about reading excel files using openpyxl.
Working with Excel Spreadsheets in Python
You all must have worked with Excel at some time in your life and must have felt the need to automate some repetitive or tedious task. Don’t worry in this tutorial we are going to learn about how to work with Excel using Python, or automating Excel using Python. We will be covering this with the help of the Openpyxl module and will also see how to get Python in Excel.