Archive

How to use Worksheet.Delete in the xlwings API way

The Delete member of the Worksheet object in the Excel object model, accessible via the xlwings API in Python, provides a programmatic way to remove a specific worksheet from a workbook. This operation is irreversible through the API itself (akin to manually deleting a sheet and not saving the workbook), so it should be used with caution, especially on unsaved workbooks where data loss can occur.

Functionality
The primary function of the Delete method is to permanently delete the worksheet object on which it is called. After deletion, the worksheet is removed from the workbook’s Worksheets collection. If the deleted sheet was the only sheet in the workbook, xlwings and Excel will typically prevent its deletion to maintain at least one visible sheet, as an Excel workbook must contain at least one visible worksheet.

Syntax & Parameters
In xlwings, the method is called directly on a Sheet object (which represents a worksheet or chart sheet). The xlwings API abstracts the underlying COM calls into a simple Python method call.

sheet.delete()

There are no parameters for this method in the standard xlwings API. The action is applied to the sheet object referenced. It’s important to note that the Sheet object in xlwings corresponds to what the Excel Object Model calls a Worksheet (for standard worksheets) or a Chart object (for chart sheets). The delete() method works on both.

Code Examples

1. Basic Deletion of a Specific Sheet:
   This example opens a workbook and deletes a sheet named “SheetToRemove”.

import xlwings as xw

# Open the workbook (use full path if needed)
wb = xw.Book("example.xlsx")

# Access the specific worksheet
sheet_to_delete = wb.sheets["SheetToRemove"]

# Delete the worksheet
sheet_to_delete.delete()

# Save the workbook to persist the change
wb.save()
wb.close()

2. Deleting the Active Sheet:
   This example deletes whichever sheet is currently active in the open workbook.

import xlwings as xw

app = xw.App(visible=False) # Start Excel in the background
wb = app.books.open("data.xlsx")

# Delete the active sheet
wb.sheets.active.delete()

wb.save("data_modified.xlsx")
wb.close()
app.quit()

3. Conditional Deletion Based on Content:
   A more practical example involves checking sheet names or content before deletion. This script deletes all sheets whose name contains the word “Temp”.

import xlwings as xw

wb = xw.Book("report.xlsx")

# Create a list of sheets to delete first to avoid iteration issues
sheets_to_delete = [sht for sht in wb.sheets if "Temp" in sht.name]

for sht in sheets_to_delete:
    print(f"Deleting sheet: {sht.name}")
    sht.delete()

wb.save()

How to use Worksheet.Copy in the xlwings API way

The Copy method of the Worksheet object in xlwings is a powerful tool for duplicating worksheets within or across workbooks. This functionality is essential for tasks such as creating templates, backing up data, or reorganizing workbook structures without manual copying and pasting. By leveraging the Excel object model through xlwings, users can automate these processes efficiently in Python.

In xlwings, the Copy method is accessed via the api property, which provides direct access to the underlying Excel object model. The syntax follows the pattern of the Excel VBA Copy method, where you specify the location for the copied sheet. The method signature is Copy(Before, After), with both parameters being optional. The Before parameter accepts a Worksheet object indicating the sheet before which the copy should be placed, while After specifies the sheet after which the copy should be inserted. If neither Before nor After is provided, Excel creates a new workbook to hold the copied worksheet. It’s important to note that you cannot use both Before and After simultaneously; specifying one excludes the other. These parameters allow precise control over the placement of the duplicated sheet within the workbook’s tab order.

For example, to copy a worksheet named “DataSheet” and place it before an existing sheet called “SummarySheet” in the same workbook, you can use the following xlwings code:

import xlwings as xw
wb = xw.Book("example.xlsx")
data_sheet = wb.sheets["DataSheet"]
summary_sheet = wb.sheets["SummarySheet"]
data_sheet.api.Copy(Before=summary_sheet.api)

This code snippet opens a workbook, references the “DataSheet” and “SummarySheet”, and copies “DataSheet” to appear directly before “SummarySheet”. The copied sheet will automatically be named “DataSheet (2)” by Excel to avoid naming conflicts.

