Work Automation – Iterating Excel Spreadsheet data

Excel is an integral part for the majority of us working, learning to iterate through a spreadsheet automatically definitely has its advantage. Here we will iterate columns in an Excel spreadsheet using the Openpyxl library. This library allows us to read and write to cells in an Excel spreadsheet with much ease. Do note CSV file types are not compatible with the library and will require to be converted in to an XLSX format.

workbook = openpyxl.load_workbook('C:/Location/Excel.xlsx')
sheet = workbook ["Sheet_Name"]

sheet.delete_rows(sheet.min_row, 1)

Using a for loop we define each column and then begin iterating through the cells. In this example the sheet contains only 2 columns, Name and Email which are then being printed.

for i in range(1,sheet.max_row+1):
Name = str(sheet.cell(row = i, column = 1).value)
Email = str(sheet.cell(row = i, column = 2).value)
print (Name, Email)

Full source code for the project can be found below.

import openpyxl
workbook = openpyxl.load_workbook('C:/Location/Excel.xlsx')
sheet = workbook ["Sheet_Name"]
sheet.delete_rows(sheet.min_row, 1)
for i in range(1,sheet.max_row+1):
     Name = str(sheet.cell(row = i, column = 1).value)
     Email = str(sheet.cell(row = i, column = 2).value)
     print (Name, Email)

Leave a Reply