Copying worksheets in Excel is often necessary in various scenarios, such as creating backups of unprocessed sheets or generating new workbooks based on existing data. While Excel provides a built-in move or copy sheet function, this process can be time-consuming and prone to errors, especially when working with large datasets. Fortunately, in this article, we'll show you how to copy a worksheet in Excel documents using Python. You'll find three practical solutions tailored to different needs, making the process faster and more efficient.
Python Library to Copy Worksheet in Excel Files
To complete the task more easily, it is recommended to try some Python libraries, such as Spire.XLS, Apose, Openpyxl, and so on. Among them, Spire.XLS (Spire.XLS for Python) stands out for its intuitive methods, allowing both experts and beginners to copy worksheets in Excel files quickly without hassle. So, in this article, we will use Spire.XLS for Python to demonstrate the whole process of duplicating worksheets in Excel spreadsheets.
You can install it using the pip command: pip install Spire.XLS.
How to Copy a Worksheet in One Excel Document
Duplicating worksheets within the same workbook often helps people to create backups and preserve original data. Whatever your need is, you can fulfill it with the Workbook.Worksheets.Add() and the Worksheet.CopyForm() methods. Let’s dive into the detailed steps!
Steps to copy a worksheet in the same Excel workbook:
Create an object of the Workbook class, and use the Workbook.LoadFromFile() method to read an Excel file from the local storage.
Get a specified worksheet with the Workbook.Worksheets[] property.
Add a new worksheet to the workbook by calling the Workbook.Worksheets.Add() method.
Copy the retrieved worksheet to the newly added one using the Worksheet.CopyFrom() method.
Save the updated Excel file with the Workbook.SaveToFile() method.
Here is the code example of copying the first worksheet in an Excel file:
from spire.xls import *
from spire.xls.common import *
# Initialize an instance of the Workbook class
workbook = Workbook()
# Load an Excel workbook
workbook.LoadFromFile("Input.xlsx")
# Get the first worksheet
sourceSheet = workbook.Worksheets[0]
sheetName = sourceSheet.Name + "_Copy"
# Add a new worksheet with a specific name to the workbook
destSheet = workbook.Worksheets.Add(sheetName)
# Copy the first worksheet to the newly added worksheet
destSheet.CopyFrom(sourceSheet)
# Save the resulting workbook to another file
workbook.SaveToFile("CopyInSameWorkbook.xlsx", ExcelVersion.Version2013)
# Release the memory
workbook.Dispose()
How to Copy a Worksheet to Another Excel Workbook
There are many situations where you may need to copy a worksheet into another Excel workbook. For example, when data from multiple Excel files, such as sales reports from Divisions A, B, and C, needs to be consolidated into one summary for the headquarters. While the Workbook.Worksheets.Add() and the Worksheet.CopyForm() methods can still be used, an additional Workbook object must be created to load the target document before performing the copy operation. Here’s how to finish the task.
Steps to copy a worksheet to another Excel workbook without losing formatting:
Create a Workbook instance, and specify the file path of the source Excel spreadsheets with the Workbook.LoadFromFile() method.
Get a worksheet using the Workbook.Worksheets[] property.
Initialize a new instance of the Workbook class as the target file.
Add a new worksheet in the target workbook by calling the Workbook.Worksheets.Add() method.
Copy the worksheet from the source file to the new workbook by calling the Worksheet.CopyFrom() method.
Copy the theme of the source workbook to the target workbook using the Workbook.CopyTheme() method.
Save the resulting Excel file with the Workbook.SaveToFile() method.
Below is an example of copying the first worksheet of an Excel document to another Excel workbook:
from spire.xls import *
from spire.xls.common import *
# Initialize an instance of the Workbook class
sourceWorkbook = Workbook()
# Load the source Excel workbook
sourceWorkbook.LoadFromFile("CopyWorksheets-Input.xlsx")
# Get the first worksheet of the source workbook
sourceSheet = sourceWorkbook.Worksheets[0]
# Get the name of the first worksheet
sheetName = sourceSheet.Name + "_Copy"
# Initialize an instance of the Workbook class
destWorkbook = Workbook()
# Load the destination Excel workbook
destWorkbook.LoadFromFile("CopyWorksheets-Sample.xlsx")
# Add a new worksheet with a specific name to the destination workbook
destSheet = destWorkbook.Worksheets.Add(sheetName)
# Copy the first worksheet of the source workbook to the new worksheet of the destination workbook
destSheet.CopyFrom(sourceSheet)
# Copy the theme from the source workbook to the destination workbook
destWorkbook.CopyTheme(sourceWorkbook)
# Save the destination workbook to another file
destWorkbook.SaveToFile("CopyToAnotherWorkbook.xlsx", ExcelVersion.Version2013)
# Release resources
sourceWorkbook.Dispose()
destWorkbook.Dispose()
How to Copy All Visible Worksheets to a New Excel Workbook
In addition to copying worksheets within the same Excel workbook or to another workbook, there may be times when you need to copy all visible worksheets at once. This method can save you time and effort, especially when dealing with large workbooks with multiple hidden sheets. In this section, we’ll show you how to copy all visible worksheets to a new Excel workbook using Python.
Steps to copy all visible worksheets to a new Excel file:
Create a Workbook object, and use the Workbook.LoadFromFile() method to read the source Excel document from files.
Instantiate a new Workbook class and clear the default worksheet in it by calling the Workbook.Worksheets.Clear() method.
Iterate through worksheets in the source file.
Check if the current worksheet is visible with the Worksheet.Visibility property. If yes, add a new worksheet using the Workbook.Worksheets.Add() method.
Copy the worksheet from the source file to the new Excel document with the Workbook.CopyTheme() method.
Copy the theme of the source Excel document to the new workbook using the Workbook.CopyTheme() method.
Save the new Excel file with the Workbook.SaveToFile() method.
Here is the code example of copying all visible worksheets to a new Excel file:
from spire.xls import *
from spire.xls.common import *
# Initialize an instance of the Workbook class
sourceWorkbook = Workbook()
# Load the source Excel workbook
sourceWorkbook.LoadFromFile("CopyWorksheets-Input.xlsx")
# Initialize an instance of the Workbook class to create a new workbook
newWorkbook = Workbook()
newWorkbook.Version = ExcelVersion.Version2013
# Clear the default worksheets in the new workbook
newWorkbook.Worksheets.Clear()
# Iterate through all the worksheets in the source workbook
for sourceSheet in sourceWorkbook.Worksheets:
# Check if the current worksheet is visible
if sourceSheet.Visibility == WorksheetVisibility.Visible:
sheetName = sourceSheet.Name + "_Copy"
# Add a new worksheet with a specific name to the new workbook
destSheet = newWorkbook.Worksheets.Add(sheetName)
# Copy the worksheet from the source workbook to the new worksheet of the new workbook
destSheet.CopyFrom(sourceSheet)
# Copy the theme from the source workbook to the new workbook
newWorkbook.CopyTheme(sourceWorkbook)
# Save the new workbook to another file
newWorkbook.SaveToFile("CopyVisibleSheetsToNewWorkbook.xlsx", ExcelVersion.Version2013)
# Release resources
sourceWorkbook.Dispose()
newWorkbook.Dispose()
The Bottom Line
This page explains how to copy a worksheet in Excel using Python, including how to duplicate worksheets within the same workbook, copy them to another Excel file, and transfer all visible worksheets to a new workbook. Each method is designed to suit different needs, and the instructions are straightforward and easy to follow. We hope this guide helps you efficiently manage your Excel tasks!