Archive

How to use Worksheets.Application in the xlwings API way

The Application member of the Worksheets object in Excel’s object model provides a bridge to the overarching Excel application instance from a specific worksheets collection. In xlwings, this is accessed via the api property, which exposes the underlying COM object, allowing you to leverage Excel’s native object model directly. This is particularly useful for retrieving high-level application settings, controlling Excel’s behavior, or accessing other top-level objects that aren’t directly exposed through xlwings’ simplified object model.

Functionality:
The primary purpose of accessing Application through a Worksheets object is to obtain a reference to the Excel Application object. This reference enables you to:

• Retrieve application-wide properties (e.g., Application.Version, Application.ScreenUpdating).
• Execute application-level methods (e.g., Application.Calculate, Application.Quit).
• Access other collections like Workbooks, Windows, or AddIns.

Syntax in xlwings:
The general syntax to access the Application member via xlwings is:

ws_object.api.Application

Where ws_object is an xlwings Sheets or Worksheet object. From this, you can chain to properties or methods of the Excel Application object.

Common Properties and Methods via Application:

• Properties:
• .Version: Returns the Excel version as a string.
• .ScreenUpdating: Gets or sets a Boolean to control screen refresh.
• .DisplayAlerts: Gets or sets a Boolean to control alert displays.
• .Calculation: Gets or sets the calculation mode (e.g., xlCalculationAutomatic, xlCalculationManual). Use xlwings constants like xlwings.constants.xlCalculationAutomatic.
• Methods:
• .Calculate(): Forces a recalculation of all open workbooks.
• .Quit(): Closes the Excel application. Use with caution.

Code Examples:

1. Retrieving Excel Application Version and Controlling Screen Updates:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book() # Opens a new workbook
ws = wb.sheets[0] # Get the first worksheet

# Access Application via the worksheet
app = ws.api.Application

# Get Excel version
print(f"Excel Version: {app.Version}")

# Turn off screen updating for performance
app.ScreenUpdating = False

# Perform some operations (e.g., writing data)
ws.range('A1').value = 'Hello, World!'
wb.save('test.xlsx')

# Turn screen updating back on
app.ScreenUpdating = True

2. Changing Calculation Mode and Forcing Recalculation:

import xlwings as xw
from xlwings.constants import Calculation

wb = xw.Book('example.xlsx')
ws = wb.sheets['Data']

app = ws.api.Application

# Set calculation to manual
app.Calculation = Calculation.xlCalculationManual
print("Calculation mode set to manual.")

# After making changes to formulas, force a full calculation
app.Calculate()
print("Forced recalculation performed.")

# Revert to automatic calculation
app.Calculation = Calculation.xlCalculationAutomatic

3. Accessing Other Application-Level Collections:

import xlwings as xw

wb = xw.Book()
ws = wb.sheets[0]

app = ws.api.Application

# List all open workbooks via Application
for wb in app.Workbooks:
    print(wb.Name)

# Check if alerts are displayed
if app.DisplayAlerts:
    print("Alerts are enabled.")

How to use Worksheets.Select in the xlwings API way

In the xlwings library, the Worksheets object’s Select method provides a way to programmatically activate or bring a specific worksheet into view within an Excel workbook. This is functionally analogous to manually clicking on a worksheet tab in the Excel user interface. The primary purpose of using Select is to set the active worksheet, which is often a necessary preliminary step before performing other operations like writing data, formatting cells, or creating charts on that specific sheet.

Functionality
The Select method makes the specified worksheet the active sheet in its containing workbook window. If the workbook has multiple windows open, it activates the sheet in the active window. It’s important to note that while Select activates the sheet, for most object model operations in xlwings (like Range operations), explicitly selecting a sheet is not strictly required because you can directly qualify ranges with the sheet object. However, Select remains useful for scenarios where the visual focus needs to change for the user, or when interacting with certain Excel features that rely on the active sheet context.

Syntax and Parameters
The xlwings API closely mirrors the VBA object model. The Select method is called on a Sheet object (which is typically accessed via the sheets collection of a Book object). In xlwings, you usually obtain a sheet object first.

The basic syntax is:

sheet_object.select()

