Python Forum
openpyxl Pasting data to different cells - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: openpyxl Pasting data to different cells (/thread-27656.html)

openpyxl Pasting data to different cells - Kristenl2784 - Jun-15-2020

Hello,

Let me start off by saying I'm brand new at using Python. This script is far from what I want it to do, but I'm just getting a feel for it. This is what I would like this code to do:

* Copy all the source data which it does by using the max row and column code below, but I want it to paste the data to the destination file (template)range B13:O (O because the data always varies in length so theres no end number). Right now it just pastes the data to A1 of the template file which overwrites important formulas that I have in the first 12 rows.

* I also want the destination file to save as something else besides "template" because I will be using this template multiple times. 

import openpyxl as xl; 
  
# opening the source excel file 
filename ="C:/data.xlsx"
wb1 = xl.load_workbook(filename) 
ws1 = wb1.worksheets[1] 
  
# opening the destination excel file  
filename1 ="C:/Template.xlsx"
wb2 = xl.load_workbook(filename1) 
ws2 = wb2.worksheets[2] 
  
# calculate total number of rows and  
# columns in source excel file 
mr = ws1.max_row 
mc = ws1.max_column 
  
# copying the cell values from source  
# excel file to destination excel file 
for i in range (1, mr + 1): 
    for j in range (1, mc + 1): 
        # reading cell value from source excel file 
        c = ws1.cell(row = i, column = j) 

        ws2.cell(row = i, column = j).value = c.value 
  
# saving the destination excel file 
wb2.save(str(filename1))


RE: openpyxl Pasting data to different cells - Yoriz - Jun-15-2020

You could put a shift amount into this line
ws2.cell(row = i+12, column = j+1).value = c.value
to save as something else just change the save name
wb2.save('C:/newfile.xlsx')


RE: openpyxl Pasting data to different cells - Kristenl2784 - Jun-15-2020

(Jun-15-2020, 08:14 PM)Yoriz Wrote: You could put a shift amount into this line
ws2.cell(row = i+12, column = j+1).value = c.value
to save as something else just change the save name
wb2.save('C:/newfile.xlsx')

This worked great! Thank you.

Now, what if I have 100 source files data, data1, data2, data3... etc, and I want to loop it through all 100 files, and use the destination template to paste each data file into, and save as data, data1, data2, data3 etc.

RE: openpyxl Pasting data to different cells - Yoriz - Jun-15-2020

you would create a loop that alters the number of the save file
for index in range(1, 11):
    #rest of code
    wb2.save(f'C:/data{index}.xlsx')