Blog

How to use Application.ArbitraryXMLSupportAvailable in the xlwings API way

ArbitraryXMLSupportAvailable is a read-only property of the Application object in the Excel object model. This property returns a Boolean value that indicates whether Excel supports the use of arbitrary XML schemas. Specifically, it checks if the installed version of Excel has the capability to work with custom-defined XML maps and schemas beyond the built-in XML features. This is particularly relevant for developers who need to import, export, or manipulate data using non-standard XML formats directly within Excel. When this property returns True, it means the Excel instance can handle arbitrary XML mappings; if False, such functionality is not available, typically in older versions of Excel.

In xlwings, you access this property through the Application object, which is the top-level object representing the Excel application itself. The xlwings API provides a Pythonic way to interact with Excel’s COM interface, allowing you to check this property directly from your Python script.

Syntax in xlwings:

app.ArbitraryXMLSupportAvailable

• app: This is an instance of the xlwings App class, which corresponds to the Excel Application object. You typically obtain it by creating a new instance (app = xw.App()) or by connecting to an existing one (app = xw.apps.active).
• The property takes no parameters and returns a Boolean (True or False).

Code Example:
Below is a practical example demonstrating how to use the ArbitraryXMLSupportAvailable property in xlwings. This script checks if the current Excel application supports arbitrary XML schemas and prints a message accordingly. It also handles the Excel application properly by quitting after the operation.

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=False) # Set visible=True to see the Excel window

try:
    # Check if arbitrary XML support is available
    xml_support = app.api.ArbitraryXMLSupportAvailable

    if xml_support:
        print("This Excel instance supports arbitrary XML schemas.")
    else:
        print("Arbitrary XML schema support is NOT available in this Excel version.")

    # Optional: Display the Boolean value
    print(f"Value of ArbitraryXMLSupportAvailable: {xml_support}")

except AttributeError as e:
    print(f"Error accessing property: {e}. This may indicate an older Excel version or API issue.")

finally:
    # Close the Excel application
    app.quit()

Explanation:

• The script uses app.api.ArbitraryXMLSupportAvailable to access the property. In xlwings, the .api attribute provides direct access to the underlying Excel COM object model, ensuring compatibility with properties like this one.
• The try-except block catches AttributeError, which might occur if the property is not available in the Excel version being used (though this property is present in most modern versions).
• The finally block ensures that the Excel application is closed properly using app.quit(), which is good practice to avoid lingering processes.

How to use Application.Application in the xlwings API way

The Application object in Excel’s object model represents the entire Excel application, and in xlwings, it is accessed through the app property of a Book object or directly when creating an instance. The Application member provides a wide range of properties and methods to control the Excel environment, manage workbooks, and customize application-level settings. In xlwings, these are exposed via the api property, allowing Python scripts to interact with Excel’s COM interface seamlessly. This enables automation of tasks such as adjusting screen updating, calculating workbooks, or retrieving application information, enhancing efficiency in data analysis and visualization workflows.

Functionality:
The Application member allows control over global Excel behaviors. Key functionalities include managing calculation modes (automatic or manual), controlling screen updating to improve performance during macro execution, accessing version information, and handling events. It also provides access to other objects like workbooks and add-ins, enabling comprehensive automation.

Syntax:
In xlwings, the Application member is accessed through an App instance. The basic syntax is:
app.api.Application.PropertyOrMethod
Where app is an xlwings App object. For methods, parameters follow the method name, and their meanings align with Excel VBA documentation. Common parameters include:

• ScreenUpdating: A Boolean property to enable or disable screen refresh.
• Calculation: An enumeration to set calculation mode (e.g., xlCalculationAutomatic, xlCalculationManual).
• Version: A read-only property returning the Excel version string.

For example, to disable screen updating:
app.api.Application.ScreenUpdating = False
To set calculation to manual:
app.api.Application.Calculation = -4135 (where -4135 corresponds to xlCalculationManual).

Code Examples:

1. Controlling Screen Updating and Calculation:
   This example optimizes performance by turning off screen updates and setting calculation to manual before processing data, then restoring settings.

import xlwings as xw
app = xw.App(visible=False) # Start Excel in background
app.api.Application.ScreenUpdating = False
app.api.Application.Calculation = -4135 # Manual calculation
# Perform data operations here, e.g., open a workbook and manipulate data
wb = app.books.open('data.xlsx')
# After operations, restore settings
app.api.Application.Calculation = -4105 # Automatic calculation
app.api.Application.ScreenUpdating = True
wb.save()
app.quit()

2. Retrieving Application Information:
   This example fetches the Excel version and checks the calculation mode, useful for logging or conditional operations.

