I have some excel data that I would like to copy a range of cell values from one sheet and paste them into another workbook/sheet/any range that I select. I have the openpyxl workbook assignments working as well as selecting a range to copy. But, my copy will only paste in the same range area that it started in the source workbook. I would like to select a different paste range. Also a range can be as big or small as you select.
I have tried several other post listed here however none really point a way to change the destination range area or allow you to select where you want to place the selected range. I do feel like I will need another FOR loop to the pasting process but not sure how to write it out. I would appreciate any help on this.
Example operation
Excel Sheet Source Excel Sheet Destination
A B A B C D E
1 a e 1
2 b f 2
3 a e
4 b f
Code sample
import os
import openpyxl as xl
from openpyxl.utils import rows_from_range
#Assign Folder & Files variables
desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
practicefolder = 'Practice'
source_file = 'source_file.xlsx'
destination_file = 'destination_file.xlsx'
# opening the source excel file
filename = (desktop + '\' + practicefolder + '\' + source_file)
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
# opening the destination excel file
filename1 = (desktop + '\' + practicefolder + '\' + destination_file)
#dest = 'dest_sheet2' #For assigning different sheet
wb2 = xl.load_workbook(filename1)
ws2 = wb2.active
#Selecting the copy range below
range_str = 'A1:B2' #Select copy range
src = ws1
dst = ws2
for row in rows_from_range(range_str):
for cell in row:
dst[cell].value = src[cell].value
wb2.save(str(filename1))
3
To expand a bit on my comment you can include the calculation of the offsets in the code.
Example rather than work out that the row and column offsets needed are 2 and 3 respectively we can just enter the cell coordinate of the new location ‘D3’ and have the code calculate this.
In the code sample below I have included a new variable dst_top_left which is the top left cell where the source data is to be copied. In your example this is ‘D3’. The code then determines the row offset is 2 and the column offset is 3 and sets these when copying the data.
The copy only migrates the cell values no cell formatting is copied.
Note there is an extra import at the top of the code sample, then unchanged code in the middle is not displayed.
from openpyxl.utils.cell import coordinate_to_tuple as ctt
...
# opening the source excel file
filename = (desktop + '\' + practicefolder + '\' + source_file)
wb1 = xl.load_workbook(source_file)
src = wb1.worksheets[0]
# opening the destination excel file
filename1 = (desktop + '\' + practicefolder + '\' + destination_file)
# dest = 'dest_sheet2' #For assigning different sheet
wb2 = xl.load_workbook(destination_file)
dst = wb2.active
# Selecting the copy range below
range_str = 'A1:B2' # Select copy range
### Set the top left cell of the destination range
dst_top_left = 'D3'
### Get a tuple of the difference between the src top left and dst top left
diff = tuple(x-y for x, y in zip(ctt(dst_top_left), ctt(range_str.split(':')[0])))
cr = CellRange(range_str)
for row in src.iter_rows(max_col=cr.max_col, min_col=cr.min_col, max_row=cr.max_row, min_row=cr.min_row):
for cell in row:
### Copy src cell value to the dst cell
dst[cell.coordinate].offset(row=diff[0], column=diff[1]).value = cell.value
wb2.save(destination_file)
1