The Excel workbook is a powerful spreadsheet that enables the creation, manipulation, and analysis of data in a variety of ways. One of the useful features that workbooks offer is the ability to hide or unhide worksheets in a workbook. Hiding worksheets can help protect sensitive or confidential information, reduce clutter, or organize data more efficiently. And when users need to re-display the hidden worksheets, they can also unhide them with simple operations. This article is going to explain how to hide or unhide worksheets in Excel workbooks through Python programs using Sprie.XLS for Python.

• Hide Excel Worksheets in Python
• Unhide Excel Worksheets in Python

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

Hide Excel Worksheets in Python

The Worksheet.Visibility property in Spire.XLS for Python can be used to set the visibility of a worksheet. By assigning WorksheetVisibility.Hidden or WorksheetVisibility.StrongHidden to this property, users can change the visibility of a worksheet to hidden or very hidden (completely not shown in Excel and can only be unhidden through code).

The detailed steps for hiding worksheets are as follows:

• Create an object of Workbook class.
• Load a workbook using Workbook.LoadFromFile() method.
• Change the status of the first worksheet to hidden by assigning WorksheetVisibility.Hidden to the Workbook.Worksheets[].Visibility property.
• Change the status of the second worksheet to very hidden by assigning WorksheetVisibility.StrongHidden to the Workbook.Worksheets[].Visibility property.
• Save the workbook using Workbook.SaveToFile() method.

from spire.common import *
from spire.xls.common import *

# Create an object of Workbook
workbook = Workbook()

# Load an Excel workbook
workbook.LoadFromFile("Sample.xlsx")

# Hide the first worksheet
workbook.Worksheets[0].Visibility = WorksheetVisibility.Hidden

# Change the second worksheet to very hidden
workbook.Worksheets[1].Visibility = WorksheetVisibility.StrongHidden

# Save the workbook
workbook.SaveToFile("output/HideWorksheets.xlsx")

Unhide Excel Worksheets in Python

Unhiding a worksheet can be done by assigning WorksheetVisibility.Visible to the Workbook.Worksheets[].Visibility property. The detailed steps are as follows:

• Create an object of Workbook class.
• Load a workbook using Workbook.LoadFromFile() method.
• Unhide the very hidden worksheet by assigning WorksheetVisibility.Visible to the Workbook.Worksheets[].Visibility property.
• Save the workbook using Workbook.SaveToFile() method.

from spire.common import *
from spire.xls.common import *

# Create an object of Workbook
workbook = Workbook()

# Load an Excel workbook
workbook.LoadFromFile("output/HideWorksheets.xlsx")

# Unhide the second worksheet
workbook.Worksheets[1].Visibility = WorksheetVisibility.Visible

# Save the workbook
workbook.SaveToFile("output/UnhideWorksheet.xlsx")

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.

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.

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.

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.

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.