How to use Worksheets.Add in the xlwings API way

The Add member of the Worksheets object in the Excel object model is a method used to create a new worksheet. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS). This allows you to programmatically add sheets to a workbook, offering control over the sheet’s position and name.

Functionality
The primary purpose of the Add method is to insert a new worksheet into a workbook. You can specify where the new sheet should be placed relative to existing sheets and what its name should be. This is essential for automating report generation, data organization, or creating dynamic dashboards where the number of sheets may vary based on the data.

Syntax in xlwings
The general syntax using xlwings is:
workbook.api.Worksheets.Add(Before, After, Count, Type)

The parameters are:

• Before (Optional, Variant): A worksheet object that specifies the sheet before which the new sheet will be added. You cannot use both Before and After.
• After (Optional, Variant): A worksheet object that specifies the sheet after which the new sheet will be added. You cannot use both Before and After.
• Count (Optional, Variant): The number of new worksheets to add. The default value is 1.
• Type (Optional, Variant): The type of sheet to add. Can be xlWorksheet (value -4167) for a standard worksheet or xlChart (value -4109) for a chart sheet. The default is xlWorksheet.

To use a parameter, you typically pass a worksheet object (e.g., wb.sheets['Sheet1'].api) for Before or After, or an integer for Count. If both Before and After are omitted, the new sheet is added before the active sheet.

Code Examples

1. Add a single worksheet with a default name (e.g., “Sheet4”):

import xlwings as xw
wb = xw.Book() # Opens a new workbook
new_sheet = wb.api.Worksheets.Add()
# The new worksheet object is now in 'new_sheet'

2. Add a worksheet after a specific sheet and rename it:

import xlwings as xw
wb = xw.Book('Report.xlsx')
# Add new sheet after the sheet named "Data"
new_sheet = wb.api.Worksheets.Add(After=wb.sheets['Data'].api)
new_sheet.Name = "Summary" # Rename the new sheet

3. Add multiple worksheets at the beginning of the workbook:

import xlwings as xw
wb = xw.Book()
first_sheet = wb.sheets[0].api # Get the API object of the first sheet
# Add 3 new sheets before the first sheet
wb.api.Worksheets.Add(Before=first_sheet, Count=3)

4. Add a chart sheet at the end of the workbook:

import xlwings as xw
from xlwings.constants import ChartType
wb = xw.Book()
last_sheet = wb.sheets[-1].api # Get the API object of the last sheet
# Add a chart sheet after the last worksheet
chart_sheet = wb.api.Worksheets.Add(After=last_sheet, Type=ChartType.xlChart)
# Note: Chart sheets are a different object type than worksheets in the Excel model.