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

by

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()

More Related Contents:

  1. Preserving styles using python’s xlrd,xlwt, and xlutils.copy
  2. writing to existing workbook using xlwt [closed]
  3. Python xlwt – accessing existing cell content, auto-adjust column width
  4. Insert row into Excel spreadsheet using openpyxl in Python
  5. Edit existing excel workbooks and sheets with xlrd and xlwt
  6. django excel xlwt
  7. Pandas: Adding new column to dataframe which is a copy of the index column
  8. Play a Sound with Python [duplicate]
  9. Non-alphanumeric list order from os.listdir()
  10. How do I disable log messages from the Requests library?
  11. DLL load failed error when importing cv2
  12. How to test the membership of multiple values in a list?
  13. How to check MouseButtonPress event in PyQt6?
  14. Python strftime – date without leading 0?
  15. Defining “boolness” of a class in python
  16. TransactionManagementError “You can’t execute queries until the end of the ‘atomic’ block” while using signals, but only during Unit Testing
  17. Calculating Time Difference
  18. “is” operator result: What is happening? [duplicate]
  19. OSError: Initializing from file failed on csv in Pandas
  20. openpyxl – adjust column width size
  21. Can’t install PIL after Mac OS X 10.9
  22. Where does flask look for image files?
  23. Datetime conversion – How to extract the inferred format?
  24. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  25. What are good rules of thumb for Python imports?
  26. Why is any (True for … if cond) much faster than any (cond for …)?
  27. How can I bind arguments to a function in Python?
  28. Convert Latitude and Longitude to point in 3D space
  29. What is exactly a file-like object in Python?
  30. Numpy equivalent of itertools.product [duplicate]

Leave a Comment