How to copy over an Excel sheet to another workbook in Python

I have a string with a sourcefile path and another string with a destfile path, both pointing to Excel workbooks.

I want to take the first sheet of the sourcefile and copy it as a new tab to the destfile (doesn’t matter where in the destfile), then save it.

Couldn’t find an easy way in xlrd or xlwt or xlutils to do this. Am I missing something?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Solution 1

A Python-only solution using the openpyxl package. Only data values will be copied.

import openpyxl as xl

path1 = 'C:\Users\Xukrao\Desktop\workbook1.xlsx'
path2 = 'C:\Users\Xukrao\Desktop\workbook2.xlsx'

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

wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.create_sheet(ws1.title)

for row in ws1:
    for cell in row:
        ws2[cell.coordinate].value = cell.value

wb2.save(path2)

Solution 2

A solution that uses the pywin32 package to delegate the copying operation to an Excel application. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows machine that has MS Excel installed.

from win32com.client import Dispatch

path1 = 'C:\Users\Xukrao\Desktop\workbook1.xlsx'
path2 = 'C:\Users\Xukrao\Desktop\workbook2.xlsx'

xl = Dispatch("Excel.Application")
xl.Visible = True  # You can remove this line if you don't want the Excel application to be visible

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

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

wb2.Close(SaveChanges=True)
xl.Quit()

Solution 3

A solution that uses the xlwings package to delegate the copying operation to an Excel application. Xlwings is in essence a smart wrapper around (most, though not all) pywin32/appscript excel API functions. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows or Mac machine that has MS Excel installed.

import xlwings as xw

path1 = 'C:\Users\Xukrao\Desktop\workbook1.xlsx'
path2 = 'C:\Users\Xukrao\Desktop\workbook2.xlsx'

wb1 = xw.Book(path1)
wb2 = xw.Book(path2)

ws1 = wb1.sheets(1)
ws1.api.Copy(Before=wb2.sheets(1).api)
wb2.save()
wb2.app.quit()

Method 2

This might help if you’re not opposed to using Pandas

import pandas as pd

#change xxx with the sheet name that includes the data
data = pd.read_excel(sourcefile, sheet_name="xxx")

#save it to the 'new_tab' in destfile
data.to_excel(destfile, sheet_name='new_tab')

Hope it helps

Method 3

You could also try xlwings.

import xlwings as xw
wb = xw.Book(r'C:pathtofile.xlsx')
sht = wb.sheets['Sheet1']
new_wb = xw.Book(r'C:new_pathtofile.xlsx')
new_wb.sheets['name'] = sht

Method 4

Long battle and finally got the answer.
From xlwings source code: https://github.com/xlwings/xlwings/pull/1216/files

source_sheet.range.copy(destination_sheet.range)

In other words:

wb.sheets['Sheet1'].range('A1:A6').copy(wb.sheets['Sheet2'].range('A1:A6'))

• It works also from one workbook to another workbook.
• A range of cells must be provided. Just ensure the range is big enough to cover the full worksheet.
• The copy function copy/paste everything withing a range of cells (values, cell format, hyperlinks, cell type, …)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0