How to write a loop with openpyxl that allows me to copy a range many times below (watch photo)

Question

I work in Finance and I'm pretty new to Python. I'd like to make a loop with openpyxl which copies the cell contents B2:B15 below in the same column. Ideally many times till I get to row 184. I've worked out the following loop which works but only copies the value B2 and not B2:B15.

for row in range(16,184):
    for col in range(1,2):
        char = get_column_letter(col)
        ws[char + str(row)] = ws(['B2'].value)

I'll let you a screen of my Excel to understand what I´m trying to do. ExcelScreen Thank you very much

Answer 1

You can get in in a few simple steps.

1. Import libraries:

   import openpyxl import pandas as pd

2. Load excel to a var

   wb = openpyxl.load_workbook("c://...//yourfile.xlsx")

3. Choose sheet from excel

   sh = wb.active

4. Create an empty list

   list = []

5. Loop to read the desired cells

   for i in range(2,16): res = sh.cell(row=i,column=2).value list.append(res)

6. [Bonus step]. Convert list to dataframe

   pd.DataFrame (list, columns = ['desired_column_name'])

Saying “thanks” is appreciated, but it doesn’t answer the question. Instead, vote up the answers that helped you the most! If these answers were helpful to you, please consider saying thank you in a more constructive way – by contributing your own answers to questions your peers have asked here.

Answer 2

There are two ways of doing this. Using the "programmatic" interface ws.iter_rows() means you only need to pass in row or column numbers.

The first one is simply to use the offset method:

for row in ws.iter_rows(min_col=2, max_col=2, min_row=2, max_row=15):
    cell = row[0]
    offset = cell.offest(rows=14)
    offset.value = cell.value

You could build a loop for this that increments the offset each time.

Alternatively, you can loop through two loops at the same time:

src = ws.iter_rows(min_col=2, max_col=2, min_row=2, max_row=15)
target = ws.iter_rows(min_col=2, max_col=2, min_row=16, max_row=29)

for r1, r2 in zip(src, target):
   for c1, c2 in zip(r1, r2):
       c2.value = c1.value

Again you could build a loop for this.

Or, if you simply want to repeat the values 11 times:

src = ws.iter_rows(min_col=2, max_col=2, min_row=2, max_row=15, values_only=True)
src = [row[0] for row in src] * 11

for r1, r2 in zip(src, ws.iter_rows(min_col=2, max_col=2, min_row=16, max_row=184)):
    r2[0].value = r1