5 Ways to Add Multiple Worksheets in Excel with Python

Mastering Excel with Python can significantly streamline your data analysis tasks. Excel, being a pivotal tool in many industries, can be enhanced through Python's robust libraries such as openpyxl or xlwings. Here, we'll explore five different methods to add multiple worksheets to an Excel workbook using Python, ensuring efficiency and flexibility in your workflow.

Using OpenPyXL

OpenPyXL is one of the most popular libraries for manipulating Excel files. Here’s how you can add multiple worksheets with it:

from openpyxl import Workbook

# Create a workbook
wb = Workbook()

# Get active worksheet
ws = wb.active

# Add multiple sheets
for i in range(5):
    wb.create_sheet(f"Sheet{i+1}")

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

📌 Note: The above code will create a new workbook with 5 additional sheets, named Sheet1, Sheet2, etc.

Method 1: Sequential Sheet Creation

The most straightforward way to add worksheets is sequentially. This method is best when you want to add a known number of sheets:

• Import the Workbook class from openpyxl.
• Create a new workbook or open an existing one.
• Use a for loop to add sheets, specifying their titles.

from openpyxl import Workbook

wb = Workbook()
wb.remove(wb.active) # Remove the default sheet if not needed

for i in range(3):
    ws = wb.create_sheet(title=f"New_Sheet_{i}")

wb.save("workbook_with_multiple_sheets.xlsx")

Method 2: Bulk Sheet Creation with Names

If you have specific names for your worksheets, you can add them all at once:

from openpyxl import Workbook

wb = Workbook()

sheet_names = ["Sales Data", "Inventory", "Performance", "Budget", "Projections"]

for name in sheet_names:
    wb.create_sheet(title=name)

wb.save("custom_sheet_names.xlsx")

Method 3: Using a List of Dictionaries

This method is useful when you want to add sheets along with some default data:

from openpyxl import Workbook

wb = Workbook()

sheets = [
    {"title": "Sheet1", "data": [['A', 'B', 'C']]},  
    {"title": "Sheet2", "data": [['D', 'E', 'F']]},  
    {"title": "Sheet3", "data": [['G', 'H', 'I']]}  
]

for sheet in sheets:
    ws = wb.create_sheet(title=sheet['title'])
    for row in sheet['data']:
        ws.append(row)

wb.save("sheets_with_data.xlsx")

Method 4: Dynamic Sheet Addition with User Input

Enhance the interactivity of your script by allowing the user to dynamically add sheets:

from openpyxl import Workbook

wb = Workbook()

while True:
    sheet_name = input("Enter sheet name or 'exit' to finish: ").strip()
    if sheet_name.lower() == 'exit':
        break
    wb.create_sheet(title=sheet_name)

wb.save("dynamic_sheets.xlsx")

This approach lets users control the creation of sheets as they see fit, stopping the process when satisfied.

Method 5: Using XLWings

xlwings is another library that can interact with Excel through Python. Here’s how to add sheets:

import xlwings as xw

wb = xw.Book()
for i in range(3):
    wb.sheets.add(name=f"Sheet_{i}")
wb.save('xlwings_multiple_sheets.xlsx')
xw.apps.active.quit()  # Optional, closes Excel if opened

📌 Note: Using xlwings can interact directly with an open Excel instance, allowing for real-time updates, but remember to close the application if it was opened by your script.

To conclude, Python provides multiple methods to manage Excel workbooks programmatically, ranging from simple to dynamic approaches. Each method serves a different need, from creating sheets with known names to allowing user interaction. The choice depends on your specific requirements, whether it's a static setup or an interactive environment. These techniques, when combined with Python's data manipulation capabilities, make for a powerful toolset in automating Excel-related tasks.

Why should I use Python to manipulate Excel files?

+

Python provides automation capabilities that can save time, especially when dealing with repetitive tasks or large datasets. Libraries like openpyxl and xlwings offer extensive control over Excel functionalities, making it easier to perform complex operations beyond Excel’s built-in features.

Can I edit the contents of existing sheets with these libraries?

+

Yes, both openpyxl and xlwings allow for the manipulation of cell data, formulas, formats, and more within existing sheets. You can insert, delete, or modify data as needed.

Is openpyxl or xlwings better for adding worksheets?

+

It depends on your needs. Openpyxl is lightweight and doesn’t require Excel to be installed, making it ideal for server-side operations. Xlwings allows for real-time interaction with Excel if it’s installed, offering more dynamic manipulation but with the requirement of an Excel installation.

How do I decide which method to use when adding sheets?

+

Consider your requirements:

• If you need simple, one-time sheet creation, use Method 1 or 2.
• For sheets with predefined data, Method 3 is useful.
• If user interaction is required, go for Method 4.
• If you’re dealing with an active Excel session, Method 5 with xlwings might be your choice.

Can I automate the entire Excel workflow with Python?

+

Absolutely. Python can automate everything from data entry, calculation, formatting, chart creation, and even exporting or interacting with Excel’s UI through libraries like xlwings.