Copying worksheets in Excel is a critical skill to have, especially when you need to create new worksheets based on existing ones. By utilizing the copy function, you can avoid potential errors that may arise from manually recreating the same content. This not only saves time and effort but also ensures that your data remains accurate and reliable. In this article, we will demonstrate how to copy worksheets in Excel in Python using Spire.XLS for Python.
- Copy a Worksheet in the Same Excel Workbook
- Copy a Worksheet to Another Excel Workbook
- Copy Visible Worksheets to a New Excel Workbook
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Copy a Worksheet in the Same Excel Workbook in Python
You can copy a worksheet within the same workbook by adding a new worksheet to the workbook and then copying the data from the original worksheet to the new one.
The following steps demonstrate how to copy a worksheet within the same workbook:
- Initialize an instance of the Workbook class.
- Load an Excel workbook using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Add a new worksheet to the workbook using the Workbook.Worksheets.Add() method.
- Copy the data from the specific worksheet to the new worksheet using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
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 result workbook to another file
workbook.SaveToFile("CopyInSameWorkbook.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Copy a Worksheet to Another Excel Workbook in Python
To copy a worksheet from one workbook to another, you need to add a new worksheet to the destination workbook and then copy the data of the worksheet from the source workbook to the new worksheet of the destination workbook. If you want to maintain the source formatting, you can copy the theme of the source workbook to the destination workbook.
The following steps demonstrate how to copy a worksheet from one workbook to another:
- Initialize an instance of the Workbook class.
- Load the source workbook using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Initialize an instance of the Workbook class.
- Load the destination workbook using the Workbook.LoadFromFile() method.
- Add a new worksheet to the destination workbook using the Workbook.Worksheets.Add() method.
- Copy the specific worksheet of the source workbook to the new worksheet of the destination workbook using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Copy the theme from the source workbook to the destination workbook using the Workbook.CopyTheme (Workbook srcWorkbook) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
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)
sourceWorkbook.Dispose()
destWorkbook.Dispose()
Copy Visible Worksheets to a New Excel Workbook in Python
If you only want to share visible worksheets rather than the entire workbook with others, you can copy the visible worksheets to a new workbook.
The following steps demonstrate how to copy visible worksheets from a workbook to a new workbook:
- Initialize an instance of the Workbook class.
- Load the source workbook using the Workbook.LoadFromFile() method.
- Initialize an instance of the Workbook class to create a new workbook, then clear the default worksheets in the new workbook using the Workbook.Worksheets.Clear() method.
- Iterate through all the worksheets in the source workbook.
- Check if the current worksheet is visible using the Worksheet.Visibility property.
- If the result is true, add a new worksheet to the new workbook using the Workbook.Worksheets.Add() method.
- Copy the worksheet from the source workbook to the new worksheet of the new workbook using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Copy the theme from the source workbook to the new workbook using the Workbook.CopyTheme(Workbook srcWorkbook) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
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)
sourceWorkbook.Dispose()
newWorkbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
