Blog

How to use Range.RemoveDuplicates in the xlwings API way

The RemoveDuplicates method in the Range object is a powerful feature for data cleaning in Excel, allowing users to eliminate duplicate rows from a specified range based on selected columns. In xlwings, this functionality is accessible through the API, enabling automation of duplicate removal in workbooks directly from Python scripts. This method is particularly useful for preprocessing datasets before analysis, ensuring data integrity by retaining only unique entries.

Functionality
RemoveDuplicates identifies and removes duplicate rows within a range, keeping the first occurrence of each unique row. It operates on one or more columns as criteria for determining duplicates. For instance, if you specify a single column, rows with identical values in that column are considered duplicates; if multiple columns are selected, rows must match in all specified columns to be flagged as duplicates. The method modifies the range in place, deleting duplicate rows and shifting remaining cells up, similar to Excel’s built-in “Remove Duplicates” feature.

Syntax
In xlwings, the RemoveDuplicates method is called on a Range object. The syntax is as follows:

range_object.remove_duplicates(columns, header)

• columns: A list of integers representing the column indices within the range to check for duplicates. The indices are 1-based relative to the range, not the entire worksheet. For example, if the range starts at column A, [1] refers to column A within that range. Multiple columns can be specified, such as [1, 3] for the first and third columns of the range.
• header: An optional parameter that indicates whether the range has a header row. It accepts the xlYes, xlNo, or xlGuess constants from the xlwings.constants module. xlYes (or 1) treats the first row as a header and excludes it from duplicate checking. xlNo (or 2) includes all rows, including the first, in duplicate removal. xlGuess (or 0) lets Excel determine if a header exists based on the data. The default is xlNo.

Example Usage
Consider an Excel worksheet with data in columns A to C, where column A contains IDs, column B names, and column C values. To remove duplicate rows based on the ID column (column A), assuming the data starts at cell A1 and includes a header row, you can use the following xlwings code:

import xlwings as xw
from xlwings.constants import DeleteShiftDirection

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

# Define the range, e.g., A1:C10
data_range = ws.range('A1:C10')

# Remove duplicates based on the first column (ID) with a header
data_range.remove_duplicates(columns=[1], header=1) # header=1 corresponds to xlYes

In this example, columns=[1] specifies that duplicates should be identified using the first column of the range (column A). The header=1 parameter ensures the header row is not considered for duplicates. After execution, only rows with unique IDs remain in the range A1:C10, with other rows removed and cells shifted up.

For a more complex scenario, if you want to remove duplicates based on both ID and name (columns A and B), you can adjust the columns parameter:

# Remove duplicates based on columns A and B
data_range.remove_duplicates(columns=[1, 2], header=1)

How to use Range.PrintPreview in the xlwings API way

The PrintPreview method of the Range object in Excel is a powerful feature that allows users to see exactly how a selected range will appear when printed, without actually sending the document to a printer. This is an essential tool for formatting checks, layout adjustments, and ensuring that reports or data sets are perfectly arranged on the page before final output. In xlwings, which provides a clean and Pythonic way to automate Excel, this functionality is accessed through the api property, which exposes the underlying Excel object model. Using PrintPreview can save time and resources by preventing unnecessary print jobs and paper waste.

The syntax for calling PrintPreview via xlwings follows the pattern of the Excel VBA object model. Since PrintPreview is a method that does not take any parameters, its usage is straightforward. The general form is:

range_object.api.PrintPreview()

Here, range_object refers to an xlwings Range object, which represents a cell or a range of cells in an Excel worksheet. The .api property provides access to the native Excel COM object, allowing you to call its methods directly. The PrintPreview method, when invoked, immediately opens the print preview window for the specified range, using the current printer settings and page layout options defined in the workbook. It’s important to note that this method does not return a value; its action is purely to display the preview interface.

Consider a scenario where you have an Excel workbook with a sales report in a sheet named “Q1_Summary”, and you want to preview the print output for the data in cells A1 through G20. The following xlwings code example demonstrates how to achieve this:

import xlwings as xw

# Connect to the active Excel application or start a new one
app = xw.App(visible=True) # Set visible=True to see Excel

# Open the workbook (adjust the path to your file)
wb = app.books.open('sales_report.xlsx')

# Access the specific worksheet
sheet = wb.sheets['Q1_Summary']

# Define the range you want to preview
target_range = sheet.range('A1:G20')

# Call the PrintPreview method via the .api property
target_range.api.PrintPreview()

# The print preview window will open. The script will wait until it is closed.
# After closing the preview, you can continue with other operations or close.
wb.close()
app.quit()