This method does not take any parameters in its common usage through xlwings. Underlying the simple API, the Excel Object Model’s Select method has an optional Replace parameter (which defaults to True). This parameter controls behavior when used with the Sheets collection to select multiple sheets. However, when selecting a single worksheet via a Worksheet object (as is typical in xlwings), this parameter is largely irrelevant and is not exposed as an argument in the standard xlwings select() call. To mimic the full VBA method signature for advanced use (like selecting multiple sheets), you would need to use the underlying api property to access the raw VBA method.

Code Examples
Here are practical examples demonstrating the use of the Select method with xlwings.

Example 1: Basic Selection
This example opens a workbook and selects a specific sheet named “DataSheet”.

import xlwings as xw

# Connect to an open workbook or open a new one
wb = xw.Book('example.xlsx')

# Get a reference to the worksheet named "DataSheet"
data_sheet = wb.sheets['DataSheet']

# Select the worksheet, making it the active sheet
data_sheet.select()

# Now any operation that doesn't explicitly specify a sheet might use this active sheet.
# However, the better practice is to use the sheet object directly:
data_sheet.range('A1').value = 'Hello World'

Example 2: Selecting Multiple Sheets (Using the underlying API)
While the standard sheet.select() doesn’t support multiple selection, you can achieve it by accessing the Excel API directly via the api property. This is useful for grouping sheets.

import xlwings as xw

wb = xw.Book('example.xlsx')

# Access the underlying VBA Sheets collection
wb.api.Sheets(["Sheet1", "Sheet2"]).Select()
# This selects both Sheet1 and Sheet2, making Sheet1 the active sheet within the group.

Example 3: Iterating and Selecting
This example iterates through all worksheets and selects each one, performing an operation on it. This simulates a user clicking through each tab.

import xlwings as xw
import time

wb = xw.Book('example.xlsx')

for sheet in wb.sheets:
    sheet.select()
    # Add a timestamp in cell A1 of the now-active sheet
    sheet.range('A1').value = f"Selected at: {time.strftime('%H:%M:%S')}"
    # A short pause to visualize the selection change
    time.sleep(0.5)

How to use Worksheets.PrintPreview in the xlwings API way

The PrintPreview method in the Worksheets object is a powerful feature in Excel’s object model that allows users to preview how a worksheet will appear when printed, without actually sending it to the printer. This functionality is essential for ensuring proper formatting, layout, and page breaks before finalizing print jobs, saving both time and resources. In xlwings, a Python library that automates Excel through its COM interface, accessing this method enables seamless integration of print preview capabilities into automated scripts or applications.

Functionality:
The PrintPreview method displays the print preview dialog for a specified worksheet, showing a visual representation of the printed pages. It helps users check margins, headers, footers, scaling options, and overall page arrangement. This is particularly useful in automated reporting workflows where multiple worksheets are generated programmatically, and manual verification of each sheet’s print layout would be inefficient.

Syntax in xlwings:
In xlwings, the PrintPreview method is accessed through a Worksheet object, which is part of the Worksheets collection. The general syntax is as follows:

worksheet.api.PrintPreview()

Here, worksheet refers to an xlwings Worksheet object representing the target sheet. The .api attribute provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS), allowing calls to native methods like PrintPreview. This method does not require any parameters, as it simply activates the print preview interface for the active worksheet context.

Key Considerations:

• The PrintPreview method is a member of the Excel Worksheet object, not the Worksheets collection itself. Thus, you must call it on a specific worksheet instance.
• In xlwings, the method is invoked via the .api property to bridge Python with Excel’s COM automation. Without this, xlwings’ high-level API does not directly expose PrintPreview.
• The method opens Excel’s print preview window, which is modal by default—meaning the script will pause until the user closes the preview. To avoid interruptions in automation, consider alternative approaches like setting print properties programmatically.

Code Examples:
Below are practical examples demonstrating the use of PrintPreview with xlwings in different scenarios.

Example 1: Basic Print Preview for an Active Worksheet
This code opens an Excel workbook and triggers the print preview for the first worksheet.

import xlwings as xw

# Open an existing workbook or create a new one
wb = xw.Book('example.xlsx')
ws = wb.sheets[0] # Access the first worksheet

