Creating and manipulating Excel files is a common task in many programming projects. Sometimes you need to add a new sheet to an existing Excel file. Python, with the help of libraries like openpyxl, makes it easy to do this in just a few lines of code. In this tutorial, we will cover how to add a new sheet to an existing Excel file using Python.
Step 1: Install openpyxl
Before we begin, you must have openpyxl library installed in your Python environment. openpyxl is a widely used library for working with Excel files. You can install it using pip by running the following command:
|
1 |
pip install openpyxl |
If you have already installed the library, you can move on to the next step.
Step 2: Read the existing Excel file
In this step, we will load an existing Excel file into memory using openpyxl library. For demonstration purposes, let’s assume we have a sample Excel file called “sample.xlsx” with the following content in Sheet1:
Name Age Alice 30 Bob 25 Eve 22
Use the following Python code to open and read the file:
|
1 2 3 4 |
from openpyxl import load_workbook filename = "sample.xlsx" workbook = load_workbook(filename) |
Step 3: Add a new sheet to the Excel file
Now that we have loaded the Excel file in memory, we can add a new sheet using the create_sheet method. We can specify the title of the sheet and its index (position) in the Excel file. If no position is specified, the new sheet will be created at the end of the existing sheets.
|
1 |
new_sheet = workbook.create_sheet(title="New Sheet", index=1) |
In this example, we’re creating a new sheet with the title “New Sheet” and adding it as the second sheet (index 1) in the workbook.
Step 4: Save the changes to the Excel file
Once we’ve added the new sheet, we need to save our changes to the Excel file. We can do this using the save method and providing the filename:
|
1 |
workbook.save(filename) |
Full Python code
|
1 2 3 4 5 6 7 8 9 10 11 |
from openpyxl import load_workbook # Load existing workbook filename = "sample.xlsx" workbook = load_workbook(filename) # Create a new sheet and add it to the workbook new_sheet = workbook.create_sheet(title="New Sheet", index=1) # Save the workbook workbook.save(filename) |
After executing the above code, you will see that a new sheet titled “New Sheet” has been added to the existing Excel file as the second sheet. You can now work with this new sheet using the openpyxl library or any other Excel editing tool.
Output
Conclusion
In this tutorial, we have demonstrated how to add a new sheet to an existing Excel file using Python and the openpyxl library. This is a handy technique when you need to work with Excel files programmatically, giving you the power to customize and automate your data processing tasks.