Python Forum
openpyxl Pasting data to different cells
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
openpyxl Pasting data to different cells
#1
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))
Reply
#2
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')
Reply
#3
(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.
Reply
#4
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')
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  openpyxl convert data to float jacklee26 13 5,702 Nov-19-2022, 11:59 AM
Last Post: deanhystad
  Why is copying and pasting a block now broken? WagmoreBarkless 2 1,328 May-05-2022, 05:01 AM
Last Post: WagmoreBarkless
  Why is copying and pasting a block now broken? WagmoreBarkless 1 1,197 May-04-2022, 11:40 PM
Last Post: Larz60+
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,567 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 7,442 Feb-11-2021, 09:31 PM
Last Post: nilamo
  Can you help me to merge the cells with OpenPyXL? TurboC 1 2,164 Feb-01-2021, 12:54 AM
Last Post: Larz60+
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 2,251 Aug-17-2020, 03:44 PM
Last Post: bearcats6001
  [openpyxl] Increment cells being pasted into Template Kristenl2784 4 3,507 Jul-16-2020, 10:00 PM
Last Post: Kristenl2784
  Copy certain cells into new workbook certain cells Kristenl2784 4 2,446 Jul-14-2020, 07:59 PM
Last Post: Kristenl2784
  copy/pasting in excel WHILE keep file format zarize 0 1,917 Jun-23-2020, 03:51 PM
Last Post: zarize

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020