Blog

How to use Application.CentimetersToPoints in the xlwings API way

The Application.CentimetersToPoints method in Excel’s object model is a utility function that converts a measurement from centimeters to points. In the context of xlwings, which provides a Pythonic interface to automate Excel, this method is accessible through the Application object. It is particularly useful when you need to set dimensions, such as row heights, column widths, or shape sizes, in points—Excel’s native unit for such measurements—while working with centimeter-based data. This conversion ensures precision and consistency in layout and formatting tasks, especially in international settings where centimeters are a common metric unit.

Syntax in xlwings:
In xlwings, you call this method via the app object, which represents the Excel application. The syntax is:
app.api.CentimetersToPoints(Centimeters)

• Centimeters: Required. A numeric value or expression representing the length in centimeters that you want to convert to points. This parameter can be a single number, a variable, or a calculated result.
  The method returns a Single (floating-point) value representing the equivalent measurement in points. Note that 1 centimeter is approximately equal to 28.3465 points in Excel, as points are defined as 1/72 of an inch, and 1 inch equals 2.54 centimeters.

Example Usage with xlwings:
Below are practical examples demonstrating how to use CentimetersToPoints in xlwings for various Excel automation tasks. These examples assume you have an Excel application instance running via xlwings.

1. Converting a Single Measurement:
   This example converts 5 centimeters to points and prints the result. It is useful for quick calculations or debugging.

import xlwings as xw
app = xw.App(visible=False) # Start Excel in the background
points_value = app.api.CentimetersToPoints(5)
print(f"5 cm is equal to {points_value} points.") # Output: ~141.7325 points
app.quit()

2. Setting Column Width Based on Centimeters:
   Here, we set the width of column A in the active workbook to a specific centimeter value by converting it to points. Excel’s column width is measured in points (or character units, but points are used for precise control via API).

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets[0]
# Convert 3.5 cm to points and set as column width for column A
width_in_points = app.api.CentimetersToPoints(3.5)
ws.api.Columns("A").ColumnWidth = width_in_points
wb.save()
app.quit()

3. Adjusting Row Height Dynamically:
   This example uses a loop to set row heights for multiple rows based on a list of centimeter values. It showcases how to integrate the conversion into batch operations.

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.add()
ws = wb.sheets[0]
cm_heights = [2.0, 2.5, 3.0] # Heights in centimeters for rows 1 to 3
for i, cm in enumerate(cm_heights, start=1):
points_height = app.api.CentimetersToPoints(cm)
ws.api.Rows(i).RowHeight = points_height
wb.save("adjusted_heights.xlsx")
app.quit()

4. Calculating Shape Dimensions:
   When adding or resizing shapes, you might need to specify sizes in points. This example creates a rectangle with width and height derived from centimeter measurements.

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets[0]
# Define dimensions in centimeters
width_cm, height_cm = 4.0, 2.0
width_pts = app.api.CentimetersToPoints(width_cm)
height_pts = app.api.CentimetersToPoints(height_cm)
# Add a rectangle shape at position (100, 100) with converted dimensions
shape = ws.shapes.add_shape(
1, # Type: rectangle
100, 100, # Left and top positions in points
width_pts, height_pts
)
shape.name = "MetricRectangle"
wb.save()
app.quit()

How to use Application.CalculateUntilAsyncQueriesDone in the xlwings API way

The Application.CalculateUntilAsyncQueriesDone property is a member of the Excel object model that provides control over the calculation process when asynchronous queries, such as those from Power Query (Get & Transform Data), are involved. In scenarios where a workbook contains data connections that refresh asynchronously, Excel’s standard calculation might proceed before these queries have fully completed. This can lead to formulas returning results based on outdated or incomplete data. The CalculateUntilAsyncQueriesDone property addresses this by forcing Excel to pause further calculation until all pending asynchronous queries have finished refreshing. This ensures subsequent calculations operate on the complete, current dataset.

In xlwings, you access this property through the Application object. The property is a read/write Boolean.

xlwings API Syntax and Parameters

The property is accessed directly on the app object (an instance of xw.App). There are no method parameters as it is a property, not a method.

