Add New Excel Sheet Using Python: Easy Steps

Excel sheets are a staple in data management for businesses, researchers, and individuals. Adding a new sheet in an Excel workbook using Python opens up various automation opportunities, enhancing productivity by streamlining repetitive tasks. In this blog post, we'll guide you through the simple steps of adding a new Excel sheet using Python, ensuring you can integrate this process seamlessly into your workflow.

Why Use Python with Excel?

Python, with its rich ecosystem of libraries like openpyxl and xlsxwriter, simplifies Excel manipulation. Here are some key benefits of using Python for Excel tasks:

• Automation - Automate repetitive Excel tasks, saving time and reducing errors.
• Flexibility - Python’s versatility allows you to perform complex operations on Excel data.
• Compatibility - Works seamlessly with multiple file formats and across different operating systems.

Installing the Necessary Library

To manipulate Excel files in Python, we’ll primarily use the openpyxl library. Here’s how to get started:

1. Open your terminal or command prompt.
2. Run the command: pip install openpyxl

Creating a New Excel Workbook

Let’s dive into creating a new workbook and adding our first sheet:

from openpyxl import Workbook



wb = Workbook()



sheet = wb.active
sheet.title = “First Sheet”



wb.save(“new_workbook.xlsx”)

💡 Note: If you don’t change the title of the active sheet, it will remain "Sheet" by default.

Adding New Sheets

Here’s how to add new sheets to an existing workbook:

from openpyxl import load_workbook



wb = load_workbook(‘your_workbook.xlsx’)



wb.create_sheet(title=“Second Sheet”)
wb.create_sheet(title=“Third Sheet”, index=0)  # Insert at the beginning



wb.save(‘your_workbook.xlsx’)

Notice that we can specify the index for sheet insertion if we want the new sheet to appear at a specific position in the workbook.

Writing Data to the New Sheet

Once you have your new sheets, you can start writing data to them:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet.title = “Data Sheet”



sheet[‘A1’] = “Name”
sheet[‘B1’] = “Age”



data = [
    [“Alice”, 25],
    [“Bob”, 30],
    [“Charlie”, 35]
]
for row, item in enumerate(data, start=2):
    sheet.cell(row=row, column=1, value=item[0])
    sheet.cell(row=row, column=2, value=item[1])



wb.save(“data_workbook.xlsx”)

💡 Note: Always remember to save your workbook after making changes to avoid data loss.

Manipulating Sheets

Let’s explore some common operations on sheets:

Copying a Sheet

source_sheet = wb[“Sheet1”]
target_sheet = wb.copy_worksheet(source_sheet)
target_sheet.title = “Sheet1 Copy”

Renaming a Sheet

sheet = wb[“Sheet1”]
sheet.title = “New Name”

Deleting a Sheet

sheet_to_delete = wb[“Sheet1”]
wb.remove(sheet_to_delete)

💡 Note: Be cautious when deleting sheets; the operation is irreversible once the workbook is saved.

Using Python to manage Excel sheets not only streamlines your workflow but also reduces the potential for human error in data entry and manipulation. Whether you're creating reports, managing large datasets, or simply organizing your data, Python's capabilities with Excel make these tasks far more efficient.

Can I add a sheet with Python without overwriting the existing workbook?

+

Yes, you can. Use load_workbook with the read_only=False parameter to modify an existing workbook without overwriting it:

wb = load_workbook(filename=‘your_workbook.xlsx’, read_only=False)

What’s the difference between openpyxl and xlsxwriter?

+

openpyxl allows you to read, write, and modify Excel files, providing a wider range of functionality for working with existing files. xlsxwriter, however, is focused on creating new Excel files with charts, tables, and formatting, but can’t modify existing files.

How do I format cells in the new sheet?

+

You can set cell styles, fonts, borders, etc. using openpyxl. Here’s an example for bold text:

from openpyxl.styles import Font

cell = sheet.cell(row=1, column=1)
cell.value = “Bold Text”
cell.font = Font(bold=True)