import xlwings as xw
app = xw.App(visible=False)
version = app.api.Application.Version
calculation_mode = app.api.Application.Calculation
print(f"Excel Version: {version}")
print(f"Calculation Mode: {calculation_mode}") # -4105 for automatic, -4135 for manual
app.quit()

3. Managing Workbooks via Application:
   The Application member can list all open workbooks, aiding in multi-workbook automation.

import xlwings as xw
app = xw.App(visible=True)
# Open multiple workbooks
wb1 = app.books.open('file1.xlsx')
wb2 = app.books.open('file2.xlsx')
# Access workbooks through Application
open_workbooks = app.api.Application.Workbooks
print(f"Number of open workbooks: {open_workbooks.Count}")
for wb in open_workbooks:
    print(wb.Name)
app.quit()

How to use Application.AlwaysUseClearType in the xlwings API way

Functionality
The Application.AlwaysUseClearType property in Excel’s object model is a read/write Boolean that controls whether ClearType font smoothing is used for all text within the Excel application window. ClearType is a Microsoft font rendering technology designed to improve text readability on LCD monitors. When enabled (True), text appears smoother and potentially more legible, especially at smaller font sizes or on certain displays. When disabled (False), Excel uses standard font rendering. This is an application-level setting, meaning it affects all open workbooks and persists across sessions unless changed. In xlwings, you can access and modify this property to programmatically manage the font rendering preference, which can be useful for ensuring consistent visual presentation in automated reports or when deploying Excel-based solutions across different user environments.

Syntax
In xlwings, you access this property via the App object, which represents the Excel application instance. The property is exposed as a simple attribute.

# Get the current value
current_setting = app.AlwaysUseClearType

# Set a new value
app.AlwaysUseClearType = new_value

• app: An xlwings App object instance. Typically obtained via xw.App() (for a new instance) or xw.apps collection (for an existing instance).
• current_setting: Returns a Python bool (True or False).
• new_value: A Python bool (True or False).

Remarks: This property corresponds directly to the Excel VBA Application.AlwaysUseClearType. It is only available on Windows, as ClearType is a Windows-specific technology. Attempting to access it on macOS will raise an AttributeError.

Code Examples

1. Checking the Current Setting:

import xlwings as xw

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

# Get the current ClearType setting
cleartype_enabled = app.AlwaysUseClearType
print(f"ClearType is currently enabled: {cleartype_enabled}")

2. Enabling ClearType Programmatically:

import xlwings as xw

# Start a new Excel instance (or use active)
app = xw.App()

# Ensure ClearType is turned on
app.AlwaysUseClearType = True
print("ClearType has been enabled for this Excel session.")

# ... perform other automation tasks ...

app.quit() # Close the instance

3. Conditional Toggle Based on Current State:

import xlwings as xw

app = xw.apps[0] # Access the first running Excel instance

if not app.AlwaysUseClearType:
    app.AlwaysUseClearType = True
    print("ClearType was off and has now been enabled.")
else:
    print("ClearType was already enabled.")

4. Integrating into a Larger Automation Script (with error handling for cross-platform compatibility):

import xlwings as xw
import sys

def configure_font_rendering(app_instance):
"""Attempt to set ClearType on Windows."""
if sys.platform.startswith('win'):
    try:
        app_instance.AlwaysUseClearType = True
        print("ClearType configured successfully.")
    except AttributeError as e:
        print(f"Could not set AlwaysUseClearType: {e}")
else:
    print("ClearType setting is only applicable on Windows. Skipping.")

# Usage
app = xw.App(visible=True)
configure_font_rendering(app)

# Create a workbook and add some text
wb = app.books.add()
ws = wb.sheets[0]
ws.range('A1').value = "Text displayed with ClearType smoothing (if Windows)."

wb.save('report_with_cleartype.xlsx')
app.quit()

How to use Application.AltStartupPath in the xlwings API way

The AltStartupPath property of the Application object in Excel is a read/write string property that sets or returns the full path to an alternate startup folder. This folder is used by Excel to store and locate files, such as templates, add-ins, or other workbooks, that should be automatically loaded when Excel starts. The primary startup folder is specified by the StartupPath property, but the AltStartupPath provides a secondary, user-defined location. This is particularly useful for managing different sets of startup files for various projects or user profiles without interfering with the default Excel startup configuration.

In xlwings, you can access this property through the xlwings.App object, which represents the Excel application. The property is exposed as a Python attribute, allowing you to get or set its value directly. The syntax for accessing the AltStartupPath property in xlwings is straightforward: you use the app instance (representing the Application object) and reference .api.AltStartupPath. This provides a bridge to the underlying Excel object model. Here is the basic syntax:

• To get the current alternate startup path:
  alt_path = app.api.AltStartupPath
  This returns a string containing the full path, or an empty string if no alternate startup path is set.
