Archive

How to use Range.CalculateRowMajorOrder in the xlwings API way

The CalculateRowMajorOrder member of the Range object in Excel is a property that controls the calculation order for formulas within the specified range when using the row-major calculation method. In Excel, calculation can proceed in row-major order (left to right, then top to bottom) or column-major order (top to bottom, then left to right). This property is particularly useful in scenarios where formula dependencies are arranged in a way that row-wise calculation improves performance or ensures correct results, such as in financial models or data tables with horizontal dependencies. It allows developers to programmatically set or check the calculation direction for a specific range, overriding the default application-wide setting.

In the xlwings API, which provides a Pythonic interface to Excel’s object model, the CalculateRowMajorOrder property is accessed through a Range object. The syntax for using it is straightforward, as it is a read/write property that accepts and returns a Boolean value. When set to True, Excel calculates the formulas in the range using row-major order. When set to False, it uses column-major order, which is Excel’s default behavior if not specified otherwise. This property can be applied to any Range object, from a single cell to a larger block of cells, enabling fine-tuned control over calculation logic.

The xlwings API call format for CalculateRowMajorOrder is as follows:

  • To get the current value: range_object.calculate_row_major_order
  • To set the value: range_object.calculate_row_major_order = True or range_object.calculate_row_major_order = False

Here, range_object is an instance of xlwings’ Range class, typically obtained through methods like ws.range() where ws is a Sheet object. The property does not take additional parameters; it simply reflects or alters the calculation order setting. In terms of usage, it is often combined with the calculate() method to trigger a recalculation after setting the order. Note that this property might not be available in all Excel versions or contexts, so it’s advisable to check compatibility, especially with older Excel editions.

For example, consider a scenario where you have a worksheet with formulas in cells A1:C3 that depend horizontally (e.g., cell B1 references A1, and C1 references B1). To ensure these formulas are calculated in the correct left-to-right sequence, you can set CalculateRowMajorOrder to True for that range. Below is an xlwings API code instance demonstrating this:

import xlwings as xw

# Connect to an existing Excel workbook and sheet
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Define the range A1:C3
target_range = ws.range('A1:C3')

# Set calculation order to row-major for this range
target_range.calculate_row_major_order = True

# Trigger calculation to apply the new order
target_range.calculate()

# Check the current setting
current_order = target_range.calculate_row_major_order
print(f"CalculateRowMajorOrder is set to: {current_order}")

# Optionally, revert to column-major order
target_range.calculate_row_major_order = False
target_range.calculate()

# Close the workbook (optional, based on workflow)
wb.save()
wb.close()

How to use Range.Calculate in the xlwings API way

The Calculate member of the Range object in Excel’s object model is a method used to force a manual recalculation of a specific range of cells. This is particularly useful when you have formulas within a defined range and you want to ensure they are updated immediately, bypassing Excel’s automatic calculation settings. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model.

Functionality
The primary purpose of Calculate is to recalculate all formulas within the specified Range object. Unlike recalculating the entire workbook or sheet, this method allows for targeted calculation, which can improve performance in large workbooks. It is effective regardless of the application’s calculation mode (automatic or manual). This is essential in automation scripts where data consistency and timely updates are critical before proceeding with further operations.

Syntax
In xlwings, you access the Calculate method via the api property of a Range object. The basic syntax is:

range_object.api.Calculate()

Here, range_object is an xlwings Range object, such as one obtained via sheet.range('A1:B10'). The Calculate method does not take any parameters. It operates directly on the range and does not return a value.

Code Examples
Below are practical examples demonstrating the use of Calculate in xlwings.

Example 1: Recalculating a Single Cell
Suppose cell A1 contains a formula like =B1*2. If B1 changes, you can force A1 to update:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the range for cell A1
cell_a1 = sheet.range('A1')

# Recalculate only cell A1
cell_a1.api.Calculate()

# Verify the updated value
print(cell_a1.value)

Example 2: Recalculating a Larger Range
For a range with multiple formulas, such as A1:A10 containing references to other cells:

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the range A1:A10
range_a1_a10 = sheet.range('A1:A10')