# Activate print preview
ws.api.PrintPreview()

Example 2: Preview Multiple Worksheets in a Loop
This script iterates through all worksheets in a workbook and opens print preview for each, allowing batch inspection.

import xlwings as xw

wb = xw.Book('report.xlsx')
for sheet in wb.sheets:
    print(f"Previewing: {sheet.name}")
    sheet.api.PrintPreview()
    # Note: The script waits for user to close each preview window

Example 3: Conditional Print Preview Based on Content
Here, print preview is only triggered for worksheets that contain data beyond a certain threshold, optimizing the workflow.

import xlwings as xw

wb = xw.Book('data.xlsx')
for ws in wb.sheets:
    # Check if the worksheet has more than 10 used rows
    if ws.used_range.last_cell.row > 10:
        print(f"Previewing large sheet: {ws.name}")
        ws.api.PrintPreview()

How to use Worksheets.PrintOut in the xlwings API way

The PrintOut method in the Worksheets collection, accessible via the xlwings API, provides a powerful way to print worksheets directly from Python. This functionality is essential for automating report generation and document distribution workflows, allowing you to control various aspects of the printing process without manual intervention in Excel.

Functionality
The PrintOut method sends one or more worksheets to a printer. You can specify a range of pages, the number of copies, whether to print to a file, and other standard print settings. It is particularly useful for batch printing multiple sheets or generating physical/digital copies of reports as part of an automated script.

Syntax in xlwings
The method is called on a Sheet object (representing a single worksheet) or can be applied to multiple sheets via the sheets collection. The xlwings syntax mirrors the VBA object model closely.

sheet.api.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

Parameters and Their Meanings

Parameter	Data Type	Description	How to Access/Example Value
From	Integer	The page number at which to start printing. If omitted, printing starts from the beginning.	Set to a number, e.g., 1.
To	Integer	The page number at which to stop printing. If omitted, printing goes to the last page.	Set to a number, e.g., 3.
Copies	Integer	The number of copies to print. Default is 1.	Set to a number, e.g., 2.
Preview	Boolean	True to have Excel invoke print preview before printing. False (default) to print immediately.	Use True or False.
ActivePrinter	String	Sets the name of the active printer.	Provide a printer name string. Often left as None to use default.
PrintToFile	Boolean	True to print to a file. If True, you must specify PrToFileName.	Use True or False.
Collate	Boolean	True (default) to collate multiple copies.	Use True or False.
PrToFileName	String	If PrintToFile is True, this is the name of the file to print to.	Provide a full file path string.
IgnorePrintAreas	Boolean	True to ignore any print areas set in the worksheet and print the entire sheet.	Use True or False.

Most parameters are optional. In xlwings, you pass them as keyword arguments, and you can omit any you don’t need, using Excel’s defaults.

Code Examples

1. Basic Print: Print the active sheet immediately to the default printer.

import xlwings as xw
wb = xw.Book('report.xlsx')
wb.sheets['Sheet1'].api.PrintOut()

2. Print Multiple Copies with Collation: Print two collated copies of a specific page range from “DataSheet”.

sheet = wb.sheets['DataSheet']
sheet.api.PrintOut(From=1, To=1, Copies=2, Collate=True)

3. Print to a PDF File: Print the entire “Summary” worksheet to a PDF file, ignoring any set print area.

output_path = r'C:\Reports\summary.pdf'
wb.sheets['Summary'].api.PrintOut(PrintToFile=True, PrToFileName=output_path, IgnorePrintAreas=True)

4. Print Preview: Open the print preview for the first three sheets without actually printing.

for sheet in wb.sheets[:3]: # Loop through first three sheets
sheet.api.PrintOut(Preview=True, Copies=1)

How to use Worksheets.Move in the xlwings API way

The Move member of the Worksheets object in Excel’s object model allows for repositioning a worksheet within a workbook. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. This is particularly useful for organizing sheets in a specific order, such as moving a newly created sheet to the beginning or end of the workbook.

Functionality:
The Move method relocates a specified worksheet to a new position relative to other sheets. It can place the sheet before or after another worksheet, enabling precise control over the sheet order. This is essential for creating reports or dashboards where the sequence of sheets impacts usability and presentation.

