Archive

How to use Worksheet.TransitionExpEval in the xlwings API way

The TransitionExpEval property of a Worksheet object in Excel is a legacy feature primarily designed for compatibility with older spreadsheet applications, such as Lotus 1-2-3. When this property is set to True, it instructs Excel to evaluate formulas using the expression evaluation rules of Lotus 1-2-3 during the transition period when a file is being converted or used. This can affect how certain operators (like the string concatenation operator & or the exponentiation operator ^) and functions are interpreted. In modern Excel usage, this property is rarely needed but can be accessed and controlled via xlwings for automation scripts that interact with legacy systems or files.

Functionality:
The property serves as a switch. When enabled (True), Excel temporarily uses Lotus 1-2-3 formula evaluation rules for the specific worksheet. When disabled (False), which is the default, Excel uses its standard evaluation rules. This is typically only relevant when opening or working with files originally created in Lotus 1-2-3 to ensure formula results remain consistent during the transition.

Syntax in xlwings:
In xlwings, you access this property through the api property of a Sheet object, which exposes the underlying Excel VBA object model. The property is a Boolean (True/False).

# To get the current value
current_setting = sheet.api.TransitionExpEval

# To set a new value
sheet.api.TransitionExpEval = True # or False
  • sheet: This is your xlwings Sheet object (e.g., wb.sheets['Sheet1']).
  • .api: This provides direct access to the Excel VBA object, here a Worksheet object.
  • .TransitionExpEval: The property being accessed or modified. It expects a Boolean value.

Code Examples:

  1. Checking the Current Setting:
    This script prints the current TransitionExpEval status for all worksheets in an open workbook.
import xlwings as xw

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

for sheet in wb.sheets:
    status = sheet.api.TransitionExpEval
    print(f"Sheet '{sheet.name}' has TransitionExpEval = {status}")
  1. Enabling Transition Evaluation for a Specific Sheet:
    This script opens a workbook and enables Lotus 1-2-3 formula evaluation for a sheet named “LegacyData”.
import xlwings as xw

# Open a specific workbook
wb = xw.Book(r'C:\Path\To\Legacy_File.xlsx')

# Target a specific sheet
target_sheet = wb.sheets['LegacyData']

# Enable Transition Formula Evaluation
target_sheet.api.TransitionExpEval = True
print(f"TransitionExpEval enabled for '{target_sheet.name}'.")

# Save the workbook if needed
wb.save()
# wb.close()
  1. Conditional Workflow Based on Setting:
    This example demonstrates a check: if transition evaluation is already on, it performs a recalculation; otherwise, it turns it on and then calculates.
import xlwings as xw

wb = xw.Book.active
sheet = wb.sheets[0] # First sheet

if sheet.api.TransitionExpEval:
    print("Transition mode active. Forcing a full calculation.")
    sheet.api.Calculate() # Calculates the worksheet
else:
    print("Enabling transition mode and calculating.")
    sheet.api.TransitionExpEval = True
    sheet.api.Calculate()

# Note: The .Calculate() method is used here via .api to force a manual recalculation under the active evaluation rules.

How to use Worksheet.Tab in the xlwings API way

In xlwings, the Tab property of a Worksheet object provides access to various formatting and display options related to the worksheet’s tab (the visible sheet name tab at the bottom of the Excel window). This property is part of the Api object, which allows you to interact with the native Excel object model. The Tab property itself returns an object that has several useful members for controlling the tab’s appearance, such as its color and visibility.

Functionality:
The primary function of the Tab property is to manage the visual aspects of the worksheet tab. You can use it to:

  • Change the tab color to highlight important sheets.
  • Make a tab invisible or visible, which can be useful for hiding intermediate calculation sheets.
  • Check or set the current color state.

Syntax and Members:
The Tab property is accessed through the worksheet’s api object. The common pattern is:

ws.api.Tab