# Recalculate all formulas within this range
range_a1_a10.api.Calculate()

# Check values in the range
print(range_a1_a10.value)

Example 3: Integrating with Data Updates
In a scenario where you update source data and need dependent formulas to reflect changes immediately:

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Data']

# Update source data in column B
sheet.range('B1:B5').value = [[10], [20], [30], [40], [50]]

# Define the range with formulas in column C (e.g., C1:C5 with =B1*2)
formula_range = sheet.range('C1:C5')

# Recalculate the formulas in column C
formula_range.api.Calculate()

# Output the results
print(formula_range.value)

How to use Range.BorderAround in the xlwings API way

The BorderAround method in the Excel object model is used to apply a border around the entire range of cells. This is particularly useful for visually distinguishing a block of data, such as a summary table or a key dataset, within a worksheet. In xlwings, this method is accessed through the api property of a Range object, which provides direct access to the underlying Excel object model. The method allows you to specify the line style, weight, and color of the border.

Syntax:
The xlwings API call follows the pattern: range.api.BorderAround(LineStyle, Weight, ColorIndex, Color)

  • LineStyle: An integer or constant from the XlLineStyle enumeration that defines the style of the border line. Common values include:
  • 1 or xlContinuous: A continuous solid line.
  • -4115 or xlDash: A dashed line.
  • -4118 or xlDot: A dotted line.
  • 4 or xlDouble: A double line.
  • Weight: An integer or constant from the XlBorderWeight enumeration that specifies the thickness of the border. Typical values are:
  • 2 or xlThin: A thin border.
  • -4138 or xlMedium: A medium border.
  • 4 or xlThick: A thick border.
  • ColorIndex: An optional integer representing a color from the current color palette. Use this or Color, but not both. If omitted, the default color (usually black) is used.
  • Color: An optional argument to specify the border color as an RGB value. This can be provided as an integer (e.g., 0xFF0000 for red) or using the RGB function from the Excel object model via xlwings. If both ColorIndex and Color are omitted, the default color applies.

Example:
Here is an xlwings code example that demonstrates the use of BorderAround. First, ensure you have xlwings installed and an Excel application running.

import xlwings as xw

# Connect to the active Excel instance and get the active sheet
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets.active

# Define a range, e.g., A1:D10
target_range = sheet.range('A1:D10')

# Apply a thick, continuous red border around the range
# Using Color as an RGB integer for red (0xFF0000)
target_range.api.BorderAround(LineStyle=1, Weight=4, Color=0xFF0000)

# Alternatively, use constants from the Excel enumeration for clarity
# First, import the constants (optional, as you can use numeric values)
from xlwings.constants import LineStyle, BorderWeight
target_range.api.BorderAround(LineStyle=LineStyle.xlContinuous,
Weight=BorderWeight.xlThick,
Color=0x0000FF) # Blue border

# Another example with a dashed medium border using default color
target_range.api.BorderAround(LineStyle=-4115, Weight=-4138)

# Save the workbook if needed
wb.save('output_with_borders.xlsx')

How to use Range.AutoOutline in the xlwings API way

The AutoOutline member of the Range object in Excel is a method used to automatically create an outline for a selected range of data. Outlining is particularly useful for summarizing large datasets by grouping rows or columns, allowing users to easily expand or collapse sections to view detailed data or summaries. This feature is often applied to financial statements, reports, or any structured data where hierarchical grouping improves readability. In xlwings, AutoOutline provides a programmatic way to apply this functionality, enabling automation of outline creation in Excel workbooks through Python scripts.

Functionality:
The AutoOutline method analyzes the selected range and automatically applies outline groups based on the structure of the data. It typically groups rows or columns that contain summary formulas (like SUM or AVERAGE) or subtotals, creating a collapsible outline. This helps in organizing data into a more manageable view, where users can hide or show details with a click. In xlwings, this method allows developers to integrate outlining into automated data processing workflows, such as generating reports with expandable sections.