Syntax:
In xlwings, the syntax for moving a worksheet is:

worksheet.api.Move(Before, After)

• Before (optional): A Worksheet object representing the sheet before which the moved sheet will be placed. If specified, the moved sheet is positioned immediately before this sheet.
• After (optional): A Worksheet object representing the sheet after which the moved sheet will be placed. If specified, the moved sheet is positioned immediately after this sheet.

Notes:

• You must specify either Before or After, but not both. If both are omitted, Excel creates a new workbook containing the moved sheet.
• The parameters accept Worksheet objects, which can be obtained via xlwings (e.g., wb.sheets['SheetName']).
• Moving a sheet does not affect its content or formatting, only its position in the workbook tab order.

Code Examples:

1. Move a sheet to the beginning of the workbook:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
target_sheet = wb.sheets['DataSheet']
first_sheet = wb.sheets[0] # Get the first sheet
target_sheet.api.Move(Before=first_sheet.api)

This moves ‘DataSheet’ to appear before the first sheet, making it the new first sheet.

2. Move a sheet to the end of the workbook:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
target_sheet = wb.sheets['ReportSheet']
last_sheet = wb.sheets[-1] # Get the last sheet
target_sheet.api.Move(After=last_sheet.api)

This positions ‘ReportSheet’ after the last sheet, placing it at the end.

3. Move a sheet relative to another specific sheet:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
sheet_to_move = wb.sheets['Sheet1']
reference_sheet = wb.sheets['Sheet3']
sheet_to_move.api.Move(After=reference_sheet.api)

How to use Worksheets.FillAcrossSheets in the xlwings API way

The FillAcrossSheets member of the Worksheets object in Excel is a powerful method used to copy a specified range from one worksheet to the same range on all other worksheets within the same workbook. This functionality is particularly useful when you need to maintain consistent formatting, formulas, or static data (like headers or standard values) across multiple sheets in a report or dashboard. Instead of manually copying and pasting to each sheet, FillAcrossSheets automates this process, ensuring uniformity and saving significant time.

In the Excel object model, accessed via xlwings, the syntax for this method is as follows:

Worksheets.FillAcrossSheets(Range, Type)

The method requires two parameters:

• Range: This is a required parameter. It specifies the range of cells to be copied across the worksheets. In xlwings, this is typically provided as an xlwings.Range object. You can define it using the Range property on a sheet object, for example, sheet.range("A1:D10").
• Type: This is an optional parameter that determines what content from the source range is copied. It accepts values from the XlFillWith enumeration. The most commonly used values are:
• xlFillWithAll (default, value = -4104): Copies everything from the source range—content, formulas, and formatting.
• xlFillWithContents (value = 2): Copies only values and formulas, but not the cell formatting.
• xlFillWithFormats (value = -4122): Copies only the cell formatting (like font, color, borders), but not the values or formulas.

If the Type parameter is omitted, the default behavior is xlFillWithAll.

Example Usage with xlwings:

Imagine you have a workbook with three sheets named “Q1”, “Q2”, and “Q3”. You want to set up a standard header in cells A1 through D1 on every sheet. You would write the header on the “Q1” sheet and then use FillAcrossSheets to propagate it.

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active

# Specify the workbook (use .books.active for the active workbook)
wb = app.books['Financials.xlsx']

# Define the range to copy (the header on the first sheet)
source_range = wb.sheets['Q1'].range('A1:D1')
source_range.value = ['Product', 'Region', 'Sales', 'Target'] # Set the header values
source_range.font.bold = True # Apply some formatting

# Use FillAcrossSheets to copy this range to all other worksheets in the workbook
# This targets the Worksheets collection of the specific workbook.
wb.sheets.api.FillAcrossSheets(source_range.api)

# To copy only the formatting of a complex template area:
template_range = wb.sheets['Template'].range('A1:F20')
wb.sheets.api.FillAcrossSheets(template_range.api, Type=-4122) # xlFillWithFormats

# Save and close
wb.save()
wb.close()
app.quit()

How to use Worksheets.Delete in the xlwings API way

