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_cols() methods to retrieve data.
Selecting a specific range of cells
Let’s consider a workbook called ‘book1.xlsx’ containing the following data.
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.
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)
['Name', 'Kevin', 'Max', 'Sara', 'Julie', 'Peter', 'Nina', 'Damon']
Here we are accessing all the values of the first column by iterating through each row.
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)
['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.