Iterate through columns using iter_cols of openpyxl

In this tutorial, we will learn how to iterate through columns in an excel sheet in Python. To achieve this, we use the iter_cols() method of openpyxl module.

Python provides the openpyxl module to work with excel files without even having to open the files.  We can perform operations like read, write, draw charts, rename, add, delete, and style in the sheet. Let’s take an example of an excel sheet to iterate through columns.

Creating a workbook

An excel sheet that we are currently working on to perform operations is called a workbook. We use the ‘Workbook’ class from the openpyxl module to define a new workbook. We are saving all the data to ‘itercols.xlsx’ file using the save() method.

Example:

from openpyxl import Workbook  
wb = Workbook()  
sheet = wb.active    
rows = (  
     (45, 78, 48, 65),  
     (81, 30, 52, 86),  
     (23, 95, 87, 27),  
     (25, 32, 89, 13),  
     (52, 81, 20, 44),  
     (34, 51, 86, 12))  
for row in rows:  
    sheet.append(row) 
    book.save('itercols.xlsx')

Output:

itercols

Using iter_cols() method

The iter_cols() method produces cells from the workbook by columns. We have to specify the range using indices of rows and columns.

  • If no indices are specified, it starts from A1.
  • If the worksheet has no cells, an empty tuple is returned.

Syntax:

iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)

Parameters:

  • min_col= index of smallest column (integer)
  • max_col= index of largest column (integer)
  • min_row= index of smallest row (integer)
  • max_row= index of largest row (integer)
  • values_only= specifies what should be returned (bool)

Code:

for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=4):  
    for cell in row:  
        print(cell.value, end=" ")  
    print()

Output:

45 81 23 25 52 34 
78 30 95 32 81 51 
48 52 87 89 20 86 
65 86 27 13 44 12

Continue reading,

Leave a Reply

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