This returns an object that supports properties like:

  • Color: Gets or sets the tab color. You can assign an RGB color using the RGB function from the xlwings.utils module or use Excel color constants.
  • ColorIndex: An alternative way to set colors based on Excel’s color palette indices.
  • Visible: Controls whether the tab is visible. Can be set to True, False, or xlSheetHidden/xlSheetVeryHidden constants for more control.

Code Examples:

  1. Changing the Tab Color:
    This example sets the tab of the active sheet to a blue color.
import xlwings as xw
from xlwings.utils import rgb_to_int

app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets.active

# Set tab color using RGB values (Red, Green, Blue)
blue_color = rgb_to_int((0, 112, 192)) # A shade of blue
ws.api.Tab.Color = blue_color

# Alternatively, use a simple color index (e.g., 3 for Red)
# ws.api.Tab.ColorIndex = 3
  1. Hiding and Unhiding a Worksheet Tab:
    This example demonstrates how to hide a specific sheet’s tab and then make it visible again.
import xlwings as xw

app = xw.App(visible=True)
wb = app.books.active

# Target a specific sheet by name
data_sheet = wb.sheets["Data"]

# Hide the tab (the sheet contents remain, but the tab is not shown)
data_sheet.api.Tab.Visible = False
# Or use the xlwings constant for clarity
# data_sheet.visible = 'hidden'

# ... some operations ...

# Make the tab visible again
data_sheet.api.Tab.Visible = True
# Using xlwings property is often simpler:
# data_sheet.visible = True
  1. Checking the Current Tab Color:
    You can also read the current color property.
import xlwings as xw

app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets[0] # First sheet

current_color = ws.api.Tab.Color
print(f"The tab color's numeric value is: {current_color}")
# Note: This returns a Long integer representing the RGB color.

How to use Worksheet.StandardWidth in the xlwings API way

The StandardWidth property in the Worksheet object of Excel’s object model represents the standard (default) width of all columns in the worksheet, measured in characters using the standard font. This value is a single-precision floating-point number that defines the default column width before any individual column adjustments are made. In xlwings, you can access this property to either retrieve or set the standard column width for a worksheet, which is useful when you need to standardize column sizes or reset them to a default state after custom changes.

Syntax in xlwings:
The StandardWidth property is accessed as an attribute of a Worksheet object in xlwings. It is both readable and writable, allowing you to get or set the value directly. The syntax is straightforward:

  • To get the current standard width: worksheet.api.StandardWidth
  • To set a new standard width: worksheet.api.StandardWidth = value
    Here, worksheet refers to an xlwings Worksheet object (e.g., obtained via wb.sheets['Sheet1'] or wb.sheets[0]), and value is a float representing the width in characters. Note that in xlwings, this property is accessed through the .api attribute, which provides direct access to the underlying Excel object model (from the pywin32 or appscript libraries), ensuring compatibility with Excel’s native behavior.

Parameters and Values:

  • The value parameter when setting StandardWidth must be a positive float. In Excel, typical values range from 0 to 255 characters, but practical widths depend on the default font size (e.g., in Calibri 11-point, a width of 8.43 characters is common). If you set a width outside valid bounds, Excel may adjust it automatically or throw an error.
  • When getting the property, it returns a float that reflects the current standard width. If no custom standard width has been set, it often defaults to 8.43 in newer Excel versions, but this can vary based on the workbook’s template or regional settings.

Example Usage in xlwings:
Below are code examples demonstrating how to use the StandardWidth property with xlwings in Python. These assume you have an existing Excel workbook open or created via xlwings.

  1. Getting the Standard Width:
    This example retrieves the standard column width from the active worksheet and prints it.
