Copy Move Delete Sheets Python openpyxl Guide

Excel automation is essential for productivity. Python openpyxl makes it easy. This guide covers sheet operations.

You will learn to copy, move, and delete worksheets. These skills save time and reduce errors in data processing.

Installing openpyxl

First, ensure openpyxl is installed. Use pip for installation. This library handles Excel files efficiently.

pip install openpyxl

Basic Workbook Setup

Start by creating a workbook. Add multiple sheets for demonstration. This setup helps show the operations clearly.

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Create multiple sheets
ws1 = wb.active
ws1.title = "Sales_Data"
ws2 = wb.create_sheet("Inventory")
ws3 = wb.create_sheet("Reports")

# Save the workbook
wb.save("workbook_operations.xlsx")

Copying Worksheets

Copying sheets preserves original data. Use the copy_worksheet method. It creates an identical duplicate sheet.

The copy includes all cells, styles, and formulas. The new sheet gets a default name. You can rename it as needed.

from openpyxl import load_workbook

# Load existing workbook
wb = load_workbook("workbook_operations.xlsx")

# Copy the Sales_Data sheet
sales_sheet = wb["Sales_Data"]
copied_sheet = wb.copy_worksheet(sales_sheet)

# Rename the copied sheet
copied_sheet.title = "Sales_Data_Backup"

# Save changes
wb.save("workbook_operations.xlsx")

After running this code, your workbook will have four sheets. The backup sheet contains all original sales data.

Moving Worksheets

Moving changes sheet positions. Use sheet index manipulation. This controls the order of tabs in Excel.

The sheet order matters for presentation. You can move sheets to any position in the workbook.

from openpyxl import load_workbook

# Load workbook
wb = load_workbook("workbook_operations.xlsx")

# Move Inventory sheet to first position
inventory_sheet = wb["Inventory"]
wb._sheets.insert(0, wb._sheets.pop(wb._sheets.index(inventory_sheet)))

# Save the reordered workbook
wb.save("workbook_operations.xlsx")

Now Inventory appears first. This technique helps organize related sheets together logically.

Deleting Worksheets

Deleting removes unwanted sheets. Use the remove method. This permanently deletes the specified worksheet.

Always verify before deletion. There is no undo function in openpyxl. Data loss is permanent.

from openpyxl import load_workbook

# Load workbook
wb = load_workbook("workbook_operations.xlsx")

# Delete the Reports sheet
reports_sheet = wb["Reports"]
wb.remove(reports_sheet)

# Save changes
wb.save("workbook_operations.xlsx")

The Reports sheet is now gone. Your workbook contains only three remaining worksheets.

Complete Example

Here is a comprehensive example. It combines all three operations. This shows practical workflow automation.

from openpyxl import Workbook

# Create initial workbook
wb = Workbook()
main_sheet = wb.active
main_sheet.title = "Main_Data"

# Add sample data
main_sheet["A1"] = "Product"
main_sheet["B1"] = "Sales"
main_sheet["A2"] = "Widget A"
main_sheet["B2"] = 1500

# Create additional sheets
analysis_sheet = wb.create_sheet("Analysis", 1)
temp_sheet = wb.create_sheet("Temporary", 2)

# Copy main sheet for backup
backup_sheet = wb.copy_worksheet(main_sheet)
backup_sheet.title = "Main_Data_Backup"

# Move Analysis to first position
wb._sheets.insert(0, wb._sheets.pop(wb._sheets.index(analysis_sheet)))

# Delete temporary sheet
wb.remove(temp_sheet)

# Save final workbook
wb.save("complete_operations.xlsx")
print("Workbook operations completed successfully!")

Workbook operations completed successfully!

Advanced Considerations

Consider named ranges when copying sheets. They may need updating. Our Excel Named Ranges Python openpyxl Guide covers this topic.

Formulas referencing other sheets require attention. They might break after moving or deleting. Check our Python openpyxl Formulas and Cell Evaluation guide.

For large files, consider performance. Our Handle Large Excel Files Efficiently Python openpyxl article offers optimization tips.

Error Handling

Always include error handling. This prevents crashes when sheets don't exist. Use try-except blocks for safety.

from openpyxl import load_workbook

try:
    wb = load_workbook("workbook_operations.xlsx")
    
    # Safe deletion with error handling
    if "NonExistentSheet" in wb.sheetnames:
        sheet_to_delete = wb["NonExistentSheet"]
        wb.remove(sheet_to_delete)
        print("Sheet deleted successfully")
    else:
        print("Sheet not found - skipping deletion")
        
    wb.save("workbook_operations.xlsx")
    
except Exception as e:
    print(f"Error occurred: {str(e)}")

Best Practices

Always work on copies of important files. This prevents data loss. Test operations on sample data first.

Use descriptive sheet names. This makes automation scripts more readable. Avoid spaces in sheet names.

Document your sheet operations. Add comments to your code. This helps with maintenance and debugging.

Conclusion

Python openpyxl provides powerful sheet management. You can copy, move, and delete worksheets programmatically.

These operations enable efficient Excel automation. They save time and ensure consistency across workbooks.

Remember to handle errors and backup important data. Start automating your Excel tasks with confidence today.