Manage Excel Worksheets in Python: Create, Copy, Delete, and Rename

Worksheets are the fundamental units for organizing and separating data in Excel files. A single Excel file typically contains multiple worksheets, each storing a different type of data or presenting a different analytical view. In automation scenarios, it is common to dynamically add, copy, hide, move, and delete worksheets through code. This article demonstrates how to manage worksheets programmatically in Python.

Environment Setup

This article uses Spire.XLS for Python to work with Excel files. This library provides comprehensive APIs for managing worksheet collections, covering the most common worksheet management tasks in automation.

pip install Spire.XLS

Once installed, you can access the relevant APIs through the spire.xls module.

Adding a Worksheet

Adding a worksheet is the most basic operation. Access the Worksheets collection through a Workbook object and call the Add() method with a worksheet name to create a new sheet.

from spire.xls import *

# Create a workbook object
workbook = Workbook()

# Add a new worksheet named "DataSheet"
sheet = workbook.Worksheets.Add("DataSheet")

# Write data to the new worksheet
sheet.Range["A1"].Text = "This is the newly added worksheet"

# Save the file
workbook.SaveToFile("AddWorksheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The Add() method returns the newly created worksheet object, which you can immediately use for writing data and applying formatting. By default, the new worksheet is appended at the end of all existing worksheets.

Copying a Worksheet

There are two scenarios for copying a worksheet: within the same workbook, and across workbooks.

Copying Within the Same Workbook

To copy a worksheet within the same workbook, retrieve the source worksheet's data range and copy it to a new target worksheet:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("Template.xlsx")

# Get the source worksheet
sourceSheet = workbook.Worksheets[0]

# Create the target worksheet
targetSheet = workbook.Worksheets.Add("CopyOfSource")

# Copy data
sourceRange = sourceSheet.AllocatedRange
targetSheet.CopyFrom(sourceRange, targetSheet, targetSheet.FirstRow, targetSheet.FirstColumn, True)

workbook.SaveToFile("CopyWithinWorkbook.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The last parameter of the CopyFrom() method controls whether formatting is also copied. When set to True, data, styles, merged cells, and other formatting are preserved in the copy.

Copying Across Workbooks

Copying across workbooks allows you to transfer the content of a worksheet from one Excel file to another:

from spire.xls import *

# Load the source file
sourceWorkbook = Workbook()
sourceWorkbook.LoadFromFile("Source.xlsx")
srcSheet = sourceWorkbook.Worksheets[0]

# Load the target file
targetWorkbook = Workbook()
targetWorkbook.LoadFromFile("Target.xlsx")

# Add a new worksheet in the target file and copy content
targetSheet = targetWorkbook.Worksheets.Add("ImportedSheet")
targetSheet.CopyFrom(srcSheet)

targetWorkbook.SaveToFile("CopyAcrossWorkbooks.xlsx", ExcelVersion.Version2013)
targetWorkbook.Dispose()
sourceWorkbook.Dispose()

This approach is particularly useful in data consolidation scenarios, such as merging departmental data tables into a single summary file.

Hiding and Showing Worksheets

In certain cases, you may need to hide worksheets that contain auxiliary data or intermediate calculation results, so that only key information is visible to readers. You can control visibility through the Visibility property of a worksheet:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("MultiSheet.xlsx")

# Hide a specific worksheet
workbook.Worksheets["CalcSheet"].Visibility = WorksheetVisibility.Hidden

# Show a previously hidden worksheet
workbook.Worksheets["Report"].Visibility = WorksheetVisibility.Visible

workbook.SaveToFile("HideShowSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The WorksheetVisibility enum provides two values: Hidden and Visible. Hiding a worksheet does not delete its content — the data remains intact but is no longer visible in the Excel interface.

Moving a Worksheet

When the order of worksheets needs to be adjusted, use the MoveWorksheet() method to move a sheet to a specified position:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("MultiSheet.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Move it to the third position (index starts at 0)
sheet.MoveWorksheet(2)

workbook.SaveToFile("MoveSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The MoveWorksheet() method takes an integer parameter representing the target position index. The move operation does not alter the worksheet's content or data — it only changes the order of tabs in the tab bar.

Deleting a Worksheet

When a worksheet is no longer needed, you can remove it from the workbook by index or by name:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("MultiSheet.xlsx")

# Delete a worksheet by index
workbook.Worksheets.RemoveAt(1)

# You can also delete by name
# workbook.Worksheets.Remove("TempSheet")

workbook.SaveToFile("RemoveSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Deletion is irreversible — once executed, the worksheet and all its data are permanently removed. Before performing batch deletions, iterate through all worksheet names first to verify which sheets will be removed.

Setting Worksheet Tab Colors

Assigning colors to worksheet tabs provides a quick visual way to distinguish between different types of data sheets, improving file readability:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("MultiSheet.xlsx")

# Set tab colors for different worksheets
workbook.Worksheets[0].TabColor = Color.get_Red()
workbook.Worksheets[1].TabColor = Color.get_Green()
workbook.Worksheets[2].TabColor = Color.get_LightBlue()

workbook.SaveToFile("TabColor.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The TabColor property accepts a Color object. You can use predefined colors such as Red, Green, and LightBlue, or specify custom RGB values.

Freezing Panes

Freezing panes keeps header rows or columns visible while scrolling, which is a common setting when working with large data tables:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("DataSheet.xlsx")

sheet = workbook.Worksheets[0]

# Freeze the first row (the area above row 2, column 1 is frozen)
sheet.FreezePanes(2, 1)

workbook.SaveToFile("FreezePanes.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The FreezePanes() method takes two parameters: a row number and a column number. The area above and to the left of the specified position is frozen. For example, FreezePanes(2, 1) freezes the first row, FreezePanes(1, 2) freezes the first column, and FreezePanes(3, 2) freezes the first two rows and the first column simultaneously.

Getting All Worksheet Names

In automation workflows, you often need to retrieve the names of all worksheets in a file for further processing:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("MultiSheet.xlsx")

# Iterate through and get all worksheet names
for sheet in workbook.Worksheets:
    print(sheet.Name)

workbook.Dispose()

This operation is commonly used to dynamically generate a worksheet list, locate a specific worksheet by name, or perform data validation before batch processing.

Conclusion

This article covered the core operations for managing Excel worksheets in Python, including adding, copying, hiding, moving, and deleting worksheets, as well as setting tab colors and freezing panes. These operations address the most common worksheet management needs in everyday Excel automation.

Key takeaways:

1. Use Worksheets.Add() to add a new worksheet
2. Use CopyFrom() to copy worksheet content within the same workbook or across workbooks
3. The Visibility property controls worksheet display and hiding
4. MoveWorksheet() adjusts the worksheet order
5. FreezePanes() sets frozen panes to improve the readability of large data tables

Building on these fundamentals, you can further combine data writing, formatting, and file conversion capabilities to create comprehensive Excel automation workflows.