Load a apreadsheet and copy a row and pasted it in a different location

Question

How can I copy a row for example from D51 to F51 and paste these values in the row T20 to AF20.

I know how to load a spreadsheet

workbook = load_workbook(output)
sheet = workbook.active

But I dont know how to itenarate in a loop to get this

    sheet["T2"] = "=D6"
    sheet["U2"] = "=E6"
    sheet["V2"] = "=F6"
    sheet["W2"] = "=G6"
    sheet["X2"] = "=H6"
    sheet["Y2"] = "=I6"
    sheet["Z2"] = "=J6"
    sheet["AA2"] = "=K6"
    sheet["AB2"] = "=L6"
    sheet["AC2"] = "=M6"
    sheet["AD2"] = "=N6"
    sheet["AE2"] = "=O6"
    sheet["AF2"] = "=P6"

score 1 · Accepted Answer · answered May 08 '22 at 17:55

You can achieve this by using code below... Note that the file output.xlsx is opened, updated and saved. The function num_to_excel_col is borrowed from here. This will update columns 20 (T) onwards for the next 15 columns (all row 2) with the text as "=D6", "=E6", etc. The num_to_col function will convert the col number to equivalent excel string (for eg. 27 will be converted to AA, etc.)

import pandas as pd
import numpy as np
import openpyxl

workbook = openpyxl.load_workbook('output.xlsx')
ws = workbook.active

def num_to_excel_col(n):
    if n < 1:
        raise ValueError("Number must be positive")
    result = ""
    while True:
        if n > 26:
            n, r = divmod(n - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(n + ord('A') - 1) + result

outcol = 4 #Paste in col 'D'

for col in range(20,35): #col 20 is T and doing this for next 15 columns
    txt = "="+num_to_excel_col(outcol)+"6"
    print(txt)
    ws.cell(row=2, column=col).value = txt
    outcol += 1

workbook.save("output.xlsx")

Thanks for your help and sorry for my next question. How can I make it works if instead of the locations asked in the question, I would want to know for example sheet["T3"] = "=D11 ... sheet["AF3"] = "=P11"" I am trying to create a function to reuse your code (that worked) many occasion — Manolo Dominguez Becerra, May 08 '22 at 18:44
From what was in the original question to now - you are changing the row number, is that correct? In that case, assuming you are getting that inputs as integers, then replace the ````"6"```` by ````str(out_row)```` and similarly replace the ````row=2````, by ````row=input_row```` — Redox, May 08 '22 at 19:04

Load a apreadsheet and copy a row and pasted it in a different location

1 Answers1