How to copy worksheet onto a different worksheet (not as additional worksheet)

Question

I'm trying to copy a worksheet from one excel file, to a worksheet on different excel file. But the problem is it is adding it as a additional sheet and not pasting on the existing sheet and overwriting the sheet. I know that I am using Before=wb2.Worksheets(1) which adds the new sheet before the existing sheet, but what is the argument to paste onto the existing sheet instead?

import time, os.path, os
from win32com.client import Dispatch

path1 = 'C:\\example.xlsx'
path2 = 'C:\\Todolist2.xlsx'
path3 = 'C:\\example2.xlsx'

xl = Dispatch("Excel.Application")
xl.Visible = True

wb1= xl.Workbooks.Open(Filename=path1)
wb2= xl.Workbooks.Open(Filename=path2)

ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))

Answer 1

How to copy a worksheet from one excel file to another?

from openpyxl import load_workbook
from copy import copy

def copySheet(target, source):
    for (row, col), source_cell in source._cells.items():
        target_cell = target.cell(column=col, row=row)
        target_cell._value = source_cell._value
        target_cell.data_type = source_cell.data_type

        if source_cell.has_style:
            target_cell.font = copy(source_cell.font)
            target_cell.border = copy(source_cell.border)
            target_cell.fill = copy(source_cell.fill)
            target_cell.number_format = copy(source_cell.number_format)
            target_cell.protection = copy(source_cell.protection)
            target_cell.alignment = copy(source_cell.alignment)
        
        if source_cell.hyperlink:
            target_cell._hyperlink = copy(source_cell.hyperlink)

        if source_cell.comment:
            target_cell.comment = copy(source_cell.comment)
        
    for attr in ('row_dimensions', 'column_dimensions'):
        src = getattr(source, attr)
        trg = getattr(target, attr)
        for key, dim in src.items():
            trg[key] = copy(dim)
            trg[key].worksheet = trg

    target.sheet_format = copy(source.sheet_format)
    target.sheet_properties = copy(source.sheet_properties)
    target.merged_cells = copy(source.merged_cells)
    target.page_margins = copy(source.page_margins)
    target.page_setup = copy(source.page_setup)
    target.print_options = copy(source.print_options)


"copy to"
wb1 = load_workbook(path_to)
target = wb1.create_sheet("lol")
"copy from"
wb2 = load_workbook(path_from)
source = wb2.active

copySheet(target=target, source=source)

wb1.save("fusion.xlsx")

Answer 2

@tomcy

The code is below. What I am really trying to accomplish is to be able to keep rewriting data to Todolist2.xlsx. I would really want to have Todolist2.xlsx open in excel application in windows and have it update the sheet whenever there is new data. So far I have found two ways to do this. One is the code you are helping me with using openpyxl. Doing it this way I think I will have to write data to Todolist2 then open. Then with new data, it will have to close before writing data back in. Then reopen it again. Below is what I have so far. Using the 10 sleep to allow me the chance to update example.xlsx so as to simulate writing new data to Todolist2. It works the first go, but on the second, it gives me permission denied to Todolist2.

import openpyxl as xl
from copy import copy
import time

path1 = 'C:\\example.xlsx'
path2 = 'C:\\Todolist2.xlsx'

wb1 = xl.load_workbook(filename=path1)
ws1 = wb1.worksheets[0]

wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.worksheets[0]

while True:
    for row in ws1:
        for cell in row:
            ws2[cell.coordinate].value = cell.value
            if cell.has_style:
                ws2[cell.coordinate].font = copy(cell.font)
                ws2[cell.coordinate].border = copy(cell.border)
                ws2[cell.coordinate].fill = copy(cell.fill)
                ws2[cell.coordinate].number_format = 
                copy(cell.number_format)
                ws2[cell.coordinate].protection = copy(cell.protection)
                ws2[cell.coordinate].alignment = copy(cell.alignment)
    wb2.save(path2)
    wb2.close()
    time.sleep(10)     #during this time I will modify example.xlsx and 
                       #save, so on the next go around it rewrites the 
                       #new data to Todolist1.xlsx

The second way I'm trying to solve this is with win32com. This allows me to keep Todolist2 open in excel in windows while it writes to it from example, example1, then example2. But the problem is, it does not write on the activesheet, it keeps adding additional sheets. So on this one, If I can find a way to keep rewriting over the active sheet in Todolist2 or after it adds the additional sheet, if I can only delete one sheet i'm golden.

import time, os.path, os
from win32com.client import Dispatch

path1 = 'C:\\example.xlsx'
path2 = 'C:\\Todolist2.xlsx'
path3 = 'C:\\example2.xlsx'
path4 = 'C:\\example3.xlsx'


xl = Dispatch("Excel.Application")
xl.Visible = True

wb1= xl.Workbooks.Open(Filename=path1)
wb2= xl.Workbooks.Open(Filename=path2)

ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))

time.sleep(5)

wb3= xl.Workbooks.Open(Filename=path3)
ws3 = wb3.Worksheets(1)

ws2 = wb2.Worksheets(3)      #it seems by using (3) is the only way it 
                             #allows me to delete one sheet before it 
                             #adds another.
ws2.Delete()
ws3.Copy(Before=wb2.Worksheets(1))

time.sleep(5)

wb4= xl.Workbooks.Open(Filename=path4)
ws4 = wb4.Worksheets(1)

ws2.Delete()            #I got into trouble here, and throws error even 
                        #though it does the delete and copy
ws4.Copy(Before=wb2.Worksheets(1))

Answer 3

One way to do it is using openpyxl library. If the cell has style, we can copy it to new sheet also.

import openpyxl as xl
from copy import copy

path1 = 'C:\\example.xlsx'
path2 = 'C:\\Todolist2.xlsx'

wb1 = xl.load_workbook(filename=path1)
ws1 = wb1.worksheets[0]

wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.worksheets[0]

for row in ws1:
    for cell in row:
        ws2[cell.coordinate].value = cell.value
        if cell.has_style:
            ws2[cell.coordinate].font = copy(cell.font)
            ws2[cell.coordinate].border = copy(cell.border)
            ws2[cell.coordinate].fill = copy(cell.fill)
            ws2[cell.coordinate].number_format = copy(cell.number_format)
            ws2[cell.coordinate].protection = copy(cell.protection)
            ws2[cell.coordinate].alignment = copy(cell.alignment)

wb2.save(path2)

Then you will see your sheet2 is replaced by sheet1.