Getting "TypeError: 'int' object is not iterable" error halfway through a sheet

[Bill St. Louis]

So I'm running code like this:

from openpyxl import load_workbook

# Set the filename to avoid issues with blanks, etc.
file = input("Enter file name: ")

wb = load_workbook(file)
ws = wb.active
ws.title = "Activities"

# Set properties for styling the header cells
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side, NumberFormatDescriptor
# Define a Font object ft where text is bold (b=True)
ft = Font(b=True)
# Define an Alignment object al where contents of cell are centered horizontally and vertically
al = Alignment(horizontal='center', vertical='center', wrapText=True)
# Define fill color for header
fl = PatternFill(patternType='solid', fgColor = 'DDEBF7')
# Define cell borders for header
bd = Border(left=Side(border_style='thin', color='000000'),
                right=Side(border_style='thin', color='000000'),
                top=Side(border_style='thin', color='000000'),
                bottom=Side(border_style='thin', color='000000'),
                diagonal=Side(border_style='thin', color='000000'),
                vertical=Side(border_style='thin', color='000000'),
                horizontal=Side(border_style='thin', color='000000'),
                outline=True)
               
# Iterate thru the columns in the header row and assign font, alignment, fill and border attributes
for row in ws['A1':'K1']:
    for cell in row:
        cell.font = ft
        cell.alignment = al
        cell.fill = fl
        cell.border = bd

# Change heading text
# ws['B1'].value = "zCA Manager"

# Set height of header row
# ws.row_dimensions[1].height = 32   # Set header row to double height to accomodate word wrap

# Adjust column widths and hide appropriate columns

# ws.column_dimensions['A'].hidden = True    
ws.column_dimensions['A'].width = 52        
ws.column_dimensions['B'].width = 29        
ws.column_dimensions['B'].hidden = True    
ws.column_dimensions['C'].width = 29        
ws.column_dimensions['C'].hidden = True      
ws.column_dimensions['D'].width = 29      
ws.column_dimensions['E'].width = 23        
ws.column_dimensions['F'].width = 61        
ws.column_dimensions['G'].width = 25        
ws.column_dimensions['I'].width = 17        
ws.column_dimensions['J'].width = 9.67        
ws.column_dimensions['L'].width = 9.67        

# iterate through rows and adjust values of certain cells

# Define fill color for workshop rows
fl_wkshp = PatternFill(patternType='solid', fgColor = 'D7FBE0')

for row in range(2, ws.max_row + 1):
    rowNum = str(row)
   