• Get the current value: current_state = app.api.CalculateUntilAsyncQueriesDone
• Set the value: app.api.CalculateUntilAsyncQueriesDone = True or app.api.CalculateUntilAsyncQueriesDone = False

Property Value:
The property accepts and returns a Boolean value.

Value	Meaning
True	Excel will wait for all asynchronous queries to complete before continuing with any pending calculations.
False	(Default) Excel will not wait for asynchronous queries to finish; calculations may proceed with potentially stale query data.

Usage Example with xlwings

A typical use case is to set this property to True before triggering a full workbook calculation or before running a macro that depends on the latest query data. It is good practice to restore the original setting afterward.

import xlwings as xw

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

# Store the original setting
original_setting = app.api.CalculateUntilAsyncQueriesDone
print(f"Original CalculateUntilAsyncQueriesDone setting: {original_setting}")

try:
    # Ensure Excel waits for async queries
    app.api.CalculateUntilAsyncQueriesDone = True

    # Refresh all data connections (queries)
    app.api.ActiveWorkbook.RefreshAll()

    # Now perform a full calculation. Excel will wait for RefreshAll to finish.
    app.api.Calculate()

    # Your code to work with the calculated data...
    ws = app.api.ActiveSheet
    print(f"Value in A1 after refresh and calculation: {ws.Range('A1').Value}")

finally:
    # Restore the original setting
    app.api.CalculateUntilAsyncQueriesDone = original_setting
    print(f"CalculateUntilAsyncQueriesDone restored to:      {app.api.CalculateUntilAsyncQueriesDone}")

How to use Application.CalculateFullRebuild in the xlwings API way

The Application.CalculateFullRebuild member in Excel performs a complete recalculation of all formulas in all open workbooks, including those that may depend on external data sources or custom functions. It ensures that every calculation is refreshed, which is particularly useful after making significant changes to data or formulas that might not update automatically through standard calculation methods. In xlwings, this functionality can be accessed via the api property, allowing Python scripts to trigger a full rebuild of calculations in Excel, similar to pressing Ctrl+Alt+Shift+F9 in the Excel interface. This is beneficial in scenarios where partial recalculations might leave stale values, such as when working with complex financial models, data analysis pipelines, or macros that modify large datasets.

Syntax in xlwings:
To use CalculateFullRebuild in xlwings, you need to reference the Excel Application object through the xlwings App or via an existing workbook. The member is a method with no parameters. The basic syntax is:

app.api.CalculateFullRebuild()

Here, app represents an xlwings App instance connected to Excel. The api property provides direct access to the underlying Excel object model, enabling you to call the CalculateFullRebuild method. There are no arguments to pass, as the method simply triggers a full recalculation across all open workbooks in that Excel instance.

Example Usage:
Below is a practical example demonstrating how to use CalculateFullRebuild in a Python script with xlwings. This example assumes you have Excel open with workbooks containing formulas that need a complete refresh.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active # Use the currently running Excel application
# Alternatively, start a new instance: app = xw.App()

# Trigger a full recalculation of all formulas in all open workbooks
app.api.CalculateFullRebuild()

print("Full recalculation completed for all open workbooks.")

# You can also specify a particular workbook if needed, but note that CalculateFullRebuild applies globally
wb = app.books['MyWorkbook.xlsx'] # Reference a specific workbook
# Even when referencing a workbook, CalculateFullRebuild still affects all open workbooks in the app
app.api.CalculateFullRebuild()

# To ensure changes are saved, you might add:
wb.save()
app.quit() # Close the Excel application if done

How to use Application.CalculateFull in the xlwings API way

The CalculateFull method of the Application object in Excel is a powerful feature for ensuring complete and accurate recalculation of all formulas in all open workbooks. This method forces a full calculation, meaning it recalculates every formula, regardless of whether Excel’s calculation engine considers them dirty or not. This is particularly useful in scenarios where you have complex, interdependent formulas, or when you have programmatically changed a large number of cells and want to guarantee that all subsequent formulas reflect these changes before proceeding. Unlike the standard Calculate method, which might only recalculate formulas marked as needing an update, CalculateFull provides a thorough and definitive recalculation cycle.

