3 Ways to Add a New Sheet to an Excel File in Python

Adding new sheets to an Excel file using Python can greatly enhance your data manipulation capabilities. This guide will walk you through three effective methods to add a new sheet to an Excel file with Python, ensuring you understand how to do it using both third-party libraries and Python's built-in functionalities.

Using openpyxl Library

The openpyxl library is one of the most popular tools for working with Excel files in Python. Here’s how you can use it to add a new sheet:

Step-by-Step Guide

• Install openpyxl: If you haven’t already, install openpyxl using pip:

      pip install openpyxl
  

• Import the Library:

      from openpyxl import Workbook
  

• Open an Existing Workbook or Create a New One: You can either load an existing Excel file or create a new workbook:

      # Load existing workbook
      wb = openpyxl.load_workbook(‘example.xlsx’)
  
  # Or create a new one
  wb = Workbook()
  
  
  

• Add New Sheet: Use the create_sheet() method to add a new sheet:

      new_sheet = wb.create_sheet(“NewSheet1”, 0)
  

  Here, “NewSheet1” is the name of the new sheet, and 0 specifies its position as the first sheet.
• Save Workbook: After making changes, save the workbook:

      wb.save(‘example.xlsx’)
  

🔍 Note: The position parameter in create_sheet() is optional; if not provided, the sheet will be added at the end.

Using pandas Library

Pandas is not just for data manipulation; it’s also excellent for handling Excel files. Here’s how to add a new sheet:

Step-by-Step Guide

• Install pandas and openpyxl: If not installed:

      pip install pandas openpyxl
  

• Import Necessary Modules:

      import pandas as pd
      from openpyxl import load_workbook
  

• Read Existing Excel File: Use pandas to load the Excel file:

      excel_file = pd.read_excel(‘example.xlsx’, sheet_name=None)
  

• Create a New DataFrame or Use an Existing One: For instance:

      new_data = pd.DataFrame({‘A’: [1, 2, 3], ‘B’: [‘a’, ‘b’, ‘c’]})
  

• Write to a New Excel File: You can use the ExcelWriter to add new sheets:

      with pd.ExcelWriter(‘example.xlsx’, mode=‘a’, engine=‘openpyxl’, if_sheet_exists=‘overlay’) as writer:
          new_data.to_excel(writer, sheet_name=‘NewSheet2’, index=False)
  

  The mode='a' appends to the existing file, and if_sheet_exists='overlay' adds the sheet if it doesn’t exist, overlaying if it does.

📚 Note: Using pandas for this operation requires both pandas and openpyxl to be installed, as pandas uses openpyxl to handle Excel files.

Using Python’s Built-in Library (xlwt and xlrd)

If you prefer avoiding third-party libraries, Python offers a more basic method with xlwt for writing and xlrd for reading:

Step-by-Step Guide

• Install xlwt and xlrd: If not installed:

      pip install xlwt xlrd
  

• Import the Libraries:

      import xlrd
      import xlwt
  

• Open Existing Workbook:

      existing_wb = xlrd.open_workbook(‘example.xls’, formatting_info=True)
      sheets = existing_wb.sheet_names()
  

• Create New Workbook:

      new_wb = xlwt.Workbook()
  

• Copy Existing Sheets:

      for sheet in sheets:
          old_sheet = existing_wb.sheet_by_name(sheet)
          new_sheet = new_wb.add_sheet(sheet)
          for row in range(old_sheet.nrows):
              for col in range(old_sheet.ncols):
                  new_sheet.write(row, col, old_sheet.cell_value(row, col))
  

• Add New Sheet:

      new_sheet = new_wb.add_sheet(“NewSheet3”)
      new_sheet.write(0, 0, “This is a new sheet.”)
  

• Save the New Workbook:

      new_wb.save(‘example.xls’)
  

⚠️ Note: The `xlrd` and `xlwt` libraries are relatively old and lack many modern features. Consider them for simple applications or when other libraries are not feasible.

Having explored these three methods for adding new sheets to an Excel file in Python, we've covered a range of tools from easy-to-use libraries like openpyxl and pandas to the more basic xlwt/xlrd. Each method has its advantages:

• openpyxl provides rich features for Excel manipulation with simple syntax.
• pandas offers data analysis alongside Excel operations, making it versatile for data-centric tasks.
• xlwt/xlrd might be your go-to if avoiding third-party dependencies or for simpler Excel manipulations.

Remember, the choice of method depends on your specific needs, the complexity of your tasks, and the libraries you're comfortable with or already using in your project. Whether it’s for quick data entry, detailed analysis, or simply updating existing spreadsheets, Python’s vast ecosystem ensures you have multiple paths to achieve your Excel-related goals efficiently.

Can I add a sheet to an Excel file without overwriting existing sheets?

+

Yes, using libraries like openpyxl or pandas, you can append to existing workbooks without losing data in other sheets.

How can I add multiple sheets at once in an Excel file?

+

With openpyxl or pandas, you can loop through your list of sheets or data frames and use the respective methods to create multiple sheets in one go.

Are there any limitations to using Python’s built-in libraries for Excel manipulation?

+

Yes, xlwt and xlrd are less feature-rich compared to modern libraries like openpyxl. They lack support for newer Excel features and might not handle large files or complex operations as gracefully.