# Trim the time off the end of the date fields
    if not (ws['J' + rowNum].value == None):
        ws['J' + rowNum].value = ws['J' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
    if not (ws['K' + rowNum].value == None):
        ws['K' + rowNum].value = ws['K' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
    if not (ws['L' + rowNum].value == None):
        ws['L' + rowNum].value = ws['L' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
   
# Populate the Client Name field
    if not (ws['B' + rowNum].value == None):
        ws['D' + rowNum].value = ws['B' + rowNum].value
    elif not (ws['C' + rowNum].value == None):
        ws['D' + rowNum].value = ws['C' + rowNum].value
    else:
        ws['D' + rowNum].value = None
       
# Look for workshop entries and highlight the rows if the word appears there
    print ("Column A Value: %s" % ws['A' + rowNum].value)
               
    if ("workshop" in ws['A' + rowNum].value):
        for cell in row:
            cell.fill = fl_wkshp

wb.save(file)

Basically, what happens is that I'll get a hundred or so rows into the sheet and the error "TypeError: 'int' object is not iterable" will occur on the line "for cell in row: cell.fill = fl_wkshp" (second to last instruction).  This works fine for many rows and then just dies.  If I delete the offending row and those thereafter, the program completes.  If I delete the offending row and leave the subsequent rows in the sheet, it iterates a bit further and then fails again.

I don't know if this is a memory problem, a row problem, or what.  I don't get why all of a sudden it would fail.  I don't see anything unusual about the offending rows or column values.

Help?

[Charlie Clark]

Am .09.2016, 17:04 Uhr, schrieb Bill St. Louis <wfs...@gmail.com>:

> Basically, what happens is that I'll get a hundred or so rows into the

> sheet and the error "*TypeError: 'int' object is not iterable*" will

> occur on the line "for cell in row: cell.fill = fl_wkshp" (second to
> last
> instruction). This works fine for many rows and then just dies. If I
> delete the offending row and those thereafter, the program completes.
> If I delete the offending row and leave the subsequent rows in the
> sheet, it
> iterates a bit further and then fails again.

> I don't know if this is a memory problem, a row problem, or what. I
> don't get why all of a sudden it would fail. I don't see anything
> unusual about the offending rows or column values.

Hi Bill,

nice to see someone using the API so extensively! Can't really say very
much about the problem without a full traceback, and ideally with the
relevant files but I do note that you do set row to a counter:

for row in range(2, ws.max_row + 1):
rowNum = str(row)

If the following code ever executes within this loop then you can expect
problems:

for cell in row:
cell.fill = fl_wkshp

ws.max_row and the like are really only supposed to be informative. Much
better to use ws.iter_rows() here and if you update to 2.4 (either the
beta or a checkout) it has very clear syntax for setting boundaries.

for row in ws.iter_rows(min_row=2, max_col=12):
if row[1].value is not None:
rows[3] = row[1].value
…
for cell in row[9:11]:
if not cell.value:
cell.value = cell.value[:10]

if 'workshop' in row[0].value:

for cell in row:
cell.fill =fl_wkshp

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Bill St. Louis]

Charlie, thanks for the great, quick response, as usual!  :-)  It appears that the pip install of openpyxl is just for the 2.3.5 release; how would I install 2.4?  I'm not all that adept at the packaging for python libraries. 

[Charlie Clark]

Am .09.2016, 18:27 Uhr, schrieb Bill St. Louis <wfs...@gmail.com>:

> Charlie, thanks for the great, quick response, as usual! It appears

> that the pip install of openpyxl is just for the 2.3.5 release; how
> would I
> install 2.4? I'm not all that adept at the packaging for python
> libraries.

pip install --pre openpyxl will install the beta

Using a checkout is covered in the docs:

https://openpyxl.readthedocs.io/en/latest/index.html#working-with-a-checkout

The 2.4 branch contains a couple of fixes here and there.

[Bill St. Louis]

I apologize for my brain-deadness.  When I run the install, I get this:

BillMBP15:bin Bill$ pip3.5 install --pre openpyxl
Requirement already satisfied (use --upgrade to upgrade): openpyxl in /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages
Requirement already satisfied (use --upgrade to upgrade): jdcal in /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages (from openpyxl)
Requirement already satisfied (use --upgrade to upgrade): et_xmlfile in /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages (from openpyxl)
BillMBP15:bin Bill$ pip3.5 show openpyxl
---
Metadata-Version: 1.1
Name: openpyxl
Version: 2.3.3
Summary: A Python library to read/write Excel 2010 xlsx/xlsm files
Home-page: http://openpyxl.readthedocs.org
Author: See AUTHORS
Author-email: eric.gazoni@gmail.com
License: MIT/Expat
Location: /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages
Requires: jdcal, et-xmlfile
Classifiers:
  Development Status :: 5 - Production/Stable
  Operating System :: MacOS :: MacOS X
  Operating System :: Microsoft :: Windows
  Operating System :: POSIX
  License :: OSI Approved :: MIT License
  Programming Language :: Python
  Programming Language :: Python :: 2.6
  Programming Language :: Python :: 2.7
  Programming Language :: Python :: 3.3
  Programming Language :: Python :: 3.4

Am I doing something wrong that's preventing the 2.4 code from installing?

[Charlie Clark]

Am .09.2016, 21:29 Uhr, schrieb Bill St. Louis <wfs...@gmail.com>:

>
> Am I doing something wrong that's preventing the 2.4 code from
> installing?

pip install -U --pre openpyxl

fill yer boots…

[Aaron Bredon]

On Thu, Sep 1, 2016 at 11:04 AM Bill St. Louis <wfs...@gmail.com> wrote:

So I'm running code like this:

for row in range(2, ws.max_row + 1):
    rowNum = str(row)

OK - so 'row' is an integer containing the row number

... 

   
# Trim the time off the end of the date fields
    if not (ws['J' + rowNum].value == None):
        ws['J' + rowNum].value = ws['J' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
    if not (ws['K' + rowNum].value == None):
        ws['K' + rowNum].value = ws['K' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
    if not (ws['L' + rowNum].value == None):
        ws['L' + rowNum].value = ws['L' + rowNum].value[:10]   # Trim the time off of the date/time field generated by SC
   
# Populate the Client Name field
    if not (ws['B' + rowNum].value == None):
        ws['D' + rowNum].value = ws['B' + rowNum].value
    elif not (ws['C' + rowNum].value == None):
        ws['D' + rowNum].value = ws['C' + rowNum].value
    else:
        ws['D' + rowNum].value = None
       
# Look for workshop entries and highlight the rows if the word appears there
    print ("Column A Value: %s" % ws['A' + rowNum].value)
               
    if ("workshop" in ws['A' + rowNum].value):
        for cell in row:
            cell.fill = fl_wkshp

and you try to iterate over 'row' - which is an integer containing the number of the row.

you might want to try something like the following:

for rownumber,row in enumerate(ws.iter_rows(row_offset=2), start=2):

    rowNum=str(row number)

This should give you both the row number and the actual row that you can access the cells in.

[Bill St. Louis]

Followup:  Thanks, Aaron and Charlie.  It's working now using the iter_rows method.  I had some other minor Python glitches such as case handling with the "in" function.  But all's well now.  As usual, y'all are extremely helpful!