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

2. 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

3. 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

4. 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