import xlwings as xw
# Connect to an open Excel instance or create a new one
app = xw.App(visible=False) # Set visible=True to see Excel
wb = app.books.add() # Create a new workbook
ws = wb.sheets[0] # Access the first worksheet
# Get the current standard width
current_width = ws.api.StandardWidth
print(f"Current standard column width: {current_width} characters")
# Output might be: Current standard column width: 8.43 characters
wb.close()
app.quit()
  1. Setting the Standard Width:
    This example sets a new standard width for all columns in a worksheet and then verifies the change. It can be useful for resetting widths or applying a uniform default.
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx') # Open an existing workbook
ws = wb.sheets['DataSheet'] # Access a specific worksheet by name
# Set a new standard width (e.g., 10 characters)
new_width = 10.0
ws.api.StandardWidth = new_width
# Verify the change by retrieving the value
updated_width = ws.api.StandardWidth
print(f"Updated standard column width: {updated_width} characters")
# Save the workbook with the new standard width
wb.save()
wb.close()
app.quit()
  1. Resetting to Default and Comparing Widths:
    This example shows how to use StandardWidth in conjunction with individual column widths. It sets a custom standard, adjusts a specific column, and then resets all columns to the standard.
import xlwings as xw
app = xw.App(visible=True) # Make Excel visible for observation
wb = app.books.add()
ws = wb.sheets[0]
# Set a broader standard width
ws.api.StandardWidth = 12.5
# Adjust a specific column (e.g., column A) to a different width
ws.range('A:A').column_width = 15
print(f"Column A width: {ws.range('A:A').column_width}")
print(f"Standard width: {ws.api.StandardWidth}")
# Reset all columns to the standard width by iterating (if needed)
# Note: Setting StandardWidth alone doesn't change already-adjusted columns;
# you may need to loop through columns or use ClearFormats.
# For a quick reset, you can use:
ws.api.Cells.EntireColumn.ColumnWidth = ws.api.StandardWidth
print("All columns reset to standard width.")
wb.close()
app.quit()

How to use Worksheet.StandardHeight in the xlwings API way

The StandardHeight property in the Excel object model, accessible through the Worksheet object, refers to the standard (default) row height for the worksheet, measured in points. This value is typically set based on the default font size of the workbook’s normal style. In xlwings, you can retrieve this property to understand or utilize the baseline row height for formatting or layout calculations. It is a read-only property, meaning you cannot directly set it to change the default row height; instead, you would adjust row heights individually or modify the normal style’s font.

Syntax in xlwings:

worksheet.api.StandardHeight
  • worksheet: An xlwings Sheet object representing the Excel worksheet.
  • .api: This attribute provides access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS).
  • StandardHeight: The property that returns a float value representing the standard height in points.

Example Usage:
Suppose you want to compare the current height of a specific row (e.g., row 5) with the standard row height to determine if it has been customized. You can use the StandardHeight property in conjunction with the RowHeight property.

import xlwings as xw

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

# Get the standard row height
standard_height = ws.api.StandardHeight
print(f"Standard row height: {standard_height} points")

# Get the height of row 5
row5_height = ws.range("5:5").row_height
print(f"Row 5 height: {row5_height} points")

# Compare and provide feedback
if row5_height == standard_height:
    print("Row 5 uses the standard height.")
else:
    print(f"Row 5 is customized. Difference: {row5_height - standard_height} points.")

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

How to use Worksheet.Sort in the xlwings API way

The Sort member of the Worksheet object in the xlwings library provides a powerful way to programmatically sort data ranges within an Excel worksheet. It directly leverages Excel’s native sorting capabilities, allowing for multi-key sorts with custom orders. This is essential for automating data organization tasks in reports or datasets directly from Python.

Functionality:
The primary function is to sort a specified range of cells. You can define one or more sort keys (columns or rows to sort by), specify the sort order (ascending or descending), and, for advanced cases, even apply custom sort orders (like sorting by day-of-week or month name). It operates similarly to the sort functionality in the Excel UI but is controlled via code.

Syntax:
The basic syntax in xlwings is:

worksheet.api.Sort.SortFields.Clear()
worksheet.api.Sort.SortFields.Add(
Key=range_to_sort_by,
SortOn=sort_on_parameter,
Order=sort_order,
CustomOrder=custom_order,
DataOption=data_option
)
worksheet.api.Sort.SetRange(range_to_sort)
worksheet.api.Sort.Header = header_setting
worksheet.api.Sort.MatchCase = match_case_setting
worksheet.api.Sort.Orientation = orientation
worksheet.api.Sort.SortMethod = sort_method
worksheet.api.Sort.Apply()

Key parameters include:

  • Key: An xlwings Range object representing the column or row that serves as the sort key.
  • Order: Use win32c.SortOrder.xlAscending (1) or win32c.SortOrder.xlDescending (2).
  • Header: Specifies if the first row contains headers. Use win32c.YesNoGuess.xlYes (1), xlNo (2), or xlGuess (0).
  • Orientation: Defines if sorting is by column (win32c.SortOrientation.xlSortColumns, 1) or by row (xlSortRows, 2).
  • SortOn: Typically win32c.SortOn.xlSortOnValues (0).
  • CustomOrder: A string indicating a custom list (e.g., “Low,Medium,High”).
  • SetRange: The main Range object encompassing all cells to be sorted.

Code Examples:

  1. Simple Single-Column Sort:
    Sort data in range A1:C10 based on values in column A (the first column) in ascending order, assuming the first row is a header.
import xlwings as xw
from xlwings.constants import SortOrder, YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('data.xlsx')
ws = wb.sheets['Sheet1']
sort_range = ws.range('A1:C10')

ws.api.Sort.SortFields.Clear()
ws.api.Sort.SortFields.Add(Key=ws.range('A1:A10'), Order=SortOrder.xlAscending)
ws.api.Sort.SetRange(sort_range.api)
ws.api.Sort.Header = YesNoGuess.xlYes
ws.api.Sort.Apply()

wb.save()
wb.close()
app.quit()
  1. Multi-Column Sort:
    Sort range A1:D100 first by column C (descending), then by column A (ascending).
import xlwings as xw
from xlwings.constants import SortOrder, YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('report.xlsx')
ws = wb.sheets[0]
main_range = ws.range('A1:D100')

ws.api.Sort.SortFields.Clear()
# First key: Column C
ws.api.Sort.SortFields.Add(Key=ws.range('C1:C100'), Order=SortOrder.xlDescending)
# Second key: Column A
ws.api.Sort.SortFields.Add(Key=ws.range('A1:A100'), Order=SortOrder.xlAscending)
ws.api.Sort.SetRange(main_range.api)
ws.api.Sort.Header = YesNoGuess.xlYes
ws.api.Sort.Apply()

wb.save()
app.quit()
  1. Sort with Custom Order:
    Sort a list in A1:A20 by the custom priority “High”, “Medium”, “Low”.
import xlwings as xw
from xlwings.constants import YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('tasks.xlsx')
ws = wb.sheets['Priority']

sort_range = ws.range('A1:A20')
ws.api.Sort.SortFields.Clear()
ws.api.Sort.SortFields.Add(Key=sort_range.api,
CustomOrder="High,Medium,Low")
ws.api.Sort.SetRange(sort_range.api)
ws.api.Sort.Header = YesNoGuess.xlNo # No header in this single column
ws.api.Sort.Apply()

wb.save()
app.quit()

How to use Worksheet.Shapes in the xlwings API way

In Excel, the Shapes collection of a Worksheet object contains all the shape objects on the sheet, such as rectangles, lines, text boxes, pictures, charts, and other drawing objects. Through xlwings, you can programmatically access and manipulate these shapes, enabling automation of tasks like adding annotations, inserting logos, or modifying chart elements. The Shapes member provides methods to add new shapes, iterate through existing ones, and control their properties, making it a powerful tool for enhancing the visual appeal and functionality of Excel reports.

The xlwings API for accessing the Shapes collection is straightforward. You can reference it via a Worksheet object. The syntax is:

worksheet.shapes

