Select a specific range of cells in openpyxl Python

In this tutorial, we will learn how to get a range of cells from an excel sheet using openpyxl module of Python. We are going to use worksheet.cell() method to achieve this. We can also do this using iter_rows() and iter_cols() methods to retrieve data.

Selecting a specific range of cells

Let’s consider a workbook called ‘book1.xlsx’ containing the following data.

Select a specific range of cells in openpyxl Python

Now we are trying to access a specific range of cells using the worksheet.cell() method. It has ‘row‘ and ‘column‘ attributes that accept integer values. This method can only return individual values so we can use a list comprehension to store data.

Example 1:

import openpyxl
workbook=openpyxl.load_workbook("book1.xlsx")
worksheet=workbook.active
data=[worksheet.cell(row=i,column=1).value for i in range(1,9)]
print(data)

Output:

['Name', 'Kevin', 'Max', 'Sara', 'Julie', 'Peter', 'Nina', 'Damon']

Here we are accessing all the values of the first column by iterating through each row.

Example 2:

import openpyxl
workbook=openpyxl.load_workbook("book1.xlsx")
worksheet=workbook.active
data=[worksheet.cell(row=i,column=j).value for i in range(2,6) for j in range(1,3)]
print(data)

Output:

['Kevin', 50, 'Max', 81, 'Sara', 67, 'Julie', 74]

Here we are specifying the range of row and column attributes using two different loops. In this way, we can access data from excel sheets in any different format.

Check:

Leave a Reply

Your email address will not be published. Required fields are marked *