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.
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')
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.
iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)
- 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)
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()
45 81 23 25 52 34 78 30 95 32 81 51 48 52 87 89 20 86 65 86 27 13 44 12