This returns a Shapes collection object. Key methods and properties include:

  • add_shape(shape_type, left, top, width, height): Adds a new shape. Parameters: shape_type (integer, e.g., 1 for rectangle, 9 for oval), left and top (distance from left/top edges in points), width and height (in points).
  • count: Property that returns the number of shapes on the worksheet.
  • item(index): Returns a specific shape by index (1-based) or name.
  • Iteration: You can loop through shapes using a for loop.

Common shape types (MsoAutoShapeType enumeration from Microsoft documentation) include:

ValueShape Type
1Rectangle
5Rounded Rectangle
9Oval
13Line
17Text Box

For example, to add a red rectangle and a text box to a worksheet, and then list all shapes:

import xlwings as xw

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

# Add a rectangle shape
rect = ws.shapes.add_shape(1, left=50, top=50, width=100, height=50)
rect.api.Fill.ForeColor.RGB = 0xFF0000 # Set fill color to red via the underlying API

# Add a text box
text_box = ws.shapes.add_shape(17, left=200, top=50, width=150, height=30)
text_box.text = "Sample Text" # Set text content

# Count and iterate through shapes
print(f"Total shapes: {ws.shapes.count}")
for shape in ws.shapes:
    print(f"Shape name: {shape.name}, type ID: {shape.api.AutoShapeType}")

# Modify an existing shape by index
if ws.shapes.count > 0:
    first_shape = ws.shapes[0] # Access by index (0-based in xlwings)
    first_shape.left = 10 # Move it horizontally

# Save changes
wb.save()

How to use Worksheet.ScrollArea in the xlwings API way

The ScrollArea property in the Worksheet object of the Excel object model defines the range of cells that users are allowed to scroll within a specific worksheet. When set, scrolling is restricted to this defined area, preventing users from accessing cells outside of it. This is particularly useful for protecting certain parts of a worksheet, such as headers, footers, or sensitive data, while allowing interaction within a designated data entry or view area. In xlwings, this property can be accessed and modified through the api property, which provides direct access to the underlying Excel object model.

The syntax for using the ScrollArea property in xlwings involves the api property of a Worksheet object. The property is a string that represents the range address in A1-style notation. To get the current scroll area, read the property; to set it, assign a string specifying the range. If the scroll area is cleared, set the property to an empty string. The parameter is a string that can be:

  • A range address, such as “A1:D10”.
  • An empty string (“”) to clear the restriction.
  • None to check if no area is set.

For example, setting the scroll area to “B2:F20” limits scrolling to cells within that rectangle. It’s important to note that this property only affects scrolling via the user interface; programmatic access through xlwings or other methods remains unrestricted. Additionally, if the worksheet is protected, the scroll area might interact with other protection settings, so testing in the target environment is advisable.

Here are xlwings API code examples demonstrating the use of the ScrollArea property:

  1. Setting a scroll area: Restrict scrolling to the range A1 to E15 in the active worksheet.
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
ws.api.ScrollArea = "A1:E15"
  1. Getting the current scroll area: Retrieve and print the currently set scroll area.
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
scroll_area = ws.api.ScrollArea
print(f"Current scroll area: {scroll_area}")
  1. Clearing the scroll area: Remove any scrolling restrictions by setting the property to an empty string.
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
ws.api.ScrollArea = ""
  1. Checking if no scroll area is set: Determine whether scrolling is unrestricted by comparing the property to an empty string or using a conditional check.
import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
if not ws.api.ScrollArea:
    print("No scroll area is set; scrolling is unrestricted.")
else:
    print(f"Scroll area is set to: {ws.api.ScrollArea}")

How to use Worksheet.Rows in the xlwings API way

The Rows member of the Worksheet object in xlwings provides access to a collection representing all the rows within a specific worksheet. This is a powerful feature for performing operations on entire rows, such as selecting, formatting, hiding, or deleting them. It is analogous to the Rows property in the Excel VBA object model and allows for efficient, row-level manipulations without needing to iterate through individual cells manually.