Syntax:
In xlwings, the AutoOutline method is accessed through the Range object. The syntax is straightforward, as it does not require any parameters. It is called directly on a Range instance to apply outlining to that specific range.

range_object.api.AutoOutline()

Here, range_object is an xlwings Range object (e.g., sheet.range('A1:D10')). The .api attribute provides access to the underlying Excel object model, allowing direct calls to Excel methods like AutoOutline. Since AutoOutline is a method with no parameters in Excel VBA, it is invoked without arguments in xlwings as well.

Example:
Consider an Excel worksheet with sales data where rows contain monthly details and subtotal rows. Using xlwings, you can automatically outline this data to group monthly entries under their respective quarters. Below is a code example that demonstrates how to use AutoOutline with xlwings.

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book('sales_report.xlsx') # Ensure the file exists or use xw.Book() for a new workbook
sheet = wb.sheets['Sheet1']

# Select the range containing the data to outline, e.g., from A1 to D20
data_range = sheet.range('A1:D20')

# Apply automatic outlining to the range
data_range.api.AutoOutline()

# Save the workbook to persist changes
wb.save('sales_report_outlined.xlsx')
wb.close()

How to use Range.AutoFit in the xlwings API way

The AutoFit member of the Range object in Excel is a method used to automatically adjust the width of columns or the height of rows to fit their contents. This is particularly useful for improving readability and presentation in worksheets, as it ensures that all data is fully visible without manual resizing. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control over Excel’s native features.

Functionality:
The AutoFit method resizes the columns or rows within a specified range to the minimum size required to display all content without clipping. For columns, it adjusts the width based on the longest entry in each column; for rows, it adjusts the height based on the tallest text or object in each row. This method enhances data visualization by eliminating extra white space or truncated text, making reports and dashboards more professional.

Syntax:
In xlwings, the syntax for using AutoFit is:

range.api.AutoFit()

Here, range refers to an xlwings Range object. The method does not take any parameters. It is called directly on the range, and it automatically applies to all columns or rows within that range. Note that AutoFit is a method of the Excel Range object, accessed via the .api attribute in xlwings, which bridges Python and Excel’s COM interface.

Examples:

  1. AutoFit Columns for a Specific Range:
    To adjust the width of columns A to C in the active sheet:
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
ws.range("A:C").api.AutoFit() # AutoFits columns A, B, and C

This code selects the range from column A to C and applies AutoFit to resize each column based on its content.

  1. AutoFit Rows for an Entire Worksheet:
    To adjust the height of all rows in a worksheet:
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
ws.used_range.api.AutoFit() # AutoFits both columns and rows in the used range

Using used_range ensures that only cells with data are affected, optimizing performance by avoiding empty areas.

  1. AutoFit After Data Entry:
    After populating a range with data, AutoFit can be applied dynamically:
import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
# Add some data
ws.range("A1").value = ["Longer Text Example", "Short", "Another Long Entry"]
# AutoFit the columns to fit the new content
ws.range("A1:C1").api.AutoFit()

How to use Range.AutoFilter in the xlwings API way

The AutoFilter member of the Range object in Excel, accessible via the xlwings API, is a powerful feature for programmatically applying and managing autofilters on worksheet data. It allows for dynamic data filtering based on specified criteria, enabling users to focus on relevant subsets of information within a larger dataset. This functionality is essential for data analysis tasks where sifting through rows to find matching records is required.

In xlwings, the AutoFilter is applied through the api property, which provides direct access to the underlying Excel object model. The primary method used is AutoFilter. The syntax for applying a basic autofilter to a range is straightforward. For more complex filtering involving specific criteria, the AutoFilter method can accept parameters.