• To set a new alternate startup path:
  app.api.AltStartupPath = "C:\\Your\\Folder\\Path"
  You must provide a valid folder path as a string. Note that backslashes in Windows paths should be escaped (e.g., "C:\\Folder") or you can use raw strings (e.g., r"C:\Folder").

The property does not accept parameters beyond the path string itself. It is important to ensure that the specified folder exists and has appropriate permissions; otherwise, Excel may ignore it or throw an error. The AltStartupPath is persistent across Excel sessions if saved in a workbook or template, but setting it via xlwings only affects the current instance unless explicitly saved.

Here are some practical xlwings code examples demonstrating the use of AltStartupPath:

1. Retrieving the Current Alternate Startup Path:
   This example connects to a running Excel instance, retrieves the alternate startup path, and prints it.

import xlwings as xw
app = xw.apps.active # Get the active Excel application
alt_startup_path = app.api.AltStartupPath
print(f"Alternate Startup Path: {alt_startup_path}")

2. Setting a New Alternate Startup Path:
   This example sets a new alternate startup folder and then verifies the change by retrieving it.

import xlwings as xw
app = xw.App(visible=True) # Start a new Excel application
new_path = r"C:\MyExcelStartupFiles" # Use a raw string for Windows path
app.api.AltStartupPath = new_path
# Verify the setting
updated_path = app.api.AltStartupPath
print(f"Updated Alternate Startup Path: {updated_path}")
# Save the setting by saving a workbook or closing properly
app.quit()

3. Checking and Using the Alternate Startup Path for File Operations:
   This example checks if an alternate startup path is set, and if so, lists the files in that folder.

import xlwings as xw
import os
app = xw.apps.active
alt_path = app.api.AltStartupPath
if alt_path and os.path.exists(alt_path):
    files = os.listdir(alt_path)
    print(f"Files in Alternate Startup Path: {files}")
else:
    print("No valid alternate startup path set.")

How to use Application.AlertBeforeOverwriting in the xlwings API way

The AlertBeforeOverwriting property of the Application object in Excel is a useful setting that controls whether Excel displays a warning message before overwriting existing non-blank cells when performing operations like dragging or filling data. This feature helps prevent accidental data loss by prompting users to confirm the action. In xlwings, you can access and modify this property through the api property, which provides direct access to the underlying Excel object model.

Functionality:
The AlertBeforeOverwriting property is a boolean value. When set to True, Excel will show an alert dialog box if an operation would overwrite non-empty cells, giving the user the option to cancel or proceed. When set to False, no warning is issued, and data is overwritten silently. This is particularly relevant in automated scripts where you might want to suppress prompts to ensure uninterrupted execution.

Syntax:
In xlwings, the property is accessed via the Application object. The general syntax is:

app.AlertBeforeOverwriting

• Get the current value: current_setting = app.AlertBeforeOverwriting
• Set the value: app.AlertBeforeOverwriting = True or app.AlertBeforeOverwriting = False

Here, app refers to the xlwings App instance, which represents the Excel application. The property does not take any parameters; it is a simple read/write boolean property.

Example Usage:
Below are practical xlwings API code examples demonstrating how to use the AlertBeforeOverwriting property.

1. Checking the Current Setting:
   This example retrieves the current state of the alert setting and prints it.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
# Get the current AlertBeforeOverwriting value
alert_status = app.AlertBeforeOverwriting
print(f"AlertBeforeOverwriting is currently set to: {alert_status}")

2. Disabling Alerts to Overwrite Data:
   In automated tasks, you might want to turn off alerts to avoid interruptions. This example sets the property to False, performs a data fill operation that would overwrite cells, and then restores the original setting.

import xlwings as xw

app = xlwings.apps.active
# Save the original setting
original_setting = app.AlertBeforeOverwriting

# Disable overwrite alerts
app.AlertBeforeOverwriting = False

# Perform an operation that overwrites data (e.g., filling a range)
wb = app.books.active
sheet = wb.sheets['Sheet1']
# Overwrite cells A1:A5 with new values
sheet.range('A1:A5').value = [10, 20, 30, 40, 50]

# Restore the original alert setting
app.AlertBeforeOverwriting = original_setting
print("Operation completed with alerts temporarily disabled.")

3. Enabling Alerts for Safe Operations:
   To ensure user confirmation during manual-like operations in a script, you can enable the alert.

import xlwings as xw

app = xlwings.apps.active
# Ensure alerts are enabled
app.AlertBeforeOverwriting = True

# Now, if a range with data is overwritten, Excel will show a prompt
wb = app.books.active
sheet = wb.sheets['Sheet1']
# Attempt to overwrite non-empty cells (this will trigger an alert if cells contain data)
sheet.range('B1:B3').value = ['New', 'Data', 'Here']
# Note: In an interactive session, the alert dialog would appear, pausing the script until user response.