Another common use case is copying a worksheet to a new workbook. By omitting both Before and After parameters, Excel generates a new workbook containing only the copied worksheet. For instance:

import xlwings as xw
wb = xw.Book("source.xlsx")
source_sheet = wb.sheets["SourceSheet"]
source_sheet.api.Copy()

After executing this, a new Excel workbook will open with a worksheet named “SourceSheet” that is a duplicate of the original. This is particularly useful for exporting specific sheets to separate files for distribution or analysis.

When copying between different workbooks, you need to reference the target workbook’s worksheets for the Before or After parameters. For example, to copy “Sheet1” from one workbook and place it after “SheetA” in another workbook:

import xlwings as xw
wb1 = xw.Book("workbook1.xlsx")
wb2 = xw.Book("workbook2.xlsx")
sheet1 = wb1.sheets["Sheet1"]
sheeta = wb2.sheets["SheetA"]
sheet1.api.Copy(After=sheeta.api)

How to use Worksheet.ClearCircles in the xlwings API way

The ClearCircles method of the Worksheet object in Excel is used to remove all circles that have been applied to cells via data validation error alert circles. These circles typically appear when data validation rules are violated, visually indicating invalid entries in a worksheet. The method is particularly useful for cleaning up the visual interface after data validation errors have been corrected or when preparing a sheet for presentation or further processing. It operates on the entire worksheet, affecting all cells within it.

In xlwings, the ClearCircles method can be accessed through a Worksheet object. The syntax is straightforward, as it does not require any parameters. The method is called directly on the worksheet instance.

Syntax:

worksheet.api.ClearCircles()

Here, worksheet is an xlwings Worksheet object representing the target sheet. The .api attribute provides access to the underlying Excel object model, allowing direct invocation of the native ClearCircles method. No arguments are needed, as the action applies to all circles from data validation errors on that specific worksheet.

Example:
Consider a scenario where a worksheet contains data validation rules, such as restricting input in column A to whole numbers between 1 and 10. If a user enters text like “abc” in cell A5, Excel may display a red circle around that cell (depending on error alert settings). To programmatically remove all such circles after reviewing and correcting the data, you can use the following xlwings code:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.App(visible=False) # Set to True if you want to see Excel
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']

# Assume data validation errors exist and circles are visible
# Clear all circles from data validation errors
sheet.api.ClearCircles()

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

How to use Worksheet.ClearArrows in the xlwings API way

The ClearArrows member of the Worksheet object in the Excel object model is accessible through the xlwings library, a powerful tool for automating Excel with Python. This method specifically targets the removal of tracer arrows within a worksheet. Tracer arrows are visual aids in Excel that help users understand formula dependencies and precedents by drawing arrows from cells that provide data to the cells that use that data (dependents) or from cells that are referenced by a formula (precedents). The ClearArrows method is essential for cleaning up the worksheet view, removing these arrows to declutter the interface, especially after auditing formulas or during the preparation of a final report.

In the xlwings API, the ClearArrows method is called directly on a Worksheet object. The syntax is straightforward, as the method does not require any parameters. It corresponds to the ClearArrows method in the Excel VBA object model, which clears all tracer arrows on the specified worksheet.

Syntax:

worksheet.api.ClearArrows()

Here, worksheet is an xlwings Worksheet object. The .api property provides direct access to the underlying Excel object model, allowing you to call native Excel methods like ClearArrows. This method clears all tracer arrows—both precedent and dependent arrows—from the active sheet. There are no parameters to specify, making its usage simple and direct.

Code Example:
The following xlwings code demonstrates how to use the ClearArrows method. It assumes you have an Excel workbook open and a specific worksheet selected.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Specify the workbook (use the active workbook or open by name)
wb = app.books.active # or app.books['YourWorkbook.xlsx']

# Specify the worksheet by name or index
ws = wb.sheets['Sheet1'] # or wb.sheets[0]

# Clear all tracer arrows on the worksheet
ws.api.ClearArrows()

print("All tracer arrows have been cleared from the worksheet.")

How to use Worksheet.CircleInvalid in the xlwings API way