The core syntax for applying an autofilter in xlwings is:
range.api.AutoFilter
This will apply filters to the headers of the specified range. To set specific filter criteria, you can use the extended syntax:
range.api.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

  • Field: The integer offset of the field (column) to filter on, where the first column in the range is 1.
  • Criteria1: The primary filter criterion as a string (e.g., “>100”, “Product A”).
  • Operator: An optional constant from the XlAutoFilterOperator enumeration (e.g., xlAnd, xlOr) to define the relationship between Criteria1 and Criteria2. Common xlwings constants are win32c.XlAutoFilterOperator.xlAnd or win32c.XlAutoFilterOperator.xlOr.
  • Criteria2: The secondary filter criterion, used when Operator is xlAnd or xlOr.
  • VisibleDropDown: An optional boolean to show or hide the filter dropdown arrow (default is True).

For example, to filter the first column for values equal to “Yes”:
range.api.AutoFilter(Field:=1, Criteria1:="Yes")
To filter a numeric column for values greater than 50:
range.api.AutoFilter(Field:=2, Criteria1:=">50")
For a filter with two conditions (e.g., values between 10 and 100), you would use:
range.api.AutoFilter(Field:=3, Criteria1:=">=10", Operator:=win32c.XlAutoFilterOperator.xlAnd, Criteria2:="<=100")

Below are practical xlwings code examples. First, ensure you have a reference to the desired worksheet and range.

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets['Sheet1']
# Define the range to filter, including headers
data_range = ws.range('A1').current_region

# Example 1: Apply a basic autofilter to the entire data range
data_range.api.AutoFilter()
print("Basic autofilter applied.")

# Example 2: Filter the first column for the value "Completed"
data_range.api.AutoFilter(Field=1, Criteria1="Completed")
print("Filtered column 1 for 'Completed'.")

# Example 3: Filter the second column for values greater than 100
data_range.api.AutoFilter(Field=2, Criteria1=">100")
print("Filtered column 2 for values > 100.")

# Example 4: Clear all filters from the worksheet
ws.api.AutoFilterMode = False
print("All filters cleared.")

# Example 5: Complex filter on the third column for values between 50 and 150
import win32com.client.constants as win32c
data_range.api.AutoFilter(Field=3, Criteria1=">=50", Operator=win32c.XlAutoFilterOperator.xlAnd, Criteria2="<=150")
print("Filtered column 3 for values between 50 and 150.")

How to use Range.AutoFill in the xlwings API way

The AutoFill member of the Range object in Excel’s object model is a powerful method for automating the filling of data series into a range of cells. In xlwings, this functionality is exposed through the api property, allowing direct access to the underlying VBA-style object model. It is particularly useful for replicating Excel’s built-in autofill behavior, such as extending a series of dates, numbers, or custom lists, without manual input.

Functionality
The AutoFill method automatically fills cells in a target range based on the content of the source range. It can generate linear series, growth series, dates, and copy formats or values. Common uses include populating sequential dates, numbers, or applying a pattern across multiple cells.

Syntax in xlwings
In xlwings, you call AutoFill via the api property of a Range object. The syntax is:

range_object.api.AutoFill(Destination, Type)
  • range_object: The xlwings Range object representing the source range containing the initial data.
  • Destination: A VBA Range object specifying the target range to fill. This includes the source range and the cells to be filled.
  • Type: An optional constant from the XlAutoFillType enumeration that defines the fill type. If omitted, Excel defaults to xlFillDefault.

The Type parameter can take values such as:

  • xlFillDefault (0): Excel determines the fill type based on the source content.
  • xlFillCopy (1): Copies the values and formats from the source range.
  • xlFillSeries (2): Extends a numeric or date series.
  • xlFillFormats (3): Copies only the formats.
  • xlFillValues (4): Copies only the values.
  • xlFillDays (5): Fills days, ignoring weekends if specified.
  • xlFillWeekdays (6): Fills weekdays only.
  • xlFillMonths (7): Extends months.
  • xlFillYears (8): Extends years.
  • xlLinearTrend (9): Fills a linear trend series.
  • xlGrowthTrend (10): Fills a growth trend series.

Code Examples
Here are practical examples using xlwings to demonstrate AutoFill:

  1. Filling a Date Series: Suppose you have a start date in cell A1 and want to fill dates down to A10.