The Delete member of the Worksheets collection in the Excel object model is a method used to remove a specified worksheet from a workbook. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control over worksheet management. This method is particularly useful for automating the cleanup of temporary sheets, restructuring workbooks dynamically, or removing unnecessary data sheets in batch processing scenarios. Understanding its usage in xlwings ensures efficient workbook manipulation while maintaining compatibility with Excel’s native behavior.

Functionality
The Delete method permanently deletes a worksheet from the workbook. It does not move the sheet to the Recycle Bin; once deleted, the sheet cannot be recovered unless the workbook is closed without saving. In xlwings, this operation is performed by accessing the Excel API, so it mirrors the exact behavior of Excel’s VBA Delete method. This includes any prompts or warnings Excel might display, depending on the application settings.

Syntax
In xlwings, the Delete method is called via the api property on a Worksheet object. The syntax is:
worksheet.api.Delete()
Here, worksheet refers to an xlwings Worksheet object (e.g., obtained through wb.sheets['SheetName'] or wb.sheets[0]). The method does not take any parameters in its basic form. However, in Excel’s object model, the Delete method can be influenced by the application’s display alerts. To suppress confirmation dialogs, you can set xlwings.App().api.DisplayAlerts = False before deletion and restore it to True afterward.

Example
Below is a code example demonstrating the usage of the Delete member with xlwings. This example assumes you have an existing workbook with multiple sheets and want to remove a specific sheet programmatically.

import xlwings as xw

# Connect to an existing workbook (adjust the path as needed)
wb = xw.Book('example.xlsx')

# Access a specific worksheet by name
sheet_to_delete = wb.sheets['TempSheet']

# Disable Excel alerts to avoid confirmation prompts
app = xw.apps.active
app.api.DisplayAlerts = False

# Delete the worksheet
sheet_to_delete.api.Delete()

# Re-enable alerts
app.api.DisplayAlerts = True

# Save the workbook to persist changes
wb.save()

# Close the workbook (optional)
wb.close()

How to use Worksheets.Copy in the xlwings API way

The Copy method of the Worksheets object in Excel VBA is mirrored in xlwings through the api property, which provides direct access to the underlying Excel object model. This method is used to duplicate one or more worksheets, placing the copies either before or after a specified sheet in the workbook, or into a new workbook entirely. It is particularly useful for creating templates, generating reports, or backing up data without altering the original sheets.

Syntax in xlwings:

The method is accessed via the api property of a sheet object. The general syntax is:

sheet.api.Copy(Before, After)

• sheet: This is the xlwings Sheet object representing the worksheet you want to copy. You typically obtain it via wb.sheets['SheetName'] or wb.sheets[0].
• Before (Optional, Variant): A sheet object before which the copied sheet(s) will be placed. You cannot specify both Before and After.
• After (Optional, Variant): A sheet object after which the copied sheet(s) will be placed. You cannot specify both Before and After.

Parameter Behavior:

• If neither Before nor After is specified, Excel creates a new workbook and places the copied sheet(s) there. The new workbook becomes the active workbook.
• You must provide either the Before or After argument to place the copy within the same workbook. These arguments are passed as Excel sheet objects, which you can get via the .api property of an xlwings Sheet object.

Examples:

1. Copy a sheet to a new workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Data']
source_sheet.api.Copy() # Creates a new workbook with the copied sheet

2. Copy a sheet and place it before a specific sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Source']
target_sheet = wb.sheets['Target'] # The sheet before which we place the copy
source_sheet.api.Copy(Before=target_sheet.api)
# The new copy will be named "Source (2)" and appear before "Target".

3. Copy a sheet and place it after the last sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Report']
all_sheets = wb.sheets
last_sheet = all_sheets[len(all_sheets) - 1] # Get the last sheet object
source_sheet.api.Copy(After=last_sheet.api)
# The copy is placed at the end of the workbook.

4. Copy multiple worksheets (the entire Worksheets collection):
   To copy multiple sheets, you use the Worksheets collection’s Copy method. In xlwings, you can access this via the workbook’s sheets collection api.

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
# Select specific sheets to copy (e.g., first and third sheet)
wb.api.Worksheets([1, 3]).Copy() # Creates a new workbook with copies of sheets 1 and 3.
# Note: The index [1, 3] uses VBA's 1-based indexing.

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"

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.