The CircleInvalid method in the Worksheet object is a useful feature for data validation and error checking in Excel. When applied, it draws red circles around cells that contain data failing any validation rules set for those cells. This visual cue helps users quickly identify and correct invalid entries, enhancing data integrity. In xlwings, this method can be accessed through the api property, which provides direct access to the underlying Excel object model, allowing for seamless integration of Excel’s native functionalities into Python scripts.

The syntax for using CircleInvalid in xlwings is straightforward: worksheet.api.CircleInvalid(). This method does not take any parameters, as it simply applies the circling effect to all cells in the worksheet that currently violate validation rules. It is important to note that this method is a member of the Excel VBA Worksheet object, and xlwings bridges this by exposing it via the api attribute. Before calling CircleInvalid, ensure that data validation rules are properly set in the Excel worksheet, as the method relies on these rules to determine invalid cells. The circles are drawn based on the active validation criteria, and they can be removed by using the ClearCircles method if needed.

For example, consider a scenario where you have an Excel worksheet with a column for age entries, and you’ve set a data validation rule to only allow values between 0 and 120. If some cells contain ages outside this range, you can use xlwings to circle those invalid entries. Here’s a code instance:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.App(visible=True) # Set visible=False for background operation
wb = app.books.open('example.xlsx') # Replace with your file path
ws = wb.sheets['Sheet1'] # Specify the worksheet name

# Apply data validation rule (if not already set in Excel)
# Note: xlwings does not directly set validation; ensure it's pre-configured in Excel.
# For demonstration, assume validation is already applied in the worksheet.

# Circle invalid cells based on existing validation rules
ws.api.CircleInvalid()

# To remove the circles after correction, you could use:
# ws.api.ClearCircles()

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

How to use Worksheet.CheckSpelling in the xlwings API way

The CheckSpelling member of a Worksheet object in Excel provides a programmatic way to initiate a spell check on the text within that specific worksheet. In xlwings, this functionality is exposed through the api property, which grants direct access to the underlying Excel object model. This is particularly useful for automating document review processes or for integrating spell-checking into larger data validation and reporting workflows. The method checks the spelling of words in the worksheet’s cells, leveraging the same dictionaries and custom dictionaries used by Excel’s native spell-check feature.

Syntax in xlwings:
The method is accessed via the worksheet’s api object. Its full syntax in the Excel object model is complex, but the xlwings call typically uses the most common parameters.

worksheet.api.CheckSpelling(CustomDictionary, IgnoreUppercase, AlwaysSuggest, SpellLang)

• CustomDictionary (Optional, String): The file name of the custom dictionary to be used if the word is not found in the main dictionary. If omitted, the currently specified dictionary is used.
• IgnoreUppercase (Optional, Boolean): True to have Excel ignore words in all uppercase letters (e.g., “USA”). False to check them. If omitted, the current application setting is used.
• AlwaysSuggest (Optional, Boolean): True to have Excel display a list of suggestions for misspelled words. False to just check spelling without suggestions. If omitted, the current application setting is used.
• SpellLang (Optional, Variant): The language of the dictionary to use. This can be a language ID (LCID). It’s often omitted to use the application’s default language.

In practice, when called without arguments, it starts the interactive spell-check dialog, just like pressing F7 in Excel.

Code Examples:

1. Basic Spell Check (Interactive Dialog):
   This code opens the specified workbook, activates the first worksheet, and starts the standard Excel spell-check dialog, pausing the script until the user closes it.

import xlwings as xw
# Connect to an open workbook or open a new one
app = xw.App(visible=True)
wb = app.books.open('report.xlsx')
ws = wb.sheets[0]
# Start the interactive spell check
ws.api.CheckSpelling()
# ... other operations can follow after the dialog is closed
wb.save()
wb.close()
app.quit()

2. Spell Check with Specific Parameters:
   This example performs a spell check that ignores words in all caps and uses a specific custom dictionary file. Note that the AlwaysSuggest parameter might not prevent the dialog from appearing if misspellings are found, depending on Excel’s version and settings.

import xlwings as xw
from pathlib import Path