import xlwings as xw
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']
source_range = sheet.range('A1')
target_range = sheet.range('A1:A10')
source_range.api.AutoFill(target_range.api, 2) # xlFillSeries
  1. Copying Values and Formats: To copy the content and formatting from cell B1 to B5.
source_range = sheet.range('B1')
target_range = sheet.range('B1:B5')
source_range.api.AutoFill(target_range.api, 1) # xlFillCopy
  1. Filling Weekdays Only: Starting from a Monday in cell C1, fill weekdays down to C10.
source_range = sheet.range('C1')
target_range = sheet.range('C1:C10')
source_range.api.AutoFill(target_range.api, 6) # xlFillWeekdays
  1. Creating a Linear Trend: If you have values 1 and 3 in D1 and D2, fill a linear trend to D10.
source_range = sheet.range('D1:D2')
target_range = sheet.range('D1:D10')
source_range.api.AutoFill(target_range.api, 9) # xlLinearTrend

How to use Range.AutoComplete in the xlwings API way

The AutoComplete member of the Range object in Excel is a property that enables or disables the automatic completion feature for a specific cell. This feature, when active, suggests possible entries based on previously entered data in the same column, helping to ensure consistency and speed up data entry. It is particularly useful in scenarios where users are inputting repetitive data, such as product names, categories, or employee IDs, as it reduces typing errors and minimizes the need for manual lookups. In xlwings, the AutoComplete property is accessed through the api property of a Range object, providing direct interaction with the underlying Excel object model.

The syntax for using AutoComplete in xlwings is straightforward. It is a read-write property that accepts a Boolean value. The xlwings API call format is range.api.AutoComplete = value, where range is an xlwings Range object representing a single cell (since AutoComplete applies per cell), and value is either True to enable automatic completion or False to disable it. For example, to check the current setting, you can retrieve it with current_setting = range.api.AutoComplete. It is important to note that this property is specific to Excel’s application-level settings for a cell and does not require additional parameters.

Below are practical xlwings API code examples demonstrating the use of the AutoComplete property. First, ensure xlwings is installed and an Excel workbook is open or created via xlwings.

import xlwings as xw

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

# Example 1: Enable AutoComplete for a specific cell (e.g., A1)
range_a1 = sheet.range('A1')
range_a1.api.AutoComplete = True
print(f"AutoComplete enabled for A1: {range_a1.api.AutoComplete}")

# Example 2: Disable AutoComplete for another cell (e.g., B1)
range_b1 = sheet.range('B1')
range_b1.api.AutoComplete = False
print(f"AutoComplete enabled for B1: {range_b1.api.AutoComplete}")

# Example 3: Check the AutoComplete status for multiple cells
for cell in ['A1', 'B1', 'C1']:
    cell_range = sheet.range(cell)
    status = "Enabled" if cell_range.api.AutoComplete else "Disabled"
    print(f"AutoComplete for {cell}: {status}")

# Example 4: Toggle AutoComplete based on a condition (e.g., enable only if cell is empty)
target_range = sheet.range('D1')
if target_range.value is None:
    target_range.api.AutoComplete = True
    print("AutoComplete toggled to True for empty cell D1")
else:
    target_range.api.AutoComplete = False
    print("AutoComplete toggled to False for non-empty cell D1")

# Save and close the workbook (optional)
wb.save('example_autocomplete.xlsx')
wb.close()

How to use Range.ApplyOutlineStyles in the xlwings API way

The ApplyOutlineStyles member of the Range object in Excel is used to apply automatic outlining styles to a specified range. Outlining in Excel allows users to group rows or columns, making it easier to collapse or expand sections of data for better readability and organization, particularly in large datasets. This feature is commonly used in financial models, reports, or any structured data where hierarchical grouping is beneficial. In xlwings, this functionality is exposed through the API, enabling automation of outlining styles directly from Python scripts, which can streamline workflows in data analysis and reporting.