In the xlwings API, you access this method through the app object, which represents the Excel application. The syntax is straightforward, as the method does not take any parameters.

Syntax:

app.api.CalculateFull()

• app: This is your xlwings App instance.
• .api: This property provides direct access to the underlying Excel object model (the COM/API layer).
• .CalculateFull(): This is the method call. It requires no arguments.

Key Points:

• It affects all open workbooks in the Excel application instance.
• It is a synchronous operation; your xlwings code will wait until the full calculation is complete before executing the next line.
• This method is equivalent to pressing Ctrl+Alt+Shift+F9 in the Excel desktop application.

Code Examples:

1. Basic Full Calculation:
   This example ensures that after writing new data to a sheet, every formula in the application is recalculated.

import xlwings as xw

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

# Write some values that are inputs to formulas
app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('A1').value = 100
app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('A2').value = 200

# Force a full recalculation of all formulas in all open workbooks
app.api.CalculateFull()

# Now read a result from a formula cell, confident it's up-to-date
result = app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('C1').value
print(f"The calculated result is: {result}")

2. Using with Manual Calculation Mode:
   This is a common use case. When calculation mode is set to manual, formulas are not updated automatically. CalculateFull gives you precise control over when the heavy computation occurs.

import xlwings as xw

app = xlwings.App(visible=True) # Start a new Excel app
wb = app.books.add()

# Set calculation mode to manual for performance
app.api.Calculation = -4135 # xlCalculationManual

# Perform extensive data manipulation
sheet = wb.sheets[0]
for i in range(1, 1001):
sheet.range(f'A{i}').value = i
# Formulas in column B reference column A
sheet.range(f'B{i}').formula = f'=A{i}*2'

# After all data is written, trigger one comprehensive calculation
print("Starting full calculation...")
app.api.CalculateFull() # This will recalculate all 1000 formulas
print("Calculation complete.")

# Sample the result
print(sheet.range('B500').value) # Will correctly output 1000.0
app.quit()

How to use Application.Calculate in the xlwings API way

The Application.Calculate member in Excel’s object model is a method that forces a full recalculation of all open workbooks. In xlwings, this is exposed through the api property, allowing Python scripts to trigger the same recalculation engine that Excel uses. This is particularly useful after programmatically modifying cell values or formulas, ensuring that all dependent calculations are updated before proceeding with further operations, such as reading results or generating reports.

Functionality
The primary function of Application.Calculate is to perform a complete recalculation across all data in all open workbooks. It recalculates all formulas, updating any cells that depend on changed precedents. This is equivalent to pressing F9 in the Excel application. It is essential when your VBA macro or xlwings script changes values and needs immediate, accurate results from formulas that reference those cells. Without an explicit calculate call, Excel might not update all formulas until the next natural recalculation cycle, potentially leading to stale data being read.

Syntax
In xlwings, you access this method via the Application object obtained from a workbook or app instance. The typical syntax is:

app.application.Calculate()

Here, app refers to an xlwings App instance. The application property returns the underlying COM object (Excel’s Application), on which you call the Calculate method. The method takes no parameters. It simply triggers the recalculation.

Example
Consider a scenario where you have an Excel workbook with formulas in column B that sum values from column A. You use xlwings to write new numbers into column A and then need to read the updated totals from column B. Without a calculate, column B might still show old results.

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active # Or xw.App() for a new instance

# Open a specific workbook (adjust the path)
wb = app.books.open(r'C:\path\to\your\workbook.xlsx')
sheet = wb.sheets['Sheet1']

# Write new values to cells A1:A10
for i in range(1, 11):
    sheet.range(f'A{i}').value = i * 10

# Force a full recalculation to update formulas in column B
app.application.Calculate()

# Now read the recalculated sums from column B (assuming B1:B10 contain formulas like =SUM(A$1:A1))
for i in range(1, 11):
    total = sheet.range(f'B{i}').value
    print(f'Row {i} total: {total}')

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