custom_dict_path = str(Path.home() / 'custom.dic')
with xw.App(visible=False) as app:
wb = app.books.add()
ws = wb.sheets[0]
# Populate some cells with text for testing
ws.range('A1').value = "This is a testt for spellling."
ws.range('A2').value = "NASA and HTML are acronyms."

# Check spelling, ignoring uppercase words, using a custom dict
# The method likely returns True if no errors were found, False otherwise.
check_passed = ws.api.CheckSpelling(CustomDictionary=custom_dict_path,
IgnoreUppercase=True,
AlwaysSuggest=False)
print(f"Spell check passed without errors: {check_passed}")
# In a non-visible app, the dialog may not appear; the return value is key.

How to use Worksheet.ChartObjects in the xlwings API way

The ChartObjects member of a Worksheet object in Excel’s object model represents a collection of all embedded chart objects (charts that are placed directly on a worksheet, as opposed to chart sheets). In xlwings, this collection is accessible via the api property, which provides direct access to the underlying COM object model. This allows for detailed control over chart creation, modification, and management directly from Python.

Functionality:
The primary function of the ChartObjects collection is to enable programmatic manipulation of charts embedded within a specific worksheet. Through xlwings, you can add new charts, access existing ones, modify their properties (such as position, size, and chart type), or delete them. This is particularly useful for automating report generation, dynamically updating visualizations based on data changes, or creating dashboards where charts need to be precisely positioned alongside data.

Syntax:
In xlwings, you access the ChartObjects collection via the api property of a Sheet object (which corresponds to a worksheet). The typical syntax is:

chart_objects = ws.api.ChartObjects

Here, ws is an xlwings Sheet object representing the worksheet. The ChartObjects object has several methods and properties. A key method for adding a new chart is Add, which has the following parameters:

• Left (Required, float): The position, in points, of the left edge of the new chart object relative to the left edge of column A on the worksheet.
• Top (Required, float): The position, in points, of the top edge of the new chart object relative to the top edge of row 1.
• Width (Required, float): The width of the new chart object in points.
• Height (Required, float): The height of the new chart object in points.

The method returns a ChartObject object, which has a Chart property that provides access to the actual chart (where you set the chart type, data source, etc.).

Code Examples:

1. Adding a New Chart:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book() # Creates a new workbook
ws = wb.sheets['Sheet1']

# Add some sample data
ws.range('A1').value = [['Month', 'Sales'], ['Jan', 150], ['Feb', 200], ['Mar', 180]]

# Access the ChartObjects collection and add a new chart
chart_obj = ws.api.ChartObjects().Add(Left=100, Top=50, Width=300, Height=200)
chart = chart_obj.Chart

# Set the chart type and data source
chart.ChartType = 51 # xlColumnClustered (value from Excel constants)
chart.SetSourceData(Source=ws.range('A1:B4').api)
chart.HasTitle = True
chart.ChartTitle.Text = "Monthly Sales"

2. Accessing and Modifying an Existing Chart:
   Assume there is already an embedded chart named “Chart 1” on the worksheet.

# Access the ChartObjects collection
all_charts = ws.api.ChartObjects

# Access a specific chart by its index (1-based) or name
chart_obj = all_charts(1) # First chart in the collection
# Or by name: chart_obj = all_charts("Chart 1")

# Modify its position and size
chart_obj.Left = 200
chart_obj.Top = 100
chart_obj.Width = 400
chart_obj.Height = 250

# Change the chart title via the Chart property
chart_obj.Chart.ChartTitle.Text = "Updated Sales Report"

3. Looping Through All Charts on a Worksheet:

# Iterate over all embedded chart objects
for chart_obj in ws.api.ChartObjects:
print(f"Chart Name: {chart_obj.Name}")
print(f"Chart Type: {chart_obj.Chart.ChartType}")
# You can perform operations like checking title or changing data series
if chart_obj.Chart.HasTitle:
    print(f"Title: {chart_obj.Chart.ChartTitle.Text}")

4. Deleting a Chart:

# Delete a specific chart by name
ws.api.ChartObjects("Chart 1").Delete()
# Or delete all charts on the worksheet
for chart_obj in ws.api.ChartObjects:
    chart_obj.Delete()

How to use Worksheet.Calculate in the xlwings API way