Syntax and Parameters
In xlwings, the ApplyOutlineStyles method is called on a Range object. The syntax is straightforward, as it does not require any parameters. It applies the default outlining styles based on the structure of the range, such as formulas or summary rows. The method corresponds to the Excel VBA Range.ApplyOutlineStyles method, and in xlwings, it is accessed as a method of a range instance.

The xlwings API call format is:

range_object.api.ApplyOutlineStyles()

Here, range_object refers to an xlwings Range object, typically obtained through sheet.range() or similar methods. The .api attribute provides access to the underlying Excel object model, allowing direct use of Excel’s native methods like ApplyOutlineStyles. Since no parameters are needed, the method is invoked simply by calling it without arguments. This applies outlining styles automatically, grouping rows or columns based on Excel’s detection of summary formulas (e.g., subtotals) or manual groupings in the range.

Example Usage
To illustrate, consider an Excel workbook where you have a dataset with subtotal rows that you want to group automatically. Using xlwings, you can open the workbook, select the relevant range, and apply outlining styles. Below is a code example that demonstrates this process:

import xlwings as xw

# Open an existing workbook or create a new one
wb = xw.Book('example.xlsx') # Replace with your file path
sheet = wb.sheets['Sheet1']

# Define the range where outlining should be applied, e.g., A1:D20
target_range = sheet.range('A1:D20')

# Apply automatic outlining styles to the range
target_range.api.ApplyOutlineStyles()

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

How to use Range.ApplyNames in the xlwings API way

The ApplyNames method in Excel’s object model is a powerful feature for applying a list of defined names to formulas within a specified range, replacing cell references with their corresponding named ranges where applicable. This enhances formula readability and maintainability. In xlwings, this functionality is accessed through the api property of a Range object, providing direct access to the underlying Excel VBA object model.

Functionality:
The primary purpose of ApplyNames is to scan formulas in a given range and substitute direct cell references (like A1 or $B$2) with defined names that refer to those cells. For example, if you have a named range “SalesData” referring to B2:B10, a formula like =SUM(B2:B10) within the specified range can be automatically converted to =SUM(SalesData). This method only applies names that are defined in the workbook.

Syntax in xlwings:
The xlwings API call mirrors the VBA syntax closely. The method is called on a Range object via its api property.

range_object.api.ApplyNames(Names, IgnoreRelativeAbsolute, UseRowColumnNames, OmitColumn, OmitRow, Order, AppendLast)

Parameters:

  • Names: An optional variant. An array of defined names to apply. If omitted, all names in the workbook are applied.
  • IgnoreRelativeAbsolute: An optional variant. If True, the method ignores the relative or absolute status of names and references. Default is True.
  • UseRowColumnNames: An optional variant. If True, the method uses the row and column names of the range if no defined name exists. Default is True.
  • OmitColumn: An optional variant. If True, replaces the entire reference with the column name if the row portion of the reference matches the named range. Default is False.
  • OmitRow: An optional variant. If True, replaces the entire reference with the row name if the column portion of the reference matches the named range. Default is False.
  • Order: An optional variant. Determines the order for evaluating row and column names. Use xlRowThenColumn (1) or xlColumnThenRow (2).
  • AppendLast: An optional variant. If True, the row and column names are appended to the existing defined name. Default is False.

Code Example:
The following xlwings script demonstrates using ApplyNames. It assumes an active workbook with defined names and formulas in a specific range.

import xlwings as xw

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

# Define a target range, e.g., from A1 to C10 on the first sheet
target_range = wb.sheets[0].range("A1:C10")

# Apply all defined names in the workbook to formulas in the target range.
# We use default parameters for IgnoreRelativeAbsolute and UseRowColumnNames.
target_range.api.ApplyNames()

# To apply only specific names, provide an array.
# For instance, applying only the names "Revenue" and "Costs":
target_range.api.ApplyNames(Names=["Revenue", "Costs"])

# Example with more parameters: Apply names, ignoring relative/absolute status,
# not using row/column names, and evaluating column names before row names.
target_range.api.ApplyNames(IgnoreRelativeAbsolute=True,
UseRowColumnNames=False,
Order=2) # 2 corresponds to xlColumnThenRow