Iterate Over Worksheets, Rows, Columns

Iterate over Worksheets, Rows, Columns

Read the OpenPyXL Documentation

Iteration over all worksheets in a workbook, for instance:

for n, sheet in enumerate(wb.worksheets):
    print('Sheet Index:[{}], Title:{}'.format(n, sheet.title))

Output:

Sheet Index:[0], Title: Sheet    
Sheet Index:[1], Title: Sheet1    
Sheet Index:[2], Title: Sheet2    

---

Iteration over all rows and columns in one Worksheet:

worksheet = workbook.get_sheet_by_name('Sheet')

for row_cells in worksheet.iter_rows():
    for cell in row_cells:
       print('%s: cell.value=%s' % (cell, cell.value) )

Output:

<Cell Sheet.A1>: cell.value=²234
<Cell Sheet.B1>: cell.value=12.5
<Cell Sheet.C1>: cell.value=C1
<Cell Sheet.D1>: cell.value=D1
<Cell Sheet.A2>: cell.value=1234
<Cell Sheet.B2>: cell.value=8.2
<Cell Sheet.C2>: cell.value=C2
<Cell Sheet.D2>: cell.value=D2  

---

Iteration over all columns of one row, for instance row==2:

for row_cells in worksheet.iter_rows(min_row=2, max_row=2):
    for cell in row_cells:
        print('%s: cell.value=%s' % (cell, cell.value) )  

Output:

<Cell Sheet.A2>: cell.value=1234  
<Cell Sheet.B2>: cell.value=8.2  
<Cell Sheet.C2>: cell.value=C2  
<Cell Sheet.D2>: cell.value=D2  

---

Iteration over all rows, only column 2:

for col_cells in worksheet.iter_cols(min_col=2, max_col=2):
    for cell in col_cells:
        print('%s: cell.value=%s' % (cell, cell.value))

Output:

<Cell Sheet.B1>: cell.value=12.5
<Cell Sheet.B2>: cell.value=8.2
<Cell Sheet.B3>: cell.value=9.8
<Cell Sheet.B4>: cell.value=10.1
<Cell Sheet.B5>: cell.value=7.7

Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2

How to iterate over a Row or Column of an Excel sheet using Python and look for specific value in cells?

To iterate over an excel sheet, you can use the sheet.nrows() function in the xlrd module.

import xlrd

workbook = xlrd.open_workbook("my_path") # Opens your file
sheet = workbook.sheet_by_index(0) # Gets the first sheet

for row in range(sheet.nrows): # Iterates over your sheet
    row_value = sheet.row_values(row)
    if row_value[1] == "my_value":
        print row_value

iterate through all rows in specific column openpyxl

You can specify a range to iterate over with ws.iter_rows():

import openpyxl

wb = openpyxl.load_workbook('C:/workbook.xlsx')
ws = wb['Sheet3']
for row in ws.iter_rows('C{}:C{}'.format(ws.min_row,ws.max_row)):
    for cell in row:
        print cell.value

Edit: per your comment you want the cell values in a list:

import openpyxl

wb = openpyxl.load_workbook('c:/_twd/2016-06-23_xlrd_xlwt/input.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
mylist = []
for row in ws.iter_rows('A{}:A{}'.format(ws.min_row,ws.max_row)):
    for cell in row:
        mylist.append(cell.value)
print mylist 

Iterate over a column, open worksheet, and Copy/Paste

This type of question (Copy from workbook to workbook) has been asked and answered dozens of times on here and there are many many great answers to reference and learn from. But that only takes care of objective #4.

Objective #1 can be completed by using the Worksheet_Change event or the Workbook_SheetChange event. These are triggered every time any cell formula/value is edited. So it would trigger when your users are entering new data.

If you have a specific set of sheets you want to monitor, put a Worksheet_Change script into those sheet's code module. Something like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    
    If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
        For Each Cell In Intersect(Target, Me.Columns("D"))
            If Cell.Value = "Coord Issue" Then Call prmpt
        Next Cell
    End If
End Sub

If you want to watch for changes on every sheet in the workbook, put a script into the Workbook's code module like the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Cell As Range
    
    If Not Intersect(Target, Sh.Columns("D")) Is Nothing Then
        For Each Cell In Intersect(Target, Sh.Columns("D"))
            If Cell.Value = "Coord Issue" Then Call prmpt
        Next Cell
    End If
End Sub

In my example scripts I wrote Call prmpt to reference the code you have already written. But that won't work if your sub prmpt is not available from the calling code module. The sub prmpt will need to be in the same workbook or will need to be properly referenced like Call YourOtherWorkbook.prmpt

To explain the other parts of my example script:
Intersect can be used to check if the changed cells were in Column D by seeing if the returned value from Intersect is Not Nothing. If the changed range was in column D then the returned range will be those cells and therefore Not Nothing.

Then we can loop through that range cell by cell checking if any of the newly changed values meet the criteria. If they meet the criteria, we can call on the sub you have written.

Using pandas combine worksheets, iterate through a specific column, add rows to a new list

import pandas as pd
import numpy as np

# read excel file into notebook assign to pro2019
pro2019 = pd.read_excel(path_to_file, sheet_name=None)

# concatenate all of the worksheets within the file removing the index 
# from individual sheets
df = pd.concat(pro2019, ignore_index=True)

# create empty list to store farmer codes
pro_codelist = []

# iterate through the df column titled "FARMER CODE"
# append each code to pro_codelist
for code in df["FARMER CODE"]:
    pro_codelist.append(code)

---

Related Topics