Search code examples
pythonexcelpandasdataframexlwings

Use python to iterate over cells in excel range and copy values

I have 3 tables like below in excel. This is more a python for loop question and less a excel question (but it still involves excel)

ID,Name,Age,Subject
1,abc,21,maths
2,def,28,science
3,rew,38,social

ID,Name,Age,Subject
11,adbc,21,maths
23,dedf,28,science
39,rewd,38,social

ID,Name,Age,Subject
111,ancbc,21,maths
232,dedsf,28,science
391,rewsdd,38,social

So, my table range for values is given below

Table1 = D18:G20  (1st row,1st column is D18 (value is 1) and last row, last column is G20 (value is social)
Table2 = Q18:T20
Table3 = AB18:AE20

I would like to do the below

a) Copy D18th value to Q18 and AB18th.

b) Similarly copy D19th value to Q19th and AB19th

c) Repeat this to copy each cell value from Table1 to Table2 and Table3

So, I was trying like below (using Xlwings which allows us to copy range)

sheet1.range("D18:G18").copy()
sheet1.range("Q18:T18").paste()
sheet1.range("AB18:AE18").paste()
sheet1.range("D19:G19").copy()
sheet1.range("Q19:T19").paste()
sheet1.range("AB19:AE19").paste()
sheet1.range("D20:G20").copy()
sheet1.range("Q20:S20").paste()
sheet1.range("AB20:AE20").paste()

But the above is not elegant/efficient. You can see am writing individual line for each range.

Is there any python way to do this over loop where at every iteration a specified range is copied and pasted to the provided multiple destination area

update - input excel screenshot with 2 tables

enter image description here

Solution

  • As you rightly suggested, a for loop should suffice, a double for loop (using openpyxl) :

    from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')
ws = wb['Sheet1']
source = ws['D18':'G20']
table2 = ws['Q18':'T20']
table3 = ws['AB18':'AE20']

for row1, row2, row3 in zip(source, table2, table3):
    for cell1, cell2, cell3 in zip(row1, row2, row3):
        cell2.value = cell1.value
        cell3.value = cell1.value

wb.save('sample.xlsx')