In this example, the PrintPreview window will display exactly how the range A1:G20 from the “Q1_Summary” sheet will look on a printed page. Users can then inspect margins, page breaks, headers, and footers. To automate further, you might integrate this into a larger script that formats the range based on certain conditions before previewing. For instance, you could adjust column widths or apply a print area before calling PrintPreview:

# Set the print area to the target range
sheet.page_setup.print_area = target_range.address

# Optionally, set to landscape orientation for a wider range
sheet.page_setup.orientation = 'landscape'

# Now preview
target_range.api.PrintPreview()

How to use Range.PrintOut in the xlwings API way

The PrintOut member of the Range object in Excel’s object model is a method used to print a specified range of cells. In xlwings, this functionality is accessible through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful for automating the printing of specific data sections, reports, or charts directly from Python scripts, enabling seamless integration of Excel printing tasks into automated workflows.

Functionality
The PrintOut method prints the specified range. It allows control over various print settings such as the number of copies, whether to collate, and the printer to use. This can help in generating hard copies of data without manual intervention, ideal for batch processing or scheduled reporting.

Syntax
In xlwings, the syntax for calling PrintOut is:

range_object.api.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

Here, range_object is an xlwings Range object (e.g., sheet.range('A1:D10')). The parameters are optional and map directly to Excel’s PrintOut method arguments:

• From: The starting page number to print. If omitted, printing starts from the beginning.
• To: The ending page number to print. If omitted, printing goes to the end.
• Copies: The number of copies to print. Default is 1.
• Preview: If True, Excel will show the print preview instead of printing immediately. Default is False.
• ActivePrinter: A string specifying the printer name to use. If omitted, the default printer is used.
• PrintToFile: If True, the output is sent to a file. Default is False.
• Collate: If True, copies are collated when printing multiple copies. Default is True.
• PrToFileName: The file name if PrintToFile is set to True. If omitted, Excel prompts for a file name.

Code Examples

1. Basic printing of a range:
   This example prints the range A1 to D10 on the active sheet with default settings.

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets.active
# Define the range and print
print_range = sheet.range('A1:D10')
print_range.api.PrintOut()

2. Printing multiple copies with collation:
   Here, two collated copies of the range B2:F20 are printed.

import xlwings as xw
wb = xw.books.active
sheet = wb.sheets.active
print_range = sheet.range('B2:F20')
print_range.api.PrintOut(Copies=2, Collate=True)

3. Printing to a file:
   This sends the output of the range A1:Z100 to a PDF file named “report.pdf” without showing preview.

import xlwings as xw
wb = xw.books.active
sheet = wb.sheets.active
print_range = sheet.range('A1:Z100')
print_range.api.PrintOut(PrintToFile=True, PrToFileName="report.pdf", Preview=False)

4. Using print preview:
   To preview the print output for the range C5:G15 before printing.

import xlwings as xw
wb = xw.books.active
sheet = wb.sheets.active
print_range = sheet.range('C5:G15')
print_range.api.PrintOut(Preview=True)

How to use Range.PasteSpecial in the xlwings API way

The PasteSpecial method of the Range object in Excel VBA is a powerful feature for pasting data with specific attributes or operations, such as values, formats, or formulas. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This allows Python scripts to leverage Excel’s native capabilities for advanced data manipulation.

Functionality
PasteSpecial is used to paste a copied range into a target range with special options. It is commonly applied after copying data using the Copy method. Key uses include pasting only values (excluding formulas), formats, column widths, or performing mathematical operations like addition or multiplication on the existing data. This is essential for tasks like data cleaning, report formatting, and batch updates without altering underlying formulas.

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

range.api.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Parameters:

• Paste: Specifies the part of the copied data to paste. It is an Excel constant (e.g., xlPasteAll for everything, xlPasteValues for values only). Common values are accessed through xlwings.constants.
• Operation: Optional. Specifies a mathematical operation to apply during paste, such as xlNone (default, no operation), xlAdd, xlMultiply. Also from xlwings.constants.
• SkipBlanks: Optional. If True, blanks in the copied range are not pasted, preventing overwriting of existing data. Default is False.
• Transpose: Optional. If True, the data is transposed (rows and columns swapped) during paste. Default is False.

For example, xlPasteValues corresponds to the Excel constant -4163. In xlwings, you can use xlwings.constants.xlPasteValues or the numeric value directly. The parameters are typically passed as keyword arguments for clarity.

Code Examples

1. Pasting only values from a copied range:

import xlwings as xw
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']
# Copy range A1:B5
sheet.range('A1:B5').copy()
# Paste special as values into D1
sheet.range('D1').api.PasteSpecial(Paste=xw.constants.xlPasteValues)

