How to use Range.FillLeft in the xlwings API way

The FillLeft method in xlwings provides a powerful way to replicate data or formulas from the rightmost cell of a specified range into the cells to its left. This functionality is directly modeled after the Range.FillLeft method from the Excel Object Model. It is particularly useful for quickly populating adjacent cells with consistent data, extending a series, or copying formulas across a row without manually dragging the fill handle. In data preparation and formatting tasks, using FillLeft via xlwings automates these processes, making scripts more efficient and less error-prone compared to manual Excel operations.

Syntax and Parameters

In xlwings, the FillLeft method is called on a Range object. The basic syntax is:

Range.fill_left()

Notably, the xlwings implementation of FillLeft does not accept parameters in the same way the native Excel VBA method does. In VBA, Range.FillLeft has an optional parameter. However, in xlwings, the method is parameterless. The behavior is determined entirely by the range you apply it to. The range defines the destination area to be filled. The rightmost cell of this range acts as the source cell. The method then fills the contents and formatting from this source cell leftwards into the rest of the cells within the specified range.

To control the scope of the fill, you define the target range. For example, if you select a horizontal range of 5 cells (e.g., B1:F1), calling fill_left() will copy the value/formula from cell F1 (the rightmost cell) into cells E1, D1, C1, and B1.

Code Examples

Here are practical examples demonstrating the use of Range.fill_left() in xlwings:

1. Filling Constant Values:
   This example sets a value in the rightmost cell and fills it to the left.

import xlwings as xw

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

# Set a value in cell E1
sht.range('E1').value = 100
# Define the range from B1 to E1 and fill left from E1
sht.range('B1:E1').fill_left()
# Now cells B1, C1, D1 will all contain the value 100

2. Filling Formulas:
   This example shows how to propagate a formula leftwards.

import xlwings as xw

wb = xw.books.active
sht = wb.sheets[0]

# Assume column F has numbers. Put a SUM formula in G1 that sums F1:F10.
sht.range('G1').formula = '=SUM(F1:F10)'
# Fill this formula left into cells D1, E1, and F1.
# The range D1:G1 defines G1 as the source.
sht.range('D1:G1').fill_left()
# Cell D1 will now contain =SUM(A1:A10), E1 will have =SUM(B1:B10), etc.,
# as Excel adjusts the formula references relatively.

3. Using with a Variable Range:
   This example uses a variable to define the range dynamically.

import xlwings as xw

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

# Find the last used column in row 3
last_col = sht.range('A3').end('right').column
# Create a range from column 2 to the last column in row 3
fill_range = sht.range((3, 2), (3, last_col))
# Perform the fill left operation
fill_range.fill_left()
# This will copy the content from the last cell in row 3 to all preceding cells in the defined range.