How to use Worksheets.Add2 in the xlwings API way

The Worksheets.Add2 method in Excel’s object model is a powerful feature for creating new worksheets within a workbook. In xlwings, this method is accessible through the api property, which provides direct access to the underlying Excel object model. The Add2 method is an enhanced version of the traditional Add method, offering additional parameters for more control over the sheet creation process, such as specifying the sheet type. It is particularly useful for automating the generation of reports, dashboards, or data logs in Excel workbooks.

Syntax in xlwings:
The xlwings API call for the Add2 method follows this general format:
workbook.api.Worksheets.Add2(Before, After, Count, Type)

• Before (optional): A Worksheet object that specifies the sheet before which the new sheet will be added. If omitted, the new sheet is added after all existing sheets.
• After (optional): A Worksheet object that specifies the sheet after which the new sheet will be added. If both Before and After are omitted, the new sheet is added as the last sheet.
• Count (optional): An Integer that specifies the number of sheets to add. The default is 1.
• Type (optional): An XlSheetType constant that specifies the sheet type. Common values include:
• xlWorksheet (default): A standard worksheet.
• xlChart: A chart sheet.
• xlExcel4MacroSheet: A macro sheet (for compatibility).
• xlExcel4IntlMacroSheet: An international macro sheet.

Example Usage:
Here are several xlwings API code examples demonstrating the use of the Worksheets.Add2 member:

1. Adding a single worksheet at the end of the workbook:

import xlwings as xw
wb = xw.Book() # Open a new workbook
new_sheet = wb.api.Worksheets.Add2()
new_sheet.Name = "DataSummary"

2. Adding a worksheet before a specific sheet:

import xlwings as xw
wb = xw.Book('report.xlsx')
target_sheet = wb.sheets['Sheet1']
new_sheet = wb.api.Worksheets.Add2(Before=target_sheet.api)
new_sheet.Name = "Introduction"

3. Adding multiple chart sheets after a specific sheet:

import xlwings as xw
wb = xw.Book('data.xlsx')
after_sheet = wb.sheets['RawData']
# Add two chart sheets
chart_sheets = wb.api.Worksheets.Add2(After=after_sheet.api, Count=2, Type=-4109) # -4109 is xlChart
chart_sheets.Item(1).Name = "Chart1"
chart_sheets.Item(2).Name = "Chart2"

4. Using constants for sheet types (requires importing win32com.client or similar):

import xlwings as xw
from win32com.client import constants
wb = xw.Book()
new_chart_sheet = wb.api.Worksheets.Add2(Type=constants.xlChart)
new_chart_sheet.Name = "AnalysisChart"