This copies data from A1:B5 and pastes only the values (no formulas or formatting) starting at D1.

2. Pasting formats and performing an addition operation:

import xlwings as xw
wb = xw.Book('data.xlsx')
sheet = wb.sheets[0]
# Copy range C1:C10
sheet.range('C1:C10').copy()
# Paste special with formats and add to existing values in E1:E10
sheet.range('E1').api.PasteSpecial(Paste=xw.constants.xlPasteFormats, Operation=xw.constants.xlAdd)

Here, the formats from C1:C10 are applied to E1:E10, and the values are added to the existing ones in the target range.

3. Using transpose and skipping blanks:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('source.xlsx')
sheet = wb.sheets['Data']
# Copy a horizontal range
sheet.range('A1:J1').copy()
# Paste special as values, transposed vertically, skipping blanks
sheet.range('A2').api.PasteSpecial(Paste=-4163, Transpose=True, SkipBlanks=True)
wb.save()
app.quit()

How to use Range.Parse in the xlwings API way

The Parse method of the Range object in Excel is a powerful tool for breaking down text data within cells into separate columns, based on a specified delimiter or fixed-width format. This is particularly useful when dealing with imported or pasted data that is not properly structured across columns, such as addresses, full names, or log entries that are combined into a single cell. In xlwings, this functionality is accessible through the api property, which provides direct access to the underlying Excel object model, allowing for precise control over data parsing operations.

Functionality:
The primary purpose of the Parse method is to parse the text in a range of cells and distribute the parsed components into adjacent cells to the right. It can handle both delimiter-based parsing (like commas or tabs) and fixed-width parsing, depending on how the parse line is defined in Excel. This method effectively mimics the “Text to Columns” wizard in Excel’s UI but can be automated via code.

Syntax in xlwings:
The method is called on a Range object through the api property. The basic syntax is:

range.api.Parse(ParseLine, Destination)

• ParseLine: A string that specifies how to parse the data. For delimiter parsing, it is a string containing the delimiter characters (e.g., "," for commas). For fixed-width parsing, it consists of numbers indicating column widths in characters, separated by spaces or commas (e.g., "5 10 15"). This parameter is optional; if omitted, Excel uses the default delimiter settings or the last used parse line.
• Destination: A Range object specifying the top-left cell of the destination range where the parsed data will be placed. This parameter is optional; if omitted, parsing occurs in place, overwriting the original data with the parsed results in the same range.

Code Examples:

1. Delimiter Parsing: Suppose cell A1 contains “John,Doe,30” and you want to split it into three columns based on commas.

import xlwings as xw
wb = xw.Book.active()
ws = wb.sheets['Sheet1']
# Parse the text in cell A1 using a comma delimiter
ws.range('A1').api.Parse(ParseLine=",")
# After execution, A1 will contain "John", B1 will contain "Doe", and C1 will contain "30"

2. Fixed-Width Parsing: If cell A2 has “2023-01-15LogEntry” with dates taking the first 10 characters and the log entry the rest, you can parse it by fixed widths.

# Define fixed widths: first 10 characters for date, remaining for log
ws.range('A2').api.Parse(ParseLine="10")
# This will split "2023-01-15" into A2 and "LogEntry" into B2

3. Parsing a Range with Destination: To parse data from column A and place the results starting at cell D1 to avoid overwriting original data.

source_range = ws.range('A1:A5')
destination_range = ws.range('D1')
source_range.api.Parse(Destination=destination_range)
# The parsed data from A1:A5 will be output starting at D1, extending across multiple columns as needed

How to use Range.NoteText in the xlwings API way

The NoteText member of the Range object in the Excel object model is accessible through the xlwings library in Python, enabling programmatic interaction with cell notes (formerly known as comments in older Excel versions, but distinct from the modern threaded comments). This functionality is crucial for automating the retrieval, modification, or insertion of textual annotations within specific cells of an Excel workbook, facilitating tasks such as data validation notes, instructional text, or historical data tracking directly from Python scripts.

In xlwings, the NoteText property is used to get or set the text of a cell note. The syntax for accessing this property is straightforward, as it behaves like a standard property of a Range object. The general format is range_object.note_text, where range_object is an instance of xlwings’ Range. When getting the property, it returns a string containing the note’s text if a note exists; otherwise, it returns None. When setting the property, you assign a string to it, which will either create a new note with that text or update the text of an existing note in the specified cell. It’s important to note that this property specifically handles the legacy “note” feature, not the newer “comments” (threaded comments) introduced in recent Excel versions, which are managed through a different API.

