Python openpyxl: Create, Delete, Copy Multiple Sheets

python openpyxl multiple sheets create delete and copy requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When working with Excel files in Python, openpypyxl is the standard library for reading and writing .xlsx workbooks. Managing multiple sheets—creating, deleting, and copying them—is a common requirement. This article covers the the exact API for python openpypyxl multiple sheets create delete and copy, including the behavior of each operation and the the limitations you should know before using them in production.

Creating Sheets in a Workbook

The create_sheet method adds a new sheet to a workbook. By default, it appends the the sheet at the end, but you can specify a position with the index parameter.

from openpyxl import Workbook
n
wb = Workbook()
# The workbook always starts with one sheet named "Sheet"
default_sheet = wb.active

# Create a new sheet at the end
ws1 = wb.create_sheet(title="Data")

# Create a sheet at the beginning (index 0)
ws2 = wb.create_sheet(title="Summary", index=0)

# Rename the default sheet
default_sheet.title = "Raw Data"n```

The `title` parameter is optional; if omitted, openpyxl generates a name like `Sheet1`, `Sheet2`, etc. The `index` parameter is zero-based, so `index=0` places the sheet at the front. If you need to move a sheet after creation, you can use `wb.move_sheet(sheet, offset)` to shift its position.

## Deleting Sheets Safely

Removing a sheet is done with `wb.remove(sheet)` or the `del` statement on the workbook's sheet list. The `remove` method is safer because it raises a `KeyError` if the sheet does not exist, and it also updates the active sheet index if necessary.

```python
# Remove a sheet by object
wb.remove(ws1)

# Remove by name using del
del wb["Data"]

One common mistake is trying to delete the only remaining sheet. openpyxl requires at least one sheet in a workbook. If you attempt to remove the last sheet, it will raise a ValueError. To avoid this, ensure you create a replacement sheet before deleting the last one. Also, if you delete the active sheet, openpyxl automatically sets the first remaining sheet as active, which is usually what you want.

Copying Sheets Within a Workbook

openpyxl provides a copy_worksheet method on the Workbook object. This creates a duplicate of the source sheet, including cell values, formatting, column widths, and row heights. The copy is placed at the end of the workbook by default.

wb = Workbook()
ws = wb.active
ws.title = "Original"
ws["A1"] = "Data"

# Copy the sheet
ws_copy = wb.copy_worksheet(ws)
ws_copy.title = "Copy"

The copied sheet is independent; changes to the copy do not affect the original. However, copy_worksheet does not copy images, charts, or other embedded objects. If your sheet contains these, you must re-add them manually. Also, formulas are copied as-is, but the cell references are not adjusted—they point to the same cells in the copy, which may or may not be what you need.

Copying Sheets Between Workbooks

Copying a sheet from one workbook to another is not directly supported by copy_worksheet. That method only works within the same workbook. To transfer data between separate workbooks, you need to manually copy cell values and styles. A common approach is to iterate over the source sheet's cells and write them to a new sheet in the destination workbook.

from openpyxl import load_workbook

src_wb = load_workbook("source.xlsx")
src_ws = src_wb["Data"]

dst_wb = Workbook()
dst_ws = dst_wb.active
dst_ws.title = "Data"

for row in src_ws.iter_rows():
    for cell in row:
        dst_ws[cell.coordinate] = cell.value
        # Copy style if needed
        if cell.has_style:
            dst_ws[cell.coordinate]._style = cell._style

This copies values and basic styles, but it does not copy merged cells, column widths, or row heights. For a more complete transfer, you would need to replicate those properties as well. If you need to copy entire workbooks, consider using openpyxl's load_workbook and then saving under a new name, but that copies the whole file, not individual sheets.

Preserving Formatting and Data When Copying

When using copy_worksheet, openpyxl copies most formatting: number formats, fonts, fills, borders, and alignment. It also copies column widths and row heights. However, it does not copy page setup, print settings, or conditional formatting. If these matter, you must apply them to the copy manually.

For manual copying between workbooks, you can copy the style object directly, as shown above. This works for most style attributes, but be aware that some style objects are shared and may need to be recreated to avoid side effects. For merged cells, you can copy the merged ranges using ws.merged_cells.ranges and then apply them to the destination.

Performance and Memory Considerations

openpyxl loads an entire workbook into memory when you use load_workbook. For large files with many sheets, this can consume significant RAM. When you copy a sheet, the memory footprint roughly doubles for that sheet's data. If you are processing multiple sheets, consider using read_only mode for reading and write_only mode for writing, but these modes have limitations—they do not support copying or formatting operations.

If you need to copy a large sheet, it is often more efficient to read the source workbook with read_only=True and write to the destination with write_only=True, copying cell values only. This avoids holding both full workbooks in memory. However, this approach loses all formatting, so it is only suitable when you only need raw data.

Common Pitfalls and How to Avoid Them

One frequent error is attempting to copy a sheet that has a name that already exists. openpyxl automatically appends a number to the copy's title (e.g., "Sheet1 Copy1"), and then you may rename it, but if you rename to an existing name, it will raise a ValueError. Always check the existing sheet names before assigning a new title.

Another issue is deleting a sheet while iterating over the workbook's sheet list. Modifying the list during iteration can cause skipped sheets. Instead, collect the sheets to delete first and then remove them in a separate loop.

Finally, remember that copy_worksheet does not copy the active sheet status. If you need the copy to be active, call wb.active = index_of_copy after copying.