-1

I cannot get openpyxl to iterate through all the rows I have in an excel notebook.

I am trying to get the code to print all values in a specific column, however, it just iterates over the 500 items and prints the first row 500 times.

This is my code so far:

import pandas as pd
import openpyxl
import requests

wb = openpyxl.load_workbook('search.xlsx')
ws = wb['Results']
df = pd.read_excel (r'search.xls', sheet_name='Results')

for cell in ws:
  y = ws.cell(row=2, column=1).hyperlink.target
  print(y)

Edit: Thanks to @topsail, I got the solution:

for row in ws.iter_rows(min_row=2):  
       print(row[0].hyperlink.target)
sgacs
  • 1
  • 3
  • this statement looks like it always address the same cell `ws.cell(row=2, column=1)` so even though its in a loop, it will always get the hyperlink target from cell B1. I'm not sure but probably you could try `y = cell.hyperlink.target` ... but I don't like cell as a variable name since cell is also the name of a cell object. – topsail Jun 04 '22 at 14:07
  • It is now saying `'tuple' object has no attribute 'hyperlink'` – sgacs Jun 04 '22 at 14:11
  • Okay that sounds like cell in worksheet gives you a tuple of cells. Not sure ... might be a tuple of cells in a row. Or maybe all the (used) cells in the worksheet. Generally I might prefer to loop over rows rather than cells. Then you loop over each row and get the value of the first cell in the row. (Note I should have said A2 I think in my first comment sorry!) – topsail Jun 04 '22 at 14:13
  • `for cell in df.index: print(df['IDRAC Number'][cell])` This goes through all rows but doesn't take the hyperlinks, just the numbers from the hyperlinks – sgacs Jun 04 '22 at 14:23
  • Okay, this looks like it works: ```for row in ws.iter_rows(min_row=2): print(row[0].hyperlink.target)``` This means iterate the rows, but start at row 2, and for each row, we are getting the hyperlink target from the first cell in the row. This will fail if any cells don't have hyperlinks though - so may need try/catch here in that case. – topsail Jun 04 '22 at 14:34
  • Okay I've added that as an answer. Further information on iterating cells is here: https://stackoverflow.com/questions/38619471/iterate-through-all-rows-in-specific-column-openpyxl – topsail Jun 04 '22 at 14:38

1 Answers1

0
import openpyxl

wb = openpyxl.load_workbook('Search.xlsx')
ws = wb['Result']

for row in ws.iter_rows(min_row=1):
    if row[0].hyperlink is not None:
        print(row[0].hyperlink.target)
    else:
        print(f"no hyperlink in cell {row[0].coordinate}")
topsail
  • 2,186
  • 3
  • 17
  • 17