The Calculate method of the Worksheet object in Excel’s object model is a powerful tool for forcing a recalculation of all formulas within a specific worksheet. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This is particularly useful when you have a workbook with manual calculation enabled or when you need to ensure that a specific sheet’s formulas are up-to-date before proceeding with further data analysis or visualization steps, without recalculating the entire workbook.

Functionality:
The primary function is to trigger a recalculation of all formulas on the worksheet. This is equivalent to pressing F9 while the specific sheet is active in the Excel application. It ensures that any dependent cells are updated based on the latest values of their precedent cells.

Syntax in xlwings:
The method is accessed via the api property of a Sheet object (which corresponds to a Worksheet). The general syntax is:

sheet.api.Calculate()

Where sheet is your xlwings Sheet object. This method does not take any parameters.

Important Consideration:
This method calculates only the formulas on the specified worksheet. If your formulas have dependencies on cells in other worksheets within the same workbook, those dependent formulas will also be recalculated to ensure consistency. However, it does not calculate formulas in other workbooks (external links).

Code Examples:

1. Basic Recalculation:
   This example opens a workbook, selects a specific sheet, and forces a calculation.

import xlwings as xw

# Open the workbook (or connect to an existing instance)
wb = xw.Book("financial_model.xlsx")
# Get the specific worksheet
data_sheet = wb.sheets["DataInput"]

# ... (some code that might change cell values programmatically) ...

# Force calculation of all formulas on the 'DataInput' sheet
data_sheet.api.Calculate()

2. Using in a Loop for Iterative Models:
   For models that require iterative solving or simulation, you can recalculate the sheet within a loop.

import xlwings as xw
import numpy as np

wb = xw.Book("simulation.xlsx")
model_sheet = wb.sheets["MonteCarlo"]

# Assume cell A1 holds a seed value for the simulation
for i in range(100):
    # Update an input cell with a new random value
    model_sheet.range("A1").value = np.random.rand()
    # Recalculate the sheet to update all dependent formulas (e.g., in outputs B1:B10)
    model_sheet.api.Calculate()
    # Read and store a result
    result = model_sheet.range("B10").value
    # ... process the result ...

3. Conditional Recalculation:
   Recalculate only if a certain condition is met, such as when manual calculation mode is active.

import xlwings as xw

app = xw.apps.active
wb = app.books.active
summary_sheet = wb.sheets["Summary"]

# Check if the application's calculation mode is set to manual
if app.api.Calculation == -4135: # -4135 corresponds to xlCalculationManual
    print("Manual calculation mode detected. Calculating 'Summary' sheet.")
    summary_sheet.api.Calculate()
else:
    print("Automatic calculation is on.")

How to use Worksheet.Activate in the xlwings API way

The Activate member of the Worksheet object in the Excel object model is used to make a specific worksheet the active sheet in a workbook. When a worksheet is activated, it becomes the currently selected sheet that the user sees and interacts with in the Excel application window. 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 sheet activation within a Python script.

Functionality:
The primary purpose of the Activate method is to bring a worksheet to the foreground, ensuring it is the focused sheet. This is particularly useful in automation scripts where subsequent operations, such as data entry, formatting, or chart creation, need to be performed on a specific sheet. Activating a sheet can also enhance user experience in interactive applications by directing attention to the relevant data.

Syntax:
In xlwings, you access the Activate method via the api property of a Sheet object (which corresponds to a Worksheet in Excel). The method does not take any parameters.

sheet.api.Activate()

Here, sheet is an xlwings Sheet object representing the worksheet you want to activate. The api property returns the native Excel Worksheet object, and Activate() is called on it. No arguments are required, making it straightforward to use.

Code Examples:

1. Basic Activation:
   This example demonstrates how to activate a worksheet named “DataSheet” in an open workbook.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active

# Access the worksheet named "DataSheet" and activate it
sheet = wb.sheets["DataSheet"]
sheet.api.Activate()

# Alternatively, using the xlwings Sheet object directly (though api is explicit)
# sheet.activate() # xlwings' own method, which internally may use api.Activate()

After running this code, “DataSheet” will become the active sheet in Excel.