For example, consider a scenario where you need to read an existing note from cell A1 in the active worksheet and then update it with additional information. The xlwings code would be:

import xlwings as xw

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

# Define the target cell range
target_cell = sheet.range('A1')

# Get the existing note text
current_note = target_cell.note_text
print(f"Current note in A1: {current_note}")

# Set new note text, adding to the existing content if desired
new_text = "Updated: Review completed on 2023-10-05."
target_cell.note_text = new_text

# Alternatively, append to the existing note
if current_note:
    updated_note = current_note + "\n" + new_text
else:
    updated_note = new_text
    target_cell.note_text = updated_note

Another common use case is iterating through a range of cells to check for notes and perform actions based on their content. For instance, you might want to extract all notes from a column and log them:

# Assume we have data in column B from rows 1 to 10
for i in range(1, 11):
    cell = sheet.range(f'B{i}')
    note = cell.note_text
    if note:
        print(f"Note in B{i}: {note}")

How to use Range.NavigateArrow in the xlwings API way

The NavigateArrow method in Excel’s Range object is a powerful feature for tracing cell dependencies, particularly useful in auditing formulas by following precedent or dependent arrows. In xlwings, this method is accessible through the api property, which provides direct access to the underlying Excel object model. It allows you to programmatically navigate through tracer arrows that indicate relationships between cells, such as which cells affect the active cell (precedents) or which cells are affected by it (dependents). This functionality is essential for debugging complex spreadsheets, as it helps visualize and inspect formula linkages.

Syntax in xlwings:
The xlwings API call follows the Excel object model closely. The syntax is:

range_object.api.NavigateArrow(TowardPrecedent, ArrowNumber, LinkNumber)

• TowardPrecedent (Required, Boolean): Specifies the direction of navigation. Set to True to move toward precedents (cells that affect the active cell), or False to move toward dependents (cells affected by the active cell).
• ArrowNumber (Required, Integer): Indicates which tracer arrow to follow, typically starting from 1 for the first arrow. In cases with multiple arrows, this parameter selects a specific one.
• LinkNumber (Optional, Variant): Used if the arrow is an external reference arrow; it specifies which external link to follow. If not applicable, this can be omitted or set to 1.

Example Usage:
Suppose you have an Excel workbook where cell B10 contains a formula referencing cells A1:A5 and C10. You can use xlwings to trace precedents and navigate through arrows. First, ensure xlwings is installed and connected to Excel:

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets.active
# Select a range, e.g., cell B10
target_range = ws.range("B10")
# Navigate toward precedents using the first tracer arrow
try:
    # This moves the selection to the precedent cells along the arrow
    result_range = target_range.api.NavigateArrow(True, 1)
    print("Navigated to precedent:", result_range.Address)
except Exception as e:
    print("Error navigating arrow:", e)

How to use Range.Merge in the xlwings API way

In Excel automation using the xlwings library, the Merge functionality of the Range object is essential for formatting and organizing data presentation. The Range object represents a cell or a group of cells in an Excel worksheet, and its Merge member allows you to combine multiple adjacent cells into a single larger cell. This is particularly useful for creating headers, grouping data under a common label, or improving the visual layout of reports. The merging operation can be performed either temporarily for display purposes or permanently, affecting the underlying cell structure.

The syntax for using the Merge member in xlwings is straightforward. For a given Range object, you can call the merge() method. The method does not require any parameters in its basic form, but it has optional arguments to control the merging behavior. The general format is:

range_object.merge(across=False)

Here, across is an optional boolean parameter. When set to True, the merge operation combines cells in each row of the specified range independently, which is similar to Excel’s “Merge Across” feature. If across is False (the default), the entire range is merged into a single cell. It’s important to note that the Range object must refer to a contiguous block of cells; otherwise, an error may occur. The merge operation will also clear any existing content in the range except the top-left cell, whose value is retained in the merged cell.

To illustrate, consider the following xlwings code examples. First, ensure you have an active workbook and worksheet. You can start by importing xlwings and setting up a connection:

import xlwings as xw
wb = xw.Book() # Opens a new workbook
ws = wb.sheets[0] # Accesses the first worksheet

Example 1: Merging a range of cells into one large cell. Suppose you want to merge cells A1 through C1 to create a header:

header_range = ws.range('A1:C1')
header_range.merge()
header_range.value = "Sales Report" # Sets the value for the merged cell

This combines cells A1, B1, and C1, and the text “Sales Report” will be centered across the merged area.

Example 2: Using the across parameter to merge cells row-wise. If you have a range from A3 to C5 and want to merge each row independently (e.g., for subheadings), you can do:

row_range = ws.range('A3:C5')
row_range.merge(across=True)

This results in three merged cells: one covering A3:C3, another for A4:C4, and a third for A5:C5. You can then assign different values to each row, such as:

ws.range('A3').value = "Q1"
ws.range('A4').value = "Q2"
ws.range('A5').value = "Q3"

Example 3: Checking if a cell is part of a merged range. While the Merge member is primarily for creating merged cells, you can use the merge_area property to identify merged regions. For instance:

merged_cell = ws.range('A1')
if merged_cell.merge_area.count > 1:
    print("This cell is merged.")

How to use Range.ListNames in the xlwings API way

The ListNames property of the Range object in Excel’s object model is accessible through the xlwings API, providing a powerful way to retrieve a collection of all defined names that intersect with a specified range. This is particularly useful for auditing, data validation, or dynamically interacting with named ranges within a workbook. In xlwings, this property is exposed as a method on a Range object, returning a list of Name objects, each representing a defined name in the workbook.

Functionality:
The primary function is to list all defined names (created via “Formulas” > “Define Name” or through VBA) whose ranges have at least one cell in common with the specified Range object. If the specified range does not intersect with any defined names, an empty list is returned. This allows for targeted inspection of named ranges within specific worksheet areas.

Syntax:
In xlwings, the syntax is:

range_object.api.ListNames

Here, range_object is an xlwings Range instance (e.g., sheet.range('A1:D10')). The .api attribute provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS). The ListNames property does not take any parameters. It returns a pywin32 Names collection object, which can be iterated over or converted to a list. Each item in this collection is a pywin32 Name object, corresponding to a defined name in Excel.

Key Points:

• The property is read-only.
• It returns names defined at both the workbook and worksheet scope, but only if they intersect with the range.
• The intersection is based on the actual range referenced by the name, not just its starting cell.

Example:
Consider an Excel workbook where the following names are defined:

• SalesData referring to Sheet1!$A$1:$F$100
• Quarter1 referring to Sheet1!$B$1:$B$25
• Summary referring to Sheet2!$A$1:$A$10

The following xlwings code demonstrates how to use ListNames:

import xlwings as xw

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

# Define a range on Sheet1
target_range = sheet.range('A1:C30')

# Get the list of names intersecting with this range
names_collection = target_range.api.ListNames

# Check if any names were found
if names_collection:
    print("Names intersecting with range A1:C30:")
    for name_obj in names_collection:
        # Access properties of the Name object
        name = name_obj.Name # The name string (e.g., "SalesData")
        refers_to = name_obj.RefersTo # The range reference (e.g., "=Sheet1!$A$1:$F$100")
        print(f" - {name}: {refers_to}")
else:
    print("No defined names intersect with the specified range.")

How to use Range.Justify in the xlwings API way

The Justify method of the Range object in Excel is used to redistribute the text contents of a specified range into adjacent cells, effectively “justifying” or aligning the text by filling the cells in the range evenly. This is particularly useful for formatting text data that is initially concentrated in a single cell or a few cells, spreading it out across multiple cells to improve readability or meet specific layout requirements. In xlwings, this method can be accessed through the api property, which provides direct access to the underlying Excel object model.

Functionality:
The Justify method takes the text in the top-left cell of the specified range and distributes it horizontally or vertically into the other cells within that range. It breaks the text into segments based on spaces or line breaks and fills the cells sequentially. If the range includes multiple rows or columns, the text is distributed accordingly. This method is often used for formatting purposes, such as when importing data that arrives in a single cell but needs to be split across several columns for analysis.

Syntax:
In xlwings, the syntax for calling the Justify method is as follows:

range_object.api.Justify()

Here, range_object refers to an xlwings Range object, such as one obtained via sheet.range('A1:C3'). The Justify method does not require any parameters; it operates directly on the range specified by range_object. It is important to note that this method modifies the worksheet in place and does not return a value. If the range contains merged cells or is not contiguous, Excel may raise an error or produce unexpected results.

Example:
Consider a scenario where cell A1 contains the text “Excel Data Analysis with Python,” and you want to distribute this text across cells A1, B1, and C1. Using xlwings, you can achieve this with the following code:

import xlwings as xw

# Open the workbook and select the sheet
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Define the range over which to justify the text
target_range = sheet.range('A1:C1')

# Set the initial text in cell A1
sheet.range('A1').value = "Excel Data Analysis with Python"

# Apply the Justify method to distribute the text
target_range.api.Justify()

# Save and close the workbook
wb.save()
wb.close()