Functionality
The primary function of the Rows property is to return a Range object that represents one or more rows. You can use it to apply formatting (like font color or row height), adjust visibility, clear contents, or delete rows entirely. It is particularly useful for applying uniform changes across a row or for dynamically handling rows based on conditions.

Syntax
The basic syntax to access rows in xlwings is:

ws.rows

This returns a Range object for all rows in the worksheet ws. You can also specify a particular row or a range of rows using indexing or slicing:

  • ws.rows[0] or ws.rows[1]: Accesses the first row. Note: xlwings uses 1-based indexing by default for rows and columns when using the rows property in this context, aligning with Excel’s row numbering (row 1, row 2, etc.).
  • ws.rows[1:5]: Accesses rows 1 through 4 (the slice is 1-based and end-exclusive, similar to Python slicing, but here it refers to Excel rows 1 to 4).
  • ws.rows['1:5']: Alternative string notation to access rows 1 to 5 (inclusive).

Once you have the Range object, you can call various methods and properties. Common parameters include:

  • For formatting: Use properties like row_height, font, color, or methods like autofit().
  • For operations: Use methods like delete(), clear(), hide, or unhide.
  • To get values: Use the value property to retrieve data as a list of lists.

Examples
Here are some practical xlwings API code examples using the Rows member:

  1. Selecting and Formatting All Rows: Set a uniform row height for the entire worksheet.
import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
ws.rows.row_height = 20 # Sets height of all rows to 20 points
  1. Hiding Specific Rows: Hide rows 5 to 10 based on a condition.
ws.rows['5:10'].hidden = True # Hides rows 5 through 10
  1. Deleting Rows: Remove rows 3 to 7 from the worksheet.
ws.rows['3:7'].delete() # Deletes rows 3 to 7, shifting rows up
  1. Clearing Contents: Clear data from the first 5 rows without deleting the rows.
ws.rows[:5].clear_contents() # Clears values but keeps formatting (using 0-based slicing for the first 5 rows)
# Alternatively, with 1-based: ws.rows['1:5'].clear_contents()
  1. Autofitting Rows: Adjust row heights to fit the content automatically.
ws.rows.autofit() # Autofits all rows based on cell content
  1. Iterating Over Rows: Loop through each row to perform custom operations, such as checking values.
for row in ws.rows:
    if row.value and row.value[0] == 'Target': # Check first cell in each row
row.color = (255, 200, 200) # Highlight row with a light red color

How to use Worksheet.Range in the xlwings API way

The Range member of the Worksheet object in the xlwings API is a fundamental and versatile tool for interacting with cells, ranges, and data within an Excel worksheet. It is the primary gateway for reading, writing, and manipulating cell content, formatting, and formulas. Functionally, it allows you to select a specific cell, a rectangular block of cells, or even non-contiguous cells, enabling operations like data input, extraction, and formatting adjustments.

Syntax and Parameters:
The basic syntax to access a range is through a Worksheet object. The most common call is:
ws.range(cell1, cell2=None)

  • ws: The xlwings Worksheet object.
  • cell1: A required argument that defines the start of the range. It can be a string address (e.g., "A1"), a tuple of integers for the row and column (e.g., (1, 1) for A1), or an xlwings Range object.
  • cell2: An optional argument that defines the end of the range for a multi-cell selection. It accepts the same formats as cell1. If omitted, the range is a single cell defined by cell1.

You can also use the shorthand indexing syntax: ws["A1"] or ws["A1:C5"].

Key Properties and Methods of the Range Object:
Once you have a Range object, you can use its properties and methods. Key members include:

  • .value: Gets or sets the values in the range. For a single cell, it returns a scalar (e.g., number, string). For multiple cells, it returns a nested list (list of lists).
  • .formula: Gets or sets the Excel formula string for the range.
  • .address: Returns the absolute address of the range as a string.
  • .row / .column: Return the first row or column number of the range (1-indexed).
  • .rows / .columns: Return a Range object representing the rows or columns within the range.
  • .autofit(): Automatically adjusts the width of columns or height of rows to fit their contents.
  • .clear(): Clears the content and formatting of the range.
  • .copy(destination): Copies the range to a specified destination range.
  • .paste(): Pastes the contents of the Clipboard into the range.

Code Examples:

  1. Reading and Writing Values:
import xlwings as xw
wb = xw.Book("data.xlsx")
ws = wb.sheets["Sheet1"]

# Write a single value
ws.range("B2").value = "Total Revenue"

# Write a list of lists (2D array)
data = [[1, 2, 3], [4, 5, 6]]
ws.range("A4").value = data # Expands from A4 to C5

# Read a single cell
revenue = ws.range("D10").value
print(revenue)

# Read a contiguous range into a 2D list
table_data = ws.range("A1:C10").value
for row in table_data:
    print(row)
  1. Using Formulas and Formatting:
# Insert a SUM formula
ws.range("C15").formula = "=SUM(C5:C14)"

# Autofit columns for a specific range
ws.range("A1:F20").autofit()

# Clear a range
ws.range("OldData!A1:Z100").clear()
  1. Working with Rows and Columns:
# Get the address of a dynamically defined range
start_cell = (5, 2) # Row 5, Column 2 (B5)
end_cell = (15, 5) # Row 15, Column 5 (E15)
my_range = ws.range(start_cell, end_cell)
print(f"Range address: {my_range.address}")

# Access entire rows/columns
ws.range("5:5").value = ["Q1", "Q2", "Q3", "Q4"] # Entire row 5
ws.range("C:C").column_width = 15 # Set width of entire column C

How to use Worksheet.QueryTables in the xlwings API way

The QueryTables member of the Worksheet object in Excel’s object model represents a collection of QueryTable objects, each of which corresponds to a data query table that retrieves and refreshes data from an external source, such as a database, web page, or text file. In xlwings, this collection can be accessed and manipulated to automate data import and management tasks directly from Python. This functionality is particularly useful for automating repetitive data refresh operations or integrating external data feeds into Excel workbooks programmatically.

Functionality:
The QueryTables collection allows you to:

  • Access existing query tables on a specific worksheet.
  • Add new query tables (though note that creating new query tables via xlwings may require using the underlying Excel object model via the .api property, as xlwings does not have a dedicated high-level method for this).
  • Refresh data in query tables to update the imported information.
  • Modify properties of query tables, such as connection strings or refresh settings.

Syntax:
In xlwings, you access the QueryTables collection through a Worksheet object. The basic syntax is:

worksheet.api.QueryTables

This returns a COM object representing the collection, which you can then iterate over or access by index. For example, to get the first query table on a worksheet:

first_query_table = worksheet.api.QueryTables(1)

Common methods and properties include:

  • Refresh(): Refreshes the query table to update data.
  • BackgroundQuery: A property that can be set to True or False to control whether the query runs in the background.
  • Connection: A property that holds the connection string for the data source.

Example:
Below is a practical example demonstrating how to use the QueryTables member in xlwings to refresh all query tables on a worksheet and adjust a property. This assumes you have an existing Excel workbook with query tables set up.

import xlwings as xw

# Connect to the active workbook or open a specific one
wb = xw.Book.active # Or use xw.Book('path_to_file.xlsx')
ws = wb.sheets['Sheet1'] # Replace with your sheet name

# Access the QueryTables collection
query_tables = ws.api.QueryTables

# Check if there are any query tables
if query_tables.Count > 0:
    print(f"Found {query_tables.Count} query table(s).")

# Iterate through each query table and refresh it
for i in range(1, query_tables.Count + 1):
    qt = query_tables(i)
    print(f"Refreshing query table: {qt.Name}")
    qt.Refresh()

# Example: Disable background query for the first table
if i == 1:
    qt.BackgroundQuery = False
    print(f"Set BackgroundQuery to False for {qt.Name}.")
else:
    print("No query tables found on this worksheet.")

# Save the workbook if needed
wb.save()