2. Activating a Sheet in a New Workbook:
   This example creates a new workbook, adds a worksheet, and activates it.

import xlwings as xw

# Start a new Excel instance and add a workbook
app = xw.App(visible=True) # Set visible=True to see Excel
wb = app.books.add()

# Add a new worksheet named "Report"
new_sheet = wb.sheets.add(name="Report")
new_sheet.api.Activate()

# Now "Report" is the active sheet; you can perform operations on it
new_sheet.range("A1").value = "Activated Sheet Data"

3. Iterating and Activating Sheets Based on Condition:
   This example activates the first worksheet that contains a specific value in cell A1.

import xlwings as xw

wb = xw.books.active # Assume an open workbook
target_value = "Summary"

for sheet in wb.sheets:
    if sheet.range("A1").value == target_value:
        sheet.api.Activate()
        print(f"Activated sheet: {sheet.name}")
        break

How to use Worksheets.VPageBreaks in the xlwings API way

The VPageBreaks member of the Worksheets object in Excel’s object model represents the collection of vertical page breaks within a specific worksheet. In xlwings, this collection provides programmatic control over where pages are divided when printing, allowing for precise adjustment of print layouts. This is particularly useful for optimizing report formats, ensuring data is not awkwardly split across pages, and automating print setup tasks in financial, operational, or analytical reports.

Functionality
The VPageBreaks collection enables you to add, remove, and manage vertical page breaks. Each break is represented by a VPageBreak object, which has properties such as location (the column where the break occurs) and type (automatic or manual). Key actions include inserting breaks at specific columns, iterating through existing breaks to inspect or modify them, and clearing manual breaks to reset the layout.

Syntax in xlwings
In xlwings, you access VPageBreaks via a Sheet object. The basic syntax is:

sheet.api.VPageBreaks

This returns a VPageBreaks collection. To add a new break, use the Add method:

sheet.api.VPageBreaks.Add(Before)

• Before: A required parameter that specifies the column where the break will be inserted. It must be a Range object representing the leftmost column of the new page. For example, to add a break before column D (the fourth column), you would reference sheet.range('D1') or use sheet.api.Range('D1'). The break is placed to the left of this column.

To reference an existing break, index the collection (1-based indexing):

vbreak = sheet.api.VPageBreaks(1) # First vertical page break

Common properties of a VPageBreak object include:

• vbreak.Location: Returns a Range object indicating the column where the break is set.
• vbreak.Type: Returns an integer indicating the break type (e.g., xlPageBreakAutomatic or xlPageBreakManual). In xlwings, you can use constants from the win32com.client.constants module if needed, but often direct manipulation suffices.

Code Examples

1. Adding a Vertical Page Break
   To insert a manual vertical page break before column F on the active sheet:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('report.xlsx')
sheet = wb.sheets['Data']
# Add break before column F (sixth column)
sheet.api.VPageBreaks.Add(Before=sheet.range('F1').api)
wb.save()
app.quit()

2. Listing All Vertical Page Breaks
   To iterate through and print details of each vertical break:

import xlwings as xw
wb = xw.Book('financial_model.xlsx')
sheet = wb.sheets[0]
vbreaks = sheet.api.VPageBreaks
print(f"Number of vertical page breaks: {vbreaks.Count}")
for i in range(1, vbreaks.Count + 1):
    vbreak = vbreaks(i)
    location = vbreak.Location.Column
    # Check break type: 1 = xlPageBreakAutomatic, 2 = xlPageBreakManual
    break_type = "Automatic" if vbreak.Type == 1 else "Manual"
    print(f"Break {i}: Before column {location}, Type: {break_type}")

3. Removing All Manual Vertical Page Breaks
   To clear only manual breaks while leaving automatic ones (e.g., from page setup):

import xlwings as xw
wb = xw.Book('sales_data.xlsx')
sheet = wb.sheets['Summary']
vbreaks = sheet.api.VPageBreaks
# Iterate backwards to avoid index issues when deleting
for i in range(vbreaks.Count, 0, -1):
    vbreak = vbreaks(i)
    if vbreak.Type == 2: # Manual break
        vbreak.Delete()
wb.save()