Python [Openpyxl - iterate through each column]

Marouan H 0
Viewed 22

I would like to browse the entire "B" column of my Excel file, However, when I tried all the possibilities, I often got the first two columns with the same answer. I have some problems to find the solution even if I use get_letter_column or something else. I want to make in column "A" the name of the Coin and in column "B" the volume. Here is my code

from tkinter.tix import COLUMN
from bs4 import BeautifulSoup as bs
import requests
from headers import HEADERS
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter as get_idx

book = load_workbook("Crypto.xlsx")
ws = book.active

response = requests.get("https://www.coingecko.com/", headers=HEADERS)
html = response.text

soup = bs(html, "html.parser")
coin_50 = soup.find_all("span", class_="lg:tw-flex font-bold tw-items-center tw-justify-between")[:50]
volume_50 = soup.find_all("span", class_="no-wrap")[:50]

crypto = [crypto.text.replace("\n", "") for crypto in coin_50]
volume = [volume.text for volume in volume_50]

for row in ws.iter_cols(max_row=len(crypto), min_col=1):
    for enum, cell in enumerate(row):
        cell.value = crypto[enum]

for row in ws.iter_cols(max_row=(len(volume)), cols = 2):
    for enum, cell in enumerate(row):
        cell.value = volume[enum]

book.save("Crypto.xlsx")
openpyxl excel python
1 Answers

In order for your code to work as it you'd need to specify the column to write to in the second loop. I'd expect a syntax error on this line anyway, however you'd need to specify column 2

for row in ws.iter_cols(max_row=(len(volume)), cols = 2):

change the line to this

for row in ws.iter_cols(max_row=(len(volume)), min_col=2, max_col=2):

However looping through the rows twice is inefficient, both columns can be updated on the same loop

...
crypto = [crypto.text.replace("\n", "") for crypto in coin_50]
volume = [volume.text for volume in volume_50]

for row in ws.iter_cols(max_row=len(crypto), min_col=1):
    for enum, cell in enumerate(row):
        cell.value = crypto[enum]
        ## The next column is an offset from this cell so update it as well
        cell.offset(column=1).value = volume[enum]

## Change the iteration to loop through column B 
# for row in ws.iter_cols(max_row=(len(volume)), min_col=2, max_col=2):
#     for enum, cell in enumerate(row):
#         cell.value = volume[enum]

book.save("Crypto